Proposal: Add simply way to use upper index in Meta.models.indexes

[Павел Гаркин]

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. 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.

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

[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

[Adam Johnson]

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

--
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-develop...@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.

--

Adam

[Павел Гаркин]

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 написал: