2

Infrastructure on AWS RDS:

PostgreSQL= 9.6.1

POSTGIS="2.3.0 r15146" GEOS="3.5.0-CAPI-1.9.0 r4084" PROJ="Rel. 4.9.2, 08 September 2015" GDAL="GDAL 2.1.1, released 2 016/07/07" LIBXML="2.9.1" LIBJSON="0.12" RASTER

Problem


this is the following snippet of my django class (it has other fields I skipped)

class User(AbstractUser, TimeStampedModel):

    location = gis_models.PointField(default=Point(x=0, y=0, srid=4326),
                                     blank=True,
                                     srid=4326)
    location_updated = models.DateTimeField(null=True,
                                            blank=True,
                                            editable=False)

When I run a pure select query like

SELECT * FROM Users_user where Users_uder.id=1 

I get 77 ms execution on the query. If I remove location field (i.e. all but location field) from the query - I get 3 ms response. If I put it back - again the query time grows ~20 times.

Any ideas?

performance debugged through django-debug-toolbar


DATABASES = {
  'default': {
    'ENGINE': 'django.contrib.gis.db.backends.postgis',
    'NAME': 'dbname',
    'USER': 'dbuser',
    'PASSWORD': 'dbpass',
    'HOST': 'postgres',
    'PORT': '5432',
  }
}
  • Can you run and measure this in psql command line without Django? How many rows are you fetching? Assuming it does take more time to retrieve the points, is 77ms too slow for your needs? – Udi Mar 10 '17 at 10:52
  • I have around 3,000 records and inside psql the query is super fast. The query fetches a single row (by user_id). So it's PointField implementation that makes it slow inside Django. Weird. – DmitrySemenov Mar 10 '17 at 17:56
1

The difference might be due to the additional amount of data that PostGIS has to retrieve. Also, you might be mixing the timing for the query on the DB side with the construction of the pythonic geometry. Django converts the geometry blob that comes from the DB into a Geometry object in python. This takes a little extra time as well, but I dont know how many miliseconds it takes. Note that this conversion is "lazy" so it only happens when the geometry property is accessed.

The query you describe is equivalent to the following query:

User.objects.filter(id=1)

In your model with the location PointField, the database will return a geometry, in the other case it does not. The difference might be due to that additional amount of data to transfer. A possibility to retrieve the object faster, you could defer the loading of the geometry like this:

User.objects.defer('location').filter(id=1)

However, if you do this you don't have access to the geometry, and a second query to the DB will be required if you want to use it.

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.