PostgreSQL EXPLAIN Plan Analyzer

Plan label:

Short label for the pasted plan and exported review artifacts.

Input format:

Use Auto for normal paste workflows; JSON or XML works best with EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON/XML).

EXPLAIN plan:

Paste the plan text, JSON, or XML. Drop a .txt, .log, .json, or .xml file directly on the textarea.

{{ explain_text.length.toLocaleString() }} chars {{ fileStatus || 'Drop TXT, LOG, PLAN, JSON, or XML onto the textarea.' }}

Cost warning:

Planner cost is arbitrary, but a consistent local threshold helps triage plan changes.

cost

Row drift warning:

Used only when the plan includes actual rows from EXPLAIN ANALYZE.

Seq scan row warning:

Keep this above small lookup tables so intentional tiny scans do not dominate the review.

rows

Plan could not be fully parsed

{{ error }}

Aspect	Value	Review detail	Copy
No review brief is available Paste PostgreSQL EXPLAIN output or load the sample plan to build a review brief.
{{ row.aspect }}	{{ row.value }}	{{ row.detail }}

Level	Node	Relation	Cost	Rows	Actual	Signals	Copy
No plan nodes parsed Use raw PostgreSQL text EXPLAIN or FORMAT JSON/XML output with cost estimates.
{{ row.level }}	{{ row.node }}	{{ row.relation }}	{{ row.cost }}	{{ row.rows }}	{{ row.actual }}	{{ row.signals }}

Severity	Signal	Evidence	Next action	Copy
No findings are available A parsed plan with cost, scan, buffer, spill, or drift evidence will populate this ledger.
{{ row.severity }}	{{ row.signal }}	{{ row.evidence }}	{{ row.action }}

Embed:

Customize

Include current inputs

Size

Advanced

Width

Height

Aspect ratio

Max height

Collapsible embed

Allow fullscreen

Referrer policy

Sandbox tokens

PostgreSQL plan review starts by separating expectation from measurement. A query can be slow because it reads too many pages, repeats an inner lookup thousands of times, sorts more data than memory can hold, or follows a join order based on stale or incomplete statistics. EXPLAIN gives those possibilities a common vocabulary before anyone changes indexes, SQL text, memory settings, or table statistics.

A plan is a tree of operations. Scan nodes read rows from tables, indexes, functions, values, or other row sources. Parent nodes join, sort, aggregate, limit, materialize, or transform those rows. Indentation matters because child nodes feed their parents, and a parent node's cost includes work done below it.

Plain EXPLAIN shows the planner's estimate: startup cost, total cost, expected output rows, and row width. EXPLAIN ANALYZE runs the statement and adds actual rows, timing, loops, buffers, temporary blocks, and spill evidence when those details are available. The second form is much richer, but it can also execute expensive or write-changing statements, so production use needs care.

PostgreSQL plan tree evidence flow from plan nodes to estimates, measured facts, and review checks.

Cost is not elapsed time. PostgreSQL cost units are planner units shaped by cost settings and statistics. A high-cost branch is still worth attention because it shows where the planner expects work to concentrate, but the number cannot be converted directly into milliseconds.

Sequential scan: A table scan that can be right for small tables, broad reports, and queries that need most rows, but risky when it reads far more data than the predicate should require.
Row estimate: The planner's expected rows emitted by a node. It is not always the number of rows examined, especially when filters remove many rows.
Runtime plan: An analyzed plan that adds actual rows, loops, timings, and often buffer evidence from a real execution.

Good plan reading is cautious. A flagged sequential scan may be harmless, an index scan can be slow when repeated many times, and a row estimate can shift after statistics refresh. Stronger conclusions come from converging clues: cost concentration, row-estimate drift, loop count, buffer reads, temporary I/O, and the query's real selectivity.

How to Use This Tool:

Use the form as a repeatable PostgreSQL plan review checklist. The most useful input is raw output copied from the database session or monitoring note that produced the plan.

