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: Robert Haas <robertmhaas@gmail.com>
Cc: "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Date: 2015-12-04T17:00:16Z
Lists: pgsql-hackers 

03.12.2015 04:03, Robert Haas пишет:
> On Tue, Dec 1, 2015 at 7:53 AM, Anastasia Lubennikova
> <a.lubennikova@postgrespro.ru> wrote:
>> If we don't need c4 as an index scankey, we don't need any btree opclass on
>> it.
>> But we still want to have it in covering index for queries like
>>
>> SELECT c4 FROM tbl WHERE c1=1000; // uses the IndexOnlyScan
>> SELECT * FROM tbl WHERE c1=1000; // uses the IndexOnlyScan
>>
>> The patch "optional_opclass" completely ignores opclasses of included
>> attributes.
> OK, I don't get it.  Why have an opclass here at all, even optionally?

We haven't opclass on c4 and there's no need to have it.
But now (without a patch) it's impossible to create covering index, 
which contains columns with no opclass for btree.

test=# create index on tbl using btree (c1, c4);
ERROR:  data type box has no default operator class for access method 
"btree"

ComputeIndexAttrs() processes the list of index attributes and trying to 
get an opclass for each of them via GetIndexOpClass().
The patch drops this check for included attributes. So it makes possible 
to store any datatype in btree  and use IndexOnlyScan advantages.

I hope that this helps to clarify.

-- 
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