Re: Partial index locks

On 22 March 2014 16:28, Jim Nasby <jim(at)nasby(dot)net> wrote:
> 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 from doing this; could you share
> that with us?

Heh, no. I was just experimenting with various things, and also
trying to break stuff.

--
Thom