Postgres query plan of a UDF invocation written in pgpsql

Postgres query plan of a UDF invocation written in pgpsql

时间:2015-07-13 17:21来源:网络整理 作者:KKWL 点击:
It's possible when using the pgadmin or plsql to get a hold of a query plan for a sql statement executed via a UDF (using EXPLAIN). So, how do I get a hold of the query plan for a particular invocation of a UDF? I see the UDF abstracted awa

It's possible when using the pgadmin or plsql to get a hold of a query plan for a sql statement executed via a UDF (using EXPLAIN). So, how do I get a hold of the query plan for a particular invocation of a UDF? I see the UDF abstracted away into a single operation F() in pgadmin).

I have looked at documentation but I couldn't find anything.

Currently I'm pulling out the statements and running them manually. But this isn't going to cut it for large queries.

For example, consider the UDF below. This UDF, even though it has the ability to print out its query string, will not work with a copy-paste as it has a local created temporary table, which does not exist when you paste and execute it.

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;

------分隔线----------------------------