Unneeded index created with unique=True and db_index=False in TextField

[Some Developer]

Using Django 1.8, psycopg2 2.6 and PostgreSQL 9.4.1.

I have a model with a models.TextField(unique=True, db_index=False,
primary_key=False) field in it.

I understand that an index is created because of the comment shown in
this code:

https://github.com/django/django/blob/master/django/db/backends/postgresql_psycopg2/schema.py#L17

but even though the index is suggested for LIKE queries using non C
locales I would have thought the addition of db_index=False would have
negated that.

I feel that this is a bug. An index is not required by PostgreSQL on a
unique constraint (it may be recommended but that is beside the point)
and if I explicitly state db_index=False then the Django ORM should
remove the index even though the index is recommended.

Thoughts?

[Tommy Beadle]

I believe that Postgres will *always* create an index on a column with a UNIQUE constraint.

regression=> create table yo (id serial primary key, blah varchar(32) unique);
CREATE TABLE
regression=> \d yo
                                Table "public.yo"
 Column |         Type          |                    Modifiers                   
--------+-----------------------+-------------------------------------------------
 id     | integer               | not null default nextval('yo_id_seq'::regclass)
 blah   | character varying(32) |
Indexes:
    "yo_pkey" PRIMARY KEY, btree (id)
    "yo_blah_key" UNIQUE CONSTRAINT, btree (blah)

regression=> drop index yo_blah_key;
ERROR:  cannot drop index yo_blah_key because constraint yo_blah_key on table yo requires it
HINT:  You can drop constraint yo_blah_key on table yo instead.

--
You received this message because you are subscribed to the Google Groups "Django developers  (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-developers+unsubscribe@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at http://groups.google.com/group/django-developers.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/552DB881.1090006%40googlemail.com.
For more options, visit https://groups.google.com/d/optout.

--

Grace and Peace,
Tommy B.

I want to live like I know what I'm leaving.
--Switchfoot, "Awakening"

[Curtis Maloney]

Was the OP referring to the unique index, or the index created for the LIKE lookups?

I was involved in a discussion recently [was there something on list too?] wanting to be able to opt-out of the second index because they knew they didn't need it, and it was _huge_ on their database.

--

C

To unsubscribe from this group and stop receiving emails from it, send an email to django-develop...@googlegroups.com.
To post to this group, send email to django-d...@googlegroups.com.

Visit this group at http://groups.google.com/group/django-developers.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/CAKfxM0LJNbT%2BWtBW0n%3DD9K3QxNjLas7H3t2ZpMWfuaxXh1uxbQ%40mail.gmail.com.

[Alex Hill]

I agree this is a bug, and I think it's independent of the discussion of customisable indexes.

Postgres creates the necessary index for unique constraints automatically as Tommy said. I'm guessing other backends do too, because the explicit index creation DDL is omitted when unique is True in the superclass's _model_indexes_sql. [1] So too should the xxx_pattern_ops index DDL be omitted unless db_index is True.

Line 24 of the linked file currently reads: [2]

if db_type is not None and (field.db_index or field.unique):

# Create the special text index

The xxx_pattern_ops operator classes only help with LIKE and regex queries; the unique constraint doesn't need them. I think we can change that to the following with no ill effects:

if db_type is not None and field.db_index:

Cheers,

Alex

[1] https://github.com/django/django/blob/28e89783254ac0899a26eee324555a9033ccbe9a/django/db/backends/base/schema.py#L852

[2] https://github.com/django/django/blob/28e89783254ac0899a26eee324555a9033ccbe9a/django/db/backends/postgresql_psycopg2/schema.py#L24

To unsubscribe from this group and stop receiving emails from it, send an email to django-develop...@googlegroups.com.
To post to this group, send email to django-d...@googlegroups.com.
Visit this group at http://groups.google.com/group/django-developers.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/552DB881.1090006%40googlemail.com.
For more options, visit https://groups.google.com/d/optout.

--

Grace and Peace,
Tommy B.

I want to live like I know what I'm leaving.
--Switchfoot, "Awakening"

[Some Developer]

On 15/04/15 03:37, Curtis Maloney wrote:
> Was the OP referring to the unique index, or the index created for the
> LIKE lookups?
>
> I was involved in a discussion recently [was there something on list
> too?] wanting to be able to opt-out of the second index because they
> knew they didn't need it, and it was _huge_ on their database.
>
> --
> C
>

The index created for the LIKE lookups.

The index name even has _like appended on to the name so I'm pretty sure
that this is the problem at hand.

[Tim Graham]

#19441 is the ticket where it was decided that unique=True implies a database index. The documentation says,

"Note that when ``unique`` is ``True``, you don't need to specify

`Field.db_index`, because ``unique`` implies the creation of an index."

Unless we decide that is wrong, I think the correct condition to solve the issue would be more like `field.db_index or field.unique and field.db_index is not False`. We might also have to change the default keyword of Field from False to None so we know the difference between "unspecified" and False.

[Some Developer]

On 15/04/15 18:22, Tim Graham wrote:
> #19441 is the ticket where it was decided that unique=True implies a
> database index. The documentation says,
>
> "Note that when ``unique`` is ``True``, you don't need to specify
>
> `Field.db_index`, because ``unique`` implies the creation of an index."
>

Understood. I just want to be able to stop the creation of the index in
some way. Whether that be via db_index=False or some other new method.

I would have thought that the creation of the index would be optional. I
understand that the default it to create the index and that is fine for
the vast majority of cases (in fact until I hit this little problem I
didn't care one way or another) but a user should have the option to
tell Django not to create the index if they are sure they know what they
are doing.

> Unless we decide that is wrong, I think the correct condition to solve

> the issue would be more like `field.db_index orfield.unique and

[Kevin Christopher Henry]

This issue was the subject of https://code.djangoproject.com/ticket/24082.

There, the accepted (but not implemented) solution is the same as suggested here: allowing the user to opt out of the creation of the additional index with `db_index=False`.

[Some Developer]

On 15/04/15 23:45, Kevin Christopher Henry wrote:
> This issue was the subject of https://code.djangoproject.com/ticket/24082.
>
> There, the accepted (but not implemented) solution is the same as
> suggested here: allowing the user to opt out of the creation of the
> additional index with `db_index=False`.

Ah, sorry I didn't see that. That solution would certainly work.

Thanks.