Practical PostgreSQL guide

How to read PostgreSQL EXPLAIN ANALYZE

A query plan becomes much easier to understand when you stop reading it as a wall of text. Follow the evidence: total runtime, repeated work, row estimates, discarded rows, memory and I/O. This guide shows you what each important field means and, just as importantly, what it does not prove.

Costs ≠ millisecondsLoops matterRows reveal bad estimatesBuffers reveal I/ONode names are not verdicts

Important: EXPLAIN ANALYZE actually executes the statement

For SELECT queries this normally just means the query runs. For INSERT, UPDATE, DELETE and other statements with side effects, those effects happen unless you protect yourself with a transaction and roll it back.

BEGIN;
EXPLAIN (ANALYZE, BUFFERS) UPDATE ...;
ROLLBACK;

01 · Fundamentals

EXPLAIN vs EXPLAIN ANALYZE

EXPLAIN

Shows the plan PostgreSQL intends to use: estimated cost, estimated rows, node types and plan structure. The query is planned but not executed.

EXPLAIN ANALYZE

Executes the statement and adds measured values such as actual time, actual rows and loops so you can compare the planner's expectations with reality.

Plain EXPLAIN is useful for understanding the planner's decision. EXPLAIN ANALYZE is usually more useful for performance diagnosis because it exposes what really happened during execution.

Start with useful output

For a diagnostic plan, include actual execution and buffer information. JSON is easiest for tools and structured analysis, while text is convenient for humans. PlanCheck accepts both.

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT ...;

If exact node timing overhead is a concern during production investigation, PostgreSQL also supports disabling per-node timing while still collecting execution information. Use that deliberately; don't compare results from different EXPLAIN settings as if they were identical.

How to read the plan tree

Indentation represents parent/child relationships. Child nodes produce rows that feed their parent. A parent can combine, filter, sort or aggregate those rows. Do not simply add every node's time together: parent and child timing can overlap.

Nested Loop
  -> Index Scan on customers
  -> Index Scan on orders

Start at the top

Check total execution time and the final result shape.

Then inspect expensive branches

Look for slow nodes, large loops, spills and discarded rows.

Trace backward

Find the earliest point where reality starts diverging from the estimates.

Startup cost and total cost

Seq Scan on orders  (cost=0.00..25431.00 rows=500000 width=48)

Startup cost

Estimated work required before the node can return its first row.

Total cost

Estimated work required if the node runs to completion.

These cost values are planner units, not milliseconds. Their role is to compare candidate plans using PostgreSQL's cost model. A cost of 10,000 does not mean 10 seconds.

Estimated rows vs actual rows

Plan Rows:   14
Actual Rows: 28,492

This is one of the strongest signals in a plan. PostgreSQL chooses a strategy before execution using estimates. When the estimate is badly wrong, it may choose a join or access method that was sensible for the estimate but expensive for the real cardinality.

Underestimation

Can make repeated operations such as nested loops look much cheaper than they become.

Overestimation

Can discourage access paths that would have been attractive for the actual number of rows.

Common things to investigate include statistics freshness, data skew, correlated columns, expressions, casts and parameter-sensitive workloads. A mismatch is evidence of planner uncertainty, not proof of one specific fix.

Deep dive: row estimate mismatch

Loops: the number people miss

Index Scan using orders_customer_idx
(actual time=0.020..0.025 rows=10 loops=10000)

When a node executes more than once, PostgreSQL reports the displayed actual time and row count as per-execution averages. The loops value tells you how many executions occurred. A node that takes only 0.025 ms once can still matter when repeated 10,000 times.

Mental model

Tiny per-loop cost × huge loop count = potentially significant total work.

Actual startup time and actual total time

actual time=0.128..12.377 rows=582 loops=1

Actual startup time

Approximate elapsed time before the node produced its first row.

Actual total time

Approximate elapsed time until the node finished for one execution.

Actual times are milliseconds of real elapsed time, unlike planner costs. Use them together with rows and loops. Do not rank nodes only by their displayed time without understanding the plan hierarchy and repetition.

Scan types

Sequential Scan (Seq Scan)

Reads table pages sequentially. This can be exactly right for small tables or queries that need a large fraction of a relation. A Seq Scan is not automatically a problem.

Index Scan

Uses an index to find candidate rows, then fetches table rows as needed. Excellent for many selective access patterns, but not automatically better than a sequential scan.

Index Only Scan

Can satisfy the query from index data when visibility information allows it. Heap Fetches tells you how often PostgreSQL still had to visit the heap.

Bitmap Index Scan + Bitmap Heap Scan

Builds a bitmap of matching locations, then visits heap pages. Useful when many rows match but random index-by-index heap access would be inefficient.

Deep dive: when a sequential scan is actually a problem

Join types

Nested Loop

For each outer row, PostgreSQL evaluates the inner side. It can be excellent for a small outer input with cheap indexed lookups, but dangerous when the outer side is much larger than estimated.

Hash Join

Builds a hash table from one input and probes it with the other. Often effective for equality joins over larger inputs. Memory pressure can force extra batches and I/O.

Merge Join

Consumes sorted inputs and advances through them together. It can be attractive when inputs are already ordered or efficient sorting is possible.

