Re: BUG #16964: Quadratic performance degradation of INSERT and ADD CONSTRAINT for intarray/GIST EXCLUDE CONSTRAINT

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

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