#24082: Unique=True on TextField or CharField should not create an index
-------------------------------------+-------------------------------------
Reporter: djbug | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.7
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Description changed by djbug:
Old description:
> 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_idx" ON "book" ("name"
> text_pattern_ops);
>
> }}}
>
> instead of a `UNIQUE` constraint, an `INDEX` and an implicit index by the
> database.
New description:
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=True` as
{{{
CREATE UNIQUE INDEX "book_name_like_idx" ON "book" ("name"
text_pattern_ops);
}}}
instead of a `UNIQUE` constraint, an `INDEX` and an implicit index by the
database.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/24082#comment:2>
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 django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/063.423d68920feb1513edba161de94badc1%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
