Hacker News Reader

It’s also worth knowing that you can accelerate many sorts of range queries with a GIST index. This often ends up faster than using two separate columns.

View on HN

However, multirange GiST is implemented by merging all ranges into a single range (ignoring any gaps), which makes it less useful in many cases.

src: https://github.com/postgres/postgres/blob/f14aad5169baa5e2ac...

Right, but it just means the index is not used as the final source of truth and the DB has to run an exact check on the smaller subset of rows returned. You won't get incorrect queries, but depending on the workload it may not be a useful index for improving query speed.

Yep. In our case we were using it for scheduling purposes and "Who is free on thursday at 2pm?" and we got very little narrowing because most people are available at least some time in the morning and some time in the evening.

We've loosely landed at just materializing the dataset into another table by calling `unnest` in a trigger and then using an index on that table.

we've been speeding up our time based queries using tsrange and gist indexes and they have been really great

Reply on news.ycombinator.com