cd ..

$ cat ~/field-notes/postgres-explain-analyze-guide.md

Reading Postgres EXPLAIN Without Guessing

A query takes two seconds. Someone suggests an index. Someone else suggests more memory. Both suggestions might help, but neither explains where the two seconds went.

The query plan is the place to start. It describes the work Postgres expects to perform; execution statistics show what happened when it tried.

Capture the Query You Actually Run

Use realistic parameters and representative data. A plan for a customer with ten orders may tell you little about a customer with ten million.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC, id DESC
LIMIT 50;

Plain EXPLAIN plans a statement. Adding ANALYZE executes it, including writes and functions with side effects. Start with plain EXPLAIN when execution would be unsafe; use a disposable copy for experiments. PostgreSQL documents this distinction in its EXPLAIN command reference.

Do not treat a transaction rollback as a universal safety wrapper: it cannot undo an external action performed by a function.

Find Where the Work Expands

Consider this deliberately simplified, hypothetical plan fragment:

Index Scan using orders_customer on orders
  (cost=0.43..120.00 rows=20 width=16)
  (actual time=0.04..180.00 rows=50000 loops=1)

The planner expected 20 rows and got 50,000. That mismatch is a useful lead. A join strategy chosen for 20 rows may become expensive at 50,000.

Ask what the planner could not know. Did the table change substantially? Are a few customers much larger than the others? Do two filter columns correlate? Inspect statistics before adding an index that merely hides an estimation problem.

ANALYZE orders;

Run that as a considered maintenance action. It updates statistics; it does not reorganize the table or guarantee a different plan.

Read Loops Alongside Rows

A cheap lookup can become the expensive part of a join when it runs thousands of times. Actual row counts and times are reported per execution of a repeatedly executed node. Use loops to understand its cumulative work, and avoid adding parent and child times together: their work overlaps. These conventions are explained in PostgreSQL's plan-reading guide.

For an application example, imagine loading 50,000 orders and looking up one customer row per order. Optimizing that customer lookup by a fraction of a millisecond may matter less than reducing the orders entering the join.

Look for the first place a small expected result becomes a large actual result. The slowest-looking parent can simply be waiting for an unexpectedly large child.

Filters and Sorts Explain Discarded Work

If a query examines many rows and returns 50, ask how it found those 50. A filter applied after a broad scan can throw away most of the work. A sort may then order the survivors just to keep a short prefix.

For the example query, an index matching both selection and ordering is a reasonable candidate:

CREATE INDEX orders_customer_recent
ON orders (customer_id, created_at DESC, id DESC);

Test it on representative data before choosing a production rollout. The index also consumes storage and adds work to writes. See the separate guide to database indexes for those tradeoffs.

Do not declare every sequential scan a bug. If the application asks for most of a table, sequential access may be the sensible plan.

Measure the Whole Request Too

Buffers help distinguish cached access from reads that Postgres had to request below its shared buffer cache. A reported read is not proof of a physical disk access; the operating system may satisfy it. Sort output can also reveal temporary disk work. Use the buffer reporting reference to interpret the fields for your server version.

The application can still be slow after the database finishes: connection-pool waiting, network transfer, decoding large JSON values, and rendering all sit outside this plan.

Record the original query, parameters, plan, and request latency. Change one thing, repeat under comparable conditions, and keep the result only if it improves the workload that matters. A plan is evidence for an experiment, not a scoreboard where every node must say "Index Scan."