Explain plan

A query plan tells you what the database did, but the numbers are not in the shape you need. PostgreSQL prints each node’s time including everything below it, per loop, and buffer counts that also include the children; parallel workers, CTEs and sub-plans each bend those rules a little. This page untangles that: for every node it works out the time spent in that node alone, then points at the nodes that matter and says why they are slow.

How to use it

Get a plan. In PostgreSQL:
```
EXPLAIN (ANALYZE, BUFFERS) SELECT …;                                   -- text, what most people paste
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS, WAL, FORMAT JSON) SELECT …; -- everything
```
ANALYZE runs the query — wrap an UPDATE or DELETE in BEGIN; … ROLLBACK;. Plain EXPLAIN (estimates only) works too, with less to say. In MySQL 8.0.18 or later use EXPLAIN ANALYZE SELECT … (the indented tree), or EXPLAIN FORMAT=JSON (both the classic layout and explain_json_format_version=2 from 8.3).
Paste it into Analyse. It does not matter if it still has psql’s QUERY PLAN header and (12 rows) footer, the + at the end of JSON lines, pgAdmin’s quotes, the mysql client’s box or \G layout, or an auto_explain log prefix — those are stripped.
Read the summary and the list of problems first. Click a problem to open its node in the tree.
The Tree shows each node with its own time and share (the bar turns amber at 10% and red at 30%), actual and estimated rows with the misestimate factor (↑ more rows than planned, ↓ fewer), loops, rows removed by filters and disk reads. Click a node for every detail line, the buffers (including children and its own), parallel workers, and the advice for that node. Flame draws the same tree with width as time, Nodes by time lists the nodes hottest first, and Text is the plan re-printed in a uniform layout.
Compare two plans takes a before and an after — a new index, a different work_mem, a rewritten query — and says what changed: overall speed, scans that became index scans, joins that changed type, spills that went away, buffers, and the node-by-node times. Both plans are shown in full underneath.

The Examples menu has real plans from PostgreSQL 15, 17 and 18 and MySQL 8.4 that show the common problems: a hash join spilling into batches, a sort on disk, a seq scan that throws away 99.99% of the rows it reads, a 29× underestimate, a CTE with an InitPlan and a SubPlan, parallel workers, JIT, a bulk insert slowed by its foreign-key check.

From and to the Postgres playground

The Postgres playground runs real PostgreSQL in your browser. Each plan it prints has an Explain plan ↗ button that opens it here, with its query; if a plan was already here, Compare with the previous plan puts the two side by side — run a query, add an index, run it again, compare. The other way, type the query under Query and Run in Postgres playground opens it there as EXPLAIN (ANALYZE, BUFFERS). For the examples it also creates the sample customers and orders tables first (smaller than the ones the plans came from, so the numbers will differ).

How the times are worked out

A node’s printed time is the average per loop, so its total is time × loops. Its own time is that minus its children’s totals.
Under a Gather or Gather Merge the loops are processes (the leader and each worker) running at the same time, so the total there is time × loops ÷ processes.
A CTE runs while its CTE Scan nodes pull rows from it, so its time is inside those scans, not in the node that owns the CTE. The CTE’s time is taken out of the scans, biggest first.
InitPlan and SubPlan times are part of the node that uses them and are subtracted from it.
Times are printed to 0.001 ms. A node that ran 14 400 times at "0.001 ms" can add up to more than its parent; such children are scaled to fit, and the page says so.
In MySQL, actual time is also per loop. A subquery in the select list (Select #2 (subquery in projection)) is not counted in its parent’s time; one in a condition is.

These are the same rules explain.depesz.com and PEV2 use. The parser was tested on plans generated for it from real PostgreSQL 15, 17 and 18 and MySQL 8.4, in both formats.

What it looks for

Hotspots (nodes with most of the own time); row estimates out by 10× or more, reported where the error starts rather than on every node above it; sorts that spilled (external merge), hash joins that needed more than one batch, and HashAggregate spills, with a work_mem that would have been enough; other temporary-file writes; seq scans that keep less than 10% of what they read, with a CREATE INDEX for the filtered columns (equality columns first); index scans whose filter throws most rows away; lossy bitmaps and index rechecks; index-only scans that still visit the heap (VACUUM); seq scans and table scans repeated on the inner side of a loop; nested loops with very many cheap probes; memoize caches that rarely hit; parallel workers that did not launch; heavy reads from outside shared buffers; JIT compilation that takes a big share of a short query; foreign-key triggers; planning slower than execution. For MySQL: full table scans (with an ADD INDEX suggestion), repeated inner scans, filesorts, temporary tables and dependent subqueries run many times.

The advice is a starting point: check it against what you know about the data. An index that helps one query costs every write.

Related: Postgres playground (real PostgreSQL to try plans in), schema diff (migrations, and the locks they take), SQL formatter, DuckDB lab, SQLite internals, storage engines (B+trees, which is what an index scan walks), latency numbers.

Browser support

Any current browser, on a phone too. Nothing to install.

Privacy

Plans are parsed in this page and never sent anywhere. What you paste is kept in this browser’s local storage so it is there next time; the share link carries it only if you make one. Run in Postgres playground opens the playground page on this site with your query in its address; the playground runs it in your browser too.