skip to content

Choosing a Plan

PostgreSQL's planner costs candidate plans from table statistics and cost settings, and EXPLAIN shows what it chose. Interviewers test whether you can read a plan and fix a bad estimate.

part ofPostgreSQLread it →
on this pageshow

explore

questions

15

In PostgreSQL, what does EXPLAIN ANALYZE do to an UPDATE or DELETE, and how do you measure one without changing your data?

level: juniorimportance: must knowfreq 64%

answer

  1. it is not a dry run
  2. BEGIN, EXPLAIN ANALYZE, ROLLBACK
  3. trigger lines below the plan
  4. what ROLLBACK cannot take back

basics

~20 s

EXPLAIN ANALYZE really executes an UPDATE or DELETE and only discards its output, so in autocommit the change commits. Wrap it in BEGIN and ROLLBACK: the data change is undone, but the locks, WAL and any sequence values drawn are not.

solid answer

~50 s

`EXPLAIN ANALYZE` is not a dry run: PostgreSQL executes the statement, throws away its result rows and prints the plan with actual timings. In autocommit mode an `EXPLAIN ANALYZE DELETE` deletes for real. To measure safely I run `BEGIN; EXPLAIN (ANALYZE, BUFFERS, WAL) DELETE ...; ROLLBACK;`. The plan has a top-level `Delete on workouts` node that does the writing, with the scans that find the rows beneath it. Time in AFTER triggers, including foreign-key checks, is printed as separate `Trigger ...: time=... calls=...` lines and counted in `Execution Time`, not in the node; deferred constraint triggers are not measured at all. ROLLBACK does not undo everything: the row locks were held until it, the WAL was written, a rolled-back UPDATE or INSERT leaves dead row versions for VACUUM, and `nextval()` values are not given back.

code

sql · 6 lines
sql
BEGIN;
EXPLAIN (ANALYZE, BUFFERS, WAL)
DELETE FROM workouts
WHERE user_id = 4812
  AND started_at < '2024-10-01';
ROLLBACK;

go deeper

for a junior

Remember that EXPLAIN ANALYZE runs the statement for real, and that BEGIN, EXPLAIN ANALYZE, ROLLBACK is the habit that keeps a measured DELETE from deleting anything.

for a middle

Explain the top-level modify node, why its cost estimate ignores the write work, and where BEFORE, AFTER and foreign-key trigger time appears in the output.

for a senior

Show what ROLLBACK leaves behind on a busy primary, such as held row locks, WAL shipped to standbys, dead tuples and burned sequence values, and choose where to measure accordingly.

for a principal

Weigh when measuring writes on production is worth its locking and WAL cost against a restored copy, and how the team makes the safe routine the default.

