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

On Thu, Apr 15, 2021 at 7:17 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> PG Bug reporting form <noreply(at)postgresql(dot)org> writes:
> > I found that combination of EXCLUDE CONSTRAINT with intarray GIST have
> > quadratic degradation with relation size.
>
> Hm, I couldn't reproduce any such problem using this data set:
>
> regression=# create table test (team_id int, user_ids int[]);
> CREATE TABLE
> regression=# insert into test select i, array[i*2,i*2+1] from
> generate_series(1,4096) i;
> INSERT 0 4096
> regression=# create extension btree_gist ;
> CREATE EXTENSION
> regression=# create extension intarray ;
> CREATE EXTENSION
> Time: 19.077 ms
> regression=# ALTER TABLE test
> ADD CONSTRAINT unique_user_parties_on_team
> EXCLUDE USING gist (
> team_id WITH =,
> user_ids WITH &&
> );
> ALTER TABLE
> Time: 459.005 ms
> regression=# insert into test values (10, array[1,2]);
> INSERT 0 1
> Time: 1.086 ms
>
> regards, tom lane
>

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.

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

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