CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.4
Suzy Tros
Suzy Tros

Reputation: 373

postgreSQL explain analyze a function

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?

Upvotes: 11

Views: 14483

Answers (2)

New Alexandria
New Alexandria

Reputation: 7324

( 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

Upvotes: 0

Daniel Vérité
Daniel Vérité

Reputation: 61506

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.

Upvotes: 7

Related Questions