CODEWITHSUNDEEP
postgresqlfunctionplpgsqlexplain
Mike Moening
Mike Moening

Reputation: 473

How to explain analyze PostgreSQL plpgsql function in pgAdmin4?

We are porting MSSQL procs to PostgreSQL plpgsql functions in PG version 12. Each function RETURNS TABLE.

How can we explain analyze the inside of the functions to figure out where the bottle necks are?

Inside pgAdmin4 query window we enable the verbose explain and execute the function call like this:

select * from rts.do_something(301, 7, '[{"id":3488269, "seq":2, "ts":"2020-07-27"}]'::json);

However, the explain tab on the bottom of the window comes back with an icon just says: "rts.Function Scan" and nothing else:

There HAS to be a simple way of doing this?

Upvotes: 0

Views: 2901

Answers (1)

Megadest
Megadest

Reputation: 634

PostgreSQL query planner treats function as "black boxes". They are planned and optimized, but in a separate process.

You can peek inside by using the auto_explain module: https://www.postgresql.org/docs/current/auto-explain.html

After enabling the module, set the following parameters:

SET auto_explain.log_nested_statements = ON; -- this will log function internal statements
SET auto_explain.log_min_duration = 0; -- this will give you logs of all statements in the session

Check the documentation on the link above for more details or ask in the comments.

Upvotes: 2

Related Questions