Hi Hannes,
Thanks for reply.
I get AttribueError error when try to use Upper field name as you wrote.
from django.db.models.functions import Upper
class TestSubject(models.Model):
name = models.CharField(max_length=255)
class Meta:
indexes = [
models.Index(fields=[Upper('name')], name='upper_name_idx')
]
AttributeError: 'Upper' object has no attribute 'startswith'
I hope functional Indexes will be released in Django 3.1 they are very
usefull to manage indexes :)
Thanks, Pavel
пятница, 3 апреля 2020 г., 17:00:16 UTC+3 пользователь Hannes Ljungberg
написал:
>
> 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/7c226748-ab19-48f6-81ce-2a4370e06dfc%40googlegroups.com.
