Why does Django create an index on a unique field explicitly

Question

Update: Simplifying the Q on experimenting with psql further:

For the following Django model:

class Book(models.Model):
    name = models.TextField(unique=True)

pg_dump (PostgreSQL 9.3) shows the following table & constraints:

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);

But PostgreSQL documentation says:

PostgreSQL automatically creates a unique index when a unique constraint [...] is defined for a table.

[...] there's no need to manually create indexes on unique columns; doing so would just duplicate the automatically-created index.

Question: Why is Django creating an index on a unique column then? Maybe the justification is that it's using the operator class text_pattern_ops, hence Django needs to add another index. If that's the case, a better way would be to interpret unique=True constraint by Django as this:

CREATE UNIQUE INDEX book_name_like ON book USING btree (name text_pattern_ops);

and not have the UNIQUE constraint in the column at all. Thus a single UNIQUE INDEX with text_pattern_ops would result in DB not creating an implicit index for UNIQUE constraint.

Answer 1

Detailed discussion in bug report: https://code.djangoproject.com/ticket/24082

Triage: Accepted to skip index when db_index=False (with unique=True)