PostgreSQL过滤条件含子查询时未命中联合索引问题排查

这个现象本质是PostgreSQL查询优化器对动态参数的选择率估算偏差导致的，不同写法的执行逻辑差异如下：

• 硬编码常量值时，优化器在执行计划生成阶段就能直接拿到ticker='03041'这个确定值，可以结合表的统计信息准确估算出该过滤条件的返回行数极少，走(ticker, date)联合索引的随机IO成本远低于全表扫描成本，因此会正确选择位图索引扫描。
• 直接写标量子查询、CTE、JOIN关联时，优化器会做子查询提升，将子查询返回值作为执行期才会赋值的参数$0处理，计划阶段拿不到参数的实际值，只能用默认的等值条件选择率（默认按1/200的平均选择率估算）计算成本，最终估算出来的返回行数远高于实际值，优化器会误判并行顺序扫描的成本更低，因此选错执行计划。
• 封装VOLATILE属性自定义函数时，PostgreSQL会将这类函数的调用视为不可优化的节点，不会做子查询提升，会先执行InitPlan拿到函数返回的实际ticker值，再基于这个确定值做成本估算，逻辑和硬编码常量完全一致，因此能正常命中索引。

• 业务层拆分查询：先单独执行子查询拿到对应的ticker值，再把这个值作为常量拼接到主查询中执行，是最直接无副作用的方案，和测试的硬编码写法性能一致。
• 会话级强制自定义计划：执行主查询前先运行SET LOCAL plan_cache_mode = force_custom_plan;，强制优化器在拿到实际参数值后再生成执行计划，不使用基于默认选择率生成的通用计划，不需要修改原有SQL结构，事务结束后参数自动重置，不会影响其他查询。
• 调整优化器成本系数：如果数据库使用SSD存储，随机IO性能和顺序IO差距很小，可以把random_page_cost参数从默认的4调整为1（和seq_page_cost一致），让优化器对索引扫描的成本估算更贴合实际硬件性能，从全局层面减少选错扫描方式的概率。
• 触发自动计划切换：如果是使用预处理语句、PGbouncer这类连接池缓存执行计划的场景，PostgreSQL默认会在同一条SQL执行5次通用计划后，自动比对自定义计划的成本，只要实际参数的最优计划成本更低，就会自动切换到索引扫描的执行计划。

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