On 15 September 2015 at 18:16, Teodor Sigaev <teodor@sigaev.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.
