Re: [PROPOSAL] Covering + unique indexes.

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.