Introduction

PostgreSQL’s query planner evaluates dozens of execution strategies for every query, estimates their costs using table statistics, and picks the cheapest one — all in milliseconds. The problem is that this process is almost entirely invisible.

Explain EXPLAIN to me

Standard EXPLAIN output shows you the plan PostgreSQL chose, but not the plans it rejected. It shows a total cost number without saying what drives it, and reports a sort without saying whether the sort was avoidable. It says nothing about indexes that exist but couldn’t be used, or indexes that don’t exist but would help.

The result: EXPLAIN tells you what happened, but not what to change or whether a change would help.

The usual alternative is to guess at indexes, or wait until queries are slow enough to become incidents and troubleshoot under pressure.

What Query Doctor does

Query Doctor connects to your PostgreSQL database, reads every query tracked by pg_stat_statements, and runs each one through an analysis pipeline that surfaces information PostgreSQL knows internally but doesn’t normally expose.

Augmented explain plans

Query Doctor extends standard EXPLAIN output with fields that help you understand why a plan looks the way it does:

• Cost breakdowns — what contributes to a node’s cost (I/O, CPU, filtering, startup)
• Alternative plans — execution strategies the planner considered and rejected, with the reason
• Ineligible indexes — indexes that exist on the table but couldn’t serve the query, and why not
• Order analysis — whether a sort node receives data in the order it needs, or has to re-sort

These fields are marked with an asterisk (*) in the UI and aren’t part of standard PostgreSQL EXPLAIN output. They’re computed by the analyzer, an open-source container that runs on your machine.

Optimization without production access

Beyond explaining what’s happening today, Query Doctor can tell you what would happen if you changed the schema. It imports your database’s statistics — row counts, value distributions, correlations — and uses PostgreSQL’s own cost model to evaluate hypothetical indexes. The improvement percentages you see come from the planner itself, comparing two real plans under the same statistics.

This works with any database you can connect to — it doesn’t need to be production. The analyzer reads schema metadata and planner statistics, not your actual data. If your staging or development database has representative statistics (i.e. ANALYZE has been run), Query Doctor’s recommendations will be as accurate as if it were pointed at production.

When an improvement is found, Query Doctor provides the exact CREATE INDEX statement. When no improvement is possible, it says so, and you can stop looking.

Query analysis

Not every performance issue requires an index change. Query Doctor parses your SQL and flags common anti-patterns — functions on indexed columns, leading wildcard searches, NULL comparisons with equality operators, unnecessary DISTINCT, missing join conditions, and more. Each nudge points to the exact location in the query and explains both the problem and the fix.

Source-to-query tracing

With SQLCommenter integration, Query Doctor can trace each query back to the file, route, or function that produced it — turning a parameterized SQL string into a clickable link to your source code.

CI integration

The analyzer also runs as a GitHub Action, so you can catch query regressions in pull requests before they reach production.

What it doesn’t do

Query Doctor is an analysis and recommendation tool. It does not:

• Require production access (any database with representative statistics works — staging, development, or a read replica)
• Execute your queries (it costs plans on a copy of your schema; your queries never run against your database)
• Create or drop indexes automatically (you apply recommendations yourself, after review)

The analyzer does write to your database when you install pg_stat_statements or reset statistics, and it uploads your schema, query text and table statistics to Query Doctor. The analyzer reference has the detail.