Enter a Plan label such as a query name, endpoint, migration ticket, or report title so copied rows and downloaded reviews carry context.
Paste the EXPLAIN plan, browse for a supported text, log, plan, JSON, or XML file, drop the file on the textarea, or load the sample plan to see the expected result shape.
Leave Input format on Auto detect for normal PostgreSQL text, FORMAT JSON, or FORMAT XML output. Choose Text plan, JSON plan, or XML plan when wrapper text makes detection uncertain.
Set Cost warning, Row drift warning, and Seq scan row warning before comparing multiple plans. The default gates are 10000 cost units, 10x row drift, and 10000 sequential-scan rows.
Read Plan Review Brief first for Review status, Review thresholds, Plan shape, Estimated cost, Actual runtime, and Primary next action.
Open Plan Node Ledger and Plan Finding Ledger to confirm which node triggered each signal. Use Plan Hotspot Ladder and Row Estimate Drift when the parsed plan has enough cost or runtime rows for charting.

If the page reports that no nodes were parsed, remove shell prompts or surrounding log wrappers, use Normalize for whitespace cleanup, or force the matching input format. YAML output is a PostgreSQL option, but this page expects text, JSON, or XML.

Interpreting Results:

Review status is a triage label, not a verdict on the query. Critical review means the weighted findings are strong enough to inspect before accepting the plan. Tuning review and Watch plan point to narrower evidence. Low-signal plan means the configured gates did not find a major issue in the pasted evidence.

PostgreSQL EXPLAIN analyzer result views and verification cues
Result view	What it answers	Verification cue
Plan Review Brief	What status was assigned, which thresholds were used, where the highest cost sits, and what to check next.	Confirm whether Actual runtime says a measured time or Estimate only.
Plan Node Ledger	Which nodes, relations, costs, rows, actual values, and signal labels were parsed.	Compare a high-signal node with its parent and children before changing an index.
Plan Finding Ledger	Which rule fired, how severe it was, what evidence was found, and which next action was suggested.	Read the evidence column before acting on the severity label.
Row Estimate Drift	Which nodes differ most between estimated and actual rows.	Check table statistics, parameter values, predicates, and extended statistics before blaming the join method.

Do not treat an empty warning set as proof that the query is fast under load. The review only sees the pasted plan and the configured thresholds. Use copied CSV, DOCX, chart, or JSON outputs as review evidence, then verify the proposed change with representative parameters and a fresh plan from the same environment.

Technical Details:

PostgreSQL estimates a plan from table statistics, index metadata, predicates, join conditions, and cost parameters. The optimizer chooses a complete tree, so a node that looks expensive in isolation may still be part of the cheapest overall plan once join order, sorting, data distribution, and LIMIT behavior are considered.

Rows are easy to misread. Planned rows describe the rows expected to leave a node, not every row touched while filters run. Actual rows add measured evidence, and loops show repeated work. A small inner index scan can dominate runtime when it executes once for each row emitted by a large outer input.

Rule Core

The review compares parsed node facts with inclusive gates. A value at the gate counts as a finding.

PostgreSQL EXPLAIN analyzer heuristic rules
Signal	Rule	Default gate	Review meaning
High estimated total cost	Highest node total cost is at least the Cost warning value; five times the gate is critical.	10000 cost units	Compare within the same database settings and query family because planner costs are not milliseconds.
Large sequential scan	A `Seq Scan` reaches the sequential-scan row gate using estimated rows or actual rows.	10000 rows	Check table size and selectivity before assuming an index is missing.
Row estimate drift	The larger estimate-vs-actual ratio is at least the Row drift warning value; ten times the gate is critical.	10x	Large drift often points to stale statistics, correlated predicates, skew, or missing extended statistics.
Spill and temporary I/O	Disk sort usage, hash batches above one, or temporary block reads and writes are present.	Any	Review row volume, join shape, sorting, and memory pressure together.
Rows removed by filter	Rows removed reaches the same row gate used for large sequential scans.	10000 rows	Earlier filtering, partial indexes, or predicate rewrites may reduce wasted reads.

Formula Core

Row-estimate drift uses the larger ratio so underestimates and overestimates can be compared on the same scale. Planned and actual row counts are floored at 1 for this ratio so zero-row edge cases do not divide by zero.

drift = \max (\frac{actual rows}{planned rows}, \frac{planned rows}{actual rows})

A node estimated at 800 rows and measured at 48000 actual rows has 60x underestimated drift. A node estimated at 50000 rows and measured at 1000 actual rows has 50x overestimated drift. Both can change join choice, memory needs, and the usefulness of an index.

