#12234 closed (fixed)
db_index=True creates indexes which PostgreSQL may not be able to use
Reported by: | James Bennett | Owned by: | jbronn |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | dev |
Severity: | Keywords: | ||
Cc: | Carl Meyer | Triage Stage: | Accepted |
Has patch: | yes | Needs documentation: | yes |
Needs tests: | yes | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description (last modified by )
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.
Attachments (3)
Change History (9)
comment:1 Changed 14 years ago by
Triage Stage: | Unreviewed → Accepted |
---|---|
Version: | 1.1 → SVN |
comment:2 Changed 14 years ago by
Description: | modified (diff) |
---|
comment:3 Changed 14 years ago by
Cc: | Carl Meyer added |
---|
Changed 14 years ago by
Attachment: | charfield_index_opclass_fix_v1.diff added |
---|
comment:4 Changed 14 years ago by
Has patch: | set |
---|---|
Needs documentation: | set |
Needs tests: | set |
Owner: | changed from nobody to jbronn |
Status: | new → assigned |
Changed 14 years ago by
Attachment: | charfield_index_opclass_fix_v2.diff added |
---|
simplifed get_index_sql
Changed 14 years ago by
Attachment: | charfield_index_opclass_fix_v3.diff added |
---|
Introspect on database type string rather than Field class; added docs to databases section.
comment:5 Changed 14 years ago by
Resolution: | → fixed |
---|---|
Status: | assigned → closed |
Add extra indexes to include operator class.