Create a unique index using gist with a column of type daterange and a text column

Question

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?

Answer 1

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.