🔬 EXPLAIN Query Analyzer

About the EXPLAIN Analyzer

Paste any PostgreSQL EXPLAIN ANALYZE output — with or without the original SQL — and click Analyze. The tool parses the plan tree, identifies slow nodes, and generates specific, actionable recommendations including ready-to-run SQL.

How to generate the input

Run this in psql or any PostgreSQL client:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT …;

BUFFERS is optional but reveals cache hit/miss ratios that indicate I/O bottlenecks. Never run EXPLAIN ANALYZE on a destructive statement (DELETE, UPDATE) in production — it actually executes the query.

What the analyzer checks

• Sequential scans — full-table reads on large relations that a targeted index could replace.
• Row-estimate mismatch — when PostgreSQL's estimated row count differs from the actual count by 10× or more, indicating stale statistics.
• Disk sorts — Sort Method: external merge Disk means the sort exceeded work_mem and used temporary files.
• Nested Loop at scale — nested loops with a large iteration product that would benefit from a Hash Join or an inner-side index.
• HashAggregate spill — aggregation that batched to disk due to memory pressure.

Reading the plan tree table

• Nodes highlighted in orange-red consumed more than 25% of execution time — start your tuning there.
• Nodes highlighted in blue are table scans — they may or may not be a problem depending on row count.
• Amber actual-row values indicate a significant estimate mismatch.

Common plan patterns and what they mean

Seq Scan → Index Scan

A Seq Scan reads every row in the table from disk. It is the right choice when the query needs most of the table (e.g. a full export), but it becomes a bottleneck when only a small fraction of rows match the filter or join condition. Adding a B-tree index on the relevant column lets PostgreSQL switch to an Index Scan or Index Only Scan, which reads only the matching leaf pages — often 10–1000× faster on large tables.

When Seq Scan is intentional: PostgreSQL's planner will deliberately choose a Seq Scan over an existing index when the estimated selectivity is low (many rows match) or the table is small enough to fit in a few pages. If a Seq Scan appears on a 500-row lookup table, it is not a problem.

Hash Join vs Nested Loop vs Merge Join

PostgreSQL picks one of three join strategies depending on data volume and available indexes:

• Hash Join — builds an in-memory hash table from the smaller side, then probes it for each row of the larger side. Scales as O(n + m). Best for large, unindexed joins. Requires enough work_mem to keep the hash table in RAM; if it spills to disk, look for Batches: > 1 in the plan.
• Nested Loop — for each row in the outer relation, scans the inner relation. Scales as O(n × m) without an index. Efficient when the outer side is tiny or the inner side has an index that makes each lookup O(log m). A Nested Loop with thousands of loops and a Seq Scan on the inner side is almost always a missing index.
• Merge Join — requires both sides to be sorted on the join column. Fast when both inputs are already ordered (e.g., indexed columns), but adds an explicit Sort node if they are not. Common in range joins and when both sides are large with matching indexes.

Sort and its methods

A Sort node appears whenever PostgreSQL cannot deliver rows in the required order from an index. The Sort Method line in the plan reveals how the sort was executed:

• quicksort Memory: X kB — sorted entirely in RAM within work_mem. Fast; no action needed.
• top-N heapsort Memory: X kB — used for ORDER BY … LIMIT N; only the top N rows are kept in a heap. Efficient; no action needed.
• external merge Disk: X kB — the sort exceeded work_mem and spilled to disk. Each disk pass is orders of magnitude slower than RAM. Either increase work_mem or create an index that supplies pre-sorted output so the Sort node disappears entirely.

HashAggregate and GROUP BY

HashAggregate builds a hash table keyed on the GROUP BY columns. Like Hash Join, it requires the hash table to fit in work_mem. If the plan shows Batches: > 1, the aggregation spilled to disk. You can either increase work_mem or reduce the number of groups by pre-filtering rows before the aggregation step.

When the number of distinct groups is known to be small, PostgreSQL may choose GroupAggregate instead, which requires sorted input but uses O(1) memory per group — useful to know when tuning memory-constrained environments.

Bitmap Heap Scan + Bitmap Index Scan

When a query matches a significant but not overwhelming fraction of rows, PostgreSQL sometimes issues a Bitmap Index Scan to collect matching page addresses into a bitmap, then uses a Bitmap Heap Scan to fetch those pages in physical order — reducing random I/O compared to a plain Index Scan. This pattern is normal and healthy. It can appear with multiple indexes combined via BitmapAnd or BitmapOr nodes, which means PostgreSQL is intersecting or unioning two indexes to satisfy a compound condition without a composite index.

CTE (Common Table Expressions) and subquery fences

In PostgreSQL 12 and earlier, CTEs defined with WITH were always materialised — executed once and stored as a temporary result, preventing the planner from pushing filters inside them. From PostgreSQL 12 onward, the planner can inline CTEs automatically unless you write WITH … AS MATERIALIZED (…). If your plan shows a CTE Scan node, the CTE is being materialised; consider rewriting it as a subquery or adding NOT MATERIALIZED to let the planner optimise through it.

Planning time vs execution time

Planning time is the time PostgreSQL spent choosing the execution plan. It is normally a few milliseconds. Unusually high planning time (tens of milliseconds or more) on a simple query can indicate a very large number of joins, partitioned tables with many partitions, or excessive use of functions that prevent plan caching. Execution time is the actual wall-clock time the query ran. Always focus optimisation on execution time first; planning time is only relevant if the same simple query is executed thousands of times per second.

Rows removed by filter

Look for lines like Rows Removed by Filter: N beneath a scan node. A high removed-to-returned ratio (e.g., reading 500 000 rows to return 10) indicates the filter is applied after the scan rather than being pushed into an index. Adding an index on the filter column — or a partial index (CREATE INDEX … WHERE condition) — eliminates those wasted reads entirely.