Opened 17 months ago

Last modified 17 months ago

#30267 new Cleanup/optimization

GeoDjango does not create correct PostGIS index for 3D geometry fields

Reported by: Casper van der Wel Owned by: nobody
Component: GIS Version: 1.11
Severity: Normal Keywords: geodjango, postgis
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

When I create a model with a 3D geometryfield, an index USING gist (geom gist_geometry_ops_nd) is created automatically by geodjango in my PostGIS database. However, the spatial lookups 'intersects' and 'bboverlaps' do not use this index, which I can confirm by explaining the queries. Below some code to reproduce this issue.

I am not sure if this is a bug or if I am reporting a non-intended use case for 3D geometryfields. It appears to me as a pretty straightforward use case.

The model:

from django.contrib.gis.db import models
from django.db import connections
from django.db.models.query import QuerySet

class QuerySetExplain(QuerySet):
    def explain(self):
        cursor = connections[self.db].cursor()
        query, params = self.query.sql_with_params()
        cursor.execute('EXPLAIN %s' % query, params)
        return '\n'.join(r[0] for r in cursor.fetchall())

class PolyModel(models.Model):
    objects = QuerySetExplain.as_manager()
    geometry = models.PolygonField(srid=28992, dim=3)

After creating and running migrations, I explain the following query:

>>> from django.contrib.gis.geos import Polygon
>>> from my_app.models import PolyModel
>>> bbox = Polygon.from_bbox((0, 0, 1, 1))
>>> qs = PolyModel.objects.filter(geometry__intersects=bbox)
>>> print(qs.explain())

Seq Scan on my_app_polymodel  (cost=0.00..2964.00 rows=1 width=241)
  Filter: ((geometry && '0103000020E61000000100000005000000000000000000000000000000000000000000000000000000000000000000F03F000000000000F03F000000000000F03F000000000000F03F000000000000000000000000000000000000000000000000'::geometry) AND _st_intersects(geometry, '0103000020E61000000100000005000000000000000000000000000000000000000000000000000000000000000000F03F000000000000F03F000000000000F03F000000000000F03F000000000000000000000000000000000000000000000000'::geometry))

This says that is used a sequential scan. It did not use the index
The solution for me is creating the indices "USING gist (geometry)" with raw SQL.
Looking at PostGIS manuals, it seems you have to use the operator &&& to actually use an ND index. I can confirm that in the sql shell.

Versions: I am using django 1.11.20, PostGRES 9.3 with PostGIS 2.4. I also reproduced the issue in raw SQL using PostGRES 10.

After glancing the django source code, it appears to me that it persists in master. But I did not run any tests.

A quickfix would be always creating 2 indexes on 3D geometry fields. But that does seem a waste of resources. Using &&& instead of && as the 'bboverlaps' lookup would probably a better fix, but I am not such a PostGIS expert that I can oversee the changes necessary in the django ORM.

Thanks in advance for looking at this bug report.

Change History (3)

comment:1 Changed 17 months ago by Carlton Gibson

Type: UncategorizedCleanup/optimization

Hi Casper.

Could you take a look through the list of Postgres indexes? There have been additions since 1.11. #28887, #28126, adding Gist and SP-Gist indexes in particular.

Does using these with `Options.indexes` solve you having to use raw SQL?

(I appreciate that doesn't address your point about changing the lookup.)

comment:2 Changed 17 months ago by Tim Graham

Triage Stage: UnreviewedAccepted

Tentatively accepting, however as I'm not a GeoDjango user, I'd like to see a consensus on the geodjango mailing list before work proceeds.

comment:3 in reply to:  1 Changed 17 months ago by Casper van der Wel

Thanks for the response. I tried to add a GIST index using django.contrib.postgres.indexes. As I am using Django 1.11, I had to write the index class. But it worked, I do not have to write raw sql anymore for setting the 2D index.

class GistIndex(Index):
    suffix = '2d'

    def create_sql(self, model, schema_editor):
        return super().create_sql(model, schema_editor, using=' USING gist')

class PolyModel(models.Model):
    objects = QuerySetExplain.as_manager()
    geometry = models.PolygonField(srid=28992, dim=3)

    class Meta:
        indexes = [GistIndex(['geometry'])]

This yields a table with 2 indexes, one USING gist (geometry) and one USING gist (geometry GIST_GEOMETRY_OPS_ND)

Replying to Carlton Gibson:

Hi Casper.

Could you take a look through the list of Postgres indexes? There have been additions since 1.11. #28887, #28126, adding Gist and SP-Gist indexes in particular.

Does using these with `Options.indexes` solve you having to use raw SQL?

(I appreciate that doesn't address your point about changing the lookup.)

Note: See TracTickets for help on using tickets.
Back to Top