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
12234	db_index=True creates indexes which PostgreSQL may not be able to use	James Bennett	nobody	"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."		new	Database layer (models, ORM)	SVN					Accepted	0	0	0	0	0	0