Postgres 11.7中pg_hint_plan提示前6次失效及函数影响问题排查

这个问题其实是PostgreSQL计划缓存和pg_hint_plan交互时的典型场景，我来一步步拆解原因和可行的解决思路：

一、为何新连接前6次提示不生效？ #

核心原因在于PostgreSQL的计划缓存机制和它对查询签名的判断逻辑：

• 查询签名忽略注释内容：PostgreSQL生成查询内部签名（用于判断是否复用缓存计划）时，会直接忽略所有注释文本。而pg_hint_plan的ROWS提示是通过注释传递的，这就导致：哪怕你动态修改了注释里的行数提示，PostgreSQL仍会把这些查询视为同一个结构，共享同一组计划缓存条目。
• max_plans_per_query的默认限制：PostgreSQL默认配置max_plans_per_query = 6，这个参数控制同一个查询签名下最多能缓存6个不同的执行计划。新连接的前6次执行中，PostgreSQL会不断填充缓存，但因为无法区分不同的注释提示，生成的计划都基于默认的100行估算。直到第7次执行，缓存已满触发计划重新评估，pg_hint_plan的提示才会被正确读取并应用。

二、Immutable函数为何会影响提示生效？ #

Immutable函数的“输入相同则输出必然相同”特性，会强化PostgreSQL的计划缓存行为：

• 常量折叠与计划固化：包含immutable函数的查询，PostgreSQL在解析阶段就会尝试做常量折叠（如果函数参数是常量或参数化值），这会让执行计划变得更“固化”——优化器会更倾向于复用已缓存的计划，而不会重新解析查询中的注释提示。
• 参数化查询的缓存优先级：带有immutable函数的参数化查询，PostgreSQL会认为其计划的通用性更强，因此会更积极地缓存计划。而改用CTE或子查询后，查询结构被拆解为多个执行步骤，破坏了这种高度参数化的结构，优化器每次执行都需要重新生成计划，自然会每次读取pg_hint_plan的提示，不会出现前几次失效的问题。

三、可行的解决方案 #

1. 调整计划缓存参数：临时增大max_plans_per_query（比如设为100），但注意这会增加内存消耗，不建议长期保持高值。
2. 破坏查询签名的一致性：在查询文本中加入一个随提示变化的无意义参数（比如在SELECT列表中加一个:hint_tag占位符，每次修改提示时同步更新这个值），让PostgreSQL认为每次查询都是不同的签名，从而每次都重新生成计划并读取提示。
3. 替换Immutable函数：用CTE、子查询或stable函数替代immutable函数，打破计划缓存的触发条件，确保每次执行都重新解析提示。
4. 强制使用自定义计划：设置plan_cache_mode = force_custom_plan，让PostgreSQL每次都为当前参数值生成自定义计划，不再复用通用计划，这样pg_hint_plan的提示每次都会生效。

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