postgreSQL explain analyze 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?

If you do "explain analyse" for every individual query, you will be able to see if a particular query can be optimized. Not sure how it works on the level of function, and whether it makes sense. — Ashalynd
– Ashalynd, Commented May 30, 2015 at 11:52
You can put the function body into a PREPARE thing (arg1, arg2, ...) AS ... original body ...; and call that using explain analyze execute thing (args); — wildplasser
– wildplasser, Commented May 30, 2015 at 12:35
@wildplasser there is an error with the declare statements inside the body and if I dont declare them I get this error: utility statements cannot be prepared — Suzy Tros
– Suzy Tros, Commented May 30, 2015 at 12:59
Note: there are no "declare statements" in the question. (and there is no declare statement in SQL) — wildplasser
– wildplasser, Commented May 30, 2015 at 13:03
@wildplasser that only works if the body uses $1, $2 etc variables right? I'm looking for a way to do it for a function with named variables — Andy
– Andy, Commented Jun 26, 2024 at 16:56

Daniel Vérité · Accepted Answer · 2015-05-30 14:37:55Z

7

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.

answered May 30, 2015 at 14:37

Daniel Vérité

62.3k16 gold badges134 silver badges160 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Suzy Tros Over a year ago

I tried to do this but the output I got is what the function returns. the code I used is: LOAD 'auto_explain'; SET auto_explain.log_min_duration = 0; SET auto_explain.log_analyze = true; SET auto_explain.log_nested_statements = on; select find_4();

Daniel Vérité Over a year ago

The results of explain done by this module go into the server's log, it does not change the output you get.

New Alexandria · Accepted Answer · 2023-05-14 15:40:36Z

0

( pulling from another answer )

You can explain stored procedures through the use of this pattern for stored procedures.

drop procedure if exists get_user;
delimiter #
CREATE OR REPLACE FUNCTION get_user
(
in p_user_id int unsigned,
in p_explain tinyint unsigned
)
begin
  if (p_explain) then
    EXPLAIN ANALYZE
      select * from users where user_id = p_user_id;
  end if;

  select * from users where user_id = p_user_id;
end#

delimiter ;

call get_user(1,1);

this is obviously an inconvenient solution, if the stored procedure wraps a very complicated query

answered May 14, 2023 at 15:40

New Alexandria

7,3366 gold badges62 silver badges83 bronze badges

Collectives™ on Stack Overflow

postgreSQL explain analyze a function

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related