How Optimization Works

Most query optimization tools rely on rules and heuristics — pattern matching against known anti-patterns. Query Doctor does that too (Findings read from the SQL text), but the core of its optimization engine asks PostgreSQL itself.

Production statistics

The Collector exports the contents of PostgreSQL’s internal statistics catalogs from your database, pg_statistic and the relation metadata from pg_class. These are the same statistics that PostgreSQL’s own query planner uses to make decisions:

• Row counts and page counts for every table and index
• Column-level statistics — null fractions, distinct value counts, average widths, most common values, histograms, and correlation data
• Index metadata — which indexes exist, their sizes, and visibility information

Query Doctor loads your schema into a PostgreSQL of its own and injects those statistics into its catalog. That PostgreSQL runs on the Query Doctor instance, not on your machine, and it holds no rows of yours. Its planner then works with the same data distribution knowledge as your production database: when it estimates that a sequential scan will read 50,000 pages or that an index scan will return 200 rows, those estimates come from your real data distribution.

What-if analysis with real cost models

Once Query Doctor has your production statistics, it runs what-if analysis for each query:

1. Baseline plan — EXPLAIN runs against your query unchanged, on the injected statistics. Its cost is what your production system would plan today.

2. Candidate indexes — the query’s WHERE clauses and join conditions are read for columns an index could filter on, and candidates are generated across them, including multi-column combinations. A column that is only selected, sorted or grouped gets no candidate, because an index on it cannot serve those positions.

3. Optimized plan — for each candidate index set, the indexes are created inside a transaction, EXPLAIN runs again, and the new cost is captured. The transaction is then rolled back, so neither the indexes nor the injected statistics survive it.

4. Comparison — the baseline and optimized costs are compared directly. If the optimized cost is at least 5% lower, the query is marked as having improvements available, and the specific CREATE INDEX statements are surfaced as recommendations.

None of this is heuristic. The improvement percentage you see comes from PostgreSQL’s own planner evaluating two real plans with real statistics. It’s the same calculation PostgreSQL would make if those indexes actually existed on your production system.

Why the numbers are comparable to production

Traditional query advisors estimate improvements using rules like “adding an index on a filtered column should help.” That’s directionally true, but it can’t tell you how much it helps — or whether it helps at all for your specific data distribution.

Two of the three things that produce a cost estimate are the same on both sides:

• Same statistics — the planner’s row estimates, selectivity calculations, and join cardinality estimates all derive from pg_statistic. Injecting your production statistics is what makes those estimates match the ones your production planner would produce.
• Same planner logic — Query Doctor doesn’t reimplement PostgreSQL’s planning. It runs PostgreSQL’s own planner, on the same major version your database runs, so it accounts for the same edge cases, cost model nuances, and optimization strategies.

The third is the cost parameters that weight one operation against another (seq_page_cost, random_page_cost, cpu_tuple_cost, and the rest). Those are not carried over, and the limitation below says what that costs you.

What the percentage means

When Query Doctor reports a cost reduction, it means:

PostgreSQL’s planner estimates that the query’s total cost would drop by that much if the recommended indexes were created, given the current data distribution in your database.

It’s a direct comparison of two plans produced by the same planner with the same statistics, and the only difference between them is the available indexes.

When the cheapest improvement is a rewrite of the query rather than a set of indexes, the two plans are the query as written and the rewritten query, under the same statistics and the same indexes.

The index search has a time limit for each query. A rewrite’s cost doesn’t depend on that search, so in a CI run and in optimize_query, when it runs past the limit, the rewrites are still costed. A rewrite that clears the threshold is then reported with a note that no index was costed. It doesn’t mean no index would help.

A rewritten query can need indexes of its own. When the index search finishes, each rewrite gets a search over its own SQL, and the indexes that make it cheaper are listed with the rewrite, never with the query’s indexes, which are for the SQL as written. An index is kept only if the rewrite costs at least 5% less with it than without it. When the rewrite is cheaper only with its indexes, for example a HOIST_CORRELATED_EXISTS rewrite whose grouped subquery needs an index on the columns it filters, the indexes come first: create them, then ship the rewrite.

By default, a new query whose improvement cuts its cost by at least half, saving more than 100 cost units, fails the CI check. A smaller improvement shows on the run page and doesn’t block. Queries where no index combination, and no rewrite that was costed, produces at least a 5% improvement report no improvement found.

Limitations

Statistics can be stale

The accuracy of the analysis depends on the accuracy of your database’s statistics. PostgreSQL collects statistics via ANALYZE (or autovacuum’s auto-analyze), and these are sample-based snapshots. If your data distribution has changed significantly since the last ANALYZE, the statistics — and therefore the optimizer’s estimates — may not reflect reality.

Signs of stale statistics:

• Tables that have had large bulk inserts or deletes since the last ANALYZE
• Columns with highly skewed distributions where the sample didn’t capture the skew
• Tables where autovacuum is infrequent or disabled

You can manually refresh statistics on specific tables:

ANALYZE my_table;

Write overhead isn’t measured

Query Doctor evaluates the read performance impact of adding indexes. It doesn’t account for the write overhead: every INSERT, UPDATE, and DELETE must also update each index on the affected table. For write-heavy tables, adding indexes has a real cost that isn’t reflected in the optimization percentage.

Indexes are found on the generic plan

Query Doctor searches for indexes in GENERIC_PLAN mode, which plans a query without its parameter values. After five executions of a prepared statement, PostgreSQL switches to that plan when it costs no more than the custom plans did. A driver that sends unnamed statements gets a plan for each call’s own values instead, and an index the generic plan uses can go unused by every one of them. A filter on a column where one value covers most rows is the usual case: the generic plan reads status = $1 as one value among many, while every real call asks for the common one.

So, for a query with parameters, each recommended index is then checked against plans with the parameters bound. The values come from your column statistics, in two sets: the value a typical row holds, and each column’s most common value. A range comparison such as created_at > $1 is bound near the end of the histogram it opens toward, so it matches a small slice of rows, as most range calls do. An index is recommended only if one of those plans costs at least 5% more without it. The result is in the optimization’s boundValues: the indexes it dropped, those that help only the most common values, and both plans’ costs.

The check needs a value for every parameter. When a parameter is compared with an expression or a column the statistics don’t hold, it is skipped, and the indexes stand on the generic plan alone. The check also can’t add an index that only a bound value would use, such as a partial index whose predicate names a parameter, and it doesn’t yet cover the indexes a rewrite’s own search recommends.

Tuned cost parameters aren’t carried over

The planner Query Doctor costs on runs PostgreSQL’s default cost parameters. Your production statistics are carried over; seq_page_cost, random_page_cost, effective_cache_size and the rest are not. Lowering random_page_cost on SSD-backed storage is the common one. If you have tuned any of them, your production planner weights index access differently from the one producing these numbers, and the two costs are no longer directly comparable.

Statistics target limits

PostgreSQL’s default statistics target is 100 (the number of histogram buckets and most-common-values entries). For columns with very high cardinality or unusual distributions, this may not capture enough detail for precise estimates. You can increase the target per column:

ALTER TABLE my_table ALTER COLUMN my_column SET STATISTICS 500;
ANALYZE my_table;

This gives the planner more detailed distribution data to work with.