Upper index does not seem to work with iexact Django field lookup

user_7433420 0
Viewed 471

I have some references (strings) that I want to use to filter a query with a case-insensitive exact match. To do so, I used iexact Django field lookup.

Model.objects.filter(column1__iexact=reference)

Since I have a very large number of rows on that table, I wanted to create an index to speed up the query. Django uses the UPPER function to perform the iexact lookup. So I created the following migration (DB is Postgresql):

class Migration(migrations.Migration):

    dependencies = [
        ('app', 'previous_migr'),
    ]

    operations = [
        migrations.RunSQL(
            sql=r'CREATE INDEX "index_name_idx" ON "table" (UPPER("column1") );',
            reverse_sql=r'DROP INDEX "index_name_idx";'
        ),
    ]

Unfotunately, this index does not seem to be used while querying.

Why do I think this ? Here are the request times with :

  • iexact ~= 17s
  • exact < 1s

Here is the generated (truncated) SQL query :

SELECT "table"."id" FROM "table" 

WHERE (
    "table"."is_removed" = false 
    AND (UPPER("table"."column1"::text) = UPPER('blablabla') 
    OR UPPER("ordering_order"."column2"::text) = UPPER('blablabla'))) 
ORDER BY "table"."id" ASC
)

Is there something wrong with my migration ? Any other option to do this query with good performance ?

indexing postgresql django database
1 Answers

You can filter with the Upper expression in the query, so:

from django.db.models import Upper

Model.objects.annotate(
    column1_upper=Upper('column1')
).filter(column1_upper=reference.upper())

or in django-3.2, we can work with a .alias(…) to boost performance slightly more:

Model.objects.alias(
    column1_upper=Upper('column1')
).filter(column1_upper=reference.upper())

The above is however not a case-insensitive search. It is a common mistake to assume that two items are equivalent when you convert both to lowercase/uppercase. For example in German, the eszett ß has as uppercase 'SS', other characters do not have a lowercase/uppercase variant. In order to match case invariant, you need to take a look at casefolding.

user_675790
Related