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?

Because there is no difference for non-unique indexes between (f1,f2,f3) and (f1, f2) INCLUDE (f3). In second case we just got index with unordered f3 column.

Oh no, it's possible that f3 column type has not btree operator class... If we want to support this case then intervention in planner will be a bit invasive.

BTW, I don't see in foreseen future another unique access methods.
Teodor Sigaev                      E-mail: teo...@sigaev.ru
                                      WWW: http://www.sigaev.ru/

Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:

Reply via email to