PostgreSQL EXPLAIN Plan Visualizer (and MySQL EXPLAIN JSON)
See where a query spends its time: the plan as a tree, the slowest steps and what to fix.
Try before you buy.
- Free preview: every count and time of the plan, with the first plan steps, hints and slowest steps (up to 10 each).
- Locked until you unlock it: download and copy.
- Unlock: Premium pass, ₹799 for 30 days or ₹249 for 1 week, a one-time payment that never renews. Unlock: Premium pass, $19.99 for 30 days or $7.99 for 1 week, a one-time payment that never renews.
Ways to unlock shows how to get the full result.
Printing this result is locked in the free preview.
PostgreSQL text or JSON output of EXPLAIN (ANALYZE, BUFFERS), or MySQL’s EXPLAIN FORMAT=JSON or EXPLAIN ANALYZE. You can also drop a file here. It is read in your browser, never uploaded.
Plan analysis
Hints
Plan
Slowest steps
Plan-wide lines
Compare with another plan
| Measure | Plan A | Plan B | Change |
|---|
| Table | Plan A | Plan B |
|---|
Locked in the free preview. Opens the ways to unlock this result.
Locked in the free preview. Batch runs unlock with a pass.
Locked in the free preview. Query results unlock with a pass.
About the PostgreSQL EXPLAIN Plan Visualizer (and MySQL EXPLAIN JSON)
Paste the output of EXPLAIN (ANALYZE, BUFFERS) — PostgreSQL’s text format as psql or pgAdmin prints it, or its JSON format — and see the query plan as a tree of steps. Each step shows its own time (the time of its children taken off) and its share of the run as a bar, the rows it returned against the planner’s estimate with the factor of the error, its loops and the buffers it read from cache and from disk. The slowest steps are ranked, and hints point to the usual problems: sequential scans that throw most rows away, nested loops that run their inner side thousands of times, sorts and hashes that spill to disk, row estimates far enough off to suggest stale statistics, parallel workers that were planned but never started, and more.
Plans can be kept in this browser under a name and compared: the times, buffers and the way each table is read, before and after a change. MySQL plans get a basic view of the same kind: EXPLAIN FORMAT=JSON (versions 1 and 2) and the TREE output of EXPLAIN ANALYZE.
Without a pass the page shows a free preview: every count and time of the plan, with the first steps, hints and slowest steps; the rest of the report and its copy and download unlock with a Premium pass. The plan never leaves your browser.
How to use it
- Run the query with
EXPLAIN (ANALYZE, BUFFERS) SELECT …;in psql or your SQL client. ANALYZE really runs the statement: wrap an INSERT, UPDATE or DELETE inBEGIN; … ROLLBACK;. - Paste the whole output — the QUERY PLAN header and the row count may stay — or open the file you saved it to. JSON output (
FORMAT JSON) works too. - Read the hints first, then the plan: the bar under each step is its share of the run, and an arrow beside the rows shows how far the estimate was off.
- Save the plan under a name, change the query or add the index, and compare the new plan with the saved one.
- With a pass, copy or download the report as Markdown for a pull request or a ticket; the free preview shows the first rows of each list.
Examples
Seq Scan on orders o (actual … rows=48210 loops=1) Filter: ((status = 'paid'::text) AND …) Rows Removed by Filter: 951790
Serious: Sequential scan keeps 4.8% of the rows it reads — an index on the columns the filter tests may let PostgreSQL read only the matching rows.
Sort Method: external merge Disk: 2840kB
Serious: A sort spilled 2.8 MB to disk — raise work_mem for this query only, until the sort method becomes quicksort.
Index Scan using tenk2_unique2 on tenk2 t2 (actual time=0.003..0.003 rows=1.00 loops=10)
Own time 0.03 ms: 0.003 ms per loop × 10 loops; rows 10 (1 per loop).
Actual times and rows are averages per loop, so the visualizer multiplies them by the loops, as the PostgreSQL manual explains.
Plan A: 421 ms, Seq Scan on orders · Plan B: 67.4 ms, Index Scan using orders_status_created_idx
Execution time 6.2× faster; blocks read 33.7× fewer; orders: Seq Scan → Index Scan (orders_status_created_idx).
Common uses
- Find the step that makes a slow query slow, and why.
- Check that a new index is used, and what it saves, before and after.
- Explain a plan to a teammate in a pull request or a ticket.
- Read a MySQL EXPLAIN without decoding its JSON by hand.
Reading the numbers
- Actual time is in milliseconds, given as the time to the first row and to the last; it is an average per loop. The visualizer multiplies by the loops to get each step’s total, then takes the children’s totals off to get its own time.
- Rows are an average per loop too, so rows × loops is what the step produced. The estimate beside them is the planner’s, for the same loops; ↑ 48× means 48 times more rows than expected, ↓ fewer.
- Parallel steps under Gather report averages over the worker processes and the leader, and their loops count every process: the visualizer divides those loops by the processes, so a parallel step’s time is the time it ran, not the sum over the processes.
- Buffers count 8 kB pages: hit were found in shared buffers, read came from the operating system or the disk, temp went to temporary files. A step’s buffers include its children’s; the visualizer shows what each step read itself.
- Costs are the planner’s units, not milliseconds; a plan without ANALYZE has only them, and the bars then show each step’s own share of the estimated cost.
What the hints look for
- Sequential scans whose filter removes more than 90% of at least 1,000 rows.
- Nested loops whose inner side runs 1,000 times or more, especially as a scan without an index.
- Sorts with an external method or disk space, hash tables with more than one batch, and hash aggregates with disk usage — work that did not fit in work_mem.
- Row estimates off by 10× or more, reported where the error starts rather than at every step above it (steps a LIMIT stopped early return fewer rows than estimated by design and are left out).
- Gather steps that launched fewer workers than planned, lossy bitmap heap scans, index-only scans that still fetched rows from the table, join filters that discard many row pairs, long JIT compilation, slow triggers (such as foreign-key checks without an index) and planning that took longer than the run.
Hints are leads, not verdicts: check each against the query and the data before you change anything.
Sources
- PostgreSQL manual: Using EXPLAIN, EXPLAIN, Statistics Used by the Planner, work_mem and hash_mem_multiplier, How Parallel Query Works, Index-Only Scans, When to JIT?
- MySQL manual: EXPLAIN, EXPLAIN Output Format, ORDER BY Optimization, Optimizer Statistics
Limitations
- The hints come from the plan alone: the visualizer does not see the query, the indexes that exist or the data, so a hint can be wrong for your case.
- MySQL plans get a basic view: tables, access types, estimated rows and costs, and actual times from EXPLAIN ANALYZE; MySQL prints no buffer counts.
- Plans of PostgreSQL versions before 9.6 may lack some figures (buffers in JSON, parallel workers); everything that is printed is read, and lines that could not be placed are listed.
- Very large plans (thousands of steps) are shown in full but can take a moment to draw.
- Saved plans are kept only in this browser (at most 10, each up to 300 kB); clearing the site’s data removes them.
Privacy
On this device: nothing is sent
Everything happens in your browser. What you enter or open here is not uploaded or stored by MySmartCoPilot.
Frequently asked questions
What do I get without a pass?
Without a pass, PostgreSQL EXPLAIN Plan Visualizer (and MySQL EXPLAIN JSON) shows every count and time of the plan, with the first plan steps, hints and slowest steps (up to 10 each). Until you unlock it, the result can’t be downloaded or copied. A Premium or Ultimate pass, a one-time payment that never renews, unlocks the full result. The pricing page lists the passes and their prices.
Does the visualizer run my query?
No. It reads what EXPLAIN printed; nothing is sent anywhere. EXPLAIN ANALYZE itself does run the query on your server, so run data-changing statements inside BEGIN … ROLLBACK.
Why does the sum of the steps differ from the Execution Time?
Execution Time also covers starting and finishing the executor and triggers, while each step’s time is measured inside it; measuring every row also adds a little overhead (EXPLAIN (ANALYZE, TIMING OFF) avoids it, without per-step times).
What do “hit” and “read” mean in the buffers?
Both count 8 kB pages. Hit pages were already in PostgreSQL’s shared buffers; read pages came from the operating system’s cache or the disk. Many reads on a repeated query point to too small a cache or a scan of more data than needed.
Which formats can I paste?
PostgreSQL’s text format (from psql, pgAdmin, DBeaver or the server log, with or without the QUERY PLAN header and quotes) and its JSON format; MySQL’s EXPLAIN FORMAT=JSON in version 1 or 2, and the TREE output of EXPLAIN ANALYZE or EXPLAIN FORMAT=TREE.
Where are saved plans kept?
In this browser’s local storage, under this tool’s settings. They are not uploaded and no other site can read them; remove one with Delete, or all by clearing the site’s data.