pgqe Postgres Query Explainer v0.6.0

EXPLAIN tells you what PostgreSQL did. It does not tell you which part was expensive, why the planner chose it, or what to change. pgqe answers those three questions.

The shortest useful thing

psql -c 'explain (analyze, buffers) select …' | pgqe

No flags on the psql side and no temp file: pgqe reads standard input when you give it no filename, and strips psql's box borders, the QUERY PLAN header and the trailing (N rows) itself. Keep the plan around if you want to re-run against it with more context later:

psql -c 'explain (analyze, buffers) select …' > plan.txt
pgqe plan.txt

Either way you get the plan tree with each node's real cost attributed to it, a table you can sort by whatever is hurting, and a list of findings — named problems with the evidence behind them.

The same goes for the schema: a pg_dump file arrives with psql meta-commands around it (\restrict/\unrestrict, which every dump has carried since PostgreSQL 16.10/17.6/18), and pgqe strips them rather than choking. Paste the file as it came.

pgqe reads whatever you hand it. FORMAT TEXT, JSON, YAML, XML; with or without ANALYZE, BUFFERS, VERBOSE, SETTINGS, WAL; with COSTS OFF or TIMING OFF. It also copes with how plans actually arrive — psql's box borders, a QUERY PLAN header, a trailing (N rows), a forwarded email's > prefixes, a log line's LOG: duration: … plan: wrapper. Strip nothing; pipe or paste it.

The two numbers that matter

Exclusive time

A plan node's reported time includes everything its children did, and it is per loop. Neither is what you want. A node that reports 4 seconds may have spent 3.9 of them waiting on a child; a node that reports 0.02ms may have run 200,000 times.

pgqe computes what each node is actually responsible for:

