find_unused_indexes
Identify unused, duplicate, and overlapping PostgreSQL indexes to reduce storage space and improve database performance by removing unnecessary indexes.
Instructions
Find indexes that are not being used or are duplicates.
Identifies:
Indexes with zero or very few scans since last stats reset
Duplicate indexes (same columns in same order)
Overlapping indexes (one index is a prefix of another)
Removing unused indexes can:
Reduce storage space
Speed up INSERT/UPDATE/DELETE operations
Reduce vacuum and maintenance overhead
Input Schema
| Name | Required | Description | Default |
|---|---|---|---|
| schema_name | No | Schema to analyze (default: public) | public |
| min_size_mb | No | Minimum index size in MB to include | |
| max_scan_ratio | No | Maximum scan ratio (scans/rows) to consider an index unused | |
| include_duplicates | No | Include analysis of duplicate/overlapping indexes |
Input Schema (JSON Schema)
{
"properties": {
"include_duplicates": {
"default": true,
"description": "Include analysis of duplicate/overlapping indexes",
"type": "boolean"
},
"max_scan_ratio": {
"default": 0.01,
"description": "Maximum scan ratio (scans/rows) to consider an index unused",
"type": "number"
},
"min_size_mb": {
"default": 0,
"description": "Minimum index size in MB to include",
"type": "number"
},
"schema_name": {
"default": "public",
"description": "Schema to analyze (default: public)",
"type": "string"
}
},
"required": [],
"type": "object"
}