Chapter 39Lesson 3

Reading EXPLAIN ANALYZE

How to read a Postgres query plan with EXPLAIN ANALYZE, so you can prove why a query is slow and confirm an index fixed it.

The invoices list page you built shipped fast: fifty seeded rows, instant render, everyone moves on. Then a customer grows the table to two hundred thousand invoices, and the same page takes three seconds to load.

You already have a suspect. Earlier in this chapter you added a composite index for exactly this query, and you ruled out N+1 since this is one query, not two hundred round-trips. But how do you know the index is the fix, and once you ship it, how do you confirm Postgres uses it rather than scanning the whole table?

Stop guessing and ask Postgres directly. One idea frames the lesson: a query says what you want, the planner decides how to get it, and EXPLAIN ANALYZE shows that how with real numbers attached.

The planner decides how, EXPLAIN shows the decision

A SQL query is declarative . When you write select * from invoices where status = 'pending' order by created_at desc limit 20, you describe the result: twenty pending invoices, newest first. You say nothing about how to get it: scan every row and discard the rest, or walk an index straight to the pending ones already in order?

That choice belongs to the query planner . It lists the access paths available (scan the table, walk this index or that one, pick a join algorithm), estimates the cost of each, and chooses the cheapest. This is the machinery behind indexes: an index changes how the engine reaches the rows, not what comes back, so two plans for one query return identical results at wildly different speeds.

EXPLAIN shows that choice. Prefix any query with it and Postgres prints the plan it would run, without running it:

explain select * from invoices where status = 'pending';

The plan comes annotated with cost estimates , the planner’s guess at each step’s expense. It returns instantly, because the query never runs. But a guess is only half the picture.

EXPLAIN ANALYZE is the other half. It runs the query and prints the same plan with real timings and row counts measured during execution, sitting right beside the estimates:

explain analyze select * from invoices where status = 'pending';

This is the one to reach for. The gap between estimate and actual is, more often than you’d think, the bug: a plan that expects ten rows and returns a million made its decisions on bad information.

One field misleads everyone at first. Cost estimates look like cost=0.00..25.40, but they are not milliseconds; cost is an arbitrary planner unit, good only for comparing nodes or plans against each other. Real time lives in a separate actual time= field, which only ANALYZE adds.

select * from invoices where status = 'pending';

Declarative: it names the result, not how to reach it.

Seq Scan on invoices  (cost=0.00..25.40 rows=6 width=72)
  Filter: (status = 'pending')

The plan the planner chose, with cost estimates. The query did not run.

Seq Scan on invoices  (cost=0.00..25.40 rows=6 width=72)
                      (actual time=0.018..0.124 rows=6 loops=1)
  Filter: (status = 'pending')
  Rows Removed by Filter: 194
Planning Time: 0.071 ms
Execution Time: 0.146 ms

The same plan, executed. Estimates and actuals side by side, and the query really ran.

Running it: copy the SQL, prefix it, read the output

Getting a plan is easy; reading it is the skill, and that fills the rest of this lesson.

Turn on logger: true on your Drizzle client, run the page, and copy the exact SQL Drizzle logged. Paste it into a SQL console such as Neon’s SQL editor, psql, or Drizzle Studio, and prefix it with explain (analyze, buffers). Drizzle builds the query; you diagnose the raw SQL it emits, no ORM in the loop.

For a one-off probe in a script, you can run a plan from application code:

const plan = await db.execute(
  sql`explain (analyze, buffers) select * from invoices where status = 'pending'`,
);

But you hand-write the SQL, and what comes back is rows of plan text, not a typed result. Fine for a quick probe; the console is cleaner for the interactive diagnosis you’ll do far more often.

Note the (analyze, buffers) in both. BUFFERS adds to every node a count of how many pages came from RAM versus disk, the best single signal for whether a query is slow because it missed the cache. Postgres 18, your Neon production target, turns BUFFERS on automatically with ANALYZE; older Postgres, including the one running the exercises below, does not. Rather than track which version does which, always write (analyze, buffers): a no-op where buffers are already on, correct everywhere else.

EXPLAIN ANALYZE executes the query. On a SELECT that’s harmless. On an UPDATE, DELETE, or INSERT it performs the write, so the rows really change. To get a plan for a destructive statement without committing it, wrap it in a transaction and roll the transaction back:

begin;
explain (analyze, buffers) delete from invoices where status = 'draft';
rollback;

Or drop ANALYZE and use plain EXPLAIN for an estimate-only plan that never touches the data.

Reading the plan tree from the deepest node up

