PostgreSQL EXPLAIN ANALYZE: read plans without guessing

PostgreSQL EXPLAIN shows how the planner intends to run a query. EXPLAIN ANALYZE executes it and adds observations from that run. Reading the plan helps you distinguish an inaccurate row estimate, repeated work and an expensive access path before deciding what to change.

This walkthrough uses a disposable table on PostgreSQL 18.6. We compare the same selective query before and after one index, then change the predicate and examine a repeated subplan. The results are observed plan behavior on synthetic data, not a production speed claim.

Choose a query and a safe test boundary

Start with workload evidence, such as the report in our pg_stat_statements guide, to choose a query worth investigating. Capture its real parameter values, role and database context. A plan for an invented parameter can answer a different question from the slow request. See the PostgreSQL extension reference for configuration details.

Use a separate disposable database for this exercise. If you are choosing the environment, our PostgreSQL hosting guide separates managed-service responsibilities from running your own server. Do not paste the fixture or cleanup commands into a production database.

The EXPLAIN reference states that ANALYZE executes the statement. SELECT results are discarded from the output, but execution and side effects still occur. Even a rollback is not a universal undo mechanism: PostgreSQL sequence advances are not rolled back. Review functions and external effects before analyzing unfamiliar SQL.

Create a small, reproducible fixture

Connect directly with psql to a fresh test database. Keep this session open and confirm its identity before applying these session-only lab settings:

SELECT version(), current_database(), current_user;
SET statement_timeout = '10s';
SET lock_timeout = '2s';
SET max_parallel_workers_per_gather = 0;

The timeouts bound statements and lock waits for this exercise; they are not production recommendations. PostgreSQL’s timeout settings define their scope. Parallel workers are disabled here to keep the example plans easier to follow, not to improve performance.

Stop if plan_lab already exists. In the disposable database, create the fixture once:

CREATE SCHEMA plan_lab;
CREATE TABLE plan_lab.events (
    id integer PRIMARY KEY,
    account_id integer NOT NULL,
    amount integer NOT NULL,
    note text NOT NULL
);
INSERT INTO plan_lab.events
SELECT n, n % 1000, n % 100, repeat('x', 80)
FROM generate_series(1, 200000) AS g(n);
ANALYZE plan_lab.events;

This creates 200,000 rows and 1,000 account IDs, with 200 rows per account. The primary key indexes id; initially there is no index on account_id. The separate ANALYZE command collects planner statistics. It is different from the ANALYZE option that executes a query inside EXPLAIN.

Read the estimated plan first

EXPLAIN
SELECT id, amount
FROM plan_lab.events
WHERE account_id = 42;

Our plain EXPLAIN output was:

Seq Scan on events  (cost=0.00..5577.00 rows=199 width=8)
  Filter: (account_id = 42)

The planner chose a sequential scan, with an estimate of 199 output rows. width=8 estimates the average output-row width in bytes. The cost pair represents startup and total planner cost, not milliseconds. Those units depend on the planner cost settings.

Read rows as rows emitted by the node, not the entire number examined. This plan can estimate a small result while still visiting the whole table. A parent such as LIMIT may also stop requesting rows before a child finishes its estimated work.

Compare estimates with actual rows

BEGIN READ ONLY;
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SETTINGS)
SELECT id, amount
FROM plan_lab.events
WHERE account_id = 42;
ROLLBACK;

The read-only transaction is an additional boundary for the measured SELECT. It does not eliminate CPU, I/O or lock use. Our lab also confirmed that read-only transaction mode rejects an analyzed UPDATE of this table. In our separate validation harness, the measured SELECT plans ran with a role granted SELECT access only. The steps in this article do not create or configure that role.

This excerpt retains the node and buffer lines from our first text capture; settings and timing summaries are omitted:

Seq Scan on events  (cost=0.00..5577.00 rows=199 width=8) (actual rows=200.00 loops=1)
  Filter: (account_id = 42)
  Rows Removed by Filter: 199800
  Buffers: shared hit=3077

The estimate of 199 was close to the observed 200 rows. The node examined the table and rejected 199,800 rows through the filter. That makes the access path the useful next question in this fixture; a large estimation error was not the issue.

TIMING OFF omits per-node clock measurements while retaining actual row counts and overall execution timing. SETTINGS reports relevant nondefault planner settings. We kept complete text and JSON captures in the test record. They came from separate executions of the same SQL, so cache conditions and buffer counts can differ between captures. The excerpts below use the text captures; the article focuses on counts and plan structure rather than a timing ratio.

Change one thing: add an account index

Finish the read-only transaction, then create this index in the test database:

CREATE INDEX events_account_idx ON plan_lab.events (account_id);

Run the same EXPLAIN ANALYZE block again. Our plan changed to:

Bitmap Heap Scan on events  (cost=5.84..635.32 rows=199 width=8) (actual rows=200.00 loops=1)
  Recheck Cond: (account_id = 42)
  Heap Blocks: exact=200
  Buffers: shared hit=200 read=2
  ->  Bitmap Index Scan on events_account_idx  (cost=0.00..5.79 rows=199 width=0) (actual rows=200.00 loops=1)
        Index Cond: (account_id = 42)
        Index Searches: 1
        Buffers: shared read=2

