GeoDjango is creating two indexes for geometry field

[George Silva]

Hello everyone,

Environment:

Ubuntu 14.04
Postgresql 9.3.4
PostGIS 2.1.3
Django 1.7

I'm using a simple model with a custom shapefile importer. Here's the model.

class Dsei(models.Model):
    """Modelo que representa um DSEI na base de dados"""

    dsei_siasi = models.OneToOneField(DseiSiasi,
                                      related_name="dsei_geosi",
                                      db_constraint=False)

    geometria = models.PolygonField(verbose_name=u"Geometria",
                                    srid=SRID_PADRAO,
                                    db_index=True,
                                    db_column="geom_geometria")

    def __unicode__(self):
        return "(Geosi) " + self.dsei_siasi.nome

Note that I have a geometric field (geometria), with a specified db_index=True.

I'm using django 1.7 and I noticed that my migration is correct, specifying the field. BUT, when I try to import using my custom shapefile importer, PostgreSQL complains that the value is to large for the index size. When I peeked at the table definition in pgAdmin3, it reported me two indexes on the geometric field, a BTREE and a GIST index.

-- Table: aldeias_dsei

-- DROP TABLE aldeias_dsei;

CREATE TABLE aldeias_dsei
(
  id serial NOT NULL,
  geom_geometria geometry(Polygon,4674) NOT NULL,
  dsei_siasi_id numeric(9,0) NOT NULL,
  CONSTRAINT aldeias_dsei_pkey PRIMARY KEY (id),
  CONSTRAINT aldeias_dsei_dsei_siasi_id_key UNIQUE (dsei_siasi_id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE aldeias_dsei
  OWNER TO postgres;

-- Index: aldeias_dsei_f8945348

-- DROP INDEX aldeias_dsei_f8945348;

CREATE INDEX aldeias_dsei_f8945348
  ON aldeias_dsei
  USING btree
  (geom_geometria);

-- Index: aldeias_dsei_geom_geometria_id

-- DROP INDEX aldeias_dsei_geom_geometria_id;

CREATE INDEX aldeias_dsei_geom_geometria_id
  ON aldeias_dsei
  USING gist
  (geom_geometria);

My guess is that:

a) perhaps the migration when applying is creating both indexes
b) database backend is somehow defaulting to normal behavior AND creating the specific index, resulting in two indexes.

Upon checking the code for sql_indexes_for_field method, looks like B is correct. It's calling super.

I just now saw that GeoDjango has a spatial_index keyword argument, but db_index=True is also creating a spatial index.

This should be fixed, to:

When you specify a spatial_index=True, only a spatial index should be created (this looks correct);
When you specify a db_index=True, only a BTREE index should be created (both indexes are being created, GIST and BTREE);

My error in code will be solved, I'll switch from db_index to spatial_index and it will be solved, but the creation of these both indexes should not happen.

[Claude Paroz]

As you discovered, the immediate solution is to use spatial_index instead of db_index.
You are right in that the current state of index creation/management is a bit blurry now, but "we have a plan™"! See ​https://github.com/django/deps/pull/6/files
Hopefully this planned refactoring of indexes will solve this issue. I don't think it's worth keeping the ticket open. Thanks for the detailed report anyway.

[George Silva]

Well, this is great news.

What is the timeline for this feature? 1.8?

Very cool.