A plan is a tree printed as indented text. Each line is a node: an operation like “scan this table” or “join these two inputs”, followed by its estimates and, with ANALYZE, its actuals. Indentation is depth , so a node indented under another is its child.

Execution runs opposite to how you read. The deepest, most-indented node runs first; its output feeds the parent one level up, and results flow upward to the root at the top, which runs last and is the result you receive. Reading the top line and stopping gives you the finish, not the start.

To read a plan, follow this recipe:

Find the deepest node. That’s where data first enters the query, through a table scan or an index walk.
Walk outward and up one level at a time, watching actual rows at each node as rows get filtered, joined, sorted, and aggregated.
Find the node where time concentrates, or where the row count explodes or collapses unexpectedly. That node is your suspect.

The diagram below walks one real four-node plan in execution order, deepest first, then up.

↑ results flow upward — deepest node runs first

Sort (actual time=2.31..2.34 rows=20 loops=1)

Sort Key: i.created_at DESC

-> Hash Join (actual time=0.42..2.10 rows=180 loops=1)

Hash Cond: (i.customer_id = c.id)

-> Seq Scan on invoices i (actual time=0.01..1.40 rows=180 loops=1)

-> Hash (actual time=0.30..0.30 rows=40 loops=1)

-> Seq Scan on customers c (actual time=0.01..0.18 rows=40 loops=1) runs first

Deepest node, so it runs first. Postgres reads all 40 customer rows. Nothing above has happened yet.

↑ results flow upward — deepest node runs first

Sort (actual time=2.31..2.34 rows=20 loops=1)

Sort Key: i.created_at DESC

-> Hash Join (actual time=0.42..2.10 rows=180 loops=1)

Hash Cond: (i.customer_id = c.id)

-> Seq Scan on invoices i (actual time=0.01..1.40 rows=180 loops=1)

-> Hash (actual time=0.30..0.30 rows=40 loops=1) now running

-> Seq Scan on customers c (actual time=0.01..0.18 rows=40 loops=1) ✓ done

Its parent, the Hash node, takes those 40 rows and builds a hash table keyed by customer id — ready to be probed.

↑ results flow upward — deepest node runs first

Sort (actual time=2.31..2.34 rows=20 loops=1)

Sort Key: i.created_at DESC

-> Hash Join (actual time=0.42..2.10 rows=180 loops=1) now running

Hash Cond: (i.customer_id = c.id)

-> Seq Scan on invoices i (actual time=0.01..1.40 rows=180 loops=1) now running

-> Hash (actual time=0.30..0.30 rows=40 loops=1) ✓ done

-> Seq Scan on customers c (actual time=0.01..0.18 rows=40 loops=1) ✓ done

Now the join runs: Postgres scans the 180 invoices and probes the hash for each one’s customer. Output: 180 joined rows, flowing up.

↑ results flow upward — deepest node runs first

Sort (actual time=2.31..2.34 rows=20 loops=1) runs last

Sort Key: i.created_at DESC

-> Hash Join (actual time=0.42..2.10 rows=180 loops=1) ✓ done

Hash Cond: (i.customer_id = c.id)

-> Seq Scan on invoices i (actual time=0.01..1.40 rows=180 loops=1) ✓ done

-> Hash (actual time=0.30..0.30 rows=40 loops=1) ✓ done

-> Seq Scan on customers c (actual time=0.01..0.18 rows=40 loops=1) ✓ done

Last, the root sorts those rows by created_at descending and returns the final 20. The top line is where execution ends, not where it begins.

Now run a real plan yourself. The sandbox below has a small seeded table: run it as-is, then read the output and answer which line ran first.

Run this as-is to see a real query plan, then read it top to bottom. Which line ran first, the top line or the most-indented bottom one? (Remember: the deepest node executes first, and its output flows up to the root.)

View schema & data

CREATE TABLE invoices (
  id int PRIMARY KEY,
  organization_id int NOT NULL,
  status text NOT NULL,
  amount_cents int NOT NULL
);
INSERT INTO invoices (id, organization_id, status, amount_cents) VALUES
  (1, 1, 'paid', 12000),
  (2, 1, 'pending', 4500),
  (3, 1, 'paid', 9900),
  (4, 1, 'pending', 30000),
  (5, 1, 'draft', 1500),
  (6, 1, 'paid', 7200);

The four plan-node numbers to read

A plan node carries a dozen fields, but four tell you almost everything.

Index Scan using idx_invoices_org_created on invoices
  (cost=0.42..18.30 rows=12 width=72) (actual time=0.015..0.040 rows=180 loops=200)
  Buffers: shared hit=120 read=8

