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.

pg_stat_all_indexes shows only statistics about read/fetch/scan and not the actual size and number of values in the index — oved_s
– oved_s, Commented Sep 28, 2017 at 13:23
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? :) — Vao Tsun
– Vao Tsun, Commented Sep 28, 2017 at 13:38
pg_indexes_size shows the total index size of a table. I want the specific index size. — oved_s
– oved_s, Commented Sep 28, 2017 at 15:28
use pg_table_size('index_name') for individual index - but it will only show you the size on disk, not the amount of data — Vao Tsun
– Vao Tsun, Commented Sep 28, 2017 at 15:41

Elvin Ahmadov · Accepted Answer · 2022-04-14 12:27:17Z

104

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.

answered Apr 14, 2022 at 12:27

Elvin Ahmadov

1,2061 gold badge10 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Pencilcheck Over a year ago

Probably the best answer I have seen, awesome

sequielo · Accepted Answer · 2021-12-14 05:52:59Z

68

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;

edited Dec 14, 2021 at 5:52

sequielo

1,6411 gold badge20 silver badges27 bronze badges

answered May 27, 2021 at 18:21

akshay jain

6995 silver badges2 bronze badges

1 Comment

Samuli Asmala Over a year ago

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 stackoverflow.com/a/70397779/1317406 for more information.

Alexis Wilke · Accepted Answer · 2022-11-22 01:00:23Z

62

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.

edited Nov 22, 2022 at 1:00

Alexis Wilke

21.2k11 gold badges111 silver badges183 bronze badges

answered Sep 28, 2017 at 15:43

Vao Tsun

52.5k13 gold badges114 silver badges149 bronze badges

3 Comments

oved_s Over a year ago

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

Vao Tsun Over a year ago

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

oved_s Over a year ago

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

paperskilltrees · Accepted Answer · 2024-07-22 23:35:01Z

3

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

answered Jul 22, 2024 at 23:35

paperskilltrees

3815 silver badges16 bronze badges

Collectives™ on Stack Overflow

PostgreSQL index size and value number

4 Answers 4

1 Comment

1 Comment

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related