Postgres Pro
Downloads
Home   >   mailing lists

Re: BUG #16964: Quadratic performance degradation of INSERT and ADD CONSTRAINT for intarray/GIST EXCLUDE CONSTRAINT - Mailing list pgsql-bugs

From Maxim Boguk
Subject Re: BUG #16964: Quadratic performance degradation of INSERT and ADD CONSTRAINT for intarray/GIST EXCLUDE CONSTRAINT
Date
Msg-id CAK-MWwRqJhFShbk__bxOxzC9Ctf8bc3VqivdbNPdLgXUQ5-KUg@mail.gmail.com
Whole thread Raw
In response to Re: BUG #16964: Quadratic performance degradation of INSERT and ADD CONSTRAINT for intarray/GIST EXCLUDE CONSTRAINT  (Maxim Boguk <maxim.boguk@gmail.com>)
List pgsql-bugs
Tree view


Hi Tom,

Please see my dataset (2000 rows)
I found an issue actually not related to the btree_gist at all...

Only intarray_gist is enough:
\i test_table_plain_dump_no_constraint.sql
create extension intarray;
\timing on

ALTER TABLE test
ADD CONSTRAINT unique_user_parties
      EXCLUDE USING gist (
        user_ids WITH &&
);

My laptop requires at least 5 minutes to finish it.
Inserts also awfully slow.

After future research I found that
test=# create index test_idx on test using gist(user_ids gist__int_ops);
CREATE INDEX
Time: 200375.964 ms (03:20.376)
test=# create index test1_idx on test using gist(user_ids gist__intbig_ops);
CREATE INDEX
Time: 86.798 ms

have few orders of magnitude difference in runtime...

So I tried
test=# ALTER TABLE test
ADD CONSTRAINT unique_user_parties
      EXCLUDE USING gist (
        user_ids gist__intbig_ops WITH &&                
        );
ALTER TABLE
Time: 172.176 ms

With work without any performance issues.

So I got bitten by gist__int_ops (used by default) of intarray again.
I yet to see any realistic use case when gist__int_ops provide any performance gain over gist__intbig_ops.



--
Maxim Boguk
Senior Postgresql DBA
https://dataegret.com/

Phone RU: +7  985 433 0000
Phone UA: +380 99 143 0000
Phone AU: +61  45 218 5678

LinkedIn: http://www.linkedin.com/pub/maksym-boguk/80/b99/b1b
Skype: maxim.boguk

"Доктор, вы мне советовали так не делать, но почему мне по-прежнему больно когда я так делаю ещё раз?"

pgsql-bugs by date:

Previous
From: Maxim Boguk
Date:
Subject: Re: BUG #16964: Quadratic performance degradation of INSERT and ADD CONSTRAINT for intarray/GIST EXCLUDE CONSTRAINT
Next
From: PG Bug reporting form
Date:
Subject: BUG #16966: Nested loop joining across tables with varchar -> bpchar cast always scans varchar table first