SQL & databasesSQL

Postgres – Index Bloat Report (SQL)

Inspect PostgreSQL index bloat.

SQL
-- Requires pgstattuple extension installed
SELECT schemaname, relname AS table, indexrelname AS index, 
       bs*(sub.relpages)::bigint AS index_size
FROM pg_stat_all_indexes i
JOIN pg_class c ON c.relname = i.indexrelname
JOIN pg_class sub ON sub.relname = i.relname
JOIN (SELECT current_setting('block_size')::int AS bs) bs ON true
ORDER BY index_size DESC
LIMIT 50;
  

From Joseph Nakhle's automation workbench.

More SQL & databases scripts