Re: [PROPOSAL] Covering + unique indexes.

On 15 September 2015 at 18:16, Teodor Sigaev <teodor(at)sigaev(dot)ru> wrote:

> CREATE INDEX ... ON table (f1, f2, f3) UNIQUE(f1, f2) INCLUDE(f4);
>>
>
> I don't see an advantage this form. What is f3 column? just order? and f4
> will not be used for compare? At least now it requires additional checks
> that UNIQUE() fields are the same as first columns in definition. Non
> ordering field f4 will require invasive intervention in planner because
> now it believes that all columns in btree are ordered.
>
>
I'm also a bit confused where f3 comes in here. If it's UNIQUE on (f1,f2)
and we include f4. Where's f3?

> I agree, that form
> CREATE UNIQUE INDEX i ON t (f1, f2, f3) INCLUDE (f4)
> is clear. f4 will be used in row compare and actually planner will be able
> to use it as unique index (f1, f2, f3) with additional f4 or as
> as unique index (f1, f2, f3, f4), because uniqueness on (f1, f2, f3) gives
> warranty of uniqueness on (f1, f2, f3, f4)
>
>
I'd vote for this too. However, INCLUDE does not seem to be a reserved word
at the moment.

I think this syntax fits in nicely to with non-unique indexes too.

Regards

David Rowley

--
David Rowley http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/>
PostgreSQL Development, 24x7 Support, Training & Services