Django+PostgreSQL下istartswith大小写不敏感查询的索引优化问题

问题根源 #

Django的name__istartswith查询会生成类似UPPER("yourmodel"."name") LIKE UPPER('xxx%')的SQL语句，你之前创建的varchar_pattern_ops索引是针对原始name字段的模式匹配，无法匹配经过UPPER()函数处理后的字段，因此无法被查询利用。

解决方案 #

方案1：使用Django函数索引（Django 3.2+ 支持） #

通过自定义Func表达式，直接在模型的Meta类中创建基于UPPER(name)的索引，并指定对应的模式匹配操作类：

from django.db import models
from django.db.models import Index, Func, Q

# 自定义UPPER函数表达式
class UpperName(Func):
    function = 'UPPER'
    template = '%(function)s(%(expressions)s)'

class YourModel(models.Model):
    name = models.CharField(max_length=255)
    published = models.BooleanField(default=True)

    class Meta:
        indexes = [
            Index(
                UpperName('name'),
                name='upper_name_pat_idx',
                opclasses=['varchar_pattern_ops'],  # 针对UPPER(name)的模式匹配操作类
                condition=Q(published=True)  # 保留原有的过滤条件
            )
        ]

执行迁移后，PostgreSQL会生成如下索引：

CREATE INDEX upper_name_pat_idx ON yourmodel
USING btree (UPPER(name) varchar_pattern_ops)
WHERE published = true;

方案2：手动编写SQL迁移（兼容旧版Django） #

如果你的Django版本低于3.2，或者需要更灵活的索引定义，可以通过自定义迁移文件执行原生SQL：

1. 生成空迁移文件：

python manage.py makemigrations --empty your_app_name

2. 编辑生成的迁移文件，添加索引创建/删除的SQL：

from django.db import migrations

class Migration(migrations.Migration):
    dependencies = [
        ('your_app_name', '00xx_previous_migration'),  # 替换为你的上一个迁移文件名
    ]

    operations = [
        migrations.RunSQL(
            # 创建索引的SQL
            """
            CREATE INDEX upper_name_pat_idx ON your_app_name_yourmodel
            USING btree (UPPER(name) varchar_pattern_ops)
            WHERE published = true;
            """,
            # 回滚时删除索引的SQL
            """
            DROP INDEX IF EXISTS upper_name_pat_idx;
            """
        )
    ]

验证索引是否生效 #

可以通过查看SQL执行计划来确认索引是否被使用：

from your_app_name.models import YourModel
from django.db import connection

# 构造目标查询
queryset = YourModel.objects.filter(published=True).filter(name__istartswith='test')

# 打印生成的SQL
print("生成的SQL语句：")
print(queryset.query)

# 查看执行计划
print("\n执行计划：")
with connection.cursor() as cursor:
    cursor.execute(f"EXPLAIN {queryset.query}")
    for row in cursor.fetchall():
        print(row[0])

如果执行计划中出现Index Scan using upper_name_pat_idx，说明索引已被正确利用。

内容的提问来源于stack exchange，提问作者suvodipMondal