Search code examples
postgresqlfunctionperformancequery-optimizationplpgsql

How to identify slow queries in PostgreSQL function?

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.

Solution

  • 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: