Django — improve `startswith` performance

Question

How to improve performance of the following query?

class Worker(models.Model):
    name = models.CharField(max_length=32, db_index=True)

# This line is slow:
Worker.objects.filter(name__startswith='John')

I have already added an index to the model, but... it's simply not used. However, index does kick in when I do ordinary filtering without startswith:

# This line is fast:
Worker.objects.filter(name='John')

Why index is not used with startswith?

Answer 1

The problem is that startswith expression translates to an SQL query containing the LIKE operator, which doesn't take advantage of the default index.

Solution: add an additional index with a special operator class:

CREATE INDEX "appname_model_field_like_idx" 
ON "appname_model" ("fieldname" varchar_pattern_ops);

Step by step:

1. First, create an empty migration:

   python3 manage.py makemigrations appName --empty
   

2. Add a custom RunSQL command:

   class Migration(migrations.Migration):
   
       dependencies = [
           ('stats', '0002_auto_2010213_0159.py'),
       ]
   
       operations = [
           migrations.RunSQL(
               sql=r'''CREATE INDEX "appname_model_field_like_idx" 
                       ON "appname_model" ("fieldname" varchar_pattern_ops);''',
               reverse_sql=r'DROP INDEX "appname_model_field_like_idx";'
           ),
       ]