PostgreSQL Query Plan Analyzer
Paste your EXPLAIN ANALYZE output to get an interactive plan tree with performance warnings and optimization hints. Everything runs in your browser, nothing is sent to a server.
How to use it
- Run your query with
EXPLAIN (ANALYZE, BUFFERS)in psql or any client. TheBUFFERSoption is optional but enables the cache hit ratio check. - Copy the whole plan, including the Planning Time and Execution Time lines.
- Paste it above. The tree renders immediately and any problems are flagged on the nodes that caused them.
Note that ANALYZE executes the query. On a statement that writes, wrap it in a transaction and roll back.
What this analyzer checks
Seven rules run against every node in the plan. Each one corresponds to a problem that shows up repeatedly in real production queries.
- Sequential scan on a large table
- Triggers when: A Seq Scan returning 10,000 rows or more. Flagged as critical above 100,000.
- Postgres read the whole table because no usable index existed, or because the planner decided an index would not pay off. On a large table this is usually the single biggest cost in the plan.
- Row estimate off by 10x or more
- Triggers when: The planner's estimated row count differs from the actual count by a factor of 10 in either direction.
- The planner chose its strategy from bad statistics. Wrong estimates are how you end up with a nested loop over a million rows. Usually fixed by running ANALYZE on the table.
- Filter throwing away most of what it read
- Triggers when: Rows Removed by Filter is more than five times the rows actually returned.
- Postgres read a large number of rows and discarded nearly all of them. A more selective index, or a partial index matching the filter condition, moves that work out of the query.
- Sort spilling to disk
- Triggers when: A Sort node reporting an external merge or external sort method.
- The sort did not fit in work_mem, so Postgres wrote it to temporary files. Disk sorts are orders of magnitude slower than in-memory ones. Raise work_mem for the session, or add an index that provides the ordering.
- Nested loop running many iterations
- Triggers when: A Nested Loop node with 100 or more loops.
- The inner side is being re-executed once per outer row. That is efficient for a handful of rows and expensive at scale, where a hash join usually wins. Often a downstream symptom of a bad row estimate.
- Bitmap scan going lossy
- Triggers when: A Bitmap Heap Scan reporting lossy blocks.
- work_mem was too small to hold the exact bitmap, so Postgres fell back to tracking whole pages instead of individual rows and had to re-check each one. More work_mem restores the exact bitmap.
- Low buffer cache hit ratio
- Triggers when: Fewer than 90% of shared blocks served from cache, measured over at least 100 blocks.
- The query is reading from disk rather than from shared_buffers. Either the working set does not fit in memory, or this query touches far more data than it needs to. Requires EXPLAIN (ANALYZE, BUFFERS) to appear.
Reading the numbers
Every node in a plan carries the same four figures. Once you can read them, most plans explain themselves.
| Field | What it means |
|---|---|
| cost=0.00..4250.00 | Startup cost, then total cost, in arbitrary planner units where one sequential page read is 1.0. Not milliseconds. Only meaningful relative to other nodes. |
| rows=1000 | The planner's estimate. Compare it to actual rows: a large gap is the root cause of most bad plans. |
| actual time=0.02..45.1 | Real milliseconds, per loop. Multiply by the loop count to get the true cost of the node. |
| loops=200 | How many times the node ran. High loop counts on an expensive node are where nested loops go wrong. |
The long version, with worked examples of the five most common problems and how to fix each one, is in How to Read PostgreSQL Query Plans.
Frequently asked questions
- What is the difference between EXPLAIN and EXPLAIN ANALYZE?
- EXPLAIN shows the planner's estimate of what it thinks will happen, without running the query. EXPLAIN ANALYZE actually executes the query and reports what did happen, including real row counts and timings. The gap between the estimate and the reality is where most performance problems are hiding, so EXPLAIN ANALYZE is what this analyzer expects.
- What does cost mean in a PostgreSQL query plan?
- Cost appears as cost=0.00..4250.00. The first number is the startup cost, the work done before the first row can be returned; the second is the total cost of the node. They are not milliseconds. They are arbitrary units the planner uses to compare candidate plans against each other, calibrated so that reading one sequential page costs 1.0. A high cost is only meaningful relative to the other nodes in the same plan.
- Is my query plan sent to a server?
- No. The parser and the analysis run entirely in your browser. Nothing is uploaded, there is no account, and no plan is stored. The share feature encodes the plan into the URL itself, so a shared link carries the data rather than pointing at a copy on a server.
- Which EXPLAIN output formats are supported?
- Paste the default text output from EXPLAIN ANALYZE. For the cache hit ratio check you need EXPLAIN (ANALYZE, BUFFERS), which adds the shared block counters the ratio is calculated from.
- Why is my query slow when the plan looks fine?
- Check actual time against loops on each node, since the time shown is per loop and must be multiplied out. Then look for the largest gap between estimated and actual rows, which is where the planner was misled. If the plan genuinely looks optimal, the cost may be outside the query: lock waits, connection pool saturation, or a cold cache.