Re: Re: performance problems with bulk inserts/updates on tsrange with gist-based exclude constrains

On Wed, Sep 21, 2016 at 2:18 PM, pinker <pinker(at)onet(dot)eu> 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