pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: maxim.boguk@gmail.com
Subject: BUG #16964: Quadratic performance degradation of INSERT and ADD CONSTRAINT for intarray/GIST EXCLUDE CONSTRAINT
Date: Thu, 15 Apr 2021 10:16:41 +0000
Message-ID: <16964-5dd796d85c4846cd@postgresql.org> (raw)

The following bug has been logged on the website:

Bug reference:      16964
Logged by:          Maxim Boguk
Email address:      maxim.boguk@gmail.com
PostgreSQL version: 13.2
Operating system:   Linux
Description:        

I found that combination of EXCLUDE CONSTRAINT with intarray GIST have
quadratic degradation with relation size.
Making it completely useless with tables starting from few thousand rows.

Test dataset can be provided by request (100kb sql file).
Confirmed for 13.2 and 12.* versions.

create extension intarray;
create extension btree_gist;
\i /tmp/2.sql
CREATE TABLE
COPY 4000

structure of 
test=# \d+ test
                                      Table "public.test"
  Column  |   Type    | Collation | Nullable | Default | Storage  | Stats
target | Description 
----------+-----------+-----------+----------+---------+----------+--------------+-------------
 team_id  | integer   |           |          |         | plain    |         
    | 
 user_ids | integer[] |           |          |         | extended |         
    | 

1 or 2 user_id per array.

Performance results:
ALTER TABLE test
ADD CONSTRAINT unique_user_parties_on_team
      EXCLUDE USING gist (
        team_id WITH =,
        user_ids WITH &&
      );
ALTER TABLE
Time: 911198.957 ms (15:11.199)

(with 2k rows Time: 217885.618 ms (03:37.886))

performance of insert (on 4k rows set):
insert into test  values (10, array[1,2]);
INSERT 0 1
Time: 1204.211 ms (00:01.204)

perf record/report for ADD CONSTRAINT:
  39.65%  postgres  postgres                    [.] pg_qsort
  35.60%  postgres  _int.so                     [.] compASC
   3.68%  postgres  postgres                    [.] swapfunc
   3.26%  postgres  _int.so                     [.] _int_unique
   3.15%  postgres  _int.so                     [.] g_int_decompress
   2.69%  postgres  libc-2.31.so                [.] 0x000000000018ead1
   1.65%  postgres  _int.so                     [.] inner_int_union

perf record/report for INSERT:
  39.32%  postgres  postgres                    [.] pg_qsort
  36.08%  postgres  _int.so                     [.] compASC
   3.18%  postgres  postgres                    [.] swapfunc

It look like somewhat broken for me (especially for so simple use
case/common scenario).

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



view thread (4+ messages)  latest in thread

Message-ID: <16964-5dd796d85c4846cd@postgresql.org>
Permalink:  ../16964-5dd796d85c4846cd@postgresql.org/
Also on:    postgresql.org/message-id/16964-5dd796d85c4846cd@postgresql.org

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-bugs@postgresql.org
  Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, maxim.boguk@gmail.com
  Subject: Re: BUG #16964: Quadratic performance degradation of INSERT and ADD CONSTRAINT for intarray/GIST EXCLUDE CONSTRAINT
  In-Reply-To: <16964-5dd796d85c4846cd@postgresql.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox