Insert, Update, Delete and Merge (ModifyTable) in PostgreSQL EXPLAIN

What it does

Every INSERT, UPDATE, DELETE and MERGE plan has a ModifyTable node at the top. In text plans it’s labelled by the statement: Insert on orders, Update on orders, Delete on orders, Merge on products (in JSON output, "Node Type": "ModifyTable" with an "Operation").

The plan beneath it is an ordinary query plan that produces the rows to change: a scan with your WHERE, a join for UPDATE … FROM, a VALUES list or SELECT for INSERT. ModifyTable takes each row and does the write: stores the new row version, updates every index on the table (unless the update qualifies as HOT, which skips index updates), checks constraints, and fires row triggers.

For a partitioned table, it lists the partitions it may write to, such as Update on events_2026_10 events_1.

EXPLAIN ANALYZE really runs the statement. The rows are inserted, updated or deleted. To measure a write without keeping it, wrap it in a transaction and roll back:

BEGIN;
EXPLAIN (ANALYZE, BUFFERS) UPDATE orders SET status = 'refunded' WHERE customer_id = 4242;
ROLLBACK;

Rolled-back changes still leave dead row versions behind for vacuum to clean up, so don’t run this in a loop on a production table.

When the planner picks it

Always, for a data-changing statement. What you can influence is the plan underneath (how the rows to change are found) and what the write triggers.

Reading its numbers

Update on orders  (cost=4.58..81.89 rows=0 width=0) (actual time=4.916..4.921 rows=0.00 loops=1)
  Buffers: shared hit=269 read=61 dirtied=63
  ->  Bitmap Heap Scan on orders  (… rows=20.00 loops=1)
…
Trigger for constraint orders_customer_id_fkey: time=44.397 calls=10000

• rows=0: ModifyTable returns no rows unless the statement has RETURNING. The number of rows changed is the rows of its child (20 here), or the command tag UPDATE 20 outside EXPLAIN.
• actual time: the time to write the rows, update indexes and run BEFORE triggers. It doesn’t include AFTER triggers.
• Buffers: … dirtied=63: pages changed in memory: table pages plus every index page touched. More indexes on the table means more dirtied pages per row.
• Trigger for constraint <name>: time=… calls=… (below the plan): foreign-key checks, which PostgreSQL implements as AFTER triggers. calls is one per row checked. Your own triggers are listed the same way as Trigger <name>: time=… calls=….
• Execution Time includes the triggers, so it can be much larger than the top node’s time.
• Tuples: inserted=… updated=… on Merge on (seen on PostgreSQL 18): how many rows each WHEN action affected.

When it’s a problem

1. Finding the rows is slow. The scan under ModifyTable is a normal scan: an UPDATE … WHERE customer_id = 4242 without an index on customer_id reads the whole table. Treat it like a SELECT with the same WHERE.
2. Foreign-key checks on the referencing side. Inserting into orders checks that each customer_id exists in customers: one lookup per row. In the example, those checks took 44 ms of an 88 ms insert of 10,000 rows. They use the parent’s primary key, so they’re usually cheap per row; they add up on bulk loads.
3. Deleting from a parent table with no index on the child’s foreign key. Deleting a customer must check that no orders row points at it. Without an index on orders.customer_id, each check scans orders. In our test that was 56 ms per deleted customer against 0.36 ms with the index; deleting 10,000 customers would take nine minutes instead of four seconds. Index foreign-key columns on the referencing table (with CREATE INDEX CONCURRENTLY on a live table).
4. Many indexes. Every non-HOT update and every insert writes to every index. If writes are slow, look for unused indexes (pg_stat_user_indexes.idx_scan = 0 over a long period).
5. Huge single statements. Updating millions of rows in one statement holds locks and generates WAL and dead rows all at once. Batching by key range keeps each transaction short.

Example

PostgreSQL 18.6, default settings, every statement rolled back:

CREATE SCHEMA seo_explain;
SET search_path = seo_explain;

