Locking during UPDATE query with SUBSELECT

From: "Cenkar, Maciej" <mcenkar(at)ebay(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Locking during UPDATE query with SUBSELECT
Date: 2015-02-25 15:49:43
Message-ID: 408E2EFCA199634E9212BF6662D3783812D9D0F1@DUB-EXDDA-S31.corp.ebay.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tomas Vondra 2015-02-25 16:34:20 Re: : :Full text search query ::
Previous Message Merlin Moncure 2015-02-25 15:42:24 Re: Some indexing advice for a Postgres newbie, please?