postgreSQL explain analyse a function

Question

I am trying to understand the query optimization in postgresql and I have a function with some queries in it. Some of them are simple querys that saves a value into a variable and then the next query takes this variable to find something.. lets say:

 function()...
select type into t
from tableA
where code = a_c;

select num into n
from tableB
where id = t; 
end function...

and many more.. If I want to explain analyse the whole function I execute the command explain analyse select function(); Is this the right way to do it or should I have to explain analyse every query inside the function and if so with what values?

Answer 1

Consider using the auto_explain module:

The auto_explain module provides a means for logging execution plans of slow statements automatically, without having to run EXPLAIN by hand. This is especially helpful for tracking down un-optimized queries in large applications.

with auto_explain.log_nested_statements turned on:

auto_explain.log_nested_statements (boolean)

auto_explain.log_nested_statements causes nested statements (statements executed inside a function) to be considered for logging. When it is off, only top-level query plans are logged. This parameter is off by default. Only superusers can change this setting.