PostgreSQL未使用trgm索引：Django Concat查询适配方案问询

问题背景 #

我在PostgreSQL中为full_name（由first_name || ' ' || last_name拼接而成）创建了trigram索引：

CREATE INDEX  "user_full_name_trgm"
ON "user" using gin (upper(first_name || ' ' || last_name) gin_trgm_ops);

使用Django QuerySet对全名进行过滤时：

queryset = queryset.annotate(full_name=Concat("first_name", Value(" "), "last_name"))
queryset = queryset.filter(full_name__istartswith=full_name)

生成的WHERE子句为：

WHERE (UPPER(CONCAT("user"."first_name", CONCAT(' ', "user"."last_name"))::text) LIKE UPPER('mike%')

执行EXPLAIN ANALYZE发现未使用user_full_name_trgm索引，但改用||拼接字符串时索引会生效：

WHERE (UPPER(first_name || ' ' || last_name) LIKE UPPER('mike%')

需要解决：如何调整Django查询使其使用索引？能否修改annotate让SQL不使用Concat？或者调整索引使其兼容Concat查询？

解决方案 #

方法1：修改Django查询，用||替代Concat生成拼接逻辑 #

PostgreSQL中||是原生字符串拼接操作符，和你创建索引时的表达式一致，只要让Django生成这种逻辑就能触发索引。

方式1-1：直接在Filter中构造拼接条件（无需Annotate）

跳过annotate步骤，直接在filter里用F()表达式拼接字段和空格：

from django.db.models import F, Value

queryset = queryset.filter(
    (F('first_name') || Value(' ') || F('last_name')).istartswith(full_name)
)

这样生成的SQL会和你测试生效的语句一致，直接命中user_full_name_trgm索引。

方式1-2：自定义Func类实现||拼接（保留Annotate）

如果业务需要保留full_name的注解字段，可以自定义一个使用||的函数类：

from django.db.models import Func, F, Value

class StringConcat(Func):
    function = ''
    template = "%(expressions)s"
    arg_joiner = ' || '

# 使用自定义类生成拼接逻辑
queryset = queryset.annotate(
    full_name=StringConcat(F('first_name'), Value(' '), F('last_name'))
).filter(full_name__istartswith=full_name)

这个类会生成first_name || ' ' || last_name的SQL，而非嵌套的CONCAT函数，完美匹配现有索引。

方法2：调整索引兼容Concat查询 #

如果不想改动Django代码，可以重新创建索引，匹配Django生成的CONCAT表达式：

-- 对应Django生成的嵌套CONCAT逻辑
CREATE INDEX "user_full_name_trgm_concat"
ON "user" USING gin (
    UPPER(CONCAT("first_name", CONCAT(' ', "last_name"))::text) gin_trgm_ops
);

或者简化Django的Concat用法（PostgreSQL的CONCAT支持多参数），同时调整索引：

# Django中使用多参数Concat，避免嵌套
queryset = queryset.annotate(
    full_name=Concat("first_name", Value(" "), "last_name", output_field=models.CharField())
)

对应的索引改成：

CREATE INDEX "user_full_name_trgm_concat"
ON "user" USING gin (
    UPPER(CONCAT("first_name", ' ', "last_name")::text) gin_trgm_ops
);

这样索引表达式和Django生成的SQL完全一致，就能触发索引。

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