# 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`](https://www.depesz.com/2013/04/16/explaining-the-unexplainable/) on a function usually shows a [function scan](https://pganalyze.com/docs/explain/scan-nodes/function-scan) or result node, which gives little insight into how the queries perform.

[`auto_explain`](https://www.postgresql.org/docs/current/auto-explain.html) 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.

```sql
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).

```sql
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](https://supabase.com/dashboard/project/_/logs/postgres-logs?s=duration:). 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 [SQL Editor](https://supabase.com/dashboard/project/_/sql/new?skip=true\&source=logs\&content=select%0A%20%20timestamp%2C%0A%20%20event_message%20as%20query_and_plan%2C%0A%20%20log_attributes%5B%27parsed.user_name%27%5D%20as%20user_name%2C%0A%20%20log_attributes%5B%27parsed.context%27%5D%20as%20context%0Afrom%20logs%0Awhere%0A%20%20source%20%3D%20%27postgres_logs%27%0A%20%20and%20match%28event_message%2C%20%27duration%3A%27%29%0A%20%20and%20match%28log_attributes%5B%27parsed.context%27%5D%2C%20%27%28%3Fi%29FUNCTION_NAME%27%29%0Aorder%20by%20timestamp%20desc%0Alimit%20100%3B) with the below query:

```sql
select
  timestamp,
  event_message as query_and_plan,
  log_attributes['parsed.user_name'] as user_name,
  log_attributes['parsed.context'] as context
from logs
where
  source = 'postgres_logs'
  and match(event_message, 'duration:')
  and match(log_attributes['parsed.context'], '(?i)FUNCTION_NAME')
order by timestamp desc
limit 100;
```