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

[Casper van der Wel]

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.

[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.)

[Tim Graham]

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.

[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.)