Hannes, thank you for working on this PR! I hope we can get it into Django 3.1.
Pavel, I think the SQL in the documentation is for illustration purposes only. It seems ILIKE is not used on PostgreSQL only, as it does the UPPER / ::text conversion in its backend: https://github.com/django/django/blob/89032876f427a77ab4de26493190280377567d1c/django/db/backends/postgresql/operations.py#L87 On Fri, 3 Apr 2020 at 14:49, Hannes Ljungberg <hannes.ljungb...@gmail.com> wrote: > 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 > <https://groups.google.com/d/msgid/django-developers/55573f22-4df3-4c88-bf62-db76c5a8d61a%40googlegroups.com?utm_medium=email&utm_source=footer> > . > -- Adam -- 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/CAMyDDM0pCM1xAP1ZxUHsKwwF3CaLTpN2azcyHw-o-Esv%3DocCew%40mail.gmail.com.
