PostgreSQL index size and value number

user_8579285 0
Viewed 47376

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

postgresql-9.3 indexing postgresql
3 Answers

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.

user_53159740

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;
user_37484740

This is the query to retrieve info about:

  • Total Size

  • Total Size of all Indexes

  • Table Size

  • Index Size

  • Estimated table row count

     SELECT i.relname "Table Name",indexrelname "Index Name",
 pg_size_pretty(pg_total_relation_size(relid)) As "Total Size",
 pg_size_pretty(pg_indexes_size(relid)) as "Total Size of all Indexes",
 pg_size_pretty(pg_relation_size(relid)) as "Table Size",
 pg_size_pretty(pg_relation_size(indexrelid)) "Index Size",
 reltuples::bigint "Estimated table row count"
 FROM pg_stat_all_indexes i JOIN pg_class c ON i.relid=c.oid 
 WHERE i.relname='uploads'

Maybe for someone it will be useful.

user_46487810
Related