[Django] #26610: Case insensitive indexes
Django
-------------------------------------+-------------------------------------
Reporter: shadow7412 | Owner: nobody
Type: New feature | Status: new
Component: Database layer | Version: 1.9
(models, ORM) | Keywords: index charfield
Severity: Normal | textfield case insensitive
| optimisation
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
I was analysing some of our queries - and one of the things I noticed was
that if you use iexact, then the database needs to do a full table scan
because it is unable to use the index I had on the field.
{{{
class Example(models.Model):
text = models.CharField(index=True)
Example.objects.get(text="meow") # This will hit the index
Example.objects.get(text__iexact="meow") # This does not.
}}}
I am aware that this kind of index can be added manually by RunSQL
migrations, but I think this is probably a common enough scenario that the
ORM should attempt to handle it natively.
{{{
# PROPOSED CHANGES - THIS DOES NOT ACTUALLY WORK
class Example(models.Model):
text = models.CharField(insensitive_index=True)
Example.objects.get(text="meow") # This probably won't hit the index
(might depend on the db)
Example.objects.get(text__iexact="meow") # This would.
}}}
What are your thoughts?
--
Ticket URL: <https://code.djangoproject.com/ticket/26610>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Reporter: shadow7412 | Owner: nobody
Type: New feature | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: index charfield | Triage Stage: Accepted
textfield case insensitive |
optimisation |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: aksheshdoshi@… (added)
* needs_better_patch: => 0
* needs_tests: => 0
* version: 1.9 => master
* needs_docs: => 0
* stage: Unreviewed => Accepted
--
Ticket URL: <https://code.djangoproject.com/ticket/26610#comment:1>
Django
-------------------------------------+-------------------------------------
Reporter: shadow7412 | Owner: nobody
Type: New feature | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: index charfield | Triage Stage: Accepted
textfield case insensitive |
optimisation |
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by aaugustin):
If we're talking about Postgres, I suggest adding a case-insensitive,
case-preserving char/text field backed by the `citext` type to
`django.contrib.postgres`. This should cover most use cases.
--
Ticket URL: <https://code.djangoproject.com/ticket/26610#comment:2>
Django
-------------------------------------+-------------------------------------
Reporter: shadow7412 | Owner: nobody
Type: New feature | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: index charfield | Triage Stage: Accepted
textfield case insensitive |
optimisation |
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by shadow7412):
My current backend is indeed postgres. In my specific usage - case is
important. It's just when users are searching for models in my search box
the filter needs to be case insensitive because... well... users are very
sensitive about case insensitivity.
With the {{{citext}}} idea - would programmers have control enough to have
both a sensitive and insensitive index if required? Or would that make all
filters implicitly case insensitive?
--
Ticket URL: <https://code.djangoproject.com/ticket/26610#comment:3>
Django
-------------------------------------+-------------------------------------
Reporter: shadow7412 | Owner: nobody
Type: New feature | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: index charfield | Triage Stage: Accepted
textfield case insensitive |
optimisation |
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by aaugustin):
`citext` would make all queries and unicity checks case-insensitive. It's
still case-preserving so I assume it would work for you even if "case is
important".
--
Ticket URL: <https://code.djangoproject.com/ticket/26610#comment:4>
Django
-------------------------------------+-------------------------------------
Reporter: shadow7412 | Owner: nobody
Type: New feature | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: index charfield | Triage Stage: Accepted
textfield case insensitive |
optimisation |
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by shadow7412):
I've done some reading on {{{citext}}} - and it would indeed do the job I
want it to do.
My only concern is that iexact effectively becomes implicit, which may
surprise some developers.
I guess the question is: would people value being able to do both
insensitive and sensitive searches on the same field?
The only other way I could think to do this would be creating a functional
index, which would give the ability to have the different index kwargs as
I had in the original report, but I'm not sure which wins from a
performance point of view.
And, again, if people would find having both a useful feature.
--
Ticket URL: <https://code.djangoproject.com/ticket/26610#comment:5>
Django
-------------------------------------+-------------------------------------
Reporter: shadow7412 | Owner: nobody
Type: New feature | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: index charfield | Triage Stage: Accepted
textfield case insensitive |
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* keywords: index charfield textfield case insensitive optimisation =>
index charfield textfield case insensitive optimisation db-indexes
--
Ticket URL: <https://code.djangoproject.com/ticket/26610#comment:6>
Django
-------------------------------------+-------------------------------------
Reporter: shadow7412 | Owner: nobody
Type: New feature | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: index charfield | Triage Stage: Accepted
textfield case insensitive |
optimisation db-indexes |
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* needs_better_patch: 0 => 1
* has_patch: 0 => 1
Comment:
[https://github.com/django/django/pull/6735 PR] (tests aren't passing)
--
Ticket URL: <https://code.djangoproject.com/ticket/26610#comment:7>
Django
-------------------------------------+-------------------------------------
Reporter: shadow7412 | Owner: nobody
Type: New feature | Status: new
Severity: Normal | Resolution:
Keywords: index charfield | Triage Stage: Accepted
textfield case insensitive |
optimisation db-indexes |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
-------------------------------------+-------------------------------------
* component: Database layer (models, ORM) => contrib.postgres
--
Ticket URL: <https://code.djangoproject.com/ticket/26610#comment:8>
Django
Reporter: shadow7412 | Owner: nobody
Type: New feature | Status: new
Severity: Normal | Resolution:
Keywords: index charfield | Triage Stage: Accepted
textfield case insensitive |
optimisation db-indexes |
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* needs_better_patch: 1 => 0
--
Ticket URL: <https://code.djangoproject.com/ticket/26610#comment:9>
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: closed
Component: contrib.postgres | Version: master
Severity: Normal | Resolution: fixed
Keywords: index charfield | Triage Stage: Accepted
textfield case insensitive |
optimisation db-indexes |
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => fixed
Comment:
In [changeset:"094d630ae8d8e5e68817c313da0fd737898b216b" 094d630]:
{{{
#!CommitTicketReference repository=""
revision="094d630ae8d8e5e68817c313da0fd737898b216b"
Fixed #26610 -- Added CITextField to contrib.postgres.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/26610#comment:10>
Django
-------------------------------------+-------------------------------------
Reporter: Shadow | Owner: nobody
Type: New feature | Status: closed
Component: contrib.postgres | Version: master
Severity: Normal | Resolution: fixed
Keywords: index charfield | Triage Stage: Accepted
textfield case insensitive |
optimisation db-indexes |
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Tim Graham):
As suggested [https://groups.google.com/d/topic/django-developers/jud-
n1cBzdg/discussion on django-developers], I've created a
[https://github.com/django/django/pull/8034 PR] to change the base class
to `TextField` since `max_length` isn't used and shouldn't be required.
--
Ticket URL: <https://code.djangoproject.com/ticket/26610#comment:11>
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: new
Severity: Normal | Resolution:
textfield case insensitive |
optimisation db-indexes |
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: closed => new
* resolution: fixed =>
Comment:
Tim Graham open a PR [1] to address the issue [2] I raised on the mailing
list of this field being a subclass of CharField instead of TextField.
Thanks Tim!
[1] https://github.com/django/django/pull/8034
[2]
https://groups.google.com/forum/?utm_medium=email&utm_source=footer#!msg
/django-developers/jud-n1cBzdg/ToRMj42pBAAJ
--
Ticket URL: <https://code.djangoproject.com/ticket/26610#comment:12>
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: new
Severity: Release blocker | Resolution:
Keywords: index charfield | Triage Stage: Ready for
textfield case insensitive | checkin
optimisation db-indexes |
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* severity: Normal => Release blocker
* stage: Accepted => Ready for checkin
--
Ticket URL: <https://code.djangoproject.com/ticket/26610#comment:13>
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: new
Severity: Release blocker | Resolution:
Keywords: index charfield | Triage Stage: Accepted
textfield case insensitive |
optimisation db-indexes |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* has_patch: 1 => 0
* version: master => 1.11
* stage: Ready for checkin => Accepted
Comment:
Further discussion on the mailing list suggests to add a separate case-
insensitive field for `CharField`, `TextField`, `EmailField`, etc.
--
Ticket URL: <https://code.djangoproject.com/ticket/26610#comment:14>
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: new
Severity: Release blocker | Resolution:
textfield case insensitive | checkin
optimisation db-indexes |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* has_patch: 0 => 1
* stage: Accepted => Ready for checkin
Comment:
[https://github.com/django/django/pull/8041 PR] from Mads.
--
Ticket URL: <https://code.djangoproject.com/ticket/26610#comment:15>
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: new
Severity: Release blocker | Resolution:
Keywords: index charfield | Triage Stage: Ready for
textfield case insensitive | checkin
optimisation db-indexes |
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Tim Graham <timograham@…>):
In [changeset:"fb5bd38e3b83c7f0d1011de80f922fc34faf740b" fb5bd38]:
{{{
#!CommitTicketReference repository=""
revision="fb5bd38e3b83c7f0d1011de80f922fc34faf740b"
Refs #26610 -- Added CIText mixin and CIChar/Email/TextField.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/26610#comment:16>
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: new
Severity: Release blocker | Resolution:
Keywords: index charfield | Triage Stage: Ready for
textfield case insensitive | checkin
optimisation db-indexes |
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Tim Graham <timograham@…>):
In [changeset:"ded0632d94d3c50da29bdb0ddb061f6826b9fa48" ded0632]:
{{{
#!CommitTicketReference repository=""
revision="ded0632d94d3c50da29bdb0ddb061f6826b9fa48"
[1.11.x] Refs #26610 -- Added CIText mixin and CIChar/Email/TextField.
Backport of fb5bd38e3b83c7f0d1011de80f922fc34faf740b from master
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/26610#comment:17>
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: closed
Component: contrib.postgres | Version: 1.11
Severity: Release blocker | Resolution: fixed
Keywords: index charfield | Triage Stage: Ready for
textfield case insensitive | checkin
optimisation db-indexes |
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => fixed
--
Ticket URL: <https://code.djangoproject.com/ticket/26610#comment:18>