When a field is specified with db_index=True
, or has an implicit index, Django uses a normal CREATE indexname ON table (column)
statement to create the index in PostgreSQL. For queries using most operators this works, but if the column is text-based (e.g., TEXT
or VARCHAR
) and the locale of the database is other than 'C' -- e.g., if the database is created UTF-8 as recommended -- Postgres will not (and cannot) use this index for LIKE
queries. Getting indexes for LIKE
queries on these columns requires a second index. For example, given the following model (in an app named weblog
):
class Entry(models.Model):
title = models.CharField(max_length=255, db_index=True)
pub_date = models.DateTimeField()
body = models.TextField()
something like the following is needed to enable the index on title
to be used for all query types:
CREATE INDEX "weblog_entry_title" ON "weblog_entry" ("title");
CREATE INDEX "weblog_entry_title_like" ON "weblog_entry" ("title" varchar_pattern_ops);
The second index, created with varchar_pattern_ops
, will be used on LIKE
queries. Similarly, for a TEXT
column, a second index must be created using text_pattern_ops
.
I'm not sure whether this affects databases other than Postgres.
Add extra indexes to include operator class.