Thread: Partial index locks

Partial index locks

From
Thom Brown
Date:
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?

Thanks

Thom



Re: Partial index locks

From
Vik Fearing
Date:
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?

-- 
Vik




Re: Partial index locks

From
Thom Brown
Date:
On 22 March 2014 00:59, Vik Fearing <vik.fearing@dalibo.com> 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?

Well here's where I'm confused.  The entries in pg_locks show than a
RowExclusiveLock is being held on the index for which there is no
matching row.  What does that translate as?  There is also a
RowExclusiveLock on the table itself too, which is what I expect to
see.

Also, a delete results in all the locks being taken too.  That can't
possibly result in a new entry being put into any of those indexes.

As those indexes don't contain references to the row, what is it locking?

-- 
Thom



Re: Partial index locks

From
Tom Lane
Date:
Thom Brown <thom@linux.com> writes:
> Is it necessary for a partial index that doesn't include the row to be
> involved in locking?

Yes.  You can't determine whether the index needs to get a new entry
without examining its metadata, and that's what the lock is mainly about.

The only possible alternative would be to take the minimum possible
lock (AccessShareLock) on each index so its metadata would hold still,
and then upgrade that to RowExclusiveLock on the one(s) we find need
insertions.  This is not better; it means *more* lock management traffic
not less, and lock upgrades increase the potential for deadlocks.
        regards, tom lane



Re: Partial index locks

From
Thom Brown
Date:
On 22 March 2014 05:32, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Thom Brown <thom@linux.com> writes:
>> Is it necessary for a partial index that doesn't include the row to be
>> involved in locking?
>
> Yes.  You can't determine whether the index needs to get a new entry
> without examining its metadata, and that's what the lock is mainly about.

I see.  Why does this apply to deletes too?

> The only possible alternative would be to take the minimum possible
> lock (AccessShareLock) on each index so its metadata would hold still,
> and then upgrade that to RowExclusiveLock on the one(s) we find need
> insertions.  This is not better; it means *more* lock management traffic
> not less, and lock upgrades increase the potential for deadlocks.

Yes, I can see that wouldn't be an improvement.
-- 
Thom



Re: Partial index locks

From
Tom Lane
Date:
Thom Brown <thom@linux.com> writes:
> On 22 March 2014 05:32, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Yes.  You can't determine whether the index needs to get a new entry
>> without examining its metadata, and that's what the lock is mainly about.

> I see.  Why does this apply to deletes too?

The executor doesn't take locks on indexes for a delete.  I think the
planner probably does, though, since it wants to look at all the indexes
to see if any can be used to satisfy WHERE searches.

Possibly it would be worth hacking the planner to only take
AccessShareLock not RowExclusiveLock on target indexes in DELETE.
I can't get very excited about that though; in what circumstances
would it actually make a difference?
        regards, tom lane



Re: Partial index locks

From
Thom Brown
Date:
On 22 March 2014 15:04, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Thom Brown <thom@linux.com> writes:
>> On 22 March 2014 05:32, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> Yes.  You can't determine whether the index needs to get a new entry
>>> without examining its metadata, and that's what the lock is mainly about.
>
>> I see.  Why does this apply to deletes too?
>
> The executor doesn't take locks on indexes for a delete.  I think the
> planner probably does, though, since it wants to look at all the indexes
> to see if any can be used to satisfy WHERE searches.
>
> Possibly it would be worth hacking the planner to only take
> AccessShareLock not RowExclusiveLock on target indexes in DELETE.
> I can't get very excited about that though; in what circumstances
> would it actually make a difference?

Well I wasn't looking for things to optimise, so much as trying to
understand the logic behind the existing behaviour.  But thanks for
the explanation.

-- 
Thom



Re: Partial index locks

From
Jim Nasby
Date:
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



Re: Partial index locks

From
Thom Brown
Date:
On 22 March 2014 16:28, Jim Nasby <jim@nasby.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