The Bitmap Index Scan found matching entries through events_account_idx. The Bitmap Heap Scan fetched the corresponding table rows. The top node still emitted 200 rows; a separate ordered-result comparison confirmed that the query’s results matched before and after index creation.

This is evidence that the new index changed this query’s access path. It does not establish that every query on the table became faster. Indexes consume space and add write work, and ordinary CREATE INDEX has locking implications on an active table. Production index deployment needs its own review.

Use buffers without double-counting

In the unindexed capture, the query reported 3,077 shared-buffer hits. In the indexed capture, the parent reported 200 hits and two reads. A hit means the needed block was already in PostgreSQL’s shared buffers. A read means it had to be brought into them; the operating system may still have cached it.

The parent’s buffer counts include its children. Adding the child’s two reads to the parent’s two reads would count that work twice. Likewise, buffer counts represent accesses, not a count of distinct physical disk blocks. Keep planning buffers separate from the execution tree.

The plan-reading guide explains inclusive node statistics. Read the tree from its row sources toward their consumers, then examine where work grows or estimates diverge. Do not sum every parent and child time into a supposed query total.

Check a broader predicate before declaring victory

BEGIN READ ONLY;
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SETTINGS)
SELECT id, amount
FROM plan_lab.events
WHERE account_id < 900;
ROLLBACK;

With the index still present, this query returned 180,000 rows and the planner chose a sequential scan. Its estimate was 179,403 rows. The predicate selects 90% of the fixture, so traversing an index and fetching scattered rows is a different tradeoff from finding one account.

A sequential scan is not a diagnosis by itself. PostgreSQL’s index-usage guidance recommends testing with representative data and statistics. Do not disable sequential scans merely to make an index appear in the plan.

Read rows together with loops

This correlated subquery makes the repeated work visible:

BEGIN READ ONLY;
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SETTINGS)
SELECT a.account_id,
       (SELECT sum(e.amount)
        FROM plan_lab.events AS e
        WHERE e.account_id = a.account_id) AS total_amount
FROM (VALUES (42), (43), (44)) AS a(account_id);
ROLLBACK;

Our text capture contained:

Values Scan on "*VALUES*"  (cost=0.00..1916.19 rows=3 width=12) (actual rows=3.00 loops=1)
  Buffers: shared hit=606
  SubPlan 1
    ->  Aggregate  (cost=638.71..638.72 rows=1 width=8) (actual rows=1.00 loops=3)
          Buffers: shared hit=606
          ->  Bitmap Heap Scan on events e  (cost=5.84..638.21 rows=200 width=4) (actual rows=200.00 loops=3)
                Recheck Cond: (account_id = "*VALUES*".column1)
                Heap Blocks: exact=600
                Buffers: shared hit=606
                ->  Bitmap Index Scan on events_account_idx  (cost=0.00..5.79 rows=200 width=0) (actual rows=200.00 loops=3)
                      Index Cond: (account_id = "*VALUES*".column1)
                      Index Searches: 3
                      Buffers: shared hit=6

The inner Bitmap Heap Scan reports actual rows=200.00 loops=3. That is an average of 200 emitted rows per execution, or 600 across the three executions. The Aggregate emits one row per execution, and the outer Values Scan emits three rows. These are different positions in the same tree, not conflicting totals.

The reported 606 buffer hits already cover the repeated work; do not multiply them by three. Per-loop averages, cumulative buffer counters and inclusive parent statistics need different interpretations. This example makes those distinctions visible without forcing a particular join strategy.

Investigate the first meaningful mismatch

If estimated and actual rows differ substantially in a real plan, work from the leaf nodes upward to locate the earliest large divergence. Check data freshness, skew and correlated filters before changing memory or planner switches. The planner-statistics documentation explains why single-column statistics may not capture relationships between columns. Increasing statistics targets or adding extended statistics should follow a specific observation.

Reproduce the application’s parameterization too. PostgreSQL can choose generic or custom prepared plans; a literal query pasted into psql may use a different plan. Our PgBouncer guide covers the separate session and prepared-statement considerations when pooling connections.

Then test one change, preserve equivalent results and repeat under comparable conditions. EXPLAIN execution time is not end-to-end application latency: network transfer and application processing are outside this measurement, and default EXPLAIN behavior does not serialize the discarded result. Repeated runs also warm caches. Record versions, settings, parameters, statistics and cache conditions alongside the plan.

Clean up the fixture

DROP TABLE plan_lab.events;
DROP SCHEMA plan_lab;
RESET statement_timeout;
RESET lock_timeout;
RESET max_parallel_workers_per_gather;

Run cleanup only in the same disposable database. It removes the named table and schema and resets the session settings. Our isolated PostgreSQL 18.6 lab passed ten checks covering row counts, access paths, unchanged results, repeated subplans, the read-only boundary and cleanup. The server was stopped afterward. No production throughput, cloud performance, prepared-plan behavior or concurrent workload was tested.