[PERFORM] EXCLUDE CONSTRAINT with intarray

От: Alexey Vasiliev
Тема: [PERFORM] EXCLUDE CONSTRAINT with intarray
Дата: ,
Msg-id: 1493289524.993377359@f366.i.mail.ru
(см: обсуждение, исходный текст)
Список: pgsql-performance

Hello everyone.

I have table "events" with 10 millions records. if have this fields:

Column | Type | Modifiers | Storage | Stats target | Description
---------------------+-----------------------------+-----------------------------------------------------+----------+--------------+-------------
cached_user_ids | integer[] | | extended | |
buffered_start_time | timestamp without time zone | | plain | |
buffered_end_time | timestamp without time zone | | plain | |

I am trying to add EXCLUDE CONSTRAINT to it:

ALTER TABLE events
ADD CONSTRAINT exclusion_events_on_users_overlap_and_buffers_overlap
EXCLUDE USING gist
(
cached_user_ids WITH &&,
tsrange(buffered_start_time, buffered_end_time, '[)') WITH &&
)
WHERE (
cancelled IS FALSE
)

Database have active btree_gist and intearay extensions:

select * from pg_extension ;
extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
--------------------+----------+--------------+----------------+------------+-----------+--------------
plpgsql | 10 | 11 | f | 1.0 | |
pg_stat_statements | 10 | 2200 | t | 1.4 | |
pgcrypto | 10 | 2200 | t | 1.3 | |
btree_gist | 17046 | 2200 | t | 1.2 | |
intarray | 17046 | 2200 | t | 1.2 | |"cached_user_ids" contain small amount of intefer elements (<= 10).

Problem, that this index was build in 2 days and did not finished (I stopped it). After update 10 million records fileds "buffered_start_time" and "buffered_end_time" to NULL index was builded in 30 minutes, but after this any insert start working very slow in this table.

DATABASE=> EXPLAIN ANALYZE INSERT INTO "events" (
DATABASE(> "event_type_id", "organization_id", "start_time", "end_time", "invitees_limit", "location", "cached_user_ids", "buffered_start_time", "buffered_end_time", "created_at", "updated_at", "profile_owner_id", "profile_owner_type"
DATABASE(> )
DATABASE-> VALUES (
DATABASE(> 1, 1458, '2017-05-01 00:00:00', '2017-05-01 00:30:00', 1, 'Lutsk', '{1}', '2017-05-01 00:00:00', '2017-05-01 00:30:00', '2017-04-24 12:12:12', '2017-04-24 12:12:12', 1, 'User'
DATABASE(> )
DATABASE-> RETURNING "id"
DATABASE-> ;
QUERY PLAN
--------------------------------------------------------------------------------------------------
Insert on events (cost=0.00..0.00 rows=1 width=349) (actual time=82.534..82.536 rows=1 loops=1)
-> Result (cost=0.00..0.00 rows=1 width=349) (actual time=0.046..0.047 rows=1 loops=1)
Planning time: 0.063 ms
Trigger generate_uuid: time=3.601 calls=1
Execution time: 82.734 ms
(5 rows)Before this EXCLUDE CONSTRAINT:

DATABASE=> EXPLAIN ANALYZE INSERT INTO "events" (
DATABASE(> "event_type_id", "organization_id", "start_time", "end_time", "invitees_limit", "location", "created_at", "updated_at", "profile_owner_id", "profile_owner_type"
DATABASE(> )
DATABASE-> VALUES (
DATABASE(> 1, 1458, '2017-05-02 00:00:00', '2017-05-02 00:30:00', 1, 'Lutsk', '2017-04-24 12:12:12', '2017-04-24 12:12:12', 1, 'User'
DATABASE(> )
DATABASE-> RETURNING "id"
DATABASE->
DATABASE-> ;
QUERY PLAN
------------------------------------------------------------------------------------------------
Insert on events (cost=0.00..0.00 rows=1 width=349) (actual time=1.159..1.159 rows=1 loops=1)
-> Result (cost=0.00..0.00 rows=1 width=349) (actual time=0.011..0.011 rows=1 loops=1)
Planning time: 0.033 ms
Trigger generate_uuid: time=0.303 calls=1
Execution time: 1.207 ms
(5 rows)
So I decided remove this EXCLUDE CONSTRAINT and start testing with "user_id" field, which is integer and "user_ids" filed, which is integger[] :

# This takes 10 minutes
ALTER TABLE event_memberships2
ADD CONSTRAINT "exclude_overlap1" EXCLUDE
USING gist (user_id WITH =, duration WITH &&)
WHERE (canceled IS FALSE AND user_id IS NOT NULL AND duration IS NOT NULL);

# This takes forever minutes:
ALTER TABLE event_memberships2
ADD CONSTRAINT "exclude_overlap2" EXCLUDE
USING gist (user_ids WITH &&, duration WITH &&)
WHERE (canceled IS FALSE AND user_ids IS NOT NULL AND duration IS NOT NULL);

# This takes forever minutes:
ALTER TABLE event_memberships2
ADD CONSTRAINT "exclude_overlap3" EXCLUDE
USING gist (user_ids WITH &&)
WHERE (canceled IS FALSE AND user_ids IS NOT NULL);

# This takes forever minutes:
ALTER TABLE event_memberships2
ADD CONSTRAINT "exclude_overlap3" EXCLUDE
USING gist (user_ids gist__intbig_ops WITH &&)
WHERE (canceled IS FALSE AND user_ids IS NOT NULL);


PostgreSQL: 9.6.2


So the question: does EXCLUDE CONSTRAINT works with array fields? Maybe I am doing something wrong or don't understand problems with this indexes, which building PostgreSQL.


In the meantime, thank you so much for your attention and participation.
--
Alexey Vasiliev


В списке pgsql-performance по дате сообщения:

От: Andres Freund
Дата:
Сообщение: Re: [PERFORM] Questionaire: Common WAL write rates on busy servers.
От: "Joshua D. Drake"
Дата:
Сообщение: Re: [PERFORM] Questionaire: Common WAL write rates on busy servers.