Re: [PROPOSAL] Covering + unique indexes.

On Tue, Sep 15, 2015 at 6:08 AM, Teodor Sigaev <teodor(at)sigaev(dot)ru> wrote:

> CREATE INDEX index ON table (c1, c2, c3) UNIQUE ON (c1, c3);
>>>
>>> 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
>
>
>
>> Have you by chance looked to see what other databases have done for
>> syntax? I'm guessing this isn't covered by ANSI but maybe there's
>> already an industry consensus.
>>
>
> MS SQL and DB/2 suggests (with changes for postgresql):
> CREATE UNIQUE INDEX i ON t (a,b) INCLUDE (c)
>
> MS SQL supports both unique and non-unique indexes, DB/2 only unique
> indexes. Oracle/MySQL doesn't support covering indexes. Readed at
> http://use-the-index-luke.com/sql/clustering/index-only-scan-covering-index

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.

http://sqlperformance.com/2014/07/sql-indexes/new-index-columns-key-vs-include

--
Thomas Munro
http://www.enterprisedb.com