Maybe I’m doing something wrong, but I find I’m fighting PostGIS’s query planner way too often. The problem is that I have a table with a lot of records, the planner incorrectly thinks a geospatial filter will eliminate a lot of the rows, and then decides to do an index scan across the entire table. This happens even when I pair the geo filter with a highly selective date range query. Seems like a known problem with GIST indexes.
Reworking the query to use a subselect, wrapping the geo select around the date filtered select, seems to do the trick. But when you’re working against the system either you’re doing something the system wasn’t designed to do, or you don’t understand the system. Or both.
More investigation needed.