0

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?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Mike Moening
  • 473
  • 1
  • 4
  • 7
  • For simple queries, consider using plain SQL, or SQL functions. PL/pgSQL may be overkilll if you don't need any procedural elements. See: https://stackoverflow.com/a/24771561/939860 – Erwin Brandstetter Jul 30 '20 at 23:29

1 Answers1

2

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.

Megadest
  • 614
  • 4
  • 15
  • 1
    I've got the auto_explain enabled and loading all the time in the .conf for the server. However, nothing shows up in the Explain window in pgAdmin. Where does the output for the explain go? – Mike Moening Jul 30 '20 at 20:31
  • @MikeMoening: The output goes into the log file, not to your console. And consider `SET auto_explain.log_min_duration = 1;` or higher to suppress too much noise from very simple and fast statements. See: https://dba.stackexchange.com/q/23355/3684 – Erwin Brandstetter Jul 30 '20 at 23:25
  • Thanks, but I've been unable to locate any log files. All the logs directories I could find are blank. Where do they live? – Mike Moening Aug 01 '20 at 01:26
  • @MikeMoening OK, I guess a bit more background is in order: Pg logging should be enabled in the postgresql.conf file. I would suggest starting with examples here: https://www.endpoint.com/blog/2012/10/30/postgresql-autoexplain-module Alternatively, check out the manual here: https://www.postgresql.org/docs/current/runtime-config-logging.html A pretty cool slide deck by EnterpriseDB's Gabrielle Roth is worth gliding through as well, though it probably does step further out from your immediate question: https://wiki.postgresql.org/images/9/9d/Logging_pgopen_withnotes.pdf – Megadest Aug 04 '20 at 00:42