Re: [PROPOSAL] Covering + unique indexes.

On Tue, Sep 15, 2015 at 12:57 PM, Anastasia Lubennikova <
a(dot)lubennikova(at)postgrespro(dot)ru> wrote:

>
> Proposal Clarification.
> I see that discussion become too complicated. So, I'd like to clarify
> what we are talking about.
>
> We are discussing 2 different improvements of index.
> The one is "partially unique index" and the other "index with included
> columns".
> Let's look at example.
>
> - We have a table tbl(f1, f2, f3, f4).
> - We want to have an unique index on (f1,f2).
> - We want to have an index on (f1, f2, f3) which allow us to use index for
> complex "where" clauses.
>

Can someone write a query where F3 being ordered is a contribution?

If F1 and F2 are unique, adding F3 to a where or order by clause doesn't
seem to contribute anything.

-- Already fully ordered by F1,F2
SELECT ... ORDER BY F1, F2, F3;

-- F3 isn't in a known order without specifying F2
SELECT ... WHERE F1 = ? ORDER BY F1, F3;

-- Index resolves to a single record; nothing to order
SELECT ... WHERE F1 = ? AND F2 = ? ORDER BY F3;

-- Without a where clause, the index isn't helpful unless F3 is the first
column
SELECT ... ORDER BY F3;

What is it that I'm missing?