I'd like to make this query perfomant--
SELECT * FROM my_table WHERE foo_id = 8 AND enabled = true AND start_time < '2020-09-12T06:59:59.999Z' AND end_time > '2020-09-07T07:00:00.000Z'
This is tricky because I need less than on start_time and greater than on end_time. If there is a composite index on
foo_id, enabled, start_time, end_time the index is good at filtering down all the rows with foo_id, enabled, and start_time matching the criteria--but then does a full scan to find the rows that match end_time.
My best efforts at optimizing so far are--
SELECT * FROM my_table WHERE foo_id = 8 AND enabled = true AND start_time < '2020-09-12T06:59:59.999Z' AND id IN ( SELECT id FROM my_table WHERE end_time > '2020-09-07T07:00:00.000Z' )
SELECT * FROM my_table WHERE foo_id = 8 JOIN my_table AS t ON t.id = my_table.id WHERE t.end_time > '2020-09-07T07:00:00.000Z' AND enabled = true AND start_time < '2020-09-12T06:59:59.999Z'
Both of these efforts try to use two indexes separately: the one on
foo_id, enabled, start_time and the other on
end_time. They are both faster. But I'd like to avoid a query with a large IN list. And doing the JOIN just to sneak in a WHERE doesn't feel right.
Maybe this is good enough? INTERSECTION would serve well here. This seems like it would be common problem, so curious if I'm missing an obvious/better solution.