Re: [PROPOSAL] Covering + unique indexes.

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)

>
> Are we certain that no index type could ever support an index on (f1, f2,
> f3) UNIQUE(f1, f3)? Even if it doesn't make sense for btree, perhaps some
> other index could handle it.
> --
> Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
> Experts in Analytics, Data Architecture and PostgreSQL
> Data in Trouble? Get it in Treble! http://BlueTreble.com
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>