I am using the Postgis ST_DWithin function like this:
SELECT *, ST_X(geometry) as longitude, ST_Y(geometry) as latitude, ST_Distance_Sphere( ST_SetSRID( ST_MakePoint(-118.478255,34.303046999999999), 4326), geometry) / 1609.34 as DISTANCE FROM poi WHERE ( category =10980400 ) AND ST_DWithin(geometry::geography, (ST_SetSRID( ST_MakePoint(-118.478255,34.303046999999999), 4326)::geography ), 80467.2, false) ORDER BY DISTANCE LIMIT 10
I am using it for finding nearest points (like ATMs, restaurants) within a radius.
I have indexes available for geometry, geography and a btree index for category. When I "explain analyze" the query I can see that the query only uses category index. Not sure if it happens always or is it a runtime decision.
The problem is that there are cases where the query performs better if I remove category index and the query uses spatial index.
Similarly, there are cases when I have all indexes and the query is better by using category index. But then the cases above degrade.
Why can't postgis decide the optimum way ?? I have run VACUUM ANALYZE on the table after having all the indexes.
SELECT version() : "PostgreSQL 9.3.14"
SELECT postgis_version() : "2.1 USE_GEOS=1 USE_PROJ=1 USE_STATS=1"
Here are the indexes I have:
CREATE INDEX world_poi_idx4 ON public.poi USING gist ((geometry::geography)); CREATE INDEX world_poi_new_idx3 ON public.poi USING gist (geometry); CREATE INDEX world_poi_idx1 ON public.poi USING btree (category);
Here are the results of explain analyze (example of just one case, results are different in other cases, this is just to provide an idea):
When category index is present: https://explain.depesz.com/s/iZWj
When category index is not present: https://explain.depesz.com/s/0WH