## EXPLAIN ANALYZE executes the statement Plain `EXPLAIN` only plans a statement. `EXPLAIN ANALYZE` **runs it**: PostgreSQL executes the plan, discards whatever rows the statement would have returned, and prints the plan annotated with what actually happened. For a `SELECT` that difference is harmless. For an `UPDATE`, `DELETE`, `INSERT`, `MERGE`, `CREATE TABLE AS`, or an `EXECUTE` of one of those, it means the data really changes. In `psql`'s default autocommit mode every statement is its own transaction, so `EXPLAIN ANALYZE DELETE FROM workouts WHERE ...` deletes those workouts and commits. The PostgreSQL manual's own advice is to wrap the measurement in a transaction you throw away: ```sql BEGIN; EXPLAIN (ANALYZE, BUFFERS, WAL) DELETE FROM workouts WHERE user_id = 4812 AND started_at < '2024-10-01'; ROLLBACK; ``` ## What the plan of a data-modifying statement looks like A data-modifying statement gets a **top-level modify node**: `Insert on`, `Update on`, `Delete on` or `Merge on`, followed by the table name. That node does the writing; the nodes below it only locate the target rows or compute the new values. Points worth knowing: - Without `RETURNING` the modify node returns no rows, so its estimate usually reads `rows=0`. - The planner **adds nothing to the cost estimate** for the modification work itself, because every correct plan must perform the same writes. The modify node's cost therefore looks no bigger than its input, even when its actual time is most of the statement. - On a partitioned table the node lists one `Update on <partition>` line per target child, in the same order as the subplans beneath it. - `BUFFERS` shows the pages the statement `dirtied`; `WAL` adds a `WAL: records=... fpi=... bytes=...` line with the write-ahead log the change produced. PostgreSQL rejects `WAL` unless `ANALYZE` is also given. ## Where trigger and foreign-key time shows up Triggers are the part of a write people most often miss in the output: | Trigger kind | Where its time appears | |---|---| | `BEFORE` row trigger | Inside the modify node's actual time, and on its own `Trigger` line | | `AFTER` trigger, including a foreign-key check | On a separate `Trigger ...: time=... calls=...` line and in `Execution Time`, but not in the modify node | | Deferred constraint trigger | Nowhere: it would fire at commit, after `EXPLAIN ANALYZE` has finished | Foreign keys are enforced by internal constraint triggers. A `DELETE` from `workouts`, which `workout_laps` references, prints a line such as `Trigger for constraint workout_laps_workout_id_fkey: time=2384.551 calls=640`. That line, not the plan tree, is where the time went: each of the 640 calls searched `workout_laps` for referencing rows, which is slow when `workout_laps.workout_id` has no index. `Execution Time` includes the trigger time; `Planning Time` is reported separately. ## What ROLLBACK does not take back Rolling back undoes the data change, not everything the statement did on the way: | Side effect | Undone by ROLLBACK? | |---|---| | Rows updated, deleted or inserted | Yes; no other session ever saw them | | Row locks on the target rows | Released at ROLLBACK, but held for the whole measurement | | WAL written, and shipped to standbys | No | | New row versions written by an UPDATE or INSERT | No; they remain as dead tuples until VACUUM removes them | | Values drawn with `nextval()`, including identity columns | No; the sequence has moved on | | Actions outside the database taken by triggers or functions | No | So `BEGIN ... ROLLBACK` makes the measurement safe for the data, not free for the server. ## A safe routine on a real system 1. Run plain `EXPLAIN` first to see the plan without touching any rows. 2. Prefer a restored copy or a staging database with production-sized data for anything large. 3. If you must measure on the primary, narrow the `WHERE` clause and keep the transaction short, because concurrent writers to the same rows wait behind your locks until `ROLLBACK`. 4. Run `EXPLAIN (ANALYZE, BUFFERS, WAL)` inside `BEGIN ... ROLLBACK`, then read the modify node, the `Trigger` lines and `Execution Time` together. 5. Treat a second run with suspicion: the first one warmed the cache and, for an `UPDATE` or `INSERT`, left dead row versions the second run has to step over. The habit matters most for the statements that look harmless. A one-line `UPDATE workouts SET calories = 0` under `EXPLAIN ANALYZE` in an autocommit session is a production incident, not a measurement.

  • Why does the Delete node's estimated cost look no larger than the scan beneath it, even when it takes most of the time?
    PostgreSQL's planner adds nothing to the cost estimate for the modification work itself, because every correct plan must perform the same writes, so that work cannot change which plan wins. The modify node's cost therefore mirrors its input. Judge the write's real price from its actual time, the `dirtied` buffer count and the `WAL` line instead.
  • How would you profile a large DELETE on a busy production primary?
    Start with plain `EXPLAIN`, which touches no rows. Prefer measuring on a restored copy with production-sized data. If it must be the primary, narrow the `WHERE` clause, run `BEGIN; EXPLAIN (ANALYZE, BUFFERS, WAL) ...; ROLLBACK;` promptly, and remember that concurrent writers to those rows wait for your locks and that the WAL still ships to every standby.
  • You roll back an EXPLAIN ANALYZE INSERT into a table with an identity column; what happens to the ids it used?
    The identity column draws its values from a sequence with `nextval()`, and PostgreSQL never reclaims those values when the transaction aborts, so the ids used during the measurement become a gap. The inserted rows remain as dead tuples until VACUUM removes them. Neither breaks anything, because PostgreSQL sequences never promised gapless values.

  • EXPLAIN ANALYZE only simulates an UPDATE or DELETE, so no rows actually change.
  • ROLLBACK after EXPLAIN ANALYZE undoes every effect, including sequence values and WAL.
  • Foreign-key check time is counted inside the Delete node's actual time.
  • Deferred constraint triggers appear in the Trigger lines of EXPLAIN ANALYZE.
  • The Delete node's estimated cost includes the work of removing the rows.
open as a page

In PostgreSQL, what does the ANALYZE command do, how does it differ from VACUUM and EXPLAIN ANALYZE, and when should you run it by hand?

level: juniorimportance: must knowfreq 66%

basics

~20 s

ANALYZE samples a table and rewrites its planner statistics in pg_statistic (shown by pg_stats). Unlike VACUUM it reclaims no space; unlike EXPLAIN ANALYZE it runs no query. Run it by hand after bulk loads, on temporary tables and after changing statistics settings.

open as a page

In PostgreSQL EXPLAIN ANALYZE, what do a node's cost, rows, width, actual time and loops mean, and how do you get its total time?

level: middleimportance: must knowfreq 74%

basics

~20 s

The first group is the planner's estimate: startup..total cost in arbitrary units, rows per execution and average row width in bytes. The second is measured: per-execution average milliseconds and rows, plus loops; multiply by loops for totals.

open as a page

In PostgreSQL, what does random_page_cost model, what is its default, and why do teams on SSD storage often lower it?

level: middleimportance: must knowfreq 60%

basics

~20 s

random_page_cost is the planner's cost of fetching a page out of order, default 4.0 against seq_page_cost 1.0. On SSDs random reads cost little more than sequential ones, so lowering it lets index scans win where they really are cheaper.

open as a page

