Code

Opened 19 months ago

Closed 19 months ago

Last modified 17 months ago

#19441 closed Bug (fixed)

No Postgres _like index when unique=True

Reported by: dyve Owned by: nobody
Component: Database layer (models, ORM) Version: master
Severity: Normal Keywords: postgresql
Cc: Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by claudep)

class Species(models.Model):
    scientific_name = models.CharField(max_length=200, db_index=True)

Generates 2 indexes:

BEGIN;
CREATE INDEX "species_species_scientific_name" ON "species_species" ("scientific_name");
CREATE INDEX "species_species_scientific_name_like" ON "species_species" ("scientific_name" varchar_pattern_ops);

COMMIT;
class Species(models.Model):
    scientific_name = models.CharField(max_length=200, unique=True, db_index=True)

...does not create any indexes, because it depends on UNIQUE, but it SHOULD generate the index:

CREATE INDEX "species_species_scientific_name_like" ON "species_species" ("scientific_name" varchar_pattern_ops);

to make sure Postgres is optimized for LIKE operator.

Attachments (2)

19441-1.diff (3.5 KB) - added by claudep 19 months ago.
19441-2.diff (1.7 KB) - added by claudep 18 months ago.

Download all attachments as: .zip

Change History (16)

comment:1 Changed 19 months ago by claudep

  • Description modified (diff)
  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

comment:2 Changed 19 months ago by claudep

  • Keywords postgresql added
  • Triage Stage changed from Unreviewed to Accepted
  • Version changed from 1.5-beta-1 to master

Changed 19 months ago by claudep

comment:3 Changed 19 months ago by claudep

  • Has patch set

comment:4 Changed 19 months ago by dyve

Apologies if this is too much of a vanity request, but could this ticket's reporter be set to my user (dyve) instead of to my e-mail? Makes it easier to keep track of. Apparently I wasn't logged in properly when I filed it.

comment:5 Changed 19 months ago by aaugustin

  • Reporter changed from dylan@… to dyve

comment:6 Changed 19 months ago by akaariai

  • Triage Stage changed from Accepted to Ready for checkin

It seems that getting the LIKE index when db_index = True for unique field is correct.

I tested the patch on top of 1.5 for the indexes tests. This looks ready for commit to me.

comment:7 Changed 19 months ago by Claude Paroz <claude@…>

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

In 55972ee5c799c75f2d3a320a46297076aaae614a:

Fixed #19441 -- Created PostgreSQL varchar index when unique=True

Thanks Dylan Verheul for the report and Anssi Kääriäinen for the
review.

comment:8 Changed 19 months ago by Claude Paroz <claude@…>

In ddea0e0d7ee1a885f8ecbe45d7306a39c7780d63:

[1.5.x] Fixed #19441 -- Created PostgreSQL varchar index when unique=True

Thanks Dylan Verheul for the report and Anssi Kääriäinen for the
review.
Backport of 55972ee5c from master.

comment:9 Changed 18 months ago by dyve

Now that I;'ve worked with this solution in 1.5c1 I am wondering if this is 100% correct.

For all systems except for postgres, unique=True makes db_index=True obsolete
For postgres, you would basically need to add db_index=True to fields with unique=True if you intend to do LIKE searches on that field.

So the choice is:
A - different technical behavior varying by db vendor (db_index is considered True if unique is True)
B - different functional behavior varying by db vendor (currently implemented behavior)

My knowledge of Django philosophy on these matters is too limited to take a good stance on this, so consider this just an extra heads up. Either way this might need some extra documentation. If you don't know where to look, this is a nasty one to find in Postgres country.

Last edited 18 months ago by dyve (previous) (diff)

Changed 18 months ago by claudep

comment:10 Changed 18 months ago by claudep

Dylan, can you check the attached patch? Does it address your concerns?

The alternative would be to document that Postgres users who want to make like queries on unique fields should set db_index=True in addition to unique=True.

comment:11 Changed 18 months ago by dyve

Claude, this would be perfect. The explanation you added (unique=True implies db_index=True on all occasions) says it all, and it very elegant.

The only "added weight" you collect by solving it like this is the creation of like-indexes on postgresql when the field is used as a unique identifier and not meant for like searching, The like indexes would be created but never used. I think this is not a problem, as it is far more important to have a unified way that Django works (again, unique=True implies db_index=True) than to take different database vendors into account when deciding when or not to add db_index=True.

To but it briefly, I'd be in favor of submitting this patch as the final solution for this issue.

Version 0, edited 18 months ago by dyve (next)

comment:12 Changed 18 months ago by Claude Paroz <claude@…>

In c698c55966ed9179828857398d27bf69e64713a2:

Created special PostgreSQL text indexes when unique is True

Refs #19441.

comment:13 Changed 18 months ago by Claude Paroz <claude@…>

In 012229914cb1391ee4fcb6c183a341a6491cd377:

[1.5.x] Created special PostgreSQL text indexes when unique is True

Refs #19441.
Backport of c698c55966 from master.

comment:14 Changed 17 months ago by Claude Paroz <claude@…>

In 012229914cb1391ee4fcb6c183a341a6491cd377:

[1.5.x] Created special PostgreSQL text indexes when unique is True

Refs #19441.
Backport of c698c55966 from master.

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.