How can I identify slow queries in a Postgres function?
For example:
CREATE OR REPLACE FUNCTION my_function ()
RETURNS void AS $$
BEGIN
query#1;
query#2; --> slow query (duration 4 sec)
query#3;
query#4;
END
$$ LANGUAGE plpgsql;
After executing my_function() I get something like this in my Postgres log file:
duration: 4.904 ms statement: select my_function ();",,,,,,,,,"psql"
So I can't identify slow queries in my function.
By default, PL/pgSQL functions are black boxes to the Postgres query planner and logging.
The additional module auto_explain allows for more insights. It can be loaded dynamically, but you have to be a superuser. (Does not have to be installed like most other modules.)
To load it an individual session:
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?
Any query running in the same session will get extra logging. Just:
SELECT my_function(); -- your function
See:
