PostgreSQL index size and value number

Question

I am trying to get statistics on indexes. I am looking for total values in an index and it size.

I can only find the size of all indexes on the table. Table pg_class column relpages and reltuples shows the values for the table and not on specific index level.

In addition, function pg_indexes_size takes table name as an argument and return the total index size for that table.

is there a way to get the size and row number on index level? I am using PostgreSQL 9.3.

Thanks in advance for your help

Answer 1

pg_table_size('index_name') for individual index - but it will only show you the size on disk, not the amount of data

count(*) to get the exact current mount of rows

sum(pg_column_size(column_name)) from table_name for estimations on column data amount

you can try yourself smth like:

t=# \di+ tbl*                                     List of relations  Schema |         Name         | Type  |  Owner   |     Table      |  Size  | Description --------+----------------------+-------+----------+----------------+--------+-------------  public | tbl_pkey  | index | postgres | tbl | 156 MB |  public | tbl_unpic | index | postgres | tbl | 46 MB  |  public | tbl_x1    | index | postgres | tbl | 57 MB  | (3 rows)  t=# \dt+ tbl                         List of relations  Schema |      Name      | Type  |  Owner   | Size  | Description --------+----------------+-------+----------+-------+-------------  public | tbl | table | postgres | 78 MB | (1 row)  t=# select pg_size_pretty(pg_total_relation_size('tbl'));  pg_size_pretty ----------------  337 MB (1 row)  t=# select 78+57+46+156;  ?column? ----------       337 (1 row) 

and to check how psql gets the individual index size, run it with psql -E..

and once again - functions above work with size it takes of disk - it may/(may not) be extremely different from real amount of data. vacuuming helps here

update I don't know where you directly get the number of "rows" in index, thus can offer only indirect way. Eg let me have partial index, so "number of rows" is different from table. I can check estimations with EXPLAIN (of course you have to repeat where clause for that) checking the rows=66800 in Index Only Scan using gives me an idea on number of rows in that index (actually it is rows=64910 which you can get by explain analyze or just running count(*)). I can't find relevant info in pg_stats - maybe there's some formula. I don't know.

Answer 2

This sql will give you detailed info for table and index size

SELECT    relname  as table_name,    pg_size_pretty(pg_total_relation_size(relid)) As "Total Size",    pg_size_pretty(pg_indexes_size(relid)) as "Index Size",    pg_size_pretty(pg_relation_size(relid)) as "Actual Size"    FROM pg_catalog.pg_statio_user_tables  ORDER BY pg_total_relation_size(relid) DESC;