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

On Fri, Sep 16, 2016 at 2:01 AM, Chris Withers <chris(at)simplistix(dot)co(dot)uk>
wrote:

> Hi All,
>
> I have quite a few tables that follow a pattern like this:
>
> Table "public.my_model"
> Column | Type | Modifiers
> --------+-------------------+-----------
> period | tsrange | not null
> key | character varying | not null
> value | integer |
> Indexes:
> "my_model_pkey" PRIMARY KEY, btree (period, key)
> "my_model_period_key_excl" EXCLUDE USING gist (period WITH &&, key
> WITH =)
> Check constraints:
> "my_model_period_check" CHECK (period <> 'empty'::tsrange)
>

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.

Cheers,

Jeff