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

[Chris Withers <chris@xxxxxxxxxxxxxxxx>]

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?

cheers,

Chris


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