Horton

Re: WIP: Covering + unique indexes.

Amit Kapila <amit.kapila16@gmail.com>

View thread View on postgr.es
From: Amit Kapila <amit.kapila16@gmail.com>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Anastasia Lubennikova <a.lubennikova@postgrespro.ru>, Andrey Borodin <amborodin@acm.org>, pgsql-hackers <pgsql-hackers@postgresql.org>, Anastasia Lubennikova <lubennikovaav@gmail.com>
Date: 2016-10-05T13:04:46Z
Lists: pgsql-hackers 
On Tue, Oct 4, 2016 at 7:50 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Tue, Oct 4, 2016 at 9:20 AM, Amit Kapila <amit.kapila16@gmail.com> wrote:
>>>> I'd say that the reason for not using included columns in any
>>>> operations which require comparisons, is that they don't have opclass.
>>>> Let's go back to the example of points.
>>>> This data type don't have any opclass for B-tree, because of fundamental
>>>> reasons.
>>>> And we can not apply _bt_compare() and others to this attribute, so
>>>> we don't include it to scan key.
>>>>
>>>> create table t (i int, i2 int, p point);
>>>> create index idx1 on (i) including (i2);
>>>> create index idx2 on (i) including (p);
>>>> create index idx3 on (i) including (i2, p);
>>>> create index idx4 on (i) including (p, i2);
>>>>
>>>> You can keep tuples ordered in idx1, but not for idx2, partially ordered for
>>>> idx3, but not for idx4.
>>>
>>> Yeah, I think we shouldn't go there.  I mean, once you start ordering
>>> by INCLUDING columns, then you're going to need to include them in
>>> leaf pages because otherwise you can't actually guarantee that they
>>> are in the right order.
>>
>> I am not sure what you mean by above, because patch already stores
>> INCLUDING columns in leaf pages.
>
> Sorry, I meant non-leaf pages.
>

Okay, but in that case I think we don't need to store including
columns in non-leaf pages to get the exact ordering.  As mentioned
upthread, we can use truncated scan key to reach to leaf level and
then use the complete key to find the exact location to store the key.
This is only possible if there exists an opclass for columns that are
covered as part of including clause.  So, we can allow "order by" to
use index scan only if the columns covered in included clause have
opclass for btree.

-- 
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

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