[Django] #30267: GeoDjango does not create correct PostGIS index for 3D geometry fields
Django
-------------------------------------+-------------------------------------
Reporter: Casper | Owner: nobody
van der Wel |
Type: | Status: new
Uncategorized |
Component: GIS | Version: 1.11
Severity: Normal | Keywords: geodjango, postgis
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/30267>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Type: | Status: new
Cleanup/optimization |
Component: GIS | Version: 1.11
Severity: Normal | Resolution:
Keywords: geodjango, postgis | Triage Stage:
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* type: Uncategorized => Cleanup/optimization
Comment:
Hi Casper.
Could you take a look through the
[https://docs.djangoproject.com/en/2.2/ref/contrib/postgres/indexes
/#module-django.contrib.postgres.indexes 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
[https://docs.djangoproject.com/en/2.1/ref/models/options/#django.db.models.Options.indexes
`Options.indexes`] solve you having to use raw SQL?
(I appreciate that doesn't address your point about changing the lookup.)
--
Ticket URL: <https://code.djangoproject.com/ticket/30267#comment:1>
Django
Reporter: Casper van der Wel | Owner: nobody
Component: GIS | Version: 1.11
Severity: Normal | Resolution:
Keywords: geodjango, postgis | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Tim Graham):
* stage: Unreviewed => Accepted
Comment:
Tentatively accepting, however as I'm not a GeoDjango user, I'd like to
see a consensus on the [https://groups.google.com/forum/#!forum/geodjango
geodjango mailing list] before work proceeds.
--
Ticket URL: <https://code.djangoproject.com/ticket/30267#comment:2>
Django
Reporter: Casper van der Wel | Owner: nobody
Type: Cleanup/optimization | Status: new
Severity: Normal | Resolution:
Keywords: geodjango, postgis | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (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 [comment:1 Carlton Gibson]:
> Hi Casper.
>
> Could you take a look through the
[https://docs.djangoproject.com/en/2.2/ref/contrib/postgres/indexes
/#module-django.contrib.postgres.indexes 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
[https://docs.djangoproject.com/en/2.1/ref/models/options/#django.db.models.Options.indexes
`Options.indexes`] solve you having to use raw SQL?
>
> (I appreciate that doesn't address your point about changing the
lookup.)
>
--
Ticket URL: <https://code.djangoproject.com/ticket/30267#comment:3>
Django
Reporter: Casper van der Wel | Owner: nobody
Type: Cleanup/optimization | Status: new
Severity: Normal | Resolution:
Keywords: geodjango, postgis | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Ülgen Sarıkavak):
* cc: Ülgen Sarıkavak (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/30267#comment:4>