Recommend indexes for a workload
pg_index_advisorRecommend PostgreSQL indexes that measurably cut query cost; each candidate is EXPLAINed and validated with HypoPG before being suggested.
Instructions
Recommend indexes for a workload, and prove each one pays for itself before recommending it. Give it statements (the SQL you care about) or let it take the top N from pg_stat_statements; it plans each statement, generates candidate indexes, costs them with HypoPG hypothetical indexes, and returns only the ones that measurably cut estimated cost.
How candidates are generated, and the honest limit: this tool has NO SQL parser and does not read your SQL text. It EXPLAINs each statement and harvests the columns the PLANNER reports as filters, join keys, and sort keys, then intersects those tokens with the real column list from pg_attribute -- so a candidate can never name a column that does not exist. The extraction is deliberately loose (a token matching a real column name on a different table can slip through); HypoPG is the arbiter, and anything that does not lower cost is discarded. Column ORDER within each candidate is equality columns first (most selective first, from pg_stats), then at most one range column, then sort columns.
The search is greedy and BOUNDED. Each accepted index stays in place while the rest are re-costed on top of it, so later picks account for what earlier ones already fixed. max_candidates caps how many candidates are considered and max_explains caps total EXPLAIN round trips; when a cap stops the search early, budget_exhausted is true and the result is a truncated search, not a converged one.
PostgreSQL 18 note, and it reverses a rule you have probably internalized: PG18 added B-tree SKIP SCAN, so a multi-column index whose LEADING column the query never constrains CAN now be used. The classic 'leading column never filtered means the index is useless' heuristic is wrong on PG18+. This tool gates that prune on the server version -- on PG18+ such candidates are kept and costed (skip_scan_available: true, and an accepted one carries requires_skip_scan), below PG18 they are pruned as unusable and counted in candidates_pruned_leading_column.
Requires the HypoPG extension (CREATE EXTENSION hypopg;). Hypothetical indexes are session-scoped and are reset before the call returns, on the success and the failure path alike, so they never touch disk and never leak into a later query plan. Statements are only ever EXPLAINed, never executed, inside a BEGIN READ ONLY transaction.
Costs are PLANNER ESTIMATES, not measurements: they are the right way to compare two plans for the same statement and the wrong way to predict wall-clock time. They are weighted by calls when the workload came from pg_stat_statements, so a query run a million times outranks an identical one run twice. Validate a recommendation with pg_explain before creating it, and create it with CONCURRENTLY in production (create_statement_concurrently).
Input Schema
| Name | Required | Description | Default |
|---|---|---|---|
| limit | No | How many statements to pull from pg_stat_statements. Ignored when `statements` is given. | |
| schema | No | Only recommend indexes on tables in this schema. Candidates elsewhere are dropped. | |
| statements | No | The workload to optimize. When omitted, the top `limit` statements from pg_stat_statements are used instead (and weighted by their call counts). | |
| max_explains | No | Cap on EXPLAIN round trips spent searching (baseline plans are not counted). The search stops when the next candidate would exceed it and reports `budget_exhausted: true`. | |
| max_candidates | No | Cap on candidate indexes considered. Candidates are ranked by table sequential-scan count first. | |
| min_improvement | No | Fraction of total weighted workload cost an index must remove to be accepted (0.1 = 10%). Relative rather than absolute so it means the same thing on a small and a large database. | |
| max_index_columns | No | Widest candidate index to consider. Every narrower prefix is considered too. | |
| max_recommendations | No | Stop after this many accepted indexes, even if more would still help. |
Output Schema
| Name | Required | Description | Default |
|---|---|---|---|
| _warnings | No | ||
| statements | Yes | The workload as analyzed, in the order `helps_statements.statement` indexes into. | |
| explains_used | Yes | EXPLAIN round trips spent searching, excluding baseline plans. | |
| explain_budget | Yes | ||
| recommendations | Yes | ||
| budget_exhausted | Yes | True when `max_explains` stopped the search before it converged. | |
| final_workload_cost | Yes | Weighted total after applying every recommendation. | |
| skip_scan_available | Yes | True on PostgreSQL 18+, where B-tree skip scan exists. | |
| candidates_considered | Yes | ||
| baseline_workload_cost | Yes | Weighted total estimated cost before any recommendation. | |
| candidates_pruned_leading_column | Yes | Multi-column candidates dropped by the pre-PG18 leading-column rule. Always 0 on PG18+. |