← lasīt vairāk

Reading the Query Plan: EXPLAIN ANALYZE Without Guesswork

Slow query in hand, most teams guess at a fix: add an index, cache it, buy RAM. Postgres already wrote down the answer. How to run and read EXPLAIN ANALYZE, and what to look for in the output.

The fix you picked before reading the plan

A query is slow, and the instinct is to act: add an index, wrap it in a cache, threaten the server with more RAM. We've shipped all three fixes for queries that didn't need them, and watched the query stay slow. The plan Postgres produces before every execution usually names the actual problem in two or three lines. Reading it is a skill of maybe an hour, and it pays for itself the first time someone suggests upgrading production hardware to fix a query.

What to run

EXPLAIN shows you the plan Postgres intends to run, with cost estimates it has never measured. EXPLAIN ANALYZE runs the statement for real and shows you the plan it actually used, with real times and row counts.

There's one trap worth knowing on day one: EXPLAIN ANALYZE on an UPDATE or DELETE executes the change. It is not a dry run. If you want to inspect the plan of a data-changing statement, wrap it:

BEGIN;
EXPLAIN ANALYZE DELETE FROM webhook_deliveries WHERE created_at < now() - interval '90 days';
ROLLBACK;

The rollback costs a bit of vacuum work and nothing else. Running that statement bare in ANALYZE mode has deleted real data for people, and it never made a blog post about it.

The first three things to look at

Read a plan top to bottom, and check three things before anything else.

Rows: estimate vs actual. Every node shows estimated rows and, under ANALYZE, actual rows. When the estimate says 8,000 and the actual says 312,000, the planner is flying blind: it will keep choosing plans that fit its wrong guess, not your data. The usual fix is one command, ANALYZE orders;, which refreshes the statistics the estimates come from. Autovacuum keeps them reasonably current, but a table that just absorbed a bulk load runs on stale numbers until it fires, and the planner will keep refusing a perfectly good index until the numbers catch up.

Scan types. A Seq Scan isn't automatically bad. On a table with 500 rows, it's the fastest thing available. A sequential scan over millions of rows while a WHERE clause filters most of them out is the sign that the index either doesn't exist or can't be used. If the index exists but the plan ignores it, check whether the column got wrapped in a function: WHERE date(created_at) = ... cannot use a plain index on created_at, because the predicate is on date(created_at), not the column. Compare against a range condition instead, and the index comes back to life.

Time concentrated in one node. Real plans report the actual time per node. Ninety percent of your query sitting in one node tells you exactly where to work. A Sort node with "Sort Method: external merge Disk: 180MB" means the sort didn't fit in work_mem and spilled to disk; either sort less data or accept that raising work_mem is a temporary patch. A Nested Loop where the outer side is a million rows means the planner believed the inner probe would be cheap, which loops straight back to the row estimates.

What a plan looks like when it's fine

EXPLAIN ANALYZE
SELECT id, status FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 10;

Limit (actual time=0.21..0.23 rows=10)
  -> Index Scan Backward using idx_orders_customer_created on orders
       Index Cond: (customer_id = 42)
       (actual rows=10)

Backward index scan on a composite index over (customer_id, created_at), ten rows, no sort node because the index hands the rows over already ordered. Sub-millisecond total. Nothing to fix here; not every plan is a problem. The habit that pays is a periodic plan check on your ten hottest queries, not paranoia about every one of them.

From plan to action

  • Estimates badly wrong on a hot table: run ANALYZE, or bump default_statistics_target for that column if the distribution is skewed.
  • Seq scan on a hot filter: create the index with the filter's columns in the order the query uses them, and verify with the same EXPLAIN ANALYZE you started with.
  • Function on the indexed column: rewrite the predicate as a range, or create a matching expression index if the function is genuinely what you mean.
  • Sorts spilling to disk: either index the sort order away or reduce the data before sorting.

One more habit: keep pg_stat_statements on so the slow query finds you, instead of a customer doing it.