CODEWITHSUNDEEP
databasedjangodjango-modelsmodelindexing
BillyBBone
BillyBBone

Reputation: 3324

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

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?

Upvotes: 18

Views: 5847

Answers (4)

Tom Wojcik
Tom Wojcik

Reputation: 6189

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

Upvotes: 14

Max Malysh
Max Malysh

Reputation: 31635

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

Upvotes: 17

mlissner
mlissner

Reputation: 18206

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.

Upvotes: 3

Daniel Rucci
Daniel Rucci

Reputation: 2872

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>

Upvotes: 2

Related Questions