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

Поиск
Список
Период
Сортировка
От Chris Withers
Тема performance problems with bulk inserts/updates on tsrange with gist-based exclude constrains
Дата
Msg-id 6f34abf5-843f-3b6f-6da9-3b7e9d91edc2@simplistix.co.uk
обсуждение исходный текст
Ответы Re: performance problems with bulk inserts/updates on tsrange with gist-based exclude constrains  (John R Pierce <pierce@hogranch.com>)
Re: performance problems with bulk inserts/updates on tsrange with gist-based exclude constrains  (Jeff Janes <jeff.janes@gmail.com>)
Список pgsql-general
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


В списке pgsql-general по дате отправления:

Предыдущее
От: Arun Rangarajan
Дата:
Сообщение: Re: Unable to create oracle_fdw (foreign data wrapper) extension
Следующее
От: John R Pierce
Дата:
Сообщение: Re: performance problems with bulk inserts/updates on tsrange with gist-based exclude constrains