Data Modification Query Plans in PostgreSQL #

A slow UPDATE or DELETE has two costs, and EXPLAIN shows them in different places: the cost of finding the rows, which looks exactly like a SELECT plan, and the cost of changing them — index maintenance, constraint checks, triggers and WAL — which is concentrated in one node and a handful of summary lines.

Most write tuning mistakes come from optimising the wrong half. A DELETE whose scan takes 40 ms and whose foreign key checks take 40 minutes is not a scan problem; an UPDATE … FROM that joins the wrong way is not a trigger problem. This page covers the ModifyTable node family, the extra lines EXPLAIN ANALYZE prints for writes, and the workflow for attributing time between finding and changing rows. It belongs with execution plan fundamentals because every write starts as a query plan.

When the Optimizer Plans a Write #

INSERT, UPDATE, DELETE and MERGE are planned like queries with one extra node on top:

The planner optimises the subplan exactly as it would a SELECT — row estimates, join order, access paths — with a few write-specific rules:

Costs above the subplan — index maintenance, constraint checks, trigger execution — are not estimated in any detail. The planner does not choose a plan to minimise them, which is why they so often dominate unnoticed.

Finding rows versus changing them A stack for an UPDATE. At the bottom, the subplan scans and joins to find target rows, costed like a SELECT. Above it the ModifyTable node writes new row versions and maintains every index. Above that, constraint triggers and user triggers run per row and are reported separately. WAL generated by all of it is reported by the WAL option. Trigger / constraint time reported below the plan, per trigger Update on orders (ModifyTable) new row versions + index entries Hash Join subplan: finds target rows Seq Scan / Index Scan costed like any SELECT the planner optimises only the bottom two layers

Annotated EXPLAIN Node Breakdown #

BEGIN;
EXPLAIN (ANALYZE, BUFFERS, WAL)
UPDATE orders o
SET status = 'archived'
FROM customers c
WHERE c.id = o.customer_id
  AND c.closed_at < '2025-01-01';
ROLLBACK;
Update on orders o  (cost=… rows=0 width=0) (actual time=48210.4..48210.4 rows=0 loops=1)
  Buffers: shared hit=18402210 read=402110 dirtied=1840220 written=412008
  WAL: records=4204110 fpi=402110 bytes=1204880212
  ->  Hash Join  (cost=… rows=1812004 width=46) (actual time=402.6..4810.2 rows=1840204 loops=1)
        Hash Cond: (o.customer_id = c.id)
        ->  Seq Scan on orders o  (actual rows=94022110 loops=1)
        ->  Hash  (actual rows=40210 loops=1)
              ->  Index Scan using customers_closed_at_idx on customers c  (actual rows=40210 loops=1)
                    Index Cond: (closed_at < '2025-01-01'::date)
Planning Time: 1.2 ms
Trigger orders_audit_trg: time=21402.8 calls=1840204
Trigger for constraint shipments_order_id_fkey on orders: time=0.2 calls=0
Execution Time: 69840.1 ms

Breakdown:

The lines that matter for writes Four lines are annotated. The Update node's time minus its subplan time is the cost of writing rows and index entries. Buffers dirtied counts pages modified. The WAL line shows records and full-page images generated. Trigger lines below the plan report time per trigger, outside the node tree. Update on orders (actual time=48210.4) minus subplan 4810 ms = write cost Buffers: … dirtied=1840220 pages modified by the write WAL: records=4204110 fpi=402110 rows + index entries + page images Trigger orders_audit_trg: time=21402.8 outside the tree, added to total Execution Time = plan tree time + trigger time

Algorithm Internals #

UPDATE writes a new row version. PostgreSQL’s MVCC never modifies a row in place. An update inserts a new tuple and marks the old one expired. Unless the update qualifies as HOT — heap-only tuple: no indexed column changed and the new version fits on the same page — every index on the table receives a new entry pointing to the new version. A table with nine indexes pays nine index insertions per updated row, whether or not the indexed columns changed. The HOT conditions and fillfactor tuning are covered in fillfactor and HOT updates.

