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

[Jeff Janes]

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

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

[pinker]

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:
http://use-the-index-luke.com/sql/myth-directory/most-selective-first




--
View this message in context: 
http://postgresql.nabble.com/performance-problems-with-bulk-inserts-updates-on-tsrange-with-gist-based-exclude-constrains-tp5921498p5922219.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general