PostgreSQL自定义函数致索引失效，直接用CASE WHEN却正常的原因咨询

核心原因 #

• 优化器无法解析函数内部逻辑：PostgreSQL的查询优化器能直接识别原生CASE WHEN这类内置表达式，清楚里面用到了哪些表字段，能判断是否可以利用对应字段的索引。但自定义函数对优化器来说是个黑盒，它不会去拆解函数内部的SQL代码，只会把整个函数调用当成独立运算，自然没法关联到字段索引。
• 函数的 volatility 属性设置错误：你定义函数时用了VOLATILE属性，这个属性告诉数据库：哪怕输入参数完全一样，函数返回值也可能每次都不同。这种情况下，优化器不会对函数调用做任何缓存或预计算，更不会尝试匹配索引。而原生CASE WHEN本身是IMMUTABLE（输入相同则结果必相同）的，优化器可以放心基于它做索引匹配。
• 索引匹配规则限制：PostgreSQL的索引只能匹配直接引用字段的表达式，或者能被优化器等价转换的表达式。自定义函数调用不在这个范围内——哪怕内部逻辑和原生表达式完全一致，优化器也不会主动把函数调用替换成原生表达式，因此触发不了索引扫描。

解决办法 #

1. 修正函数的 volatility 属性
   把函数属性改成IMMUTABLE（因为你的函数输入相同参数时返回值固定，符合IMMUTABLE的定义），修改后的SQL如下：

   CREATE OR REPLACE FUNCTION "public"."div_zeroTest"("a" numeric, "b" numeric)
     RETURNS "pg_catalog"."numeric" AS $BODY$
       SELECT CASE b
       WHEN 0 THEN
           0
       ELSE
           a/b
   END;
   $BODY$
     LANGUAGE sql IMMUTABLE
     COST 100;
   

2. 创建表达式索引（可选）
   如果修改属性后还是无法利用原有索引，可以针对函数调用创建专门的表达式索引：

   CREATE INDEX idx_your_table_div_zero ON your_table (div_zeroTest(a_column, b_column));
   

   （注意把your_table、a_column、b_column替换成你实际的表名和字段名）

内容的提问来源于stack exchange，提问作者朱嘉伦