There is no universally “best” join type. Diagnose the evidence around cardinality, loops, timing, sort/hash work and available access paths rather than treating the node name as a verdict.

Deep dive: nested-loop amplification

Filters and discarded work

Filter: (customer_id = 42)
Rows Removed by Filter: 1,399,944
Actual Rows: 56

This tells you that the node did substantial work and rejected almost all of it. That can be a valuable clue, especially when the node is slow. But it does not prove an index is missing or that an index would be cheaper.

Filter

Applied to rows within a node.

Join Filter

Applied while evaluating a join and may expose wasted join combinations.

Deep dive: Rows Removed by Filter

BUFFERS and I/O

Buffers: shared hit=18240 read=912
         temp read=4096 written=4096

shared hit

PostgreSQL needed a shared buffer page and found it already cached, avoiding a physical read for that page.

shared read

Shared blocks had to be read into PostgreSQL's buffer cache. This helps identify I/O-heavy parts of the plan.

temp read / temp written

Temporary working data was read or written, commonly associated with sorts, hashes, materialization and similar operations.

Buffer counts are especially useful for distinguishing a plan that is CPU/cache heavy from one that is doing substantial storage I/O. If I/O timing is enabled, PostgreSQL can also expose time spent on reads and writes.

Sorts, memory and disk spills

Sort Method: external merge  Disk: 84240kB

A disk-backed sort is directly observable evidence that the operation did not stay entirely in its available memory. That can matter, but “increase work_mem” is not automatically the correct global answer.

Check before changing memory settings

• How many rows enter the sort?
• Can earlier filtering reduce the input?
• Can an index provide the required order?
• How many sorts/hashes can run concurrently?
• Is the spill large enough to matter in measured execution time?

Deep dive: PostgreSQL disk sorts

Parallel execution

Gather
  Workers Planned: 4
  Workers Launched: 3
  -> Parallel Seq Scan on events

Parallel plans can divide work across workers. Compare workers planned vs launched, inspect the work under Gather/Gather Merge, and remember that parallelism has coordination overhead. More workers are not automatically better.

Planning Time vs Execution Time

Planning Time:  3.421 ms
Execution Time: 812.774 ms

Planning Time is the time PostgreSQL spent generating and optimizing the plan after parsing/rewriting. Execution Time is the executor's total measured runtime and includes executor startup/shutdown and trigger execution that occurs during the statement. They diagnose different parts of the query lifecycle.

Triggers and JIT

Trigger time

A query can appear to have a cheap plan while trigger execution dominates total runtime. Always reconcile node timings with total Execution Time when write statements or trigger-heavy workloads are involved.

JIT

PostgreSQL can report JIT generation, inlining, optimization and emission time. On short queries, JIT setup overhead can sometimes be large relative to useful execution work.

Worked example: follow the evidence

Nested Loop  (cost=12.00..310.00 rows=12)
             (actual time=0.20..812.00 rows=48,391 loops=1)
  -> Seq Scan on customers
       (actual rows=48,391 loops=1)
       Filter: (status = 'active')
  -> Index Scan on orders_customer_idx
       (actual time=0.006..0.012 rows=4 loops=48,391)

Planning Time: 2.9 ms
Execution Time: 821.4 ms

Step 1 — Start with total runtime

821 ms establishes that this query is worth investigating in its workload context.

Step 2 — Compare estimate with reality

The Nested Loop expected 12 rows but produced 48,391. That is a major cardinality mismatch.

Step 3 — Notice repetition

The inner Index Scan ran 48,391 times. Each lookup is individually cheap, but repetition amplifies the work.

Step 4 — Avoid jumping to a fix

The plan proves the estimate was wrong and the inner scan repeated heavily. It does not, by itself, prove whether the best fix is statistics, an index change, SQL restructuring, schema changes, or something else.

Step 5 — Check next

Investigate the first bad estimate, statistics freshness, data skew and the selectivity of the predicates feeding the Nested Loop before changing the join strategy indirectly.

A practical EXPLAIN ANALYZE checklist

Check total Execution Time and decide whether the query is actually a problem.

Find slow nodes and nodes executed many times.

Compare Plan Rows with Actual Rows and locate the first major mismatch.

Read loops before judging apparently tiny per-node timings.

Inspect Rows Removed by Filter / Join Filter for discarded work.

Look for disk-backed sorts, hash batching and temporary I/O.

Use BUFFERS to identify cache and storage pressure.

Inspect scan and join choices in context — node names alone are not problems.

Compare Workers Planned with Workers Launched in parallel plans.

Reconcile plan-node time with trigger/JIT and overall Execution Time.

Change the smallest thing supported by evidence, then measure again.

You can do this manually. PlanCheck does the first pass for you.

Paste PostgreSQL EXPLAIN output in text or JSON. PlanCheck runs deterministic checks locally, points to the evidence behind each finding, and tells you what remains unknown before you make a change.

Analyze my EXPLAIN plan

Technical reference: PostgreSQL documentation for EXPLAIN and Using EXPLAIN. PlanCheck intentionally keeps recommendations bounded by what an execution plan can actually demonstrate.

PostgreSQL EXPLAIN documentation ↗Using EXPLAIN ↗