postgresqlindexingpostgresql-12postgresql-13

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


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


Solution

  • Use pg_indexes.

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