I help my clients acquire new users and make more money with their web businesses. I have ten years of experience with SaaS projects. If that’s something you need help with, we should get in touch!
< Back to article list

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.