Horton

Re: WIP: Covering + unique indexes.

Anastasia Lubennikova <a.lubennikova@postgrespro.ru>

View thread View on postgr.es
From: Anastasia Lubennikova <a.lubennikova@postgrespro.ru>
To: pgsql-hackers@postgresql.org
Date: 2017-10-31T08:21:30Z
Lists: pgsql-hackers

Attachments

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 
e4fbf22831c2bbcf032ee60a327b871d2364b3f5.
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 
uneasy.
Especially I worry about integration with partitioning. I'll add some 
more tests in the next message.

-- 
Anastasia Lubennikova
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Commits

  1. Adjust INCLUDE index truncation comments and code.

  2. Add commentary explaining why MaxIndexTuplesPerPage calculation is safe.

  3. Indexes with INCLUDE columns and their support in B-tree

  4. Add amcheck verification of heap relations belonging to btree indexes.

  5. Doc: move info for btree opclass implementors into main documentation.

  6. Doc: mention that you can't PREPARE TRANSACTION after NOTIFY.

  7. Remove dedicated B-tree root-split record types.

  8. Restructure index access method API to hide most of it at the C level.

  9. Split _bt_insertonpg to two functions.

  10. Major overhaul of btree index code. Eliminate special BTP_CHAIN logic for