How to see the disk space that the index occupies in the database?

Question

How to see the disk space that the index occupies in the database?

Navigation

#1 by (0 votes)

1

I have the table maintenance with the column product_id (varchar (MAX)) , but it is not the primary key, the table has 900 thousand records so I decided to create indexes to optimize searches.

I've done the following tests:

create index manutencao_id_produto_idx on manutencao using hash(id_produto);
drop index manutencao_id_produto_idx

CREATE INDEX manutencao_id_produto_idx on manutencao (DECODE(MD5(id_produto), 'HEX'));
drop index manutencao_id_produto_idx

create index manutencao_id_produto_idx on manutencao (id_produto);
drop index manutencao_id_produto_idx

The performance was similar in all of them, I want to know how much disk space each occupies, I am accessing the database with datastudio , if you have a query to get this value even better.

sql postgresql

asked by anonymous 04.11.2015 / 18:28

1 answer

redirect with htacess With this rule, how do I view the point closest to my location [closed]

score 0 · Accepted Answer

I found something:

SELECT c2.relname, c2.relpages
FROM pg_class c, pg_class c2, pg_index i
WHERE c.relname = 'manutencao' AND
      c.oid = i.indrelid AND
      c2.oid = i.indexrelid AND
      c2.relname = 'manutencao_id_produto_idx';

According to this page link , each c2.relpages is about 8 kilobytes , then:

SELECT c2.relname, (c2.relpages * 8)||' kilobytes'
FROM pg_class c, pg_class c2, pg_index i
WHERE c.relname = 'manutencao' AND
      c.oid = i.indrelid AND
      c2.oid = i.indexrelid AND
      c2.relname = 'manutencao_id_produto_idx';