PostgreSQL index size and value number

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.

Leave a Comment

Hata!: SQLSTATE[HY000] [1045] Access denied for user 'divattrend_liink'@'localhost' (using password: YES)