36

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.

Improve this question
1

1 Answer 1

Reset to default
42

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, 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.

Improve this answer
2
  • 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.... Commented Dec 8, 2014 at 12:58
  • Note: After running plpgsql function only i'm getting above msg in log file. Commented Dec 8, 2014 at 13:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.