Gist indexes understand ranges

Imagine you're selling time in a room, but that room has a limited capacity. Anybody can book an arbitrary amount of time in the future, but there can only be 5 people in the room at any given time. You'll want to query the db to ask it how many people are in the room for a certain amount of time before you sell that time.

For many many rooms and many many slices of time, you'll get a query like

select count(*) from rooms r join slices s on s.room_id = r.id where r.id = ? and tstzrange('2016-01-02', '2016-01-05') && s.time_range;

This is going to take in the many hundreds of ms to run, but never fear, an index is near.

alter table slices create index speed_things_up gist(room_id, time_range);

And now the above query takes 10s of ms to run!