Re: [PROPOSAL] Covering + unique indexes.

2015-09-16 Rod Taylor <rod(dot)taylor(at)gmail(dot)com>:

> 2015-09-15 Anastasia Lubennikova <a(dot)lubennikova(at)postgrespro(dot)ru>:
>
>> - 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.

After thinking about it a bit more, it indeed seems never useful to
have f3 in the internal nodes if it is not part of the columns that
determine the UNIQUE property. It could as well be pushed out of the
internal nodes and only appear in the leaf nodes.

In other words: It seems only useful to have a list of columns that
appear in the internal nodes AND to which the UNIQUE property applies,
plus an addition list of columns whose values are only stored in the
leaf nodes (to create a “covering index”). For non-UNIQUE indexes,
there is also only need for two lists of columns.

I don’t understand the case where it is useful anyway, according to David:

2015-09-16 David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>:

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

Could you elaborate a bit on how this is relevant to Rod’s question? I
seem to be missing something here.

greetings,

Nicolas

--
A. Because it breaks the logical sequence of discussion.
Q. Why is top posting bad?