exclusive = (this node's time × its loop count) − (the same, summed over its children)

Sort by that and the top row is the thing to fix. This is nearly always a different node than the one that looks worst in raw EXPLAIN output.

Under parallel query, this subtraction is approximate. Below a Gather, child loop counts include per-worker executions and child times are per-worker averages, so the arithmetic can go negative. pgqe clamps to zero and marks the node as clamped rather than presenting an unreliable number as exact. Treat a clamped node's attribution as a hint, not a measurement.

Row misestimation

The planner picks join strategies from its row estimates. When it expects 1 row and gets 400,000, it will have chosen a nested loop — and the nested loop is not the bug, the estimate is. pgqe reports each node's actual-versus-estimated rows as a signed factor.

Fix the worst misestimate first. Everything above it in the tree was planned on a false premise, so correcting it re-plans the whole subtree — often making several other findings disappear at once.

Common causes, in the order they're worth checking: stale statistics (analyze), correlated columns the planner assumes are independent (create statistics), a predicate the planner can't see through (a function call, a parameter it has no value for), and a default statistics target too low for a skewed column.

Give it more and it says more

Findings are only as specific as the context you supply. Three levels:

Plan only

pgqe plan.txt

Structural problems that are visible from the plan alone: a scan that discards most of what it reads, a sort spilling to disk, a hash join batching, an index-only scan doing heap fetches, partition pruning that didn't happen, workers planned but never launched, planning time exceeding execution time.

Plus the query

pgqe plan.txt --query slow.sql

(The examples from here on name a file because they add flags, but only the plan comes from standard input — psql -c '…' | pgqe --query slow.sql works the same.)

pgqe parses the SQL with PostgreSQL's own parser and maps each node's conditions back to the clause they came from. That turns "this index wasn't used" into the reason:

• a predicate wrapped in a function — where lower(email) = $1 against a plain email index
• an implicit cast that makes the index type mismatch
• a leading-wildcard like '%foo'
• not in (subquery) where not exists would give an anti-join
• deep offset pagination that reads and discards everything before it
• select * blocking an index-only scan that would otherwise be available

Findings at this level tell you what to rewrite, and to what.

Plus the schema

pgqe plan.txt --query slow.sql --schema ./schema/     # a pgsm or pgstd tree
pgqe plan.txt --query slow.sql --schema schema.sql    # pg_dump -s output
pgqe plan.txt --query slow.sql --schema <git-url> --schema-ref v2.3.0

This is where advice stops being generic — and you do not need a database for the most valuable part of it.

You do not need your whole schema either. Analyze the plan on its own first and pgqe prints the pg_dump line that extracts exactly the tables it touched:

for certain findings, give pgqe this plan's schema — this dumps only the tables it touched:

    pg_dump -s -O -x -t 'orders' -t 'customers' "$DATABASE" > schema.sql

That is usually a few dozen lines rather than your entire database, which matters if you are asking about one query. It comes with its own caveats printed underneath — chiefly that a plain FORMAT TEXT plan carries no schema names, so the patterns match those table names in any schema; EXPLAIN (VERBOSE) or FORMAT JSON qualifies them properly.

Index existence is structural. A schema repository or a dump declares every index, so pgqe can prove that none covers your predicate. The finding stops being a suggestion:

[CRITICAL] No index on public.orders covers (status, total) — the scan
           discards 99% of what it reads   (certain)
  fix: create index concurrently on public.orders ("status", "total");

The column order is the advice, not a detail: a b-tree seeks on a prefix of equality matches and can range-scan on only one further column, so equality columns lead and ranges come last. An index in the wrong order is often no better than none.

From a schema source alone you also get redundant indexes (one whose leading columns another already covers, excluding partial and unique ones, which earn their keep), foreign keys with no index on the referencing side — the classic cause of slow cascades and slow joins — tables with no way to address a row, and a jsonb path filtered on every row with no expression index for it.

One caveat pgqe states in the finding itself: a tree or dump proves what is declared, not what is deployed. If your database has drifted from the repository, pgsm verify is what proves otherwise.

Plus a snapshot

A dump proves what exists. Only a live read knows how big the table is, which indexes anything actually uses, and when it was last analyzed — and six more rules need exactly that.

pgqe snapshot --dsn "$DATABASE" --relation orders > snap.json
pgqe plan.txt --query slow.sql --schema snap.json

Use --relation — without it the collector takes every table you can read, and you only wanted one query's worth. The pg_dump hint above names the tables to ask for.

If you would rather read the query than hand a binary a connection string, it prints it:

pgqe snapshot --sql --relation orders > collect.sql   # read this
psql -tAqf collect.sql "$DATABASE" > snap.json        # then run it

Both run the identical query, and it is read-only, needs no superuser and no extensions, and reports only tables you could already select from. Paste the result into --schema — or into the box at pgqe.osss.net, which takes a snapshot but will never take a connection string.

What the statistics unlock:

• how much a proposed index will help, as selectivity on the same finding
• unused indexes — not scanned once in the reported window, with the window stated, because zero scans over an afternoon means nothing
• stale statistics, usually the direct answer to a bad row estimate
• bloat, with the reminder to check what is holding the vacuum horizon before blaming autovacuum
• scattered heap fetches, where an index's leading column has no relation to the table's physical order
• data-model observations: a wide text column expressing a handful of values across millions of rows, or one column that is most of every row a sequential scan reads

Findings from statistics say likely rather than certain where the number behind them is a sample rather than a count — n_distinct and correlation come from ANALYZE's sample, idx_scan is counted. That distinction is the whole point of the confidence field.

Reading a finding

Every finding carries four things:

• Severity — how much this is costing you.
• Confidence — Certain, Likely, or Speculative. A missing-index finding from a plan alone is speculative; the same finding with a snapshot proving no covering index exists is certain. pgqe will not present a guess as a fact.
• Evidence — the specific numbers that fired the rule, so you can check the reasoning rather than trust it.
• Remedy — what to do, and the SQL to do it where SQL is the answer.

Filter with --min-severity, and get the full reasoning for any rule with pgqe explain <rule-id>.

Sharing a plan safely

Plans contain your data. Index Cond: (email = 'someone@example.com') is a production value, and it travels with the plan into whatever chat window you paste it in.

pgqe anonymize plan.txt > safe.txt

What gets replaced comes from the parsed plan, so it cannot miss a literal the parser understood — and the replacement happens in the original text, so everything else is preserved exactly and the result is still a plan you (or anyone else's tooling) can read.

The same value maps to the same pseudonym within a document — join keys stay readable — and the mapping doesn't carry across documents, so two anonymized plans can't be correlated.

Numbers are never touched. Costs, rows, and timings are the analysis; scrubbing them would leave something safe and useless. Relation and column names are also kept by default, because they're usually the least sensitive part of a plan and the most useful to whoever is helping you read it — add --identifiers where the schema itself is confidential:

pgqe anonymize plan.txt --identifiers > safe.txt

To analyze and keep the output safe in one step, --anonymize scrubs the plan before analysis, so the tree, the findings, and the evidence they cite are all derived from text that never held a production value:

pgqe plan.txt --query slow.sql --anonymize

The web UI at pgqe.osss.net anonymizes by default.

In a browser

pgqe.osss.net runs the same engine with no install: paste a plan, optionally the query and a schema snapshot, and get the same tree, table, and findings. Analyses get a permalink you can share.

It deliberately does not accept a database connection string. Live-database access is a CLI capability; a public web form asking for production credentials is not something you should ever fill in, here or anywhere.

Where to go next

• design/architecture.md — how the pipeline is built and why the stage boundaries fall where they do.
• The sibling tools: pgsf formats the SQL, pgstd extracts the schema that feeds pgqe's richest findings, pgsm manages it declaratively, and pgt tests it.