In PostgreSQL 18, a delivery_addresses query filtering city = 'Leeds' AND postal_code = 'LS6' estimates 1,200 rows, returns 40,000 and picks a nested loop; how do extended statistics fix it?

level: seniorimportance: must knowfreq 55%

basics

~20 s

The planner multiplies the two columns' selectivities as if they were independent, so the estimate is too low by exactly the city's share. CREATE STATISTICS (dependencies, mcv) ON city, postal_code FROM delivery_addresses, followed by ANALYZE, shows it that postal_code implies city.

open as a page

In PostgreSQL, what does SET enable_seqscan = off actually do, and why is it a diagnostic test rather than a fix?

level: juniorimportance: should knowfreq 52%

basics

~20 s

It makes the planner avoid sequential scans unless no other path exists, exposing the plan it rejected so you can time it. A faster forced plan proves the estimates or cost settings are wrong; fix those, not the switch.

open as a page

In PostgreSQL 18 EXPLAIN ANALYZE, what do the Buffers counters shared hit, read, dirtied, written and temp mean, and how do you use them?

level: middleimportance: should knowfreq 56%

basics

~20 s

Buffers counts pages per node: shared hit was in shared_buffers, read was fetched from the OS or storage, dirtied was modified, written was evicted by this backend, temp is spilled work files. PostgreSQL 18 shows them with ANALYZE by default.

open as a page

In PostgreSQL, which settings decide how many parallel workers a query plans and actually gets, and why can it get fewer?

level: middleimportance: should knowfreq 45%

basics

~10 s

max_parallel_workers_per_gather (default 2) caps workers per Gather node and table size sets the planned count; max_parallel_workers and max_worker_processes (8 each) cap the shared pool, so a busy server launches fewer or none.

open as a page

In PostgreSQL, how does the planner turn a column's pg_stats row (null_frac, n_distinct, most_common_vals, most_common_freqs) into a row estimate for an equality filter?

level: middleimportance: should knowfreq 48%

basics

~20 s

If the constant is in most_common_vals, selectivity is its most_common_freqs entry. Otherwise the planner takes 1 minus the MCV frequencies minus null_frac, divides by the remaining distinct values (n_distinct minus the MCV count), and multiplies by the table's estimated rows.

open as a page

How large a sample does PostgreSQL's ANALYZE take, and what do default_statistics_target and ALTER TABLE ... SET STATISTICS actually control?

level: middleimportance: should knowfreq 42%

basics

~20 s

ANALYZE samples 300 × the statistics target rows: 30,000 at the default target of 100, whatever the table's size. The target also caps each column's most-common-value list and histogram buckets; SET STATISTICS overrides it per column, up to 10000.

open as a page

A PostgreSQL dashboard query takes 900 ms from the app, but EXPLAIN ANALYZE reports Execution Time: 40 ms; which EXPLAIN options help explain the gap?

level: seniorimportance: should knowfreq 34%

basics

~20 s

EXPLAIN ANALYZE discards rows before converting them for the client and never sends them, so detoasting, output functions and network time stay invisible. SERIALIZE measures the conversion, SETTINGS exposes session differences, and the app's prepared-statement plan may differ.

open as a page

In a PostgreSQL-backed loyalty-points service, a prepared statement is fast for five executions and then turns slow; what changed, and how do you fix it?

level: seniorimportance: should knowfreq 50%

basics

~20 s

After five custom-planned executions, PostgreSQL builds a generic plan and keeps it if its estimated cost is below the custom average. On skewed data that estimate misleads; force_custom_plan, scoped to that role or function, or better statistics fix it.

open as a page

In PostgreSQL 18, why can JIT compilation make a short query slower, and which settings decide whether a query is JIT-compiled?

level: seniorimportance: should knowfreq 34%

basics

~20 s

JIT starts when a plan's estimated total cost exceeds jit_above_cost (100000), with inlining and optimization above 500000. A short query the planner overestimates pays compile time that can exceed its runtime; jit = off or higher thresholds fix it.

open as a page

In PostgreSQL, how do join_collapse_limit, from_collapse_limit and geqo_threshold bound join-order search, and when would you set join_collapse_limit to 1?

level: seniorimportance: nice to knowfreq 24%

basics

~20 s

from_collapse_limit and join_collapse_limit (both 8) stop subqueries and explicit JOINs merging into one larger search; join problems of geqo_threshold (12) items or more go to the genetic optimizer. join_collapse_limit = 1 joins explicit JOINs in written order.

open as a page

On a 600-million-row PostgreSQL delivery_addresses table, pg_stats.n_distinct for customer_id is far below the true count; why does ANALYZE miss it, and how do you fix it durably?

level: seniorimportance: nice to knowfreq 28%

basics

~20 s

ANALYZE extrapolates distinct counts from about 30,000 sampled rows, which fails on huge tables. Measure the true ratio once, set ALTER TABLE ... ALTER COLUMN customer_id SET (n_distinct = -0.05), then ANALYZE; later ANALYZEs keep that override.

open as a page