How to optimise a simple PostGIS query?

Question

I'm quite new to PostGIS and I'm trying to understand how I can optimise a simple geo query that seems to be a bit slow.

I'm using GeoDjango, but I've tried to perform the queries directly on PostgreSQL using pgAdmin, with the same results in terms of performance.

I've a table called busstop which main geo field is a Point and I've a location table which main field is a MultiPolygon.

Given a point (which I know is within 10-15 Km range from the other records) I'm trying these queries in GeoDjango (query: "give me all the records within 2Km from the base point")

Location.objects.filter(geom__distance_lt=(base_point, D(Km=2))).count()

and

BusStop.objects.filter(point__distance_lt=(base_point, D(Km=2))).count()

Just to give you a reference, busstop contains ~18000 records while location contains ~14000. The average time to compute these queries was ~4 seconds.

What I've tried to do:

1. I've recreated the location geom field using this:

   geom = models.MultiPolygonField(geography=True, spatial_index=True)

2. I've used pgAdmin -> Manage to vacuum, analyze, reindex both location and busstop table.

Result: query time have been reduced from ~4 seconds to ~2 seconds. It's something good, but it's still not the performance I would expect for so little data.

I'm pretty sure I'm doing something wrong or I'm missing something trivial that would let me have better performance. Has anyone any idea about what else I could do to improve these simple queries time?

---

I've tried to drop the database, re-create it, re-import the initial data and executed the queries again, with this results:

1. busstop query time went from 2 seconds to 183 ms
2. location query time went from 2 seconds to 1 second

I still think the location query could be further optimised.

---

This is the query executed for location:

SELECT "api_location"."id", "api_location"."name", "api_location"."point", "api_location"."geom", "api_location"."authority", "api_location"."owner", "api_location"."uprn", "api_location"."unique_asset_id" FROM "api_location" WHERE ST_Distance("api_location"."geom", ST_GeomFromEWKB('\001\001\000\000 \346\020\000\000\210\215\325\247\202\367\001\300q\216\350\015\272\301J@'::bytea)) < 2000.0

Result of explain is:

Seq Scan on api_location  (cost=0.00..4766.05 rows=3815 width=1942)
Filter: (_st_distance(geom, '0101000020E6100000888DD5A782F701C0718EE80DBAC14A40'::geography, '0'::double precision, true) < '2000'::double precision)

Answer 1

Make sure you have a spatial index on the geom column.

Next, don't use ST_Distance since that can't use a index and has to do a full distance check.

Instead use ST_DWithin. http://postgis.net/docs/ST_DWithin.html Looks like for GeoDJango (you want geom__dwithin as described here: https://stackoverflow.com/questions/2235043/geodjango-difference-between-dwithin-and-distance-lt )

SELECT "api_location"."id", "api_location"."name", "api_location"."point", "api_location"."geom", "api_location"."authority", "api_location"."owner", "api_location"."uprn", "api_location"."unique_asset_id" 
  FROM "api_location" 
    WHERE ST_DWithin("api_location"."geom", 
   ST_GeomFromEWKB('\001\001\000\000 \346\020\000\000\210\215\325\247\202\367\001\300q\216\350\015\272\301J@'::bytea)
      ,2000) ;