Re: Batch update of indexes

From: Konstantin Knizhnik <k(dot)knizhnik(at)postgrespro(dot)ru>
To: Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Batch update of indexes
Date: 2016-02-03 16:47:12
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Attached please find patch for "ALTER INDEX ... WHERE ..." clause.
It is now able to handle all three possible situations:
1. Making index partial (add WHERE condition to the ordinary index)
2. Extend partial index range (less restricted index predicate)
3. Arbitrary change of partial index predicate

In case 2) new records are added to the index.
In other two cases index is completely reconstructed.

This patch includes src/bin/insbench utility for testing insert
performance. It can be easily excluded from the patch to reduce it size.
Also it is better to apply this patch together with "index-only scans
with partial indexes" patch:

only in this case regression test will produce expected output.

On 27.01.2016 23:15, Robert Haas wrote:
> On Wed, Jan 20, 2016 at 4:28 AM, Konstantin Knizhnik
> <k(dot)knizhnik(at)postgrespro(dot)ru> wrote:
>> Please notice that such alter table statement, changing condition for
>> partial index, is not supported now.
>> But I do not see any principle problems with supporting such construction.
>> We should just include in the index all records which match new condition
>> and do not match old condition:
>> ts < '21/01/2016' and not (ts < '20/01/2016')
> You'd also need to remove any rows from the index that match the old
> condition but not the new one. In your example, that's impossible,
> but in general, it's definitely possible.

Konstantin Knizhnik
Postgres Professional:
The Russian Postgres Company

Attachment Content-Type Size
alter-index.patch text/x-patch 30.4 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Robert Haas 2016-02-03 16:55:14 Re: [POC] FETCH limited by bytes.
Previous Message Robert Haas 2016-02-03 16:36:45 Re: PostgreSQL Audit Extension