postgres 11: stupidly long update

Question

I have an update query that goes like this:

update table_1
set col_1='64e27e3e-833e-4d2f-be34-452727e07822'
from table_1 left join table_2 on table_2.id = table_1.id
where
    table_1.col_1='64e27e3e-833e-4d2f-be34-452727e07822'
    and table_1.registered_at between '2022-07-01' and '2022-08-20'
    and table_2.restaurant_id = 158;

There are only 237 records to update, and the following select works instantly:

select * from table_1 left join table_2 on table_2.id = table_1.id
where
    table_1.col_1='64e27e3e-833e-4d2f-be34-452727e07822'
    and table_1.registered_at between '2022-07-01' and '2022-08-20'
    and table_2.restaurant_id = 158;

The longest I've seen this query to run was about 7 minutes. My first guess was that it locks something that is updated during oltp, but when I run this query, pg_locks doesn't reveal any queued (that is, non-granted) locks. Besides, if I set lock_timeout to '1000ms'; the statement continues to run after 1 second passes; so it's not locks. That query terminates only after statement_timeout passes, so it must be the query itself which is executed for so long. And the rows updated in this query are not updated by any other queries -- I intentionally took the records that were registered in the past.

So, what does it take so long?

Answer 1

Do not repeat the target table in the FROM clause.

Quote from the manual

Do not repeat the target table as a from_item unless you intend a self-join (in which case it must appear with an alias in the from_item).

So you should change that to:

update table_1
  set col_1 = '64e27e3e-833e-4d2f-be34-452727e07822'
from table_2 
where table_2.id = table_1.id
  and table_1.col_1 = '64e27e3e-833e-4d2f-be34-452727e07822'
  and table_1.registered_at between '2022-07-01' and '2022-08-20'
  and table_2.restaurant_id = 158;

The LEFT JOIN you used, isn't really an outer JOIN anyway as your WHERE conditions turn that back into an inner join.

Another option you might want to try is to use an EXISTS condition:

update table_1
  set col_1 = '64e27e3e-833e-4d2f-be34-452727e07822'
from table_2 
where table_1.col_1 = '64e27e3e-833e-4d2f-be34-452727e07822'
  and table_1.registered_at between '2022-07-01' and '2022-08-20'
  and exists (select *
              from table_1
              where table_2.id = table_1.id
                and table_2.restaurant_id = 158);