Re: [PROPOSAL] Covering + unique indexes.

On 14 September 2015 at 23:12, Oleg Bartunov <obartunov(at)gmail(dot)com> wrote:

>
>
>
> On Tue, Sep 15, 2015 at 12:44 AM, Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com>
> wrote:
>
>> On 9/14/15 1:50 PM, Thomas Munro wrote:
>>
>>> CREATE [UNIQUE {ON FIRST {COLUMN | n_unique_column COLUMNS}}
>>> INDEX ON
>>> table_name (column_name1, column_name2 ...);
>>>
>>>
>>> I would use the first (simple) syntax and just throw an error if
>>> the
>>> user tries to skip a column on the UNIQUE clause.
>>>
>>> Seems, second option looks as more natural extension of CREATE
>>> UNIQUE INDEX
>>>
>>
>> True, but it's awefully verbose. :( And...
>>
>> It surprised me that you can INCLUDE extra columns on non-UNIQUE
>>> indexes, since you could just add them as regular indexed columns for
>>> the same effect. It looks like when you do that in SQL Server, the
>>> extra columns are only stored on btree leaf pages and so can't be used
>>> for searching or ordering. I don't know how useful that is or if we
>>> would ever want it... but I just wanted to note that difference, and
>>> that the proposed UNIQUE ON FIRST n COLUMNS syntax and catalog change
>>> can't express that.
>>>
>>
>> ... we might want to support INCLUDE at some point. It enhances covering
>> scans without bloating the heck out of the btree. (I'm not sure if it would
>> help other index types...) So it seems like a bad idea to preclude that.
>>
>> I don't see that UNIQUE ON FIRST precludes also supporting INCLUDE.
>> Presumably we could do either
>>
>> CREATE INDEX ... ON table (f1, f2, f3) UNIQUE(f1, f2) INCLUDE(f4);
>> or
>> CREATE UNIQUE ON FIRST 2 COLUMNS INDEX ... ON table (f1, f2, f3)
>> INCLUDE(f4);
>>
>> Personally, I find the first form easier to read.
>>
>
> Why not normal syntax with optional INCLUDE ?
>
> CREATE UNIQUE INDEX ON table (f1,f2,f3) INCLUDE (f4)
>

That's not the same thing. Then you're including f3 in the unique
constraint, which you may not want for uniqueness purposes, but may want in
the index for sorting. But then saying that, if f1 and f2 are unique,
you'd only get 1 value of f3 for each combination of f1 and f2, so sorting
probably isn't useful. You might as well only INCLUDE f3 rather than have
it in the multi-column index for sorting. So to adjust your example:

CREATE UNIQUE INDEX ON table (f1,f2) INCLUDE (f3, f4);

Is there a scenario anyone can think of where f3 here:

CREATE INDEX ... ON table (f1, f2, f3) UNIQUE(f1, f2) INCLUDE(f4);

would be advantageous outside of INCLUDE?

Out of curiosity, why is this only being discussed for unique indexes?
What if you want additional columns included on non-unique indexes?

Thom