PostgreSQL 13.11：如何在DO块/函数内获取动态执行语句的EXPLAIN ANALYZE？

1. 实现方法 #

当然可以在函数或DO块里获取动态语句的EXPLAIN ANALYZE结果，给你两种常用的实现方式：

方式一：将执行计划存入临时表 #

适合需要后续查看或处理计划结果的场景：

CREATE OR REPLACE FUNCTION get_explain_result(p1 INT, p2 TEXT)
RETURNS VOID AS $$
DECLARE
    q_sql TEXT := 'CREATE TEMP TABLE temp_result AS SELECT * FROM your_table WHERE id = $1 AND name = $2';
    explain_sql TEXT;
BEGIN
    -- 拼接带EXPLAIN ANALYZE的动态语句
    explain_sql := 'EXPLAIN ANALYZE ' || q_sql;
    
    -- 创建存储执行计划的临时表
    CREATE TEMP TABLE IF NOT EXISTS explain_output (
        "QUERY PLAN" TEXT
    );
    
    -- 清空历史结果（可选）
    TRUNCATE TABLE explain_output;
    
    -- 执行并将计划写入临时表
    INSERT INTO explain_output
    EXECUTE explain_sql USING p1, p2;
    
    -- 之后直接查询explain_output就能看到完整执行计划
END;
$$ LANGUAGE plpgsql;

方式二：直接返回执行计划结果 #

如果需要直接拿到计划内容返回：

CREATE OR REPLACE FUNCTION get_explain_result(p1 INT, p2 TEXT)
RETURNS SETOF TEXT AS $$
DECLARE
    q_sql TEXT := 'CREATE TEMP TABLE temp_result AS SELECT * FROM your_table WHERE id = $1 AND name = $2';
    explain_sql TEXT;
    plan_record RECORD;
BEGIN
    explain_sql := 'EXPLAIN ANALYZE ' || q_sql;
    
    -- 遍历执行计划的每一行并返回
    FOR plan_record IN EXECUTE explain_sql USING p1, p2 LOOP
        RETURN NEXT plan_record."QUERY PLAN";
    END LOOP;
END;
$$ LANGUAGE plpgsql;

在DO块中执行（无需封装函数） #

如果只是临时调试，用DO块更直接：

DO $$
DECLARE
    p1 INT := 123; -- 替换成你的实际参数
    p2 TEXT := 'test'; -- 替换成你的实际参数
    q_sql TEXT := 'CREATE TEMP TABLE temp_result AS SELECT * FROM your_table WHERE id = $1 AND name = $2';
    explain_sql TEXT;
    plan_record RECORD;
BEGIN
    explain_sql := 'EXPLAIN ANALYZE ' || q_sql;
    
    RAISE NOTICE '===== 执行计划 START =====';
    FOR plan_record IN EXECUTE explain_sql USING p1, p2 LOOP
        RAISE NOTICE '%', plan_record."QUERY PLAN";
    END LOOP;
    RAISE NOTICE '===== 执行计划 END =====';
END $$;

2. 函数内执行慢、手动执行快的常见原因 #

你遇到的这种差异，大多和函数执行上下文与手动执行的上下文不一致有关，常见因素包括：

• 参数嗅探问题：函数内的动态查询可能复用了不合适的执行计划，而手动执行时PostgreSQL能根据具体参数生成最优计划。
• 临时表统计信息缺失：函数内创建的临时表没有足够的统计数据，导致优化器选择了低效的执行路径；手动执行时临时表可能已有完整统计。
• 配置参数差异：函数执行时继承的配置（比如work_mem、maintenance_work_mem）和手动执行时不同，导致排序、哈希等操作效率低下，可以用SHOW work_mem;在两边对比。
• 搜索路径不一致：函数的search_path可能指向了非预期的schema，导致误用到其他表。
• 事务上下文差异：函数内的执行处于函数所在事务中，手动执行可能在独立事务里，事务状态会影响锁或资源分配效率。

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