Re: [PROPOSAL] Covering + unique indexes.

On 15 September 2015 at 23:51, Nicolas Barbier <nicolas(dot)barbier(at)gmail(dot)com>
wrote:

> 2015-09-15 David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>:
>
> > I'm also a bit confused where f3 comes in here. If it's UNIQUE on (f1,f2)
> > and we include f4. Where's f3?
>
> Columns f1, f2, f3 are in the internal nodes of the tree (i.e., they
> are used to find the ultimate leaf nodes). f4 is only in the leaf
> nodes. If f4 are typically big values, and they are typically not used
> in the search predicate, it makes the upper part of the index (which
> determines how many levels the index has) larger for no good reason.
> f4 can still be retrieved without going to the heap, so including it
> in the leaf nodes makes it possible to do index-only scans more often.
>
>
Hmm, ok, I guess I was unable to see any advantage to having f3 in the
btree, if it's not to be enforced as part of the unique constraint.
I now see that this is probably to allow pre-sorted paths without having to
enforce uniqueness over all of the indexed columns.

If that's the case then I assume that we'd also want something to allow
that to be done when creating a PRIMARY KEY constraint

Regards

David Rowley

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