| From: | Jim Nasby <jim(at)nasby(dot)net> |
|---|---|
| To: | Vik Fearing <vik(dot)fearing(at)dalibo(dot)com>, Thom Brown <thom(at)linux(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
| Subject: | Re: Partial index locks |
| Date: | 2014-03-22 16:28:16 |
| Message-ID: | 532DBA20.2030602@nasby.net |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | 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(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Jim Nasby | 2014-03-22 16:37:27 | Re: ALTER TABLE lock strength reduction patch is unsafe |
| Previous Message | Jim Nasby | 2014-03-22 16:26:51 | Re: psql blows up on BOM character sequence |