Recommend index drops
recommend_index_dropsIdentifies large, unused or rarely used indexes and generates DROP INDEX CONCURRENTLY statements to reduce storage and write overhead.
Instructions
Sibling of recommend_indexes for indexes to remove. Walks pg_stat_user_indexes + pg_stat_user_tables for existing indexes that look like pure cost — large on disk but never (or barely) scanned. Three reason codes, descending strength: never_used (no recorded idx_scans since the last stats reset — candidate for drop, but verify before removal), scan_no_fetch (planner picks it but it returns no rows — usually existence-check pattern), rarely_used (scan rate below low_scan_ratio of the table's total scan activity). Primary-key / unique / exclusion-constraint indexes are excluded (dropping those would be a schema change, not a performance win); indexes below min_index_size_bytes are skipped too. Returns a ready-to-run DROP INDEX CONCURRENTLY statement per candidate. Read-only advisor — execution is on the operator.
Example: recommend_index_drops(schema='public', min_index_size_bytes=1000000, low_scan_ratio=0.01)
Input Schema
| Name | Required | Description | Default |
|---|---|---|---|
| schema | No | ||
| database | No | Optional: target a configured secondary (read-only) database by name; omit for the primary. Call list_databases to see the configured ids. | |
| low_scan_ratio | No | ||
| min_index_size_bytes | No |
Output Schema
| Name | Required | Description | Default |
|---|---|---|---|
| result | Yes |