KeiruaProd

Postgis: ST_Distance vs ST_DWithin

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.

See a typo ? You can suggest a modification on Github.