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. Parsed in your browser; the plan, and the table and column names in it, never leave this tab.
{{ error }}
{{ 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 }}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.
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.
