Re: WIP: Covering + unique indexes.

From: Anastasia Lubennikova <a(dot)lubennikova(at)postgrespro(dot)ru>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Re: WIP: Covering + unique indexes.
Date: 2017-10-31 08:21:30
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Brief reminder of the idea behind the patch:

> _Use case:_
> - We have a table (c1, c2, c3, c4);
> - We need to have an unique index on (c1, c2).
> - We would like to have a covering index on all columns to avoid
> reading of heap pages.
> Old way:
> CREATE UNIQUE INDEX olduniqueidx ON oldt USING btree (c1, c2);
> CREATE INDEX oldcoveringidx ON oldt USING btree (c1, c2, c3, c4);
> What's wrong?
> Two indexes contain repeated data. Overhead to data manipulation
> operations and database size.
> New way:
> CREATE UNIQUE INDEX newidx ON newt USING btree (c1, c2) INCLUDE (c3, c4);

To find more about the syntax you can read related documentation patches
and also take a look
at the new test - src/test/regress/sql/index_including.sql.

Updated version is attached. It applies to the commit
The first patch patch contains changes in general index routines
and the second one contains btree specific changes.

This version contains fixes of the issues mentioned in the thread above
and passes all existing tests.
But still it requires review and testing, because the merge was quite
Especially I worry about integration with partitioning. I'll add some
more tests in the next message.

Anastasia Lubennikova
Postgres Professional:
The Russian Postgres Company

Attachment Content-Type Size
0001-covering-core_v3.patch text/x-patch 109.6 KB
0002-covering-btree_v3.patch text/x-patch 42.9 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Alexander Korotkov 2017-10-31 08:26:52 Re: Re: proposal - psql: possibility to specify sort for describe commands, when size is printed
Previous Message Michael Paquier 2017-10-31 07:58:39 Re: Re: PANIC: invalid index offnum: 186 when processing BRIN indexes in VACUUM