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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
