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.
