2015-09-15 David Rowley <david.rowley@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