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