Hi Pavel,
I think this is best solved by adding support for indexes on expressions.
This would allow for indexes to be defined as:
class Meta:
indexes = [
Index(fields=[Upper('name')], name='upper_name_idx')
]
There's an open ticket for this https://code.djangoproject.com/ticket/26167
and a patch in progress https://github.com/django/django/pull/11929
- Hannes
Den fredag 3 april 2020 kl. 13:56:33 UTC+2 skrev Павел Гаркин:
>
> 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 django-developers+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-developers/55573f22-4df3-4c88-bf62-db76c5a8d61a%40googlegroups.com.