The summary score is the sum of finding weights. Parse errors, critical cost, and critical drift add more weight than informational findings. Status bands are inclusive at their lower edge.

review score = \sum_{i = 1}^{n} w_{i}

PostgreSQL plan review status score bands
Review score	Status	Meaning
`>= 7`	Critical review	Several weighted findings or a strong error condition should be checked before accepting the plan.
`>= 4` and `< 7`	Tuning review	The plan has enough evidence for focused database review.
`>= 1` and `< 4`	Watch plan	One or more weaker findings deserve context or comparison.
`0`	Low-signal plan	No configured rule added weight to the review score.

Text, JSON, and XML plans are reduced to the same node facts where possible: node type, relation, index, cost range, planned rows, actual rows, loops, buffers, sort disk use, hash batches, rows removed, and selected plan attributes. The hotspot chart ranks up to 12 nodes by actual time when runtime data exists, or by estimated cost for estimate-only plans. The drift chart ranks up to 12 nodes that contain both planned and actual rows.

Privacy and Safety Notes:

Plan text can reveal table names, index names, predicates, row counts, and sometimes literal values. Treat it as operational data, especially when sharing exports with vendors, public tickets, or chat systems.

The page parses the pasted plan in the browser and does not run SQL against a database.
EXPLAIN ANALYZE output comes from a statement that already executed before it was pasted here.
Use transaction safeguards when collecting analyzed plans for write statements, and avoid pasting secrets or customer data into plan labels or comments.

Worked Examples:

Estimate-only migration check

A reviewer pastes plain EXPLAIN output for a report query and keeps the default gates. Plan Review Brief shows Review status as Watch plan, Estimated cost near 18500, and Actual runtime as Estimate only. The next step is a safe runtime plan with representative parameters before adding an index.

Row drift after data growth

A runtime plan shows a join input estimated at 800 rows but measured at 48000 actual rows. Row Estimate Drift ranks that node at 60x underestimated, and Plan Finding Ledger lists Row estimate drift. Fresh ANALYZE and predicate selectivity checks come before memory tuning.

Parse recovery

A copied plan includes shell prompts and log prefixes, so the alert says the plan could not be fully parsed. After using Normalize and forcing Text plan, Plan Node Ledger populates with Level, Node, Relation, Cost, Rows, Actual, and Signals.

FAQ:

Does the analyzer run my query?

No. It reads pasted or loaded plan output. Runtime rows, timings, buffers, and spills appear only when the text you provide already contains EXPLAIN ANALYZE evidence.

Why did a sequential scan get flagged?

The scan reached the configured Seq scan row warning gate. Check relation size, predicate selectivity, and whether the query is expected to read most rows before treating it as an index problem.

Why is cost different from runtime?

Cost is the planner's estimate in arbitrary units. Runtime needs analyzed output and can still be affected by cache state, data distribution, parameter values, locks, and timing overhead.

Can I paste JSON or XML EXPLAIN output?

Yes. Use FORMAT JSON or FORMAT XML output and select the matching Input format if auto detection does not choose it. YAML output is not parsed here.

What should I do when no nodes are parsed?

Paste raw PostgreSQL text output or valid JSON/XML plan output with cost estimates, remove unrelated log wrappers, and choose the matching input format. The empty-state message in Plan Node Ledger confirms when no plan nodes were found.

Glossary:

Plan node: One operation in the query plan tree, such as a scan, join, sort, aggregate, materialize, or limit.
Startup cost: The planner's estimated work before a node can start returning rows.
Total cost: The planner's estimated work for a node to return all rows, including child-node work.
Actual rows: The measured row count reported by an analyzed plan after the statement ran.
Row estimate drift: The larger ratio between planned rows and actual rows for the same node.
Temporary blocks: Temporary working storage used by operations such as sorts, hashes, and materialization.

References:

Using EXPLAIN, PostgreSQL Global Development Group.
EXPLAIN command reference, PostgreSQL Global Development Group.
ANALYZE command reference, PostgreSQL Global Development Group.
How to explain a query plan in PostgreSQL, Simplified Guide.
How to enable pg_stat_statements in PostgreSQL, Simplified Guide.