Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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,
    in_categories_ INTEGER[] DEFAULT '{}')
RETURNS SETOF post_result_entry AS $$
    join_string CHARACTER VARYING := ' ';
    from_where_date CHARACTER VARYING := ' ';
    to_where_date CHARACTER VARYING := ' ';
    query_string_ CHARACTER VARYING := ' ';
    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 = ' ||
        ' INNER JOIN un_cat uc ON = 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 = fp.user_id' ||
    'WHERE fu.forum_id = ' || forum_id_ || ' AND
    to_tsvector(''english'',fp.post_text) @@ to_tsquery(''english'','''|| query_||''')' || 
        from_where_date || 

    RAISE NOTICE '%', query_string_ ;

    EXECUTE query_string_;
$$ LANGUAGE plpgsql;
share|improve this question

migrated from Aug 29 '12 at 15:39

This question came from our site for professional and enthusiast programmers.

up vote 11 down vote accepted

You should be able to use auto-explain. Turn it on and

SET auto_explain.log_min_duration = 0;

and you should get the plans in your log for all statements run in that session.

You might also want to set

SET auto_explain.log_analyze = true; but you'll essentially run everything double - once for 'real' and once to EXPLAIN ANALYZE on. During a non-timing performance testing phase, this output can be much more useful than EXPLAIN plans alone, as it provides what plan actually happened.

share|improve this answer
As @Erwin points out below, you should set the auto_explain.log_nested_statements = ON as well. – rfusca Aug 29 '12 at 15:22
Thanks, that did the trick. it's a shame this functionality isn't accessible via a GUI. – Hassan Syed Aug 31 '12 at 5:44

I addition to @rfusca's advice: SQL statements inside plpgsql functions are considered nested statements and you need to set the additional Parameter auto_explain.log_nested_statements.

Unlike some other extensions, you don't have to run CREATE EXTENSION for this one. Just load it dynamically into your session with LOAD. Your session could look like this:

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(...);

May produce a lot of log output.
The current manual on auto_explain.
Depesz wrote a blog article about it when it was introduced with PostgreSQL 8.4.

share|improve this answer
+1 - been so long, I forgot about needing to set the log_nested_statements line – rfusca Aug 29 '12 at 15:22
You deserve the credit for bringing up the right tool anyway. – Erwin Brandstetter Aug 29 '12 at 15:23
I have a postgres database on Amazon's managed service (RDS), for which LOAD 'auto_explain'; returns ERROR: access to library "auto_explain" is not allowed. What in that case? I've had some success hacking my functions with return query explain select … but that's laborious and slow. – poshest Apr 19 at 11:16

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.