Opened 5 years ago

Closed 5 years ago

Last modified 5 years ago

#12234 closed (fixed)

db_index=True creates indexes which PostgreSQL may not be able to use

Reported by: ubernostrum Owned by: jbronn
Component: Database layer (models, ORM) Version: master
Severity: Keywords:
Cc: carljm Triage Stage: Accepted
Has patch: yes Needs documentation: yes
Needs tests: yes Patch needs improvement: no
Easy pickings: UI/UX:

Description (last modified by ubernostrum)

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)

charfield_index_opclass_fix_v1.diff (2.2 KB) - added by jbronn 5 years ago.
Add extra indexes to include operator class.
charfield_index_opclass_fix_v2.diff (2.1 KB) - added by jbronn 5 years ago.
simplifed get_index_sql
charfield_index_opclass_fix_v3.diff (3.3 KB) - added by jbronn 5 years ago.
Introspect on database type string rather than Field class; added docs to databases section.

Download all attachments as: .zip

Change History (9)

comment:1 Changed 5 years ago by Alex

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Triage Stage changed from Unreviewed to Accepted
  • Version changed from 1.1 to SVN

comment:2 Changed 5 years ago by ubernostrum

  • Description modified (diff)

comment:3 Changed 5 years ago by carljm

  • Cc carljm added

Changed 5 years ago by jbronn

Add extra indexes to include operator class.

comment:4 Changed 5 years ago by jbronn

  • Has patch set
  • Needs documentation set
  • Needs tests set
  • Owner changed from nobody to jbronn
  • Status changed from new to assigned

Changed 5 years ago by jbronn

simplifed get_index_sql

Changed 5 years ago by jbronn

Introspect on database type string rather than Field class; added docs to databases section.

comment:5 Changed 5 years ago by jbronn

  • Resolution set to fixed
  • Status changed from assigned to closed

(In [11912]) Fixed #12234 -- Create additional indexes that use the appropriate operation class for PostgreSQL varchar and text columns when db_index=True.

comment:6 Changed 5 years ago by jbronn

(In [11913]) [1.1.X] Fixed #12234 -- Create additional indexes that use the appropriate operation class for PostgreSQL varchar and text columns when db_index=True.

Backport of r11912 from trunk.

Note: See TracTickets for help on using tickets.
Back to Top