18

I am using a PL/pgSQL function in PostgreSQL 9.3 with several complex queries inside:

create function f1()
  returns integer as
$$
declare

event tablename%ROWTYPE;
....
....

begin

FOR event IN
   SELECT * FROM tablename WHERE condition
LOOP
   EXECUTE 'SELECT f2(event.columnname)' INTO dummy_return;
END LOOP;

...

INSERT INTO ... FROM a LEFT JOIN b ... LEFT JOIN c WHERE ...

UPDATE T SET cl1 = M.cl1 FROM M WHERE M.pkcols = T.pkcols;

...

end
$$ language plpgsql;

If I ran EXPLAIN ANALYZE f1(), I only get the total time, but no details. Is there a way I can get detailed results for all queries in the function?

If queries in the function should not be optimized by Postgres, I would also ask for an explanation.

14

First, the correct syntax for the EXPLAIN call needs a SELECT. You can't just write the bare function name in SQL:

EXPLAIN ANALYZE SELECT f1();

Optimization

PL/pgSQL functions are black boxes to the query planner. Queries inside are optimized just like other queries, but separately and one by one like prepared statements, and the execution plan may be cached for the duration of the session. Details:

EXPLAIN function bodies

Like @Daniel already commented, you can use the additional module auto_explain to get more details (lots of details). Statements inside plpgsql functions are considered "nested statements". Be sure to set

SET auto_explain.log_nested_statements = ON

Detailed instructions:

As an exception to the rule, very simple SQL functions (not plpgsql) may be "inlined", i.e. the function code is inserted into the outer query and everything is executed like there wasn't a function to begin with. The query plan includes detailed information in such cases.

  • After enable the 'auto_explain' like below, load 'auto_explain'; set auto_explain.log_min_duration=0; set auto_explain.log_nested_statements=ON; SET auto_explain.log_analyze = true; I'm getting below msg in log file, '2014-12-08 18:21:59 IST LOG: could not receive data from client: No connection could be made because the target machine actively refused it' can anyone guide me wht is the actual issue.... – skumar Dec 8 '14 at 12:58
  • Note: After running plpgsql function only i'm getting above msg in log file. – skumar Dec 8 '14 at 13:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.