On Wed, Sep 21, 2016 at 2:18 PM, pinker <pinker@xxxxxxx> wrote:
Jeff Janes wrote
> Try swapping the order of the columns in the exclude constraint. You want
> the more selective criterion to appear first in the index/constraint.
> Presumably "key with =" is the most selective, especially if many of your
> periods are unbounded.
I would not be so sure with that:
As a rule, I generally don't spout random nonsense. Or at least, not without including a disclaimer. I didn't test it on the OPs exact case, because he has need blessed us with his data or his scripts. But I have tested it on other data, and it does work.
http://use-the-index-luke.com/sql/myth-directory/most- selective-first
I don't see how anything there applies to GiST indexes. Indeed, there doesn't seem to be much there worth reading at all. The only thing vaguely informative, other than trivia about other RDBMS, is "It’s useless to have the most selective column of the index on the left if very few queries filter on it", which is rather obvious, but also obviously does not apply to this case.
Cheers,
Jeff
