PostgreSQL10查询触发全表扫描未走索引问题咨询

• PostgreSQL 10版本的CTE是优化屏障：该版本对CTE的处理逻辑是单独物化CTE的结果，不会把CTE的关联条件、过滤逻辑下推到外层表查询，因此优化器无法感知到你仅需要匹配少量tx_id，反而默认按照全量关联的场景选择执行计划。
• 执行计划选择了Merge Join策略：Merge Join要求两个关联数据集都按照关联键tx_id排序，小的CTE结果集排序成本极低，但1.5亿行的大表erc20_transf排序需要先全表扫描获取所有数据，因此出现了全表扫的情况。
• 统计信息微小偏差：优化器估算CTE返回5622行，但实际仅返回340行，进一步放大了执行计划选择的错误，但核心诱因还是PG10的CTE特性限制。

方案1：把CTE替换为普通子查询（最优） #

PG10对普通子查询会做条件下推优化，优化器可以识别到可以用tx_id索引匹配少量值，直接走你基准测试验证过的索引扫描逻辑，两种写法都可以：

写法1：IN子查询 #

select 
  e20t.time_stamp,e20t.amount 
from erc20_transf e20t 
where e20t.tx_id in (
  select tx_id 
  from pol_tok_id_ops 
  where condition_id='e3b423dfad8c22ff75c9899c4e8176f628cf4ad4caa00481764d320e7415f7a9'
);

写法2：JOIN子查询 #

select 
  e20t.time_stamp,e20t.amount 
from erc20_transf e20t 
join (
  select tx_id 
  from pol_tok_id_ops 
  where condition_id='e3b423dfad8c22ff75c9899c4e8176f628cf4ad4caa00481764d320e7415f7a9'
) txs on txs.tx_id=e20t.tx_id;

方案2：保留CTE但转成数组匹配 #

如果一定要保留CTE写法，可以手动把tx_id聚合为数组，走基准测试验证过的ANY索引逻辑：

with txs as (
  select array_agg(tx_id) as tx_ids
  from pol_tok_id_ops 
  where condition_id='e3b423dfad8c22ff75c9899c4e8176f628cf4ad4caa00481764d320e7415f7a9'
)
select 
  e20t.time_stamp,e20t.amount 
from erc20_transf e20t, txs
where e20t.tx_id = any(txs.tx_ids);

方案3：会话级别禁用低效关联策略 #

临时关闭Merge Join和Hash Join，强制优化器走嵌套循环关联（小结果集驱动大表走索引）：

-- 会话级别临时生效，断开连接后自动恢复
set enable_mergejoin = off;
set enable_hashjoin = off;
-- 再执行你原来的CTE查询即可

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