Estimated rows is the planner’s guess from table statistics; actual ... rows is what came back. When they differ by more than about 10×, the planner chose its plan on bad information, usually stale stats. Here 12 versus 180 is a 15× miss, a red flag.

Index Scan using idx_invoices_org_created on invoices
  (cost=0.42..18.30 rows=12 width=72) (actual time=0.015..0.040 rows=180 loops=200)
  Buffers: shared hit=120 read=8

This node ran 200 times, and the displayed time and rows are per loop. So a node that looks cheap at 0.040ms really costs about 8ms and touches 36,000 rows. This is the most-missed number on the page, the database-side cousin of N+1.

Index Scan using idx_invoices_org_created on invoices
  (cost=0.42..18.30 rows=12 width=72) (actual time=0.015..0.040 rows=180 loops=200)
  Buffers: shared hit=120 read=8

Startup time before the first row (0.015), then total time to the last row (0.040), both per loop. Read it with loops=; alone it understates a looped node.

Index Scan using idx_invoices_org_created on invoices
  (cost=0.42..18.30 rows=12 width=72) (actual time=0.015..0.040 rows=180 loops=200)
  Buffers: shared hit=120 read=8

120 pages served from RAM (hit), 8 fetched from disk (read), again per loop. On a query that should be hot, a lot of read means the data doesn’t fit in cache or the index isn’t covering.

1 / 1

A wildly wrong estimate means the planner’s statistics are out of date. Refresh them with ANALYZE and run the plan again.

The node types you’ll meet

Postgres has dozens of plan nodes; a web app meets only these, grouped by what they do.

Scans bring rows into a plan.

Seq Scan reads every row in the table. Fine when small; on a large table it warns that a WHERE clause should have hit an index.
Index Scan walks a B-tree to find matching keys, then fetches those rows. What a selective predicate produces once the right index exists.
Index Only Scan answers from the index alone, never touching the table, because the index holds every column the query needs. That’s a covering index .
Bitmap Index Scan with Bitmap Heap Scan combines one or more indexes through a bitmap before fetching rows. The planner picks it for medium-selectivity or multi-predicate queries, where a plain index scan would jump around the table too much.

Joins combine two inputs; the node name is the algorithm chosen.

Nested Loop probes the inner input for each outer row. Wins on a small outer set, costly when the inner side is an unindexed scan run many times, so watch its loops=.
Hash Join builds a hash table from one input, probes it with the other. The workhorse for medium-to-large equality joins.
Merge Join zips together two inputs already sorted on the join key.

Post-processing nodes shape gathered rows.

Sort orders rows. Sort Method: external merge Disk: … means it spilled to disk and ran slow.
Aggregate, HashAggregate, and GroupAggregate are the fold behind count, sum, and group by.
CTE Scan and Subquery Scan read the results of a CTE or subquery.

From plan symptom to fix

Reading a plan is pattern recognition: you spot a shape, then reach for the fix you already know. Each reads the same way, what you see → what it means → the fix, and the walker below sorts between them. The shapes to know are a Seq Scan with a large Rows Removed by Filter , a Sort eating most of the time, and a Nested Loop with a high loops= on an unindexed inner scan. One symptom hides outside any single node: estimates off the actuals by orders of magnitude mean stale statistics, so run ANALYZE <table>; and re-check before changing anything else.

Your query is slow — what does the plan show?

Now watch a plan flip. Earlier you ran EXPLAIN on where status = 'pending' against a tiny table and read a Seq Scan. The sandbox below runs the same query against five thousand rows that already carry an index on status. Run it, read the plan top to bottom, and watch the access path change.

Run this as-is. It's the same selective query you ran earlier, but now against 5,000 rows, with an index on status declared in the seed (open 'View schema & data' to see it). Read the plan top to bottom: which node sits at the root now, and what happened to the Rows Removed by Filter line you saw before?

View schema & data

CREATE TABLE invoices (
  id int PRIMARY KEY,
  organization_id int NOT NULL,
  status text NOT NULL,
  amount_cents int NOT NULL
);
INSERT INTO invoices (id, organization_id, status, amount_cents)
SELECT
  g,
  (g % 50) + 1,
  CASE WHEN g % 97 = 0 THEN 'pending' ELSE 'paid' END,
  (g % 1000) * 100
FROM generate_series(1, 5000) AS g;
CREATE INDEX idx_invoices_status ON invoices (status);
ANALYZE invoices;

