skip to content

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.