Running Explain Analyze On Functions

Sometimes it can help to look at Postgres query plans inside a function. The problem is that running EXPLAIN ANALYZE on a function usually just shows a function scan or result node, which gives little insight into how the queries actually perform.

auto_explain is a pre-installed module that is able to log query plans for queries within functions.

auto_explain has a few settings that you still need to configure:

• auto_explain.log_nested_statements: log the plans of queries within functions
• auto_explain.log_analyze: capture the explain analyze results instead of explain
• auto_explain.log_min_duration: if a query is expected to run for longer than the setting's threshold, log the plan

Changing these settings at a broad scale can lead to excessive logging. Instead, you can change the configs within a begin/rollback block with the set local command. This ensures the changes are isolated to the transaction, and any writes made during testing are undone.

begin;

set local auto_explain.log_min_duration = '0';       -- log all query plans
set local auto_explain.log_analyze = true;           -- use explain analyze
set local auto_explain.log_buffers  = true;          -- use explain (buffers)
set local auto_explain.log_nested_statements = true; -- log query plans in functions

select example_func(); ---<--ADD YOUR FUNCTION HERE

rollback;

If needed, you can change these settings for specific roles, but we don't recommend configuring the value below 1s for extended periods, as it may degrade performance.

For instance, you could change the value for the authenticator role (powers the Data API).

ALTER ROLE postgres SET auto_explain.log_min_duration = '.5s';

After running your test, you should be able to find the plan in the Postgres logs. The auto_explain module always starts logs with the term "duration:", which can be used as a filter keyword.

You can also filter for the specific function in the log explorer with the below query:

select
  cast(postgres_logs.timestamp as datetime) as timestamp,
  event_message as query_and_plan,
  parsed.user_name,
  parsed.context
from
  postgres_logs
  cross join unnest(metadata) as metadata
  cross join unnest(metadata.parsed) as parsed
where regexp_contains(event_message, 'duration:') and regexp_contains(context, '(?i)FUNCTION_NAME')
order by timestamp desc
limit 100;