Обсуждение: performance problems with bulk inserts/updates on tsrange with gist-based exclude constrains
performance problems with bulk inserts/updates on tsrange with gist-based exclude constrains
От
Chris Withers
Дата:
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