Today I Learned

hashrocket A Hashrocket project

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 = where = ? 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!

See More #sql TILs
Looking for help? Hashrocket developers believe that data quality is as important as code quality. We enjoy all the challenges of relational databases, from finding the fastest index, to structuring data to fit the needs of an application. We're eager to share our experiences; check out PG Casts, our series of free PostgreSQL screencasts.