Hi,
Django ORM uses UPPER operator when using *__iexact *operator
https://code.djangoproject.com/ticket/3575
I have faced performance degradation when querying with *iexact* operator.
Example model
class Subject(models.Model):
name = models.CharField(max_length=255)
class Meta:
indexes = [
models.Index(fields=['name', ],
name='%(app_label)s_%(class)s_n_like_idx',
opclasses=('varchar_pattern_ops',)),
models.Index(fields=['name', ],
name='%(app_label)s_%(class)s_n_idx')
]
Iexact operator doesn't use these indexes. Only with *exact *operator. Also
tried to use text_pattern_ops no affects :(
I also noticed a mismatch in the documentation
<https://docs.djangoproject.com/en/3.0/ref/models/querysets/#iexact>. It
says it will use SELECT ... WHERE name ILIKE 'beatles blog'; but it's not.
When querying it using *SELECT ... WHERE UPPER(name::text) = 'John Doe'*.
So can't use gist_trgm_ops opclass
<https://www.postgresql.org/docs/10/pgtrgm.html>.
I've decided to add explicitly into *migrations *to fix this.
operations = [
migrations.RunSQL( sql=r'CREATE INDEX "upper_name_idx" ON "subject"
(UPPER("name"));',
reverse_sql=r'DROP INDEX "stats_siteuser_upper_idx";' ),
]
I' ve attached 2 screenshots with queries before add and after
*upper(name) *index.
As you see performance boost too much to ignore it.
My propose is to add possibility to *simply* add upper index support to
allow Django do by own
For example:
class Subject(models.Model):
name = models.CharField(max_length=255)
class Meta:
indexes = [
models.Index(fields=['name', ],
name='%(app_label)s_%(class)s_n_upper_idx',
opclasses=('varchar_pattern_ops',), to_upper=['name']),
]
Thanks, Pavel
--
You received this message because you are subscribed to the Google Groups
"Django developers (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-developers/83876b3e-f625-4f61-84b8-7660ab70a03a%40googlegroups.com.
