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
