A colleague published a very impressive commit recently.
Way faster, cost drops from 161877 to 212 […].
The thing is, the changed seemed inoffensive:
- self.filter(coords__distance_lte=(point, D(km=distance_km)))
+ self.filter(coords__dwithin=(point, D(km=distance_km)))
What is happening here ? This piece of code takes place inside a Django ORM query that calls Postgis.
In Postgresql/Postgis, ST_DWithin and ST_Distance have different use cases. StackOverflow explains both:
ST_Distance actually calculates the distance between all the pairs of points, so, as such, no index could be used. So your query will do a sequence scan and then choose those geometries that are less than the distance you specify away. You are looking for ST_DWithin, which does use an index.
SELECT SUM(population) FROM points
WHERE ST_DWithin(location, ST_GeographyFromText('SRID=4326; POINT(0 0)'), 1000);
ST_Distance is more useful for ordering results, often in conjunction with ORDER BY and/or LIMIT, that have been obtained with queries that do use an index.