Re: [PROPOSAL] Covering + unique indexes.

On 16 September 2015 at 10:38, Rod Taylor <rod(dot)taylor(at)gmail(dot)com> wrote:

>
>
> 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?
>
>
Joining relations may have more than one matching tuple for any given
unique tuple, therefore the tuples may no longer be unique on the columns
which are in the unique index.

https://commitfest.postgresql.org/6/129/ takes steps to add infrastructure
to the planner to allow it to know when this happens. Although I'm
currently "selling" it as a performance improvement patch.

Regards

David Rowley

--
David Rowley http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/>
PostgreSQL Development, 24x7 Support, Training & Services