EXPLAIN ANALYZE shows no details for queries inside a plpgsql function

I am using a PL/pgSQL function in PostgreSQL 9.3 with several complex queries inside:

create function f1()
  returns integer as
$
declare

event tablename%ROWTYPE;
....
....

begin

FOR event IN
   SELECT * FROM tablename WHERE condition
LOOP
   EXECUTE 'SELECT f2(event.columnname)' INTO dummy_return;
END LOOP;

...

INSERT INTO ... FROM a LEFT JOIN b ... LEFT JOIN c WHERE ...

UPDATE T SET cl1 = M.cl1 FROM M WHERE M.pkcols = T.pkcols;

...

end
$ language plpgsql;

If I ran EXPLAIN ANALYZE f1(), I only get the total time, but no details. Is there a way I can get detailed results for all queries in the function?

If queries in the function should not be optimized by Postgres, I would also ask for an explanation.

The correct syntax for the EXPLAIN call needs a SELECT. You can't just write the bare function name in SQL:

EXPLAIN ANALYZE SELECT f1();

Optimization

PL/pgSQL functions are black boxes to the query planner. Queries inside are optimized just like other queries, but separately and one by one like prepared statements, and the execution plan may be cached for the duration of the session. Details:

• Function Performance

EXPLAIN function bodies

Like @Daniel already commented, you can use the additional module auto_explain to get more details (lots of details). Statements inside plpgsql functions are considered "nested statements". Be sure to set:

SET auto_explain.log_nested_statements = ON;

Detailed instructions:

• Get query plan for SQL statement nested in a PL/pgSQL function

As an exception to the rule, simple SQL functions (not plpgsql) may be "inlined", i.e. the function code is inserted into the outer query and everything is executed like there wasn't a function to begin with. The query plan includes detailed information in such cases.

StackExchange Database Administrators Q#84414, answer score: 37