PostgreSQL EXPLAIN Plan Visualizer
Paste EXPLAIN (FORMAT JSON) output from PostgreSQL to see the plan as a tree with cost share, row estimates versus actual rows, and flagged scans and sorts.
The interactive tool loads here when JavaScript runs.
About this toolWhat is the EXPLAIN Plan Visualizer?
The EXPLAIN Plan Visualizer turns PostgreSQL's JSON query plan into a readable tree. Each node shows its operation (Seq Scan, Index Scan, Hash Join, Sort and so on), the table and index it uses, the planner's cost and row estimate, and, if you ran EXPLAIN ANALYZE, the actual rows and time. A bar shows each node's share of the root cost so the expensive branch stands out.
It then lists issues and recommendations: high-cost nodes, large sequential scans, slow nodes, expensive sorts, nested loops over many rows, and row estimates that are off by 10 times or more, which usually means stale statistics.
The parser reads PostgreSQL's EXPLAIN (FORMAT JSON) output, including the outer array PostgreSQL prints. MySQL's EXPLAIN FORMAT=JSON has a different structure and is rejected with an error rather than shown as a misleading empty tree. Everything runs in your browser; the plan is not sent anywhere.
- Reads PostgreSQL EXPLAIN (FORMAT JSON), with or without ANALYZE
- Tree view with cost share per node
- Actual time and rows multiplied by loops
- Flags misestimated row counts, big scans, slow nodes and costly sorts
- Runs in your browser; plans are not uploaded
Updated
How to use the EXPLAIN Plan Visualizer
- In psql or your SQL client, run EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) followed by your query. Drop ANALYZE if the query is too slow or must not run.
- Copy the whole JSON result and paste it into Execution Plan Input, or click Load Sample.
- Click Analyze Plan.
- Read the summary (root cost, estimated rows, root actual time), then walk the tree from the top. Nodes are indented under their parent.
- Check Performance Issues and Optimization Recommendations, change one thing (an index, ANALYZE, a rewrite) and compare a fresh plan.
Worked example
The sample plan
Input
Hash Join (cost 1543.2) over a Seq Scan on orders with Filter (status = 'paid'::text), 50,000 rows, and a Hash over an Index Scan on users estimated at 1,500 rows that returned 120
Output
Issues: high cost Hash Join and Seq Scan, large sequential scan on orders (50000 estimated rows), row estimate off by 13x on the users Index Scan. Recommendations include an index for the status filter if it is selective, and ANALYZE users.
The users estimate is 12.5 times too high, rounded to 13x. The sequential scan on orders is only worth indexing if paid orders are a small fraction of the table; if most orders are paid, a scan is the right plan.
Background and insight
PostgreSQL costs are in arbitrary units anchored to seq_page_cost, which defaults to 1.0 for reading one 8 kB page sequentially. A node's Total Cost includes everything beneath it, so the root cost is the planner's estimate for the whole query, and a child's share of it tells you where the planner expects the work to be. Costs are estimates; they are useful for comparing plans, not for predicting milliseconds.
EXPLAIN ANALYZE actually runs the statement and adds Actual Total Time (in milliseconds), Actual Rows and Actual Loops. Time and rows are reported per loop, so the inner side of a nested loop that ran 10,000 times shows a tiny time per loop. The visualizer multiplies by loops to show the real total. Because ANALYZE executes the query, wrap an INSERT, UPDATE or DELETE in BEGIN and ROLLBACK when you analyze it.
The single most useful thing in a plan is the gap between estimated and actual rows. The planner chooses join types and join order from its row estimates; if it expects 10 rows and gets 90,000, it may pick a nested loop that would have been right for 10. Running ANALYZE on the table, raising the statistics target for a skewed column, or creating extended statistics for correlated columns fixes most of these.
Common use cases
Debugging a slow endpoint
Paste the plan for the query behind a slow API call and find the node that holds most of the cost.
Checking an index is used
Confirm that a new index turns a Seq Scan into an Index Scan or Bitmap Heap Scan.
Spotting stale statistics
Find nodes where actual rows differ from estimates by an order of magnitude after a bulk load.
Explaining a plan to a teammate
Share a readable tree instead of a wall of nested JSON in a code review or incident write-up.
Tips and limitations
- Add BUFFERS to see shared hit and read counts in your client; they show whether data came from cache or disk.
- A Seq Scan is not automatically bad. For small tables, or filters that keep most rows, it is the cheapest plan.
- Times in plain EXPLAIN (without ANALYZE) are zero; only costs and estimated rows are available.
- Run ANALYZE table_name after large imports so the planner works from current statistics.
- Plans can differ between staging and production because data volumes differ. Capture the plan where the problem is.
Sources and further reading
- Using EXPLAIN, PostgreSQL Global Development Group. Cost units, actual time, rows and loops
- EXPLAIN, PostgreSQL Global Development Group. Options, including ANALYZE and running it inside a rolled-back transaction
- Query Planning configuration, PostgreSQL Global Development Group. seq_page_cost and the other planner cost constants
- Statistics Used by the Planner, PostgreSQL Global Development Group. Row estimates, the statistics target and extended statistics
Frequently asked questions
How do I get a JSON execution plan from PostgreSQL?
Run EXPLAIN (FORMAT JSON) SELECT ... for estimates only, or EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT ... to also execute the query and record real timings. Copy the whole output, including the outer square brackets, and paste it here.
What do cost numbers in EXPLAIN mean?
They are planner estimates in arbitrary units, where reading one page sequentially costs 1.0 by default. The first number is startup cost, the second is total cost including all child nodes. Use them to compare plans for the same query, not as milliseconds.
Why does actual time look tiny on a nested loop's inner node?
PostgreSQL reports actual time and rows per loop. If the inner side ran 10,000 times, multiply by Actual Loops to get the real total. The visualizer does that multiplication for you and shows the loop count next to the time.
Does this work with MySQL EXPLAIN output?
No. MySQL's EXPLAIN FORMAT=JSON uses a query_block structure that is quite different from PostgreSQL's plan nodes, so the tool reports that it cannot read it rather than drawing an empty tree. For MySQL 8.0.18 and later, EXPLAIN ANALYZE prints a text tree you can read directly.
Is it safe to run EXPLAIN ANALYZE on an UPDATE?
Only inside a transaction you roll back, because ANALYZE really executes the statement. Run BEGIN; EXPLAIN (ANALYZE, FORMAT JSON) UPDATE ...; ROLLBACK; and the rows are left untouched while you still get real timings.
What does a large gap between estimated and actual rows mean?
The planner's statistics do not match the data, so it may choose the wrong join method or order. Run ANALYZE on the table, raise the statistics target for skewed columns, or add extended statistics when two columns are correlated.
Is there an in-depth guide to the EXPLAIN Plan Visualizer?
Yes. “SQL Indexes and EXPLAIN Plans: Finding Why a Query Is Slow” covers the background, how it works, common pitfalls and worked examples. Read the full guide.
Related Tools You May Like
Free, in-browser tools that pair well with this one.