Insert / Update / Delete / Merge (ModifyTable)

The ModifyTable node carries out data changes. EXPLAIN labels it by operation — Insert, Update, Delete, or Merge — and it sits at the top of the plan, consuming rows from the child that identifies what to change.

For any INSERT, UPDATE, DELETE, or MERGE statement.

Necessary for writes. The performance story is in the child plan (how target rows are found) plus side effects: indexes to maintain, triggers to fire, and foreign keys to check. A Seq Scan feeding an UPDATE/DELETE on a big table usually wants an index.

The plan's child is what you actually tune

ModifyTable is the top of the plan for any INSERT, UPDATE, DELETE, or MERGE — the EXPLAIN label is the operation name ("Update on …" etc.). Its job is to take rows from its child and apply the change, plus fire triggers, maintain indexes, and check constraints.

The performance story is mostly in the child: how target rows are found. A Seq Scan feeding a big UPDATE/DELETE almost always wants an index on the WHERE predicate, exactly as for a slow SELECT.

The hidden costs

Beyond finding the rows, each modification pays:

• Index maintenance — every non-HOT update touches every index that references a changed column (and every index for INSERT/DELETE). A wide table with many indexes can spend more time on indexes than on the heap.
• Triggers — row-level triggers fire per modified row; statement-level triggers fire once. Watch Trigger … time=… lines in EXPLAIN.
• Foreign keys — referential checks add lookups against the referenced table; the planner shows them as SubPlan or Initplan blocks.

HOT updates

A HOT (Heap-Only Tuple) update is one where no indexed column changed and the new row fits on the same page — the indexes are skipped entirely. Two practical levers:

• Tables with rapidly changing non-indexed columns benefit from a lower fillfactor (e.g. 70–90) so there's headroom on each page for HOT updates.
• An index that includes the changed column makes HOT impossible — keep indexes narrow.

RETURNING is free-ish

UPDATE … RETURNING … doesn't change the plan shape; it just exposes the rows the ModifyTable already produced. There's no extra scan.

• Seq Scan hot spot — finding rows to modify without an index

Paste a plan into the analyzer →

work_memsynchronous_commit

How we tune these →

Why is my UPDATE doing a sequential scan?

The WHERE clause that selects rows to update has no supporting index, so PostgreSQL scans the whole table to find them. Index the predicate columns — the same advice as for a slow SELECT with that WHERE.

• LockRows
• Result
• Sequential Scan
• Index Scan