James Robinson <jrobinson@xxxxxxxxxxxxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
