Postgres query to find bloated indexes

The pgstattuple extension gives you real numbers, but it takes a full index lock and I cannot run it on the busy shards during business hours. For a first-pass triage, this size-ratio estimate is usually good enough to decide what to REINDEX CONCURRENTLY on Friday night.

It compares the actual size of each index against a cheap estimate of the minimum size given the current row count and average key width. Anything above roughly 2x is suspicious. Anything above 4x is usually the one causing the query planner to get weird.

SELECT
  schemaname,
  relname   AS table,
  indexrelname AS index,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size,
  round(100 * (pg_relation_size(indexrelid)::numeric
       / NULLIF(pg_relation_size(indrelid), 0)), 1) AS pct_of_table,
  idx_scan
FROM pg_stat_user_indexes
JOIN pg_index USING (indexrelid)
WHERE pg_relation_size(indexrelid) > 50 * 1024 * 1024
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 25;

Caveats: this only flags “big” indexes, not “bloated” ones per se, so pair it with idx_scan = 0 to catch indexes you never use. For real bloat numbers, the pgstattuple_approx() function on Postgres 14+ is fast and does not take a heavy lock.