Re: Order of update - Mailing list pgsql-general

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.klaver@aklaver.com