How to list all indexes of a table with their corresponding size in PostgreSQL?

user_10668436 0
Viewed 1634

I can view the total size of all indexes in a table with

SELECT pg_size_pretty (pg_indexes_size('table_name'));

and the size of a specific index with:

select pg_size_pretty(pg_relation_size('index_name'));,

but I would like to retrieve a list with size information for each index of the table separately (a list of index sizes with the corresponding index name they belong to).

postgresql-13 postgresql-12 indexing postgresql
2 Answers

Use pg_indexes.

select indexname, pg_size_pretty(pg_relation_size(indexname::regclass)) as size
from pg_indexes
where tablename = 'my_table';
user_19957380

You can use \di+ psql command:

postgres=> \di+ schema.*
                           List of relations
 Schema |  Name  | Type  | Owner | Table  | Persistence |  Size  | Description
--------+--------+-------+-------+----------------------+--------+-------------
 schema | index1 | index | owner | table1 | permanent   | 139 MB |
 schema | index2 | index | owner | table1 | permanent   | 77 MB  |
 schema | index3 | index | owner | table1 | permanent   | 73 MB  |
 schema | index4 | index | owner | table1 | permanent   | 38 MB  |
(4 rows)
user_23971030
Related