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           |
     "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

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

Reply via email to