Re: WIP: Covering + unique indexes.

On Tue, Oct 4, 2016 at 7:50 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Tue, Oct 4, 2016 at 9:20 AM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)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