Here's the New New Index Bloat Query.
Here's what I changed:
- Had it pull btree indexes only, because the calculations don't work for GIN/GiST indexes.
- Moved all of the nested subqueries up into WITH clauses for easier readability and maitainability. Since all supported Postgres versions now do CTEs, I didn't see any reason to work around them.
- Removed the \set statements, because they don't work for embedding the query in a script. And they only need setting dynamically if you've compiled PostgreSQL with nonstandard options, anyway.
- Removed a couple of CASE statements aimed at 7.X support; who cares?
- Added some extra informational columns for using this in interactive mode: table size, pretty index size, and index scans. This helps folks figure out whether to rebuild an index, ignore the bloat or to drop it.
- In the example query, filtering down to indexes with bloat over 50% and 50MB, which is our threshold for "significant bloat"
-[ RECORD 27 ]+-----------------------------
database_name | prod-web
schema_name | partitions
table_name | transactions_201308
index_name | transactions_201308_uuid_idx
bloat_pct | 52.9
bloat_bytes | 351649792
bloat_size | 335 MB
index_bytes | 664788992
index_size | 634 MB
table_bytes | 4570447872
table_size | 4359 MB
index_scans | 263
So this index would be a good candidate for deletion, since it's 50% bloated and seldom scanned.
Now, there's still more to do on this. We need a similar query for GIN/GiST/SPGiST. The query could use some more cleanup; removing one-letter table aliases, and GROUP BY 1,2,3,4 stuff. But it's vastly improved for checking which of your indexes need VACUUM/REINDEX. Thanks, Ioguix!