Locking during UPDATE query with SUBSELECT

["Cenkar, Maciej" <mcenkar@xxxxxxxx>]

Hi All,

 

I wonder if anyone can explain something I cannot easily find on google.

 

Given PostgreSQL 9.3.5 what is locking strategy when executing query such as:

 

UPDATE table SET some_col = some_val WHERE id IN (SELECT id FROM expensive_query_with_joins).

 

Is this starting to lock rows after it executed sub-select or is it locking whole table and then executing select?

 

Is there any advantage in precomputing ids from nested select to run only

UPDATE table SET some_col = some_val WHERE id IN (precomputed_values)?

 

If it changes anything table has few indices and many millions of rows.

 

Regards,

Maciek