Re: Order of update

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(dot)klaver(at)aklaver(dot)com