PostgreSQL函数内trgm GIN索引未被使用，如何触发索引命中？

嘿，这个问题我在日常维护PostgreSQL的时候碰到过好多次——函数里的动态查询死活不肯用建好的表达式索引，直接跑快得飞起，放函数里就慢得离谱。核心原因大多是PostgreSQL的查询规划器在处理函数内的动态SQL时，有一些默认的行为限制，咱们一步步来拆解解决：

1. 先检查动态SQL的参数绑定方式 #

这是最常见的坑！如果你的函数里是用字符串拼接把变量直接塞进SQL语句里（比如'WHERE col = ''' || my_var || ''''），PostgreSQL的规划器根本没办法识别这是参数，只能生成一个通用的执行计划，自然不会考虑到你的表达式索引。

正确的做法是用EXECUTE ... USING语法绑定参数，比如：

-- 错误示例：字符串拼接参数，索引无法被识别
EXECUTE 'SELECT * FROM my_table WHERE lower(user_name) = ''' || input_name || '''';

-- 正确示例：参数绑定，规划器能识别并匹配索引
EXECUTE 'SELECT * FROM my_table WHERE lower(user_name) = lower($1)' USING input_name;

这样规划器能明确识别参数类型和值，才能判断是否可以使用对应的表达式索引。

2. 确保查询表达式和索引定义完全匹配 #

表达式索引的匹配是严格的，一丁点差异都不行！比如你建的索引是：

CREATE INDEX idx_mytable_lower_username ON my_table (lower(user_name));

那你的查询语句里必须完全使用lower(user_name)这个表达式，不能写成user_name = lower(input_name)（虽然逻辑上等价，但规划器不会自动关联），也不能用其他类似的函数（比如trim(lower(user_name))）。

如果你的查询里的表达式和索引不一致，规划器肯定不会用这个索引——哪怕结果一样也不行。

3. 调整函数的稳定性属性 #

PostgreSQL函数默认是VOLATILE（不稳定），规划器会认为这个函数每次调用都可能返回不同的结果，甚至修改数据，所以不会缓存执行计划，也不会做太多优化。

如果你的函数不会修改数据，且相同输入必然返回相同输出，可以把它改成STABLE（稳定）或者IMMUTABLE（不可变）：

CREATE OR REPLACE FUNCTION my_dynamic_func(input_name text) 
RETURNS SETOF my_table 
STABLE -- 这里改成STABLE或IMMUTABLE（根据函数逻辑选择）
AS $$
BEGIN
  RETURN QUERY EXECUTE 'SELECT * FROM my_table WHERE lower(user_name) = lower($1)' USING input_name;
END;
$$ LANGUAGE plpgsql;

改成STABLE后，规划器会更愿意为函数内的查询生成优化计划，包括使用索引。

4. 检查统计信息是否过时 #

如果你的表数据最近有大量更新，PostgreSQL的统计信息可能跟不上，导致规划器错误地认为全表扫描比索引扫描更快。

可以手动更新统计信息：

ANALYZE my_table;

更新后再试试执行函数，规划器应该能正确判断索引的价值。

5. 万不得已：强制使用索引（谨慎操作） #

如果前面的方法都没用，可以考虑强制规划器使用索引，但这是最后手段，因为可能会让某些场景下的查询变慢。

方法一：临时关闭全表扫描 #

在函数内部临时设置（仅当前事务生效）：

BEGIN
  SET LOCAL enable_seqscan = off;
  RETURN QUERY EXECUTE 'SELECT * FROM my_table WHERE lower(user_name) = lower($1)' USING input_name;
END;

方法二：使用索引提示（PostgreSQL 11+支持） #

在查询里添加注释提示规划器使用指定索引：

EXECUTE 'SELECT /*+ IndexScan(my_table idx_mytable_lower_username) */ * FROM my_table WHERE lower(user_name) = lower($1)' USING input_name;

6. 排查执行计划 #

如果还是不行，建议查看函数内查询的执行计划，看看规划器为什么不用索引。可以在函数里添加日志输出执行计划：

RAISE NOTICE 'Execution Plan: %', pg_get_plan(
  EXECUTE 'EXPLAIN ANALYZE SELECT * FROM my_table WHERE lower(user_name) = lower($1)' USING input_name
);

或者直接把函数里的SQL拿出来，用EXPLAIN ANALYZE直接执行，对比两种场景的执行计划差异，就能精准定位问题所在。

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