The problem
A developer was moving a project from SQL Server to PostgreSQL. One bulk update had to fill a missing route ID on about a million delivery rows. Each delivery should copy the route ID from its client.
On SQL Server, the query took about 10 seconds. On PostgreSQL, the same query never finished. They cancelled it after five minutes. The data was the same in both databases.
The PostgreSQL version looked like this:
UPDATE client_delivery SET route_id = t1.route_id FROM client_delivery t INNER JOIN clients t1 ON t.client_id = t1.id WHERE t.route_id = 0; It looks like a direct port. It is not. Missing indexes and stale statistics were also suggested as causes. Neither one is the real problem here.
Root cause: two copies of the same table
In PostgreSQL, the table after UPDATE is the target. Every table in FROM is an extra source. Naming client_delivery again in FROM adds a second, separate copy of the table.
The PostgreSQL UPDATE docs warn about this. They say to list the target again in FROM only when you really want a self-join.
Now look at what joins to what:
-
tjoins tot1throughclient_id. - The target
client_deliveryjoins to nothing. -
WHERE t.route_id = 0filters the copyt, not the target.
So every target row pairs with every matching source row. That is a cross join.
SQL Server reads this shape another way. Its UPDATE docs say the target can match a single FROM reference to the same table, with or without an alias. That is why the port looked fine there.
The cross join causes two problems at once.
It is slow. Here is the plan on a test table with 200,000 deliveries and 20,000 clients. About 20,000 deliveries have route_id = 0.
Update on client_delivery -> Nested Loop (rows=3974600000) -> Seq Scan on client_delivery (rows=200000) -> Materialize -> Hash Join Hash Cond: (t.client_id = t1.id) -> Seq Scan on client_delivery t Filter: (route_id = 0) -> Hash -> Seq Scan on clients t1 The planner expects close to 4 billion joined rows. The run hit a 5-second statement_timeout and was cancelled. At a million rows, the pairs grow much faster still.
It is wrong. This is the worse part. The WHERE clause never touches the target, so every row gets updated. The docs also warn about one target row joining many source rows. Then only one join row is used, and which one is not readily predictable.
Here is a tiny run that shows it:
CREATE TABLE clients (id int PRIMARY KEY, route_id int NOT NULL); CREATE TABLE client_delivery ( id int PRIMARY KEY, client_id int NOT NULL REFERENCES clients(id), route_id int NOT NULL ); INSERT INTO clients VALUES (1, 10), (2, 20), (3, 30); INSERT INTO client_delivery VALUES (101, 1, 0), (102, 2, 0), (103, 3, 99), (104, 3, 30); BEGIN; UPDATE client_delivery SET route_id = t1.route_id FROM client_delivery t JOIN clients t1 ON t.client_id = t1.id WHERE t.route_id = 0; SELECT * FROM client_delivery ORDER BY id; ROLLBACK; Output on PostgreSQL 17:
UPDATE 4 id | client_id | route_id -----+-----------+---------- 101 | 1 | 10 102 | 2 | 10 103 | 3 | 10 104 | 3 | 10 All four rows changed, and all got the same value. Rows 103 and 104 already had a route, so they should not have changed. Row 102 belongs to client 2, so it should have route 20.
The fix: one reference to the target, joined in WHERE
Give the target an alias. Put only the other table in FROM. Join them in WHERE.
UPDATE client_delivery AS d SET route_id = c.route_id FROM clients AS c WHERE d.client_id = c.id AND d.route_id = 0 AND d.route_id IS DISTINCT FROM c.route_id; On the tiny tables, the result is right:
UPDATE 2 id | client_id | route_id -----+-----------+---------- 101 | 1 | 10 102 | 2 | 20 103 | 3 | 99 104 | 3 | 30 On the 200,000-row test table, the plan becomes a single hash join:
Update on client_delivery d (actual rows=0 loops=1) -> Hash Join (actual rows=20000 loops=1) Hash Cond: (d.client_id = c.id) Join Filter: (d.route_id IS DISTINCT FROM c.route_id) -> Seq Scan on client_delivery d (actual rows=20000 loops=1) Filter: (route_id = 0) Rows Removed by Filter: 180000 -> Hash (actual rows=20000 loops=1) -> Seq Scan on clients c (actual rows=20000 loops=1) Execution Time: 55.146 ms That is about 55 milliseconds on our test box. Your timing will differ, but the shape is what matters. Each target row now joins to at most one client.
Why the IS DISTINCT FROM line
Without it, a row whose client also has route 0 still gets "updated" to 0. PostgreSQL normally writes a new row version even when the data does not change. Its trigger docs say redundant updates can cost considerable unnecessary time.
IS DISTINCT FROM is also safe with nulls. A plain <> returns null when either side is null, so it would skip rows you meant to fix.
A nice side effect: the update is now safe to run twice. In the test, the second run reported UPDATE 0.
Trade-offs
- Join in
FROMvs. a subquery. A correlated subquery inSETavoids the repeated-table trap. It also never picks a random match. The PostgreSQL docs note it is often harder to read and slower than a join. - One target row, many sources. If
clients.idwere not unique, the join could match several rows per delivery. PostgreSQL would use one of them, and you could not easily predict which. Make sure the join key is a primary key or unique. - One big statement vs. batches. One
UPDATEis simplest and runs in one transaction. It also holds row locks on every changed row until commit. On a busy table, batching by ID range keeps each transaction short. The cost is more code and a table that is half done between batches. - Indexes. Here the planner chose sequential scans and a hash join, which suits touching a tenth of the table. An index on
client_delivery(route_id)may help if far fewer rows match. Check withEXPLAINbefore you add one, since each index adds write cost.
How to verify the fix
- Count first. Turn the update into a
SELECTwith the same join and filters. The count should match what you expect to change.
SELECT count(*) FROM client_delivery AS d JOIN clients AS c ON d.client_id = c.id WHERE d.route_id = 0 AND d.route_id IS DISTINCT FROM c.route_id; Read the plan. Run plain
EXPLAINon theUPDATE. ANested Loopwith no join condition on the target is the warning sign. So is a row estimate far larger than the table.Run it inside a transaction.
EXPLAIN ANALYZEreally runs the statement, so wrap it inBEGINandROLLBACK. Check that theUPDATEcount matches step 1.Set a guard rail.
SET statement_timeout = '30s';stops a bad query before it runs for hours. Pick a limit that fits your job.Check the result, then run it again. After commit, count rows still at
route_id = 0. A second run should reportUPDATE 0.
Official docs
- PostgreSQL, UPDATE: https://www.postgresql.org/docs/current/sql-update.html
- PostgreSQL, EXPLAIN: https://www.postgresql.org/docs/current/sql-explain.html
- PostgreSQL, Comparison Functions and Operators (
IS DISTINCT FROM): https://www.postgresql.org/docs/current/functions-comparison.html - PostgreSQL, Trigger Functions (
suppress_redundant_updates_trigger): https://www.postgresql.org/docs/current/functions-trigger.html - PostgreSQL, Client Connection Defaults (
statement_timeout): https://www.postgresql.org/docs/current/runtime-config-client.html - Microsoft, UPDATE (Transact-SQL): https://learn.microsoft.com/en-us/sql/t-sql/queries/update-transact-sql?view=sql-server-ver17
Written by the team at ByteBridge Talent LLC, an IT staffing company. Learn more at bytebridgetalent.com. Questions: info@bytebridgetalent.com.