1

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)
  • Please paste into your question your query and a result of EXPLAIN - it'll be better than guessing what possibly could be wrong... – Jendrusk Nov 29 '16 at 10:49
  • @Jendrusk done. Is it what you needed? Cheers – Andrea Grandi Nov 29 '16 at 11:31
  • As said @LR1234567 - this steps should help – Jendrusk Nov 29 '16 at 13:57
2

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) ;
  • Thank you so much! Using geom__dwithin the query time went from 1 second to 117 ms! – Andrea Grandi Nov 29 '16 at 14:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.