主页 / dba / 问题 / 23355
Accepted
Hassan Syed
Asked: 2012-08-29 21:21:59 +0800 CST

用 plpgsql 编写的函数调用的 Postgres 查询计划

  • 772

当使用pgadmin或获取在用户定义函数( UDF ) 中执行plsql的 sql 语句的查询计划时，使用. 那么，如何获取特定 UDF 调用的查询计划呢？我看到 UDF 被抽象为 pgadmin 中的单个操作。EXPLAINF()

我查看了文档，但找不到任何东西。

目前我正在提取语句并手动运行它们。但这不会减少大型查询。

例如，考虑下面的 UDF。这个UDF，即使它能够打印出它的查询字符串，也不能使用复制粘贴，因为它有一个本地创建的临时表，当你粘贴和执行它时它不存在。

CREATE OR REPLACE FUNCTION get_paginated_search_results(
    forum_id_ INTEGER,
    query_    CHARACTER VARYING,
    from_date_ TIMESTAMP WITHOUT TIME ZONE DEFAULT NULL,
    to_date_ TIMESTAMP WITHOUT TIME ZONE DEFAULT NULL,
    in_categories_ INTEGER[] DEFAULT '{}')
RETURNS SETOF post_result_entry AS $$
DECLARE
    join_string CHARACTER VARYING := ' ';
    from_where_date CHARACTER VARYING := ' ';
    to_where_date CHARACTER VARYING := ' ';
    query_string_ CHARACTER VARYING := ' ';
BEGIN
    IF NOT from_date_ IS NULL THEN
        from_where_date := ' AND fp.posted_at > ''' || from_date_ || '''';
    END IF;

    IF NOT to_date_ IS NULL THEN
        to_where_date := ' AND fp.posted_at < ''' || to_date_ || '''';
    END IF;

    CREATE LOCAL TEMP TABLE un_cat(id) ON COMMIT DROP AS (select * from unnest(in_categories_)) ;

    if in_categories_ != '{}' THEN
        join_string := ' INNER JOIN forum_topics ft ON fp.topic_id = ft.id ' ||
        ' INNER JOIN un_cat uc ON uc.id = ft.category_id ' ;
    END IF;

    query_string_ := '
    SELECT index,posted_at,post_text,name,join_date,quotes
    FROM forum_posts fp
    INNER JOIN forum_user fu ON
    fu.forum_id = fp.forum_id AND fu.id = fp.user_id' ||
        join_string
    ||
    'WHERE fu.forum_id = ' || forum_id_ || ' AND
    to_tsvector(''english'',fp.post_text) @@ to_tsquery(''english'','''|| query_||''')' || 
        from_where_date || 
        to_where_date
    ||';';

    RAISE NOTICE '%', query_string_ ;

    RETURN QUERY
    EXECUTE query_string_;
END;
$$ LANGUAGE plpgsql;
postgresql performance

2 个回答

  1. Best Answer

    您应该能够使用auto-explain。打开它并

    SET auto_explain.log_min_duration = 0;

    并且您应该在日志中获取该会话中运行的所有语句的计划。

    您可能还想设置

    SET auto_explain.log_analyze = true; 但你基本上会双重运行 - 一次用于“真实”，一次用于解释分析。在非计时性能测试阶段，此输出可能比单独的 EXPLAIN 计划更有用，因为它提供了实际发生的计划。

    • 19

  2. 我补充了@rfusca 的建议：plpgsql 函数中的 SQL 语句被视为嵌套语句，您需要设置额外的 Parameter auto_explain.log_nested_statements。

    与其他一些扩展不同，您不必为此运行CREATE EXTENSION。只需将其动态加载到您的会话中LOAD。为此，您必须是 超级用户。
    您的会话可能如下所示：

    加载“自动解释”；
设置 auto_explain.log_min_duration = 1; -- 排除非常快速的琐碎查询
设置 auto_explain.log_nested_statements = ON；-- 函数内的语句
-- 设置 auto_explain.log_analyze = ON; -- 也可以获取实际时间
SELECT * FROM get_paginated_search_results(...);

    请注意，默认auto_explain.log_min_duration值为-1，它禁用计划记录。因此，您需要将其设置为其他值以记录任何计划。

    可能会产生大量的日志输出。

    日志消息使用默认设置写入日志文件。要直接在客户端中获取它们（至少在 psql 中有效），还可以：

    SET client_min_messages TO log;

    看：

    当前关于 auto_explain 的手册。
    Depesz 在PostgreSQL 8.4 引入它时写了一篇关于它的博客文章。

    • 19

相关问题