Getting the query plan for statements inside a stored procedure in PostgreSQL

Question

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:

Executed LOAD '$libdir/plugins/auto_explain'; (I am using an AWS RDS PostgreSQL instance and found this command in this documentation)
Executed SET auto_explain.log_min_duration = 0;
Executed SET auto_explain.log_analyze = true;
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

score 3 · Accepted Answer · answered Mar 02 '22 at 02:40

3

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.

answered Mar 02 '22 at 02:40

jjanes

37,812
5
27
34

It works! Is this the only way of getting the query plan for a stored procedure/function? – user9492428 Mar 02 '22 at 12:57
Its the only way I know of. – jjanes Mar 02 '22 at 16:33
Got it. Thanks for the answer – user9492428 Mar 02 '22 at 19:43

Erwin Brandstetter · Answer 2 · 2023-07-17T22:15:45.013

Is this the only way of getting the query plan for a stored procedure/function?

There is another way without installing auto_explain. The EXPLAIN command basically returns a set of text, and you can work with that in a PL/pgSQL code block:

CREATE OR REPLACE PROCEDURE pg_temp.get_plan ()
  LANGUAGE plpgsql AS
$proc$
DECLARE
   _line text;
BEGIN
   FOR _line IN
      EXPLAIN ANALYZE
      SELECT 1            -- your query here !!!
   LOOP
      RAISE NOTICE '%', _line;
   END LOOP;
END
$proc$;

CALL pg_temp.get_plan();

You'll get in your client (any client that shows notices):

NOTICE:  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=1)
NOTICE:  Planning Time: 0.045 ms
NOTICE:  Execution Time: 0.035 ms
CALL

See:

Thanks for the answer. It does work only issue for me with your approach is that a new procedure will need to be created and that does not fit my current use case. No issue other than that basically. Hope it will help someone else. Thanks again! — user9492428, Mar 07 '22 at 17:42

Getting the query plan for statements inside a stored procedure in PostgreSQL

2 Answers2