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

On 9/16/2016 2:01 AM, Chris Withers 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)
>
> So, a primary key of a period column and one or more other columns
> (usually int or string) and an exclude constraint to prevent overlaps,
> and a check constraint to prevent empty ranges.
>
> However, I'm hitting performance problems on moderate bulk inserts and
> updates, with ~700k rows taking around 13 minutes. Profiling my python
> code suggests that most of the time is being taken by Postgres (9.4 in
> this case...)
>
> What can I do to speed things up? Is there a different type of index I
> can use to achieve the same exclude constraint? Is there something I
> can do to have the index changes only done on the commit of the bulk
> batches?

if (period,key) is unique, by virtue of being the primary key, then
whats the point of the exclusion ??

I'm curious, how fast do your insert/updates run if you remove the key
exclusion and check constraint ?      tsvector operations are a lot more
complicated than simple matches in indexing....




--
john r pierce, recycling bits in santa cruz