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

Question

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

Handlebar

Answer 1

Use pg_indexes.

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

Retrospective

Answer 2

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)

Stomatology

Answer 3

This would give you index and table sizes along with estimated rows.

-- Index sizes and table sizes
SELECT 
  i.schemaname schema,
  i.relname table_name,
  indexrelname index_name,
  pg_size_pretty(pg_total_relation_size(relid)) total_size,
  pg_size_pretty(pg_indexes_size(relid)) total_size_of_all_indexes,
  pg_size_pretty(pg_relation_size(relid)) table_size,
  pg_size_pretty(pg_relation_size(indexrelid)) index_size,
  reltuples::bigint estimated_row_count
FROM 
  pg_stat_all_indexes i 
  JOIN pg_class c ON i.relid=c.oid 
WHERE 
  schemaname NOT IN ('pg_catalog','pg_toast','information_schema', 'pglogical')

Horsemint