用 plpgsql 編寫的函式呼叫的 Postgres 查詢計劃

當使用pgadmin或獲取在使用者定義函式( UDF ) 中執行plsql的 sql 語句的查詢計劃時，使用. 那麼，如何獲取特定 UDF 呼叫的查詢計劃呢？我看到 UDF 被抽象為 pgadmin 中的單個操作。EXPLAIN``F()

我查看了文件，但找不到任何東西。

目前我正在提取語句並手動執行它們。但這不會減少大型查詢。

例如，考慮下面的 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;

您應該能夠使用auto-explain。打開它並

SET auto_explain.log_min_duration = 0;

並且您應該在日誌中獲取該會話中執行的所有語句的計劃。

您可能還想設置

SET auto_explain.log_analyze = true; 但你基本上會雙重執行 - 一次用於“真實”，一次用於解釋分析。在非計時性能測試階段，此輸出可能比單獨的 EXPLAIN 計劃更有用，因為它提供了實際發生的計劃。

我補充了@rfusca 的建議：plpgsql 函式中的 SQL 語句被視為嵌套語句，您需要設置額外的 Parameter auto_explain.log_nested_statements。

與其他一些擴展不同，您不必為此執行CREATE EXTENSION。只需將其動態載入到您的會話中LOAD。為此，您必須是 超級使用者。

您的會話可能如下所示：

LOAD 'auto_explain';
SET auto_explain.log_min_duration = 1; -- exclude very fast trivial queries
**SET auto_explain.log_nested_statements = ON;** -- statements inside functions
-- SET auto_explain.log_analyze = ON; -- get actual times, too
SELECT * FROM get_paginated_search_results(...);

可能會產生大量的日誌輸出。

日誌消息使用預設設置寫入日誌文件。要直接在客戶端中獲取它們（至少在 psql 中有效），還可以：

SET client_min_messages TO log;

看：

• 在 PostgreSQL 的儲存過程中獲取語句的查詢計劃

目前關於 auto_explain 的手冊。

Depesz 在PostgreSQL 8.4 引入它時寫了一篇關於它的部落格文章。

引用自：https://dba.stackexchange.com/questions/23355