Thread: Partial index locks
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
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
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
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
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
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
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
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
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