Partial index locks

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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?
--
Jim C. Nasby, Data Architect jim@nasby.net
512.569.9461 (cell) http://jim.nasby.net

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers