Re: [PROPOSAL] Covering + unique indexes.

On 16 September 2015 at 14:03, José Luis Tallón <jltallon(at)adv-solutions(dot)net>
wrote:

> 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 ?
>

We don't currently have REINDEX CONCURRENTLY.

--
Thom