1. 问题现象与背景分析
最近在数据仓库迁移项目中遇到一个有趣的现象：同一段递归CTE查询在PostgreSQL中执行仅需200ms，而在DuckDB中却需要超过15秒。这个性能差异引起了我的注意，因为两者都是现代OLAP引擎，理论上DuckDB的列式存储应该更擅长分析型查询。
经过排查，发现问题的核心在于两种数据库对递归查询（WITH RECURSIVE）的实现机制存在本质差异。PostgreSQL作为成熟的OLTP数据库，其递归查询优化器已经过多年打磨；而DuckDB虽然整体性能出色，但在某些特定场景如复杂递归查询上仍有优化空间。
2. 递归CTE的工作原理对比
2.1 PostgreSQL的执行机制
PostgreSQL采用经典的迭代式递归执行：
- 先计算非递归部分（anchor member）
- 将结果存入工作表
- 重复执行递归部分（recursive member），直到结果集为空
- 每次迭代都会自动优化JOIN顺序和访问路径
关键优化点：
- 自动识别停止条件
- 动态调整JOIN策略
- 内存工作集大小自适应
2.2 DuckDB的当前实现
DuckDB 0.8.1版本的递归查询采用更保守的策略：
- 严格按语义分阶段执行
- 默认不使用并行处理
- 中间结果物化策略较保守
- 优化器对递归深度预测不足
实测发现当递归深度超过100层时，性能下降明显。
3. 性能瓶颈的具体分析
3.1 示例查询结构
WITH RECURSIVE hierarchy AS ( -- Anchor member SELECT id, parent_id, name, 1 AS level FROM nodes WHERE parent_id IS NULL UNION ALL -- Recursive member SELECT n.id, n.parent_id, n.name, h.level + 1 FROM nodes n JOIN hierarchy h ON n.parent_id = h.id ) SELECT * FROM hierarchy;3.2 PostgreSQL的执行计划
QUERY PLAN ────────────────────────────────────────────────── CTE Scan on hierarchy (cost=2543.25..2967.25 rows=21200) CTE hierarchy -> Recursive Union (cost=0.00..2543.25 rows=21200) -> Seq Scan on nodes (cost=0.00..25.00 rows=500) -> Hash Join (cost=125.00..229.25 rows=2070) Hash Cond: (n.parent_id = h.id) -> Seq Scan on nodes n (cost=0.00..75.00 rows=5000) -> Hash (cost=95.00..95.00 rows=2400) -> WorkTable Scan on hierarchy h (cost=0.00..95.00 rows=2400)关键优化：
- 智能选择Hash Join
- 准确预估中间结果集大小
- 动态调整内存分配
3.3 DuckDB的执行计划
┌───────────────────────────┐ │ PROJECTION │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ CTE_SCAN │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ RECURSIVE_CTE │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ UNION │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ SEQ_SCAN │ └───────────────────────────┘主要问题：
- 缺乏JOIN优化提示
- 中间结果全量物化
- 无并行执行
4. 针对性优化方案
4.1 查询重写技巧
对于DuckDB，可以手动展开递归：
-- 第一层 CREATE TEMP TABLE level1 AS SELECT id, parent_id, name, 1 AS level FROM nodes WHERE parent_id IS NULL; -- 第二层 CREATE TEMP TABLE level2 AS SELECT n.id, n.parent_id, n.name, 2 AS level FROM nodes n JOIN level1 l ON n.parent_id = l.id; -- 合并结果 SELECT * FROM level1 UNION ALL SELECT * FROM level2 ...4.2 配置调优
调整DuckDB配置：
SET max_memory='8GB'; SET threads TO 4; SET preserve_insertion_order=false;4.3 索引策略
虽然DuckDB自动创建部分索引，但显式创建更有效：
-- 对递归JOIN字段创建索引 CREATE INDEX idx_nodes_parent ON nodes(parent_id);5. 深度优化建议
5.1 数据预处理
对于超深层次结构（>1000层），建议：
- 预计算路径枚举（Materialized Path）
- 使用闭包表（Closure Table）
- 定期物化热门查询路径
5.2 混合执行模式
对于复杂查询，可以：
-- 使用PostgreSQL处理递归部分 WITH pg_result AS ( SELECT * FROM postgres_scan('pg_conn', 'public', 'hierarchy_query') ) -- 在DuckDB中继续处理 SELECT * FROM pg_result WHERE ...5.3 监控指标
关键监控点：
-- 查看递归查询内存使用 PRAGMA memory_usage; -- 分析JOIN性能 PRAGMA enable_profiling; PRAGMA profiling_output='query_profile.json';6. 实际案例对比
测试环境：
- 10万节点数据
- 最大深度15层
- AWS r5.large实例
| 执行方式 | PostgreSQL | DuckDB原生 | DuckDB优化后 |
|---|---|---|---|
| 首次执行 | 218ms | 15600ms | 420ms |
| 缓存执行 | 45ms | 8200ms | 380ms |
| 内存占用 | 78MB | 1.2GB | 210MB |
优化关键：
- 限制递归深度（WHERE level < 20）
- 使用TEMPORARY TABLE分段处理
- 显式指定JOIN顺序
7. 经验总结与最佳实践
对于<100层的递归查询，PostgreSQL通常更优
DuckDB适合：
- 浅层次递归（<10层）
- 能手动展开的固定深度查询
- 列式存储优势场景（聚合分析）
通用优化原则：
# 伪代码：递归查询优化决策树 def optimize_recursive_query(db_type, query): if db_type == "duckdb": if query.max_depth > 10: return rewrite_as_iterative() else: return add_hints() else: return use_native_recursive()最后分享一个调试技巧：在DuckDB中可以通过EXPLAIN ANALYZE观察递归查询的中间结果集大小，这对识别性能瓶颈非常有用。我在实际项目中发现，当递归中间结果超过内存工作区大小时，性能会急剧下降，这时就需要考虑本文提到的分段执行方案了。
