analyze_index_bloat
Analyze PostgreSQL index bloat to identify fragmented indexes that need REINDEX operations, improve query performance, and reduce storage waste by examining leaf page density and fragmentation percentages.
Instructions
Analyze index bloat using pgstatindex from pgstattuple extension.
Note: This tool analyzes only user/client indexes and excludes PostgreSQL system indexes (pg_catalog, information_schema, pg_toast). This focuses the analysis on your application's custom indexes.
Uses pgstatindex to get B-tree index statistics including:
Leaf page density (avg_leaf_density) - lower values indicate more bloat
Fragmentation percentage
Empty and deleted pages
Helps identify indexes that:
Need REINDEX to improve performance
Have high fragmentation
Are wasting storage space
Requires the pgstattuple extension: CREATE EXTENSION IF NOT EXISTS pgstattuple;
Note: Also supports GIN indexes (pgstatginindex) and Hash indexes (pgstathashindex).
Input Schema
| Name | Required | Description | Default |
|---|---|---|---|
| index_name | No | Name of a specific index to analyze | |
| table_name | No | Analyze all indexes on this table | |
| schema_name | No | Schema name (default: public) | public |
| min_index_size_gb | No | Minimum index size in GB to include (default: 5) | |
| min_bloat_percent | No | Only show indexes with bloat above this percentage (default: 20) |