How can I create a case-insensitive database index in Django?

Question

I am using Django to create some database tables, as so:

class MetadataTerms(models.Model):
    term = models.CharField(max_length=200)
    size = models.IntegerField(default=0)
    validity = models.IntegerField(default=0, choices=TERM_VALIDITY_CHOICES)

I am then running lookup queries to find the appropriate row with the correct term, matched in a case-insensitive way. E.g.:

MetadataTerms.objects.filter(term__iexact=search_string, size=3)

This lookup clause translates to something like so in SQL:

 SELECT "app_metadataterms"."id", "app_metadataterms"."term", "app_metadataterms"."size" FROM "app_metadataterms" WHERE (UPPER("app_metadataterms"."term"::text) = UPPER('Jack Nicklaus survives')  AND "app_metadataterms"."size" = 3 );

On Postgres, I can perform an EXPLAIN query on the above, and I get this query plan:

                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Seq Scan on app_metadataterms  (cost=0.00..1233.01 rows=118 width=21)
   Filter: ((size = 3) AND (upper((term)::text) = 'JACK NICKLAUS SURVIVES'::text))

Because the term field is not indexed, and is not indexed in a case-normalized way, the above query needs to perform a slow Seq[uential] Scan operation across all database rows.

Then I insert a simple case-normalized index, e.g.:

 CREATE INDEX size_term_insisitive_idx ON app_metadataterms (upper(term), size);

The above query now runs about 6x faster:

                                         QUERY PLAN
---------------------------------------------------------------------------------------------
 Bitmap Heap Scan on app_metadataterms  (cost=5.54..265.15 rows=125 width=21)
   Recheck Cond: ((upper((term)::text) = 'JACK NICKLAUS SURVIVES'::text) AND (size = 3))
   ->  Bitmap Index Scan on size_term_insisitive_idx  (cost=0.00..5.51 rows=125 width=0)
         Index Cond: ((upper((term)::text) = 'JACK NICKLAUS SURVIVES'::text) AND (size = 3))

My question is: how can I inject the creation of advanced DB indexes into the Django model management commands?

Answer 1

Django 1.11 (2.0 should be fine too) + PostgreSQL:

1. First, create an empty migration:

   python3 manage.py makemigrations appName --empty
   

2. Django uses UPPER for inexact lookups. So create a migration for adding an UPPER(yourField) index:

   # -*- coding: utf-8 -*-
   # Generated by Django 1.11.7 on 2017-12-14 23:11
   from __future__ import unicode_literals
   
   
   from django.db import migrations
   
   class Migration(migrations.Migration):
   
       dependencies = [
           ('stats', '0027_remove_siteuser_is_admin'),
       ]
   
       operations = [
           migrations.RunSQL(
               sql=r'CREATE INDEX "stats_siteuser_upper_idx" ON "stats_siteuser" (UPPER("email"));',
               reverse_sql=r'DROP INDEX "stats_siteuser_upper_idx";'
           ),
       ]
   

Answer 2

As of 3.2 you can add *expressions to Index.

If you wanted to create

 CREATE INDEX size_term_insisitive_idx ON app_metadataterms (upper(term), size);

something like that should work.

class MetadataTerms(models.Model):
    term = models.CharField(max_length=200)
    size = models.IntegerField(default=0)
    validity = models.IntegerField(default=0, choices=TERM_VALIDITY_CHOICES)
    
    class Meta:
        indexes = [
            Index(
                Upper('term'), 'size',
                name='size_term_insisitive_idx',
            ),
        ]