On 09/15/2015 06:57 PM, Anastasia Lubennikova wrote:
Proposal Clarification.
I see that discussion become too complicated. So, I'd like to clarify what we are talking about.


[snip]
What are we doing now:
CREATE UNIQUE INDEX on tbl(f1,f2);
CREATE INDEX on tbl(f1, f2, f3, f4);

[snip]
Suggestions.
CREATE INDEX idx ON tbl (f1, f2, f3) [UNIQUE ON (f1, f2)] [INCLUDE (f4)];

Summarizing some suggestions upthread, it seems like the "best" syntax would be something similar to:

-- Non-unique index + "leaf" information (f4)
CREATE INDEX idx ON tbl (f1, f2, f3) [INCLUDING (f4)]

-- Unique index on f1,f2, + leaf information (f3)
CREATE UNIQUE INDEX idx ON tbl (f1, f2) [INCLUDING (f3)]

And, even:
ALTER INDEX idx INCLUDING (f4)
 ... which would trigger a REINDEX CONCURRENTLY internally ?


FWIW this would include also the functionality provided by the suggested
    CREATE INDEX idx ON tbl (f1, f2, f3) UNIQUE ON (f1, f2);

while being less verbose, IMHO.


The obvious inconvenient being that the indexes will grow a bit, so fewer entries will fit in memory.


Also, we don't meddle with WITH clauses (for smgr parameters or the like) nor USING <method> clauses. I reckon that implementation might be a bit intrusive (requiring changes to most index AMs even?)



Thanks!

    / J.L.

Reply via email to