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)]
I guess this would possibly be a path to create indexes... without any actual data in the table. Sequential scans would be costly, but queries with a predicate matching the sorted columns would be very quick. Not sure how REINDEX could be made to work with that though. And might end up requiring something like partitioned indexes.
Disclaimer: I *really* haven't thought this through.
-- 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 ?
