4 ms·

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.

• rubyist5eva 4y ago
  we've been speeding up our time based queries using tsrange and gist indexes and they have been really great
• phamilton 4y ago
  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/f14aad5169baa5e2ac25d49f1d18f9d5cb3bc7f2/src/backend/utils/adt/rangetypes_gist.c#L282 https://github.com/postgres/postgres/blob/f14aad5169baa5e2ac...
  • Tostino 4y ago
    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.
    • phamilton 4y ago
      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.