DELETE marks rows expired. It does not touch indexes at delete time — dead index entries are removed later by vacuum — but it fires ON DELETE foreign key actions on referencing tables. Each referencing constraint runs a query per deleted row against the referencing table, and without an index on the referencing column that query is a sequential scan. That cost appears as Trigger for constraint … time= and is the subject of foreign key trigger cost in deletes.

INSERT checks constraints and maintains indexes. Each row is inserted into the heap and every index; unique indexes check for conflicts at insertion; foreign keys on the inserted table check the referenced table per row. INSERT … ON CONFLICT uses a unique index as the arbiter to detect conflicts and then updates or skips.

MERGE (PostgreSQL 15+) is planned as a join between the source and the target, with each joined row classified as matched or not matched and routed to the corresponding action. EXPLAIN ANALYZE reports how many rows each action affected. Both are covered in INSERT ON CONFLICT and MERGE plans.

Joins in UPDATE and DELETE. UPDATE … FROM and DELETE … USING are planned as inner joins between the target and the other tables; the join algorithm choice follows the usual rules, as in UPDATE and DELETE join plans.

What each statement does per row A table of statement types against per-row work. INSERT writes one heap tuple, one entry in every index, checks unique and foreign key constraints. UPDATE writes a new heap tuple and an entry in every index unless the update is HOT. DELETE marks the tuple expired, touches no indexes immediately, and runs referencing foreign key checks. MERGE performs the matched action per joined row. heap indexes constraint work INSERT 1 new tuple every index unique + FK lookups UPDATE (non-HOT) new version every index FK if key changes UPDATE (HOT) new version none FK if key changes DELETE mark expired none (vacuum) referencing FKs indexes and referencing constraints are the hidden multipliers

Memory, I/O and Resource Behaviour #

Buffers dirtied and written. Writes dirty shared buffers; when free buffers run out, the backend writes pages itself (written= in BUFFERS), which is slower than leaving it to the background writer. Large single-statement writes almost always show high written counts.

WAL volume. Every heap change and index entry generates WAL, and the first modification of each page after a checkpoint writes a full-page image. Large batch writes shortly after checkpoints can generate several times more WAL than the same write later, as discussed in EXPLAIN SETTINGS, WAL and SERIALIZE options. WAL volume drives replication lag and archive size.

Locks. UPDATE and DELETE hold row locks on every target row until commit, and a long statement blocks concurrent writers on those rows for its whole duration. Batching changes the lock window more than it changes total cost.

Triggers and memory. AFTER row triggers — including foreign key checks — queue events per row in memory until the end of the statement. A statement touching tens of millions of rows with AFTER triggers can consume large amounts of backend memory for that queue.

Replicas and logical decoding. Every change is shipped to physical replicas as WAL and, when logical replication or change data capture is in use, decoded into per-row change events. A single statement that updates two million rows produces two million change events for downstream consumers, delivered as one large transaction that they must buffer until commit. Downstream lag after a large write is therefore often a property of the transaction’s size rather than of network or replica capacity, and batching fixes it where tuning the replica cannot.

Dead tuples. Each updated or deleted row leaves a dead version for vacuum. Large writes can push a table past autovacuum thresholds immediately and degrade following queries until vacuum catches up — see autovacuum thresholds and dead-tuple drift.

Where the 70 seconds went Of 69.8 seconds, finding rows with the hash join took 4.8 seconds, writing new row versions and index entries took 43.4 seconds, and the audit trigger took 21.4 seconds. Planning took about a millisecond. find rows (subplan) 4.8 s write versions + indexes 43.4 s audit trigger 21.4 s optimising the scan could save at most 7% of this statement

Row triggers versus statement triggers #

The audit trigger in the example cost 21 seconds because it was a FOR EACH ROW trigger: 1.84 million separate PL/pgSQL invocations, each inserting one audit row. The same audit can usually be written as a FOR EACH STATEMENT trigger using transition tables (PostgreSQL 10+), which exposes all changed rows as a relation the trigger body can query once:

CREATE TRIGGER orders_audit_stmt
AFTER UPDATE ON orders
REFERENCING OLD TABLE AS old_rows NEW TABLE AS new_rows
FOR EACH STATEMENT EXECUTE FUNCTION audit_orders_batch();

