Imagine we have this table:
CREATE TABLE reservations ( reservation_id INT GENERATED ALWAYS AS IDENTITY, room_id INT NOT NULL, date_period DATERANGE, EXCLUDE USING gist (room_id WITH =, date_period WITH &&), PRIMARY KEY (reservation_id), FOREIGN KEY (room_id) REFERENCES rooms(room_id) ON DELETE CASCADE );
The exclude using gist helps me to no overlap the date_period with the same room.
What we want is to create a composite unique index on the
room_id and the
date_period, so I could hit this index on my queries.
SELECT reservation_id FROM reservations WHERE room_id = 1 AND date_period = '[2022-09-01, 2022-09-07)';
The thing is I am not sure if I’ve already create the index with my exclude and if yes can we create a unique composite index with our overlapping date constraint?
If you use
EXPLAIN on your query, you will see that the index can be used:
EXPLAIN SELECT reservation_id FROM reservations WHERE room_id = 1 AND date_period = '[2022-09-01, 2022-09-07)'; QUERY PLAN ══════════════════════════════════════════════════════════════════════════════════════════════════════════ Index Scan using reservations_room_id_date_period_excl on reservations (cost=0.14..8.16 rows=1 width=4) Index Cond: ((room_id = 1) AND (date_period = '[2022-09-01,2022-09-07)'::daterange)) (2 rows)
If the table is small, you may have to set
enable_seqscan = off to keep PostgreSQL from using a sequential scan.
Answered By – Laurenz Albe
Answer Checked By – Clifford M. (AngularFixing Volunteer)