CREATE TABLE customers (
  id         int PRIMARY KEY,
  name       text NOT NULL,
  country    text NOT NULL,
  created_at timestamptz NOT NULL
);
INSERT INTO customers
SELECT i, 'Customer ' || i,
       (ARRAY['GB','US','DE','FR','NL','IE','ES','IT','SE','NO',
              'DK','FI','PL','PT','BE','AT','CH','CA','AU','NZ'])[1 + (i * 7) % 20],
       timestamptz '2023-01-01' + i * interval '20 minutes'
FROM generate_series(1, 50000) AS i;

CREATE TABLE orders (
  id          bigint PRIMARY KEY,
  customer_id int NOT NULL REFERENCES customers (id),
  status      text NOT NULL,
  total       numeric(10,2) NOT NULL,
  created_at  timestamptz NOT NULL
);
INSERT INTO orders
SELECT i,
       1 + (i::bigint * 7919) % 50000,
       CASE WHEN i % 100 < 90 THEN 'shipped'
            WHEN i % 100 < 97 THEN 'pending'
            ELSE 'refunded' END,
       round(((i * 37) % 100000) / 100.0, 2),
       timestamptz '2024-01-01' + i * interval '1 minute'
FROM generate_series(1, 1000000) AS i;
CREATE INDEX orders_customer_id_idx ON orders (customer_id);
CREATE INDEX orders_created_at_idx ON orders (created_at);

CREATE TABLE products (
  id    int PRIMARY KEY,
  name  text NOT NULL,
  price numeric(10,2) NOT NULL
);
INSERT INTO products
SELECT i, 'Product ' || i, round((i % 500) + 0.99, 2)
FROM generate_series(1, 100000) AS i;
VACUUM ANALYZE customers, orders, products;

Update

BEGIN;
EXPLAIN (ANALYZE, BUFFERS) UPDATE orders SET status = 'refunded' WHERE customer_id = 4242;
ROLLBACK;

Update on orders  (cost=4.58..81.89 rows=0 width=0) (actual time=4.916..4.921 rows=0.00 loops=1)
  Buffers: shared hit=269 read=61 dirtied=63
  ->  Bitmap Heap Scan on orders  (cost=4.58..81.89 rows=20 width=38) (actual time=0.024..0.066 rows=20.00 loops=1)
        Recheck Cond: (customer_id = 4242)
        Heap Blocks: exact=20
        Buffers: shared hit=23
        ->  Bitmap Index Scan on orders_customer_id_idx  (cost=0.00..4.58 rows=20 width=0) (actual time=0.011..0.011 rows=20.00 loops=1)
              Index Cond: (customer_id = 4242)
              Index Searches: 1
              Buffers: shared hit=3
Planning:
  Buffers: shared hit=101
Planning Time: 0.209 ms
Execution Time: 4.971 ms

Finding the 20 rows took 0.07 ms; writing them, with new entries in three indexes, the other 4.9 ms.

Insert, with foreign-key checks

BEGIN;
EXPLAIN (ANALYZE, BUFFERS)
INSERT INTO orders (id, customer_id, status, total, created_at)
SELECT 2000000 + i, 1 + (i * 37) % 50000, 'pending', 19.99,
       timestamptz '2026-10-09' + i * interval '1 second'
FROM generate_series(1, 10000) AS i;
ROLLBACK;

Insert on orders  (cost=0.00..300.00 rows=0 width=0) (actual time=43.344..43.344 rows=0.00 loops=1)
  Buffers: shared hit=59577 read=929 dirtied=1087 written=137
  ->  Function Scan on generate_series i  (cost=0.00..300.00 rows=10000 width=68) (actual time=0.339..2.159 rows=10000.00 loops=1)
Planning Time: 0.051 ms
Trigger for constraint orders_customer_id_fkey: time=44.397 calls=10000
Execution Time: 88.168 ms

