Skip to content

Running EXPLAIN ANALYZE on functions

有时候看看函数内部的 Postgres 查询计划会有帮助。问题是，对一个函数运行 EXPLAIN ANALYZE 通常只会显示一个 函数扫描 或结果节点，这对查询性能几乎没有什么洞察。

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

auto_explain 是一个预装模块，可以记录函数中查询的查询计划。

auto_explain 还有一些设置你需要配置一下：

  • auto_explain.log_nested_statements：在函数中记录查询的计划
  • auto_explain.log_analyze：捕获 explain analyze 结果而不是 explain
  • auto_explain.log_min_duration：如果预计查询运行时间会超过设置的阈值，就记录计划

在大范围更改这些设置可能会导致过多的日志记录。相反，你可以使用 set local 命令在 begin/rollback 块中更改配置。这确保了更改仅限于该事务，并且测试期间的任何写入都会被撤销。

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

1
begin;
2
3
set local auto_explain.log_min_duration = '0';       -- log all query plans
4
set local auto_explain.log_analyze = true;           -- use explain analyze
5
set local auto_explain.log_buffers  = true;          -- use explain (buffers)
6
set local auto_explain.log_nested_statements = true; -- log query plans in functions
7
8
select example_func(); ---<--ADD YOUR FUNCTION HERE
9
10
rollback;

如果需要，你可以为特定角色更改这些设置，但我们不建议长时间将下面的值设置为 1s，因为这可能会影响性能。

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

例如，你可以更改认证器角色的值（为数据 API 提供支持）。

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

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

在运行你的测试之后，你应该能够在 Postgres 日志 中找到该计划。auto_explain 模块的日志总是以“duration:”开头，你可以用这个作为筛选关键词。

🌐 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:

1
select
2
  cast(postgres_logs.timestamp as datetime) as timestamp,
3
  event_message as query_and_plan,
4
  parsed.user_name,
5
  parsed.context
6
from
7
  postgres_logs
8
  cross join unnest(metadata) as metadata
9
  cross join unnest(metadata.parsed) as parsed
10
where regexp_contains(event_message, 'duration:') and regexp_contains(context, '(?i)FUNCTION_NAME')
11
order by timestamp desc
12
limit 100;