Same query, same data: the first plan in this lesson ran where status = 'pending' with no index, so it was a Seq Scan whose Rows Removed by Filter counted every discarded row. Now the plan starts with Index Scan using idx_invoices_status and the Rows Removed by Filter line is gone — the engine walked the B-tree straight to the pending rows instead of reading all five thousand. That swap in access path is the whole point of declaring the index.

The one-change diagnostic loop

Everything in this lesson collapses into one discipline.

Measure. Run EXPLAIN (ANALYZE, BUFFERS) on the slow query and read it top to bottom.
Hypothesize. Name which node is expensive and why: “the Sort dominates because no index produces this order.”
Change exactly one thing. One index, one rewritten clause, one tightened WHERE. One.
Re-run and confirm. Run it again and check that the node you predicted changed the way you predicted. Repeat if it’s still slow.

Step 3 is what matters most. Change two things and you lose the signal: you can’t say which one helped, and you may have shipped a useless index that taxes every write for no benefit. That’s the difference between a fix and cargo-cult tuning.

Measure against production-shaped data, too. The planner switches strategy as a table grows, so a Seq Scan that wins at a hundred rows loses to an Index Scan at a hundred thousand. Diagnose against a toy table and you’ll “fix” problems that don’t exist and miss the ones that do. Use a realistically seeded volume, or a production-shaped branch on Neon.

The cursor-pagination query, before and after the index

The query is the cursor-pagination one you wrote: a page of invoices for an organization, newest first, with a compound cursor and a limit. EXPLAIN runs on SQL, not the Drizzle builder, so the SQL comes out roughly like this:

select * from invoices
where organization_id = $1
  and (created_at < $2 or (created_at = $2 and id < $3))
order by created_at desc, id desc
limit 21;

Its index, idx_invoices_org_created_at_id, covers (organization_id, created_at desc, id desc): tenant column first, then the sort key and tiebreaker in the exact direction the query orders. The two tabs show the plan against a populated table, before the index and after.

Before the index
After the index

Limit  (actual time=88.2..88.3 rows=21 loops=1)
  ->  Sort  (actual time=88.2..88.2 rows=21 loops=1)
        Sort Key: created_at DESC, id DESC
        Sort Method: external merge  Disk: 9512kB
        ->  Seq Scan on invoices  (actual time=0.02..71.4 rows=41204 loops=1)
              Filter: (organization_id = 1)
              Rows Removed by Filter: 158796
              Buffers: shared hit=180 read=2304
Execution Time: 90.1 ms

Two suspects, reading bottom-up. The Seq Scan reads all 200,000 rows and discards 158,796 (Rows Removed by Filter). The Sort then orders the surviving 41,204 from scratch and spills to disk (external merge): 2,304 pages read, 90ms.

Limit  (actual time=0.05..0.34 rows=21 loops=1)
  ->  Index Scan using idx_invoices_org_created_at_id on invoices
        (actual time=0.04..0.31 rows=21 loops=1)
        Index Cond: (organization_id = 1)
        Filter: ((created_at < $2) OR ((created_at = $2) AND (id < $3)))
        Rows Removed by Filter: 3
        Buffers: shared hit=24 read=0
Execution Time: 0.39 ms

One node. The Index Scan walks idx_invoices_org_created_at_id for organization_id = 1; because the index already holds rows in created_at desc, id desc order, the Limit stops it after 21 and the engine never reads the rest. The cursor’s OR can’t narrow a B-tree, so it rides along as a Filter that skips the few rows newer than the cursor (Rows Removed by Filter: 3). No Sort node, zero disk reads, 0.39ms: over two hundred times faster.

The two wins are the disk reads (read=2304 to read=0) and the vanished Sort. That is why cursor pagination and its composite index belong together: the index doesn’t just speed the lookup, it deletes a stage of work.

Beyond the console

In production, tools run EXPLAIN for you. auto_explain logs the plan of any query slower than a threshold you set (auto_explain.log_min_duration = '500ms'), so slow queries record their own plans. Hosted tools like pganalyze and Datadog database monitoring trend plans over time, letting you watch a query degrade across a deploy. You’ll run this lesson’s commands in Drizzle Studio or the Neon SQL editor.

External resources

Postgres docs — Using EXPLAIN

postgresql.org

The canonical reference for every plan node and EXPLAIN option.

pev2 — Postgres EXPLAIN visualizer

explain.dalibo.com

Paste a plan, get a navigable visual tree — invaluable for large plans.

Drizzle docs — Magic sql operator

orm.drizzle.team

How to run raw EXPLAIN with db.execute and the sql template.