43 ms to write 10,000 rows and their index entries, then 44 ms checking that each customer_id exists.

Delete from the parent, with and without the child’s index

A customer with no orders, deleted in a transaction:

BEGIN;
INSERT INTO customers VALUES (50001, 'Customer 50001', 'GB', now());
EXPLAIN (ANALYZE, BUFFERS) DELETE FROM customers WHERE id = 50001;
ROLLBACK;

Delete on customers  (cost=0.29..8.31 rows=0 width=0) (actual time=0.098..0.099 rows=0.00 loops=1)
  Buffers: shared hit=8 read=1
  ->  Index Scan using customers_pkey on customers  (cost=0.29..8.31 rows=1 width=6) (actual time=0.009..0.009 rows=1.00 loops=1)
        Index Cond: (id = 50001)
        Index Searches: 1
        Buffers: shared hit=3
Planning:
  Buffers: shared hit=33
Planning Time: 0.184 ms
Trigger for constraint orders_customer_id_fkey: time=0.355 calls=1
Execution Time: 0.545 ms

The same with DROP INDEX orders_customer_id_idx; run first inside the transaction:

Delete on customers  (cost=0.29..8.31 rows=0 width=0) (actual time=0.013..0.013 rows=0.00 loops=1)
  Buffers: shared hit=5
  ->  Index Scan using customers_pkey on customers  (cost=0.29..8.31 rows=1 width=6) (actual time=0.006..0.007 rows=1.00 loops=1)
        Index Cond: (id = 50001)
        Index Searches: 1
        Buffers: shared hit=3
Planning Time: 0.030 ms
Trigger for constraint orders_customer_id_fkey: time=56.305 calls=1
Execution Time: 56.329 ms

The plan looks identical and the Delete node is fast. All the time is in the trigger line, which scanned all of orders looking for references.

Merge

BEGIN;
CREATE TEMP TABLE price_updates AS
  SELECT id, price * 1.1 AS price FROM products WHERE id % 1000 = 0
  UNION ALL
  SELECT 100000 + g, 9.99 FROM generate_series(1, 5) AS g;
ANALYZE price_updates;
EXPLAIN (ANALYZE, BUFFERS)
MERGE INTO products p USING price_updates u ON p.id = u.id
WHEN MATCHED THEN UPDATE SET price = u.price
WHEN NOT MATCHED THEN INSERT (id, name, price) VALUES (u.id, 'New product', u.price);
ROLLBACK;

Merge on products p  (cost=0.29..782.60 rows=0 width=0) (actual time=2.076..2.077 rows=0.00 loops=1)
  Tuples: inserted=5 updated=100
  Buffers: shared hit=935 read=102 dirtied=205 written=1, local hit=1
  ->  Nested Loop Left Join  (cost=0.29..782.60 rows=105 width=23) (actual time=0.020..1.613 rows=105.00 loops=1)
        Buffers: shared hit=211 read=99, local hit=1
        ->  Seq Scan on price_updates u  (cost=0.00..2.05 rows=105 width=17) (actual time=0.006..0.019 rows=105.00 loops=1)
              Buffers: local hit=1
        ->  Index Scan using products_pkey on products p  (cost=0.29..7.43 rows=1 width=10) (actual time=0.015..0.015 rows=0.95 loops=105)
              Index Cond: (id = u.id)
              Index Searches: 105
              Buffers: shared hit=211 read=99
Planning:
  Buffers: shared hit=66 read=4
Planning Time: 0.372 ms
Execution Time: 2.137 ms

rows=0.95 loops=105: 100 of the 105 lookups found a product, so on average 0.95 rows per loop (PostgreSQL 18 shows the fraction; older versions round it to 1). local hit is the temporary table.

In Inlet

Inlet draws EXPLAIN ANALYZE as a tree and highlights the slowest step and badly misestimated row counts. Edits you make in the grid are staged and committed together in one transaction (⌘S), and Review shows the exact SQL first.