CODEWITHSUNDEEP
postgresqlindexingpostgresql-9.3
oved_s
oved_s

Reputation: 773

PostgreSQL index size and value number

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.

Upvotes: 76

Views: 111526

Answers (4)

paperskilltrees
paperskilltrees

Reputation: 382

Like Elvin Ahmadov's accepted answer but with index definition commands (with help of this answer).

SELECT
    PG_SIZE_PRETTY(PG_INDEXES_SIZE(relid)) AS "Idxs Size",
    PG_SIZE_PRETTY(PG_RELATION_SIZE(relid)) AS "Tbl Size",
    PG_SIZE_PRETTY(PG_TOTAL_RELATION_SIZE(relid)) AS "Total",
    i.relname "Tbl Name", indexrelname "Idx Name",
    PG_SIZE_PRETTY(PG_RELATION_SIZE(indexrelid)) "Idx Size",
    reltuples::BIGINT "ca. rows", ii.indexdef
FROM pg_stat_all_indexes i JOIN pg_class c ON i.relid = c.oid
JOIN pg_indexes ii ON i.indexrelname = ii.indexname
WHERE i.schemaname NOT LIKE 'pg_%'  -- exclude system tables
ORDER BY PG_INDEXES_SIZE(relid) DESC, PG_RELATION_SIZE(indexrelid) DESC

Upvotes: 3

Vao Tsun
Vao Tsun

Reputation: 51609

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.

Upvotes: 60

Elvin Ahmadov
Elvin Ahmadov

Reputation: 1060

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.

Upvotes: 88

akshay jain
akshay jain

Reputation: 669

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;

Upvotes: 65

Related Questions