Strange slow behavior of a very simple query utilizing single PointField field

Question

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',
  }
}

Answer 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.