Postgres query plan of a function invocation written in plpgsql
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.
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.