Paste the output of EXPLAIN (ANALYZE, BUFFERS) — text or JSON — and read it as a tree: how long each node took on its own, where the planner's row estimate went wrong, which reads missed the cache. Or compare two plans — before and after an index, a rewrite, a statistics refresh — node-by-node. Parsed in your browser; the plan, and the table and column names in it, never leave this tab.
{{ error }}
{{ errorA }}
{{ errorB }}
{{ snippet.sql }}{{ snippet.note }}
| Option | Effect |
|---|---|
| {{ option[0] }} | {{ option[1] }} |
Buffers line is mostly read is measuring your disk, not your query — the second run is the one that resembles production.ANALYZE your_table; costs seconds and settles the question.pg_stat_statements tells you which statement actually consumes the server's time; guessing from the application's slow pages usually does not.EXPLAIN ANALYZE on a write really writes. Wrap it in BEGIN … ROLLBACK, as in the recipe above.TIMING OFF gives honest row counts without it.{{ note }}
The bar and the time are self time — this node minus its children — because cumulative time makes every parent look slow. Click a node for its full properties.
{{ exportText }}{{ note }}
Rows matched strong (same node, same table/index) show a plain delta. Rows matched weak (same node type only — the table or index differs) or marked added/removed are a guess at what corresponds to what, not a verified identity; see the note above if most of the plan fell into those buckets. + is a metric that got worse, − is one that improved.
Self time deltas on matched nodes summed to {{ fmtTimeDelta(compareResult.totals.matchedSelfTimeDelta) }}; added node(s) contributed {{ fmtTime(compareResult.totals.addedTime) }} of new time and removed node(s) took {{ fmtTime(compareResult.totals.removedTime) }} of old time with them — those three numbers, not just the first one, are what moved the root node's total time by {{ fmtTimeDelta(compareResult.totals.rootTimeDelta) }}.
{{ compareExportText }}Fix the plan(s) above to see a comparison.
Get the right output first. EXPLAIN alone prints the planner's guesses and nothing else — its costs are in arbitrary units invented for comparing plans, not milliseconds, and a query can have a low cost and still be slow. EXPLAIN (ANALYZE, BUFFERS) runs the query and reports what really happened. Note the "runs": on an UPDATE or DELETE it will modify data, so wrap it in a transaction you roll back.
Read time, not cost. Every timing in a plan is cumulative — a node includes everything below it — which is why the root always looks like the problem. This page subtracts the children to give each node its own self time, and that is the column to sort your attention by. One more trap: actual time is an average per loop, so a node showing 0.1 ms with loops=24193 spent roughly 2.4 seconds, not 0.1 ms.
Then read the row estimates. Most bad plans are not bad algorithms, they are good algorithms chosen from wrong numbers. When a node estimates one row and returns twenty thousand, the planner picked a nested loop that would have been correct for one row and is catastrophic for twenty thousand. The fix is usually ANALYZE, occasionally CREATE STATISTICS for correlated columns, and only rarely a rewrite.
What the findings look for. Sort Method: external merge Disk: and Batches above one both mean an operation outgrew work_mem and went to disk. A sequential scan is only a problem when the filter is selective — reading a whole small table is correct and indexing it changes nothing. A filter that discards most of what it read is a candidate for an index that would not have fetched those rows. And when Buffers shows mostly read rather than hit, you are measuring a cold cache: run it twice before concluding anything.
Comparing before and after. "Compare two" pairs up the nodes of two plans — matched on node type plus the table or index it touches where that node type has one — and shows the self-time, row and cost delta for each pair. Postgres does not label nodes, so a pair that only matched on type (a join or sort with nothing else to go on) is marked uncertain, and a node with nothing to pair on the other side is marked added or removed rather than forced into a misleading match. Matched-node deltas, added-node time and removed-node time are kept as three separate figures rather than one combined total, because folding them together would credit a plan shape change to "the same nodes got slower" or the reverse.
Related: the SQL Formatter makes the query itself readable before you explain it, the JSON Toolbox handles a FORMAT JSON plan you want to diff, and the Docker Toolbox builds the Compose file for the throwaway Postgres you are testing against.
Privacy: parsing and analysis run in this tab. Query plans carry table names, column names, index names and literal values from your WHERE clauses — none of it is uploaded, logged or sent anywhere.
