Gain valuable insights into PostgreSQL index size, parent table size, and usage details with a comprehensive query. This query provides a wealth of information, including the table name, index name, whether the index is a primary key or unique, the index’s readiness status, the number of rows in the table, and various statistics on index scans and entries. Execute the following query to retrieve these details
select cl2.relname as table_name, cl1.relname as index_name, i.indisprimary as is_primary_key,
i.indisunique as is_unique_index,
i.indisready as index_is_ready, cl1.reltuples as number_of_rows,
stat.idx_scan as number_of_scans, stat.idx_tup_read as index_entries_returned,
stat.idx_tup_fetch number_of_table_rows_fetched,
pg_size_pretty(pg_relation_size(quote_ident(cl2.relname)::text)) AS table_size,
pg_size_pretty(pg_relation_size(quote_ident(cl1.relname)::text)) AS index_size
from pg_index i inner join pg_class cl1
on cl1.oid=i.indexrelid and cl1.relkind = 'i'
inner join pg_class as cl2
on cl2.oid = i.indrelid
inner join pg_stat_all_indexes stat
on stat.indexrelid = cl1.oid
inner join pg_tables tab
on tab.tablename = cl2.relname and tab.schemaname = 'public'
order by table_name ;
This query offers a detailed overview of PostgreSQL index and table-related metrics, providing a valuable resource for database administrators and analysts. For additional insights into estimating the approximate number of rows in each table, refer to https://heyitissimple.com/fast-method-to-find-postgresql-tables-approximate-row-count/