UPDATE and DELETE Join Plans #

A nightly job applies price changes with UPDATE products p SET price = s.price FROM staging_prices s WHERE s.sku = p.sku. It takes 25 minutes, and afterwards a handful of products have prices that match neither of the two staging rows someone expected. The slowness and the wrong prices have the same root: the statement is a join, and it was planned and executed with join semantics that nobody had checked.

The overall structure of write plans is covered in data modification query plans. This page covers the join underneath UPDATE … FROM and DELETE … USING.

The Planner Condition #

UPDATE t … FROM s WHERE <join condition> and DELETE FROM t USING s WHERE <join condition> are planned as an inner join between the target t and the other relations. The join is optimised like any SELECT join — hash, merge or nested loop, any join order — and its output feeds the Update or Delete node, carrying the target row’s identifier and, for updates, the new values.

Consequences that differ from SELECT:

Two ways to write a lookup update Left, UPDATE with a correlated subquery in SET: the subquery runs once per target row, unmatched rows are set to null unless a WHERE EXISTS guard is added, and no hash join is possible. Right, UPDATE FROM: planned as a join, only matched rows change, the planner can hash or merge, but duplicate source matches update a row once with an arbitrary source row. SET price = (SELECT … WHERE s.sku = p.sku) SubPlan per target row unmatched rows set to NULL needs WHERE EXISTS guard no set-based join UPDATE … FROM staging s WHERE … planned as an inner join only matched rows change hash / merge / nested loop duplicates: one arbitrary winner deduplicate the source before joining in either form

Annotated EXPLAIN Evidence #

BEGIN;
EXPLAIN (ANALYZE, BUFFERS)
UPDATE products p
SET price = s.price
FROM staging_prices s
WHERE s.sku = p.sku;
ROLLBACK;
Update on products p  (actual time=1504210.4..1504210.4 rows=0 loops=1)
  ->  Nested Loop  (actual time=0.9..1488204.6 rows=412022 loops=1)
        ->  Seq Scan on staging_prices s  (rows=100) (actual rows=410204 loops=1)
        ->  Index Scan using products_sku_idx on products p  (actual time=3.6..3.6 rows=1 loops=410204)
              Index Cond: (sku = s.sku)
Execution Time: 1504402.8 ms
-- staging table never analyzed: estimated 100 rows, actual 410,204
-- nested loop with a 3.6 ms random probe per staging row
-- join output 412,022 > 410,204 staging rows: some SKUs appear twice in staging

After analyzing the staging table and deduplicating:

ANALYZE staging_prices;

UPDATE products p
SET price = s.price
FROM (
  SELECT DISTINCT ON (sku) sku, price
  FROM staging_prices
  ORDER BY sku, loaded_at DESC          -- deterministic: latest staging row wins
) s
WHERE s.sku = p.sku
  AND p.price IS DISTINCT FROM s.price; -- skip no-op updates
Update on products p  (actual time=38204.6..38204.6 rows=0 loops=1)
  ->  Hash Join  (actual time=4102.4..9810.2 rows=188420 loops=1)
        Hash Cond: (p.sku = s.sku)
        Join Filter: (p.price IS DISTINCT FROM s.price)
        Rows Removed by Join Filter: 221782
        ->  Seq Scan on products p  (actual rows=12004110 loops=1)
        ->  Hash  ->  Subquery Scan on s  →  Unique  →  Sort  →  Seq Scan on staging_prices
Execution Time: 38410.2 ms
-- hash join once estimates are right; 221,782 unchanged prices not rewritten
Three checks on an UPDATE FROM plan Three fields are annotated. The staging scan estimate of 100 rows against 410 thousand actual shows an unanalyzed table. Join output rows larger than distinct source keys shows duplicate matches. Rows Removed by Join Filter on an IS DISTINCT FROM condition shows no-op updates skipped. Seq Scan on staging_prices (rows=100 … actual 410204) staging table never analyzed Nested Loop rows=412022 > 410204 staging rows duplicate source keys Rows Removed by Join Filter: 221782 unchanged rows not rewritten join rows above target rows means some updates were silently dropped

