#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=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.
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 databases 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.
I've also verified this with the following create table statement in
PostgreSQL (no explicit index). A `SELECT` on `name` uses an index scan
instead of a sequential scan (which means there's an implicit index). So
in this case, Django doesn't need to add a `CREATE INDEX` statement.
{{{
CREATE TABLE book (
id serial primary key,
name text UNIQUE
);
}}}
However, if the justification to add a second index is to use
`text_pattern_ops` ( [https://code.djangoproject.com/ticket/12234 Bug
Report 12234] ) then it might be more efficient to interpret a
`unique=True` in the above table as
{{{
CREATE TABLE book (
id serial primary key,
name text
);
CREATE UNIQUE INDEX book_name_like ON book USING btree (name
text_pattern_ops);
}}}
i.e. no `UNIQUE` constraint in the table, only a `UNIQUE INDEX`.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/24082#comment:3>
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/063.61f52be69734d5fa51c07c56f633c07f%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
