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 On Wednesday, April 15, 2015 at 10:37:42 AM UTC+8, 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 > > > On 15 April 2015 at 11:58, Tommy Beadle <tbe...@gmail.com <javascript:>> > wrote: > >> 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. >> >> >> On Tue, Apr 14, 2015 at 9:01 PM, Some Developer <someukd...@gmail.com >> <javascript:>> wrote: >> >>> 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? >>> >>> -- >>> 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-develop...@googlegroups.com <javascript:>. >>> To post to this group, send email to django-d...@googlegroups.com >>> <javascript:>. >>> 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" >> >> -- >> 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-develop...@googlegroups.com <javascript:>. >> To post to this group, send email to django-d...@googlegroups.com >> <javascript:>. >> 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 >> >> <https://groups.google.com/d/msgid/django-developers/CAKfxM0LJNbT%2BWtBW0n%3DD9K3QxNjLas7H3t2ZpMWfuaxXh1uxbQ%40mail.gmail.com?utm_medium=email&utm_source=footer> >> . >> >> For more options, visit https://groups.google.com/d/optout. >> > > -- 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+unsubscr...@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/a38190f7-8e70-4262-b861-6ea3ff28d612%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
