如何在DuckDB的WITH RECURSIVE SQL查询中统计每次迭代执行时间

方法1：在递归CTE中嵌入时间戳记录 #

直接在递归的每一步嵌入时间戳函数，通过计算相邻时间的差值得到单次迭代耗时，示例代码如下：

WITH RECURSIVE recursive_cte AS (
    -- 初始查询：记录第一次迭代的起止时间
    SELECT 
        1 AS iteration,
        target_column,
        current_timestamp() AS start_time,
        current_timestamp() AS end_time
    FROM your_source_table
    WHERE initial_condition
    
    UNION ALL
    
    -- 递归查询：用上一次的结束时间作为本次起始时间，记录当前结束时间
    SELECT 
        rc.iteration + 1 AS iteration,
        rc.target_column,
        rc.end_time AS start_time,
        current_timestamp() AS end_time
    FROM recursive_cte rc
    WHERE rc.iteration < 20 -- 替换为你的递归终止条件
)
SELECT 
    iteration,
    target_column,
    start_time,
    end_time,
    -- 计算单次迭代耗时（毫秒）
    DATE_DIFF('millisecond', start_time, end_time) AS execution_time_ms
FROM recursive_cte;

关键注意点 #

• 确保初始查询和递归查询的列数、数据类型完全匹配，包括时间戳列
• 如果递归逻辑包含复杂计算，要把end_time的赋值放在计算逻辑之后，保证耗时统计准确

方法2：利用DuckDB profiling功能追踪底层执行时间 #

通过启用DuckDB的性能分析，生成包含细分执行步骤的profile文件，从中提取递归迭代的耗时：

-- 启用性能分析并指定输出文件
PRAGMA enable_profiling;
PRAGMA profile_output = 'recursive_profile.json';

-- 执行你的递归查询
WITH RECURSIVE your_recursive_cte AS (...) SELECT * FROM your_recursive_cte;

-- 关闭性能分析
PRAGMA disable_profiling;

生成的recursive_profile.json文件中会包含递归算子的执行时间明细，你可以从中筛选出各迭代的耗时数据。

常见语法错误排查 #

• 避免使用非标准SQL时间函数，DuckDB推荐使用current_timestamp()或now()
• 计算时间差时，优先用DATE_DIFF函数替代手动提取时间戳，减少语法错误
• 递归终止条件要逻辑清晰，避免因无限递归导致的执行异常

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