Re: Partial index locks

On 3/21/14, 7:59 PM, Vik Fearing wrote:
> On 03/22/2014 01:43 AM, Thom Brown wrote:
>> Hi,
>>
>> I've created a table with 1000 partial indexes.  Each one matches
>> exactly one row based on the predicate WHERE id = <value>.
>>
>> However, when I perform an UPDATE of a single row in a transaction,
>> I've noticed that all those partial indexes show up in pg_locks with
>> RowExclusiveLock.
>>
>> Only 2 of those indexes have a reference to the row: the primary key
>> and a single partial index.
>>
>> Is it necessary for a partial index that doesn't include the row to be
>> involved in locking?
>
> What if the update puts the row into one of the other indexes?

Also, why are you doing this in the first place? I'm guessing you measured some non-trivial performance improvement
fromdoing this; could you share that with us?
 
-- 
Jim C. Nasby, Data Architect                       jim@nasby.net
512.569.9461 (cell)                         http://jim.nasby.net