PostgreSQL递归CTE查询调优：如何强制使用索引扫描？

针对你遇到的递归CTE与ad表合并查询后变为顺序扫描（Seq Scan）导致变慢的问题，可以通过以下几种方式尝试引导或强制优化器选择索引扫描：

1. 用NOT MATERIALIZED打破CTE优化栅栏 #

PostgreSQL 12及以上版本中，CTE默认是物化的（即独立执行并存储结果后再参与主查询），这会限制优化器将CTE的小结果集与ad表的索引扫描做关联优化。在CTE定义前添加NOT MATERIALIZED关键字，可让优化器将CTE逻辑展开到主查询中，从而有可能选择索引扫描：

WITH cte AS NOT MATERIALIZED (
    -- 你的递归CTE查询语句
)
SELECT *
FROM cte
JOIN ad ON ad.your_join_column = cte.your_join_column;

2. 将CTE改写为子查询 #

直接把递归CTE的逻辑嵌入主查询作为子查询，规避CTE的物化特性限制优化决策，优化器通常能更好地识别小结果集并匹配索引扫描：

SELECT *
FROM (
    -- 你的递归CTE查询语句
) AS cte
JOIN ad ON ad.your_join_column = cte.your_join_column;

3. 临时禁用顺序扫描（仅限临时测试） #

可以临时关闭当前会话的顺序扫描开关，强制优化器优先选择索引扫描，执行后记得恢复默认设置：

-- 临时禁用顺序扫描
SET enable_seqscan = off;

-- 执行合并查询
WITH cte AS (
    -- 你的递归CTE查询语句
)
SELECT *
FROM cte
JOIN ad ON ad.your_join_column = cte.your_join_column;

-- 恢复默认设置
SET enable_seqscan = on;

4. 验证索引与统计信息 #

• 确认ad表关联字段存在有效索引，例如：

  CREATE INDEX idx_ad_join_column ON ad(your_join_column);
  

• 更新ad表统计信息，确保优化器能准确判断数据分布：

  ANALYZE ad;
  

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