Re: "The index is not optimal" GiST warnings

James Robinson <jrobinson(at)emphasys-software(dot)com> writes:
> ... provokes the following warning 14 times (on PostgreSQL 9.5):

> DEBUG: picksplit method for column 1 of index "no_overlapping_rows_orig" failed
> HINT: The index is not optimal. To optimize it, contact a developer, or try to use the column as the second one in the CREATE INDEX command.

This just indicates that you had a whole index page full of entries with
the same daterange value. I wouldn't put a lot of concern into it
(there's a reason it's only a DEBUG message), especially if there are
only 14 occurrences in what I assume is a pretty large index.

> ... It seems that, contrary to expectation / experience with btree indices, that the index backing no_overlapping_rows_two, with 'seen' as the non-leftmost column, still is useable / preferred for daterange-only queries:

GiST indexes have a preference for queries on the leading column rather
than later columns, but it's less strong than is true for btree. AFAICS
the planner doesn't account for such an effect at all. If you're seeing
a cost estimate difference, that must come just from one index being
physically larger than the other. That wouldn't be a surprising thing,
due to possibly different choices about page splits, but it's hard to say
whether it really corresponds to any noticeable difference in access
speed. Your actual-runtime results suggest that the daterange-first
index is faster, and I'd tend to believe that over any other evidence.

regards, tom lane