#24082: Unique=True on TextField or CharField should not create an index
----------------------------------------------+--------------------
Reporter: djbug | Owner: nobody
Type: Bug | Status: new
Component: Database layer (models, ORM) | Version: 1.7
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------------------+--------------------
I've experienced this with PostgreSQL but I suspect it could be true for
other databases too.
[http://www.postgresql.org/docs/9.3/static/indexes-unique.html PostgreSQL
docs] say:
{{{
there's no need to manually create indexes on unique columns; doing so
would just duplicate the automatically-created index.
}}}
Further the docs say:
`The index covers the columns that make up the [...] unique constraint
[...] and is the mechanism that enforces the constraint.`
However this model in Django with `unique=True` on a `TextField` creates
an index on top of the unique constraint.
{{{
class Book(models.Model):
name = models.TextField(unique=True)
}}}
creates following table & constraints in PostgreSQL:
{{{
CREATE TABLE book (
id integer NOT NULL,
name text NOT NULL,
);
ALTER TABLE ONLY book ADD CONSTRAINT book_name_key UNIQUE (name);
CREATE INDEX book_name_like ON book USING btree (name text_pattern_ops);
}}}
Please correct me if I'm wrong. My conclusion is that database enforce
unique constraint by way of an index. Adding another index is a waste.
There's some mention of this fact in an old bug report
([https://code.djangoproject.com/ticket/3030#comment:3 comment 3] &
[https://code.djangoproject.com/ticket/3030#comment:6 comment 6] ) but it
looks like the issue got dropped.
However, if the justification to add a second index is
[https://code.djangoproject.com/ticket/12234 Bug Report 12234] then it
might be more efficient to interpret a unique constraint as
{{{
CREATE UNIQUE INDEX "book_name_like" ON "book" ("name" text_pattern_ops);
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/24082>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/048.d0e4184230ac68c6fd66db737f8cf490%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
