Changes between Version 2 and Version 3 of Ticket #24082
- Timestamp:
- Jan 5, 2015, 11:10:12 PM (9 years ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
Ticket #24082 – Description
v2 v3 31 31 }}} 32 32 33 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.33 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. 34 34 35 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 36 35 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. 37 36 38 37 {{{ 39 CREATE UNIQUE INDEX "book_name_like_idx" ON "book" ("name" text_pattern_ops); 40 38 CREATE TABLE book ( 39 id serial primary key, 40 name text UNIQUE 41 ); 41 42 }}} 42 43 43 instead of a `UNIQUE` constraint, an `INDEX` and an implicit index by the database. 44 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 45 46 {{{ 47 CREATE TABLE book ( 48 id serial primary key, 49 name text 50 ); 51 52 CREATE UNIQUE INDEX book_name_like ON book USING btree (name text_pattern_ops); 53 }}} 54 55 i.e. no `UNIQUE` constraint in the table, only a `UNIQUE INDEX`. 56