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 returns 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.

Pucker

pg_stat_all_indexes?.. – Ashkhabad

pg_stat_all_indexes shows only statistics about read/fetch/scan and not the actual size and number of values in the index – Pucker

yes, and pg_indexes_size that you mention shows the size indexes take on disk, not the "actual amount of data" - I thought you are ok with some aproximation? :) – Ashkhabad

pg_indexes_size shows the total index size of a table. I want the specific index size. – Pucker

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

lots of example here : wiki.postgresql.org/wiki/Index_Maintenance – Hassle

Answer 1

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.

Hoyle

Probably the best answer I have seen, awesome – Seismography

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;

Dionysian

Note that "Actual Size" above means "Table data without TOAST used for long columns". Instead of that you might want to use pg_size_pretty(pg_table_size(relid)) as "Data Size", which would give you Data Size + Index Size = Total Size. See https://mcmap.net/q/100600/-what-39-s-the-difference-between-pg_table_size-pg_relation_size-amp-pg_total_relation_size-postgresql for more information. – Ordovician

Answer 3

pg_table_size('index_name') for individual index - but it only shows you the size on disk, not the number of entries.

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

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

You can try something 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 on 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 an index, thus I can only offer indirect way. Eg let me have a partial index, so the "number of rows" in the index is different from the number of rows in the table. I can check estimations with EXPLAIN (of course you have to repeat the WHERE clause for that) checking the rows=66800 in Index Only Scan using gives me an idea on the 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.

Ashkhabad

Vao Tsun, Thanks a lot for your answer but maybe I do not fully understand Pg index maintenance. I run the following: create table stg.test_index_size as select t.id, case when t.id<25000 then 'small' else 'high' end as flag from generate_series(1,50000) as t(id); create unique index idx_id on stg.test_index_size(id); create index idx_flag on stg.test_index_size(flag); vacuum analyze stg.test_index_size; Now when I checking the size of the the two indexes with pg_table_size(quote_ident(indexrelname)::text) , I am getting the same result. I expected the flag index will be smaller – Pucker

fair point regarding stat table - editted answer - thank you. regarding the other - better ask a new question I suppose – Ashkhabad

Also pg_stat_all_indexes does not show estimate row number of the index but rather only idx_scan,idx_tup_read and idx_tup_fetch. I expect to get idx_id 50000 and idx_flag =2 – Pucker