I have a really basic plpgsql stored procedure like this:

create or replace procedure new_emp_sp (f_name varchar, l_name varchar, age integer, threshold integer)
language plpgsql
as $$
declare
    new_emp_count integer;
begin
    INSERT INTO employees (id, first_name, last_name, age)
        VALUES (nextval('emp_id_seq'), 
                random_string(10),
                random_string(20),
                age);
    select count(*) into new_emp_count from employees where age > threshold;
    update dept_employees set emp_count = new_emp_count where id = 'finance';
end; $$

After calling this stored procedure using call, how do I get the query plans for each of the statements that were executed by the procedure?

Found a couple of StackOverflow answers for a similar requirement but with functions instead of procedures using the auto_explain module and I followed the below steps but it did not work:

1. Executed LOAD '$libdir/plugins/auto_explain'; (I am using an AWS RDS PostgreSQL instance and found this command in this documentation)

2. Executed SET auto_explain.log_min_duration = 0;

3. Executed SET auto_explain.log_analyze = true;

4. Executed SET auto_explain.log_nested_statements = true;

But after executing the procedure, I did not see any visible changes in the output.

For my current requirement I cannot access any log files on the database server and can only run commands on the server through a client / programmatically

CodePudding user response：

you can have log messages sent to the client:

set client_min_messages TO log;

This works fine with auto_explain.log_nested_statements (in psql, anyway). I use it all the time to avoid needing to go scrounge through the log file.