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

Question

I have the table maintenance with the column id_product (varchar(MAX)), however the same is not the primary key, the table has 900 thousand records so I decided to create indices to optimize searches.

I’ve run 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 one occupies, I am accessing the bank with the datastudio, if you have a query to get this value even better.

Answer 1

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 http://www.postgresql.org/docs/9.0/static/disk-usage.html, each C2.relpages has 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';