Postgres – Index Bloat Report (SQL)

Below is a ready-to-use script. Review and adapt variables to your environment.

-- 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;
  

← Back to SQL