Step-by-Step Resolution #

  1. Analyze freshly loaded source tables before the write. Staging and temporary tables are the most common unanalyzed relations in write jobs:

    ANALYZE staging_prices;

  2. Check for duplicate join keys in the source:

    SELECT sku, count(*) FROM staging_prices GROUP BY sku HAVING count(*) > 1 LIMIT 10;

  3. Deduplicate deterministically with DISTINCT ON … ORDER BY or a GROUP BY that states which value wins.

  4. Skip no-op changes with IS DISTINCT FROM, which also treats nulls correctly.

  5. Match the algorithm to the ratio. Updating a small fraction of a large table: a nested loop over an index on the target key is right, provided the source estimate is accurate. Updating a large fraction: a hash join and sequential scan of the target is right. Correct estimates let the planner decide.

  6. For DELETE … USING against a large key set, the same rules apply; alternatively use DELETE … WHERE key IN (SELECT …), which the planner turns into a semi-join and which is naturally immune to duplicates in the source — see semi-join and anti-join plan shapes.

  7. Batch very large writes by key range to bound locks and WAL.

The same update, four versions The original update with an unanalyzed staging table takes 25 minutes. After ANALYZE it takes 61 seconds with a hash join. Adding deduplication keeps it at 60 seconds but makes results deterministic. Adding the IS DISTINCT FROM filter reduces it to 38 seconds by skipping 222 thousand unchanged rows. original, no ANALYZE 1,504 s + ANALYZE staging 61 s + DISTINCT ON dedup 60 s, deterministic + IS DISTINCT FROM 38 s the biggest win came from statistics, not from the SQL

Before and After #

-- BEFORE: unanalyzed staging, duplicates, no-op updates
Update → Nested Loop (loops=410204 index probes)                         Execution Time: 1504402.8 ms

-- AFTER: ANALYZE, DISTINCT ON source, IS DISTINCT FROM guard
Update → Hash Join  Rows Removed by Join Filter: 221782                   Execution Time: 38410.2 ms

Why duplicates do not raise an error #

For a SELECT, returning the same target row twice is harmless. For an UPDATE, applying two different new values to one row in one statement is ambiguous, and PostgreSQL resolves the ambiguity by applying the first and ignoring later updates to a row version already modified by the same command. The rule avoids an error but hides data quality problems in the source. MERGE takes the opposite approach: if a target row is matched by more than one source row, it raises an error — which is one reason to prefer MERGE for synchronisation jobs where duplicates indicate a bug, as described in INSERT ON CONFLICT and MERGE plans.

Common Pitfalls #

Unanalyzed staging tables. Estimates default to tiny row counts. Diagnostic signal: rows=100-style estimates on freshly loaded tables. Fix: ANALYZE after loading.

Duplicate source keys. Updates silently apply an arbitrary value. Diagnostic signal: join rows exceeding distinct target rows. Fix: deduplicate with an explicit rule.

Correlated subquery updates without a guard. Unmatched rows are set to null. Diagnostic signal: nulls appearing in updated columns. Fix: use UPDATE … FROM or add WHERE EXISTS.

Rewriting unchanged rows. Each costs a new version, index entries and WAL. Diagnostic signal: most updated rows had identical values. Fix: IS DISTINCT FROM filters.

Frequently Asked Questions #

What happens if UPDATE FROM matches a row more than once? #

The target row is updated only once, using whichever matching source row the executor processes first. The other matches are ignored without an error, so the result depends on the plan. Deduplicate the source to make the outcome deterministic.

Is UPDATE FROM faster than a correlated subquery in SET? #

Usually. UPDATE FROM is planned as a join and can use hash or merge joins, while a subquery in SET runs once per target row and touches rows without a match unless guarded with WHERE EXISTS.

Why is my UPDATE doing a nested loop over millions of rows? #

Usually because the source table’s row count was underestimated, often because it was freshly loaded and never analyzed. Run ANALYZE on the source table and re-check the plan.

Up: Data Modification Query Plans