On 15 September 2015 at 23:51, Nicolas Barbier <nicolas.barb...@gmail.com>
> 2015-09-15 David Rowley <david.row...@2ndquadrant.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
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services