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?

See also https://stackoverflow.com/questions/44820345/creating-a-gin-index-with-trigram-gin-trgm-ops-in-django-model/57038717#57038717 — Risadinha, Jul 15 '19 at 12:19

score 17 · Answer 1 · answered Dec 14 '17 at 23:45

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

First, create an empty migration:

python3 manage.py makemigrations appName --empty

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";'
        ),
    ]

For `icontains` you would need a GIN trigram index: `'CREATE INDEX "stats_siteuser_upper_idx" ON "stats_siteuser" USING gin (UPPER("email") gin_trgm_ops);'` You need the TrigramExtension to make this work. — Risadinha, Jul 15 '19 at 10:22

score 9 · Answer 2 · edited Jul 12 '23 at 17:26

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.

from django.db.models import Index
from django.db.models.functions import Upper


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',
            ),
        ]

score 3 · Answer 3 · edited Jun 20 '20 at 09:12

Prior to Django 1.9 (not yet released), you could use the sqlcustom command, but if you look at the dev documentation for the upcoming 1.9, you'll see that that command is conspicuously missing.

So:

In <= 1.8.*, @daniel-rucci's answer applies. Put your SQL in an SQL directory and it'll be run in non-deterministic order.
In >= 1.9, you need to start using the new RunSQL function as part of a migration. You could also do this in 1.7 or 1.8, if you so desired.

score 2 · Answer 4 · edited Aug 26 '15 at 14:11

To inject custom sql into the django model management commands check out django-admin.py sqlcustom

You would put an sql file containing your create index in <app_name>/sql/<model_name>.sql

From the docs on when they are applied:

The SQL files are piped directly into the database after all of the models’ table-creation statements have been executed. Use this SQL hook to make any table modifications, or insert any SQL functions into the database.

And you can view the custom sql for each app by running manage.py sqlcustom <app_name>

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

4 Answers4

So: