On 4/21/25 01:47, Peter J. Holzer wrote:
If the hash was the other way around it wouldn't work.
So let's try if we can get the optimizer to flip the plan by changing
the number of updated rows.
[a few minutes later]
#v+
hjp=> explain
with a as (select id from id_update where id > 90000 order by id desc)
update id_update as t set id = a.id + 1 from a where a.id = t.id;
╔════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╗
║ QUERY PLAN
║
╟────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────╢
║ Update on id_update t (cost=732.53..2675.61 rows=0 width=0)
║
║ -> Hash Join (cost=732.53..2675.61 rows=10006 width=38)
║
║ Hash Cond: (t.id = a.id)
║
║ -> Seq Scan on id_update t (cost=0.00..1443.00 rows=100000
width=10) ║
║ -> Hash (cost=607.46..607.46 rows=10006 width=32)
║
║ -> Subquery Scan on a (cost=0.29..607.46 rows=10006 width=32)
║
║ -> Index Only Scan Backward using id_update_pkey on
id_update (cost=0.29..507.40 rows=10006 width=4) ║
║ Index Cond: (id > 90000)
║
╚════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╝
#v-
Looks like we got it.
And indeed:
#v+
hjp=> with a as (select id from id_update where id > 90000 order by id desc)
update id_update as t set id = a.id + 1 from a where a.id = t.id;
ERROR: duplicate key value violates unique constraint "id_update_pkey"
DETAIL: Key (id)=(90002) already exists.
#v-
So, obviously that isn't guaranteed to work.
I read from here:
https://www.postgresql.org/docs/current/sql-update.html
"Use of an ORDER BY clause allows the command to prioritize which rows
will be updated; it can also prevent deadlock with other update
operations if they use the same ordering."
I went back to those docs and realized I had missed the FOR UPDATE in
the example.
explain with a as (select id from id_update where id > 90000 order by id
desc for update) update id_update as t set id = a.id + 1 from a where
a.id = t.id;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Update on id_update t (cost=3609.71..3856.94 rows=0 width=0)
CTE a
-> LockRows (cost=0.29..872.71 rows=9840 width=10)
-> Index Scan Backward using id_update_pkey on id_update
(cost=0.29..774.31 rows=9840 width=10)
Index Cond: (id > 90000)
-> Hash Join (cost=2737.00..2984.23 rows=9840 width=38)
Hash Cond: (a.id = t.id)
-> CTE Scan on a (cost=0.00..196.80 rows=9840 width=32)
-> Hash (cost=1487.00..1487.00 rows=100000 width=10)
-> Seq Scan on id_update t (cost=0.00..1487.00
rows=100000 width=10)
(10 rows)
and then:
with a as (select id from id_update where id > 90000 order by id desc
for update) update id_update as t set id = a.id + 1 from a where a.id =
t.id;
UPDATE 10000
Though at this point I would agree with you on the no guarantee point.
hjp
--
Adrian Klaver
adrian.kla...@aklaver.com