-- inside audit_orders_batch():
-- INSERT INTO orders_audit (order_id, old_status, new_status, changed_at)
-- SELECT n.id, o.status, n.status, now()
-- FROM new_rows n JOIN old_rows o USING (id);

The trigger then appears in EXPLAIN ANALYZE with calls=1, and the insert it performs is a single set-based statement. The saving is typically an order of magnitude on large writes; on single-row writes the difference is negligible, so the change matters mostly for batch jobs.

RETURNING has a cost of its own that is easy to miss. It makes the ModifyTable node emit rows, which must then be sent to the client or consumed by an outer query. A DELETE … RETURNING * on wide rows over millions of rows spends real time serialising output, and that time appears in Execution Time only when measured with the SERIALIZE option. Return only the columns the caller needs, or none at all when a row count is enough.

Step-by-Step Tuning Workflow #

  1. Capture safely. Run EXPLAIN (ANALYZE, BUFFERS, WAL) inside BEGIN … ROLLBACK, on a copy of production data where possible.

  2. Split the time three ways. Subplan time (the top child’s actual time), write time (ModifyTable time minus subplan time), and trigger time (the sum of Trigger lines).

  3. If the subplan dominates, tune it like a query: indexes on filter and join columns, estimates, join order.

  4. If the write dominates, count indexes on the target table and check HOT eligibility:

    SELECT count(*) FROM pg_index WHERE indrelid = 'orders'::regclass;
SELECT n_tup_upd, n_tup_hot_upd FROM pg_stat_user_tables WHERE relname = 'orders';

    Drop unused indexes, avoid updating indexed columns needlessly, and consider fillfactor for update-heavy tables.

  5. If triggers dominate, identify them from the Trigger lines. For Trigger for constraint, index the referencing column; for user triggers, move per-row logic to statement-level triggers with transition tables where possible.

  6. Batch large writes. Break the statement into chunks by key range so locks, WAL bursts and dead tuples stay bounded, and let vacuum run between batches.

  7. Skip no-op updates. Add a condition such as AND status IS DISTINCT FROM 'archived' so rows already in the target state are not rewritten.

  8. Re-measure with the same options and compare all three components.

Common Pitfalls #

Tuning the scan of a trigger-heavy write. The subplan may be a small fraction of the total. Diagnostic signal: large Trigger lines below the plan. Fix: attribute time before optimising.

Forgetting ROLLBACK. EXPLAIN ANALYZE performs the write. Diagnostic signal: data actually changed after an investigation. Fix: always wrap in a transaction you roll back.

Updating rows to the value they already have. Each still writes a new version and index entries. Diagnostic signal: WAL records far above the number of rows that really needed changing. Fix: add a IS DISTINCT FROM condition.

Unindexed foreign keys on delete. Each deleted parent row scans the child table. Diagnostic signal: Trigger for constraint … time= in seconds or minutes. Fix: index referencing columns.

Many indexes on hot update tables. Every non-HOT update touches all of them. Diagnostic signal: low n_tup_hot_upd ratio and slow updates. Fix: remove unused indexes and avoid indexing frequently updated columns.

One giant statement. Locks and WAL accumulate for its entire duration. Diagnostic signal: replication lag and blocked sessions during the write. Fix: batch by key ranges.

Frequently Asked Questions #

Why does EXPLAIN ANALYZE of an UPDATE show rows=0? #

The ModifyTable node — Update, Delete, Insert or Merge — returns no rows unless the statement has a RETURNING clause. Look at its child subplan for the number of rows found, and at the WAL and trigger lines for the work done.

Are triggers included in node times in EXPLAIN ANALYZE? #

No. Trigger execution, including foreign key constraint checks, is reported in separate Trigger lines after the plan with total time and call count. It is included in Execution Time but not in any node’s actual time.

Why is an UPDATE slower on a table with many indexes? #

Unless an update is HOT, PostgreSQL writes a new row version and inserts a new entry into every index on the table, even indexes on columns that did not change. More indexes mean more writes and more WAL per updated row.

Can UPDATE or DELETE use parallel query? #

Not for the statement’s subplan in current releases. The ModifyTable node and the plan finding the rows run in the leader. INSERT … SELECT can use parallel workers for the SELECT part.

Up: Execution Plan Fundamentals