id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
24082	Unique=True on TextField or CharField should not create an index	djbug		"
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`. 

"	Bug	new	Database layer (models, ORM)	1.7	Normal		db-indexes	Shai Berger Simon Charette emorley@… Mariusz Felisiak Phil Krylov Semyon Pupkov Can Sarıgöl Peter Thomassen	Accepted	0	0	0	0	0	0