EXPLAIN describes the plan PostgreSQL intends to execute. EXPLAIN ANALYZE executes that plan and adds measurements. Comparing them is useful only after interpreting what each number counts. An apparent mismatch can reflect stale estimates, repeated execution, or a parent that stopped requesting rows. Matching cardinalities strengthen one part of the model; they do not prove the chosen plan is the fastest possible plan.

Read a node in its context

Estimated rows describes the output of a node, not necessarily all tuples it examines. A scan may examine thousands of tuples and emit ten after filtering. Startup cost and total cost are planner units, not milliseconds. Parent costs include child work, so adding every node’s cost double-counts the tree. Actual parent timing is also generally inclusive rather than a list of independent charges.

For repeatedly executed nodes, actual row counts and timings are reported per execution, averaged across loops. A node returning two rows with three loops supplied six rows in total. Filter-removal counts are likewise per-loop averages, so rounding can matter. Start at the leaves and follow where the observed population first differs materially from the estimate, while respecting those execution boundaries.

A LIMIT, EXISTS, or another early-stopping parent may leave a child only partly consumed. The child’s estimate can describe its full potential output while the actual count describes only rows requested. That is not automatically a statistics error. Sorts can also read their entire input while delivering only the requested top rows.

When it breaks

Running EXPLAIN ANALYZE on a write actually performs the write. A rollback can protect transactional table changes, but does not reverse every possible side effect, such as sequence advancement or an external action invoked by a function. Choose a disposable fixture or understand the complete statement before measuring it.

Another mistake is diagnosing storage from BUFFERS alone. A shared-buffer hit avoids a PostgreSQL buffer miss; a reported read can still be served by the operating system cache. Buffer counts describe block accesses, not distinct blocks or guaranteed physical-device reads. Likewise, accurate rows do not exclude lock waits, memory spills, configuration problems, or an access path that the search did not select.

Check it quickly

Use PostgreSQL 18 and psql. The transaction creates three parents and thirty children, then compares a repeated lateral lookup with a simple top-three query. All data is temporary and the final rollback removes the fixture.

\set ON_ERROR_STOP on
BEGIN;
CREATE TEMP TABLE parents (id integer PRIMARY KEY);
CREATE TEMP TABLE children (parent_id integer,value integer);
INSERT INTO parents VALUES (1),(2),(3);
INSERT INTO children SELECT 1+(g-1)%3,g FROM generate_series(1,30) AS g;
ANALYZE parents;
ANALYZE children;
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT p.id,c.value FROM parents p CROSS JOIN LATERAL (
  SELECT value FROM children WHERE parent_id=p.id ORDER BY value DESC LIMIT 2
) c;
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT value FROM children ORDER BY value DESC LIMIT 3;
SELECT p.id,c.value FROM parents p CROSS JOIN LATERAL (
  SELECT value FROM children WHERE parent_id=p.id ORDER BY value DESC LIMIT 2
) c ORDER BY p.id,c.value DESC;
DO $$ BEGIN
  IF (SELECT array_agg(value ORDER BY parent_id,value DESC) FROM (
       SELECT p.id AS parent_id,c.value FROM parents p CROSS JOIN LATERAL (
         SELECT value FROM children WHERE parent_id=p.id ORDER BY value DESC LIMIT 2
       ) c
     ) s) IS DISTINCT FROM ARRAY[28,25,29,26,30,27] THEN RAISE EXCEPTION 'wrong lateral result'; END IF;
END $$;
ROLLBACK;

The PostgreSQL 18.6 lateral plan used a nested loop. Its inner Limit reported actual rows of two and loops=3, producing six joined rows. The child scan returned ten matching rows per loop and removed twenty, so it examined all thirty children on each of three passes. Read those numbers together instead of concluding that the whole query inspected only ten rows.

The final ordered results were parent one with values 28 and 25, parent two with 29 and 26, and parent three with 30 and 27. The assertion checks those memberships independently of the plan’s appearance. Correct answers remain required even if another PostgreSQL version chooses different access nodes.

For the separate top-three query, the sort estimated thirty output rows but emitted three because its parent stopped there. Its scan still read thirty rows. The fixture had just been analyzed, and this mismatch demonstrates consumption semantics rather than evidence that refreshing statistics would fix anything.

Measure the question you need answered

TIMING OFF keeps actual row counts and overall execution time while avoiding repeated per-node clock reads. BUFFERS adds useful context about page access, and JSON output can support systematic comparison. Neither option makes the instrumented query identical to application latency: client transmission, rendering, connection setup, and concurrent load need their own observations. Preserve the parameters and relevant session settings when comparing plans, then explain a change in terms of rows processed, loops, spills, or waits rather than declaring every new node name a regression.

Sources