调试性能问题

Debug slow-running queries using the Postgres execution planner.

explain() 是一种提供 Postgres EXPLAIN 查询执行计划的方法。它是调试慢查询和了解 Postgres 如何执行特定查询的强大工具。这个功能适用于任何查询，包括通过 rpc() 发出的查询或写操作。

启用 `explain()`#

🌐 Enabling explain()

explain() 默认是禁用的，以保护你的数据库结构和操作的敏感信息。我们建议在非生产环境中使用 explain()。运行以下 SQL 以启用 explain()：

1
-- enable explain
2
alter role authenticator
3
set pgrst.db_plan_enabled to 'true';
4
5
-- reload the config
6
notify pgrst, 'reload config';

使用 `explain()`#

🌐 Using explain()

要获取查询的执行计划，你可以将 explain() 方法链到 Supabase 查询中：

🌐 To get the execution plan of a query, you can chain the explain() method to a Supabase query:

1
const { data, error } = await supabase
2
  .from('instruments')
3
  .select()
4
  .explain()

示例数据 #

🌐 Example data

举个例子，来看下面这个 instruments 表的设置：

🌐 To illustrate, consider the following setup of a instruments table:

1
create table instruments (
2
  id int8 primary key,
3
  name text
4
);
5
6
insert into instruments
7
  (id, name)
8
values
9
  (1, 'violin'),
10
  (2, 'viola'),
11
  (3, 'cello');

预期的回应 #

🌐 Expected response

这个回答通常看起来像这样：

🌐 The response would typically look like this:

1
Aggregate  (cost=33.34..33.36 rows=1 width=112)
2
  ->  Limit  (cost=0.00..18.33 rows=1000 width=40)
3
        ->  Seq Scan on instruments  (cost=0.00..22.00 rows=1200 width=40)

默认情况下，执行计划会以文本格式返回。不过，你也可以通过指定 format 参数来以 JSON 格式获取它。

🌐 By default, the execution plan is returned in TEXT format. However, you can also retrieve it as JSON by specifying the format parameter.

生产环境使用，带有预请求保护 #

🌐 Production use with pre-request protection

如果你需要在生产环境中启用 explain()，请确保通过限制对 explain() 功能的访问来保护你的数据库。你可以通过使用一个根据 IP 地址过滤请求的预请求函数来实现：

🌐 If you need to enable explain() in a production environment, ensure you protect your database by restricting access to the explain() feature. You can do so by using a pre-request function that filters requests based on the IP address:

1
create or replace function filter_plan_requests()
2
returns void as $$
3
declare
4
  headers   json := current_setting('request.headers', true)::json;
5
  client_ip text := coalesce(headers->>'cf-connecting-ip', '');
6
  accept    text := coalesce(headers->>'accept', '');
7
  your_ip   text := '123.123.123.123'; -- replace this with your IP
8
begin
9
  if accept like 'application/vnd.pgrst.plan%' and client_ip != your_ip then
10
    raise insufficient_privilege using
11
      message = 'Not allowed to use application/vnd.pgrst.plan';
12
  end if;
13
end; $$ language plpgsql;
14
alter role authenticator set pgrst.db_pre_request to 'filter_plan_requests';
15
notify pgrst, 'reload config';

pgrst.db_pre_request 配置只适用于 Data API（PostgREST）。它不适用于 Realtime、Storage 或其他 Supabase 产品。

🌐 The pgrst.db_pre_request configuration only works with the Data API (PostgREST). It does not work with Realtime, Storage, or other Supabase products.

如果你正在使用 db_pre_request 调用一个函数（比如 set_information()），这个函数会在每个请求上设置上下文或执行检查，而你希望其他 Supabase 产品也有类似的行为，那么你必须直接在行级安全 (RLS) 策略中调用这个函数。

🌐 If you're using db_pre_request to call a function (like set_information()) that sets up context or performs checks on every request, and you need similar behavior for other Supabase products, you must call the function directly in your Row Level Security (RLS) policies instead.

示例：

如果你有一个 db_pre_request 函数调用 set_information()，并返回 true 来设置上下文或执行检查，并且你有一个像这样的 RLS 策略：

🌐 If you have a db_pre_request function that calls set_information() that returns true to set up context or perform checks, and you have an RLS policy like:

1
create policy "Individuals can view their own todos."
2
on todos for select
3
using ( (select auth.uid()) = user_id );

要在其他 Supabase 产品中实现相同的行为，你需要在 RLS 策略中直接调用该函数：

🌐 To achieve the same behavior with other Supabase products, you need to call the function directly in your RLS policy:

1
create policy "Individuals can view their own todos."
2
on todos for select
3
using ( set_information() AND (select auth.uid()) = user_id );

这确保了在评估 RLS 策略时，该函数会被调用，适用于所有产品，而不仅仅是数据 API 请求。

🌐 This ensures the function is called when evaluating RLS policies for all products, not only Data API requests.

性能考虑：

要注意，直接在RLS策略中调用函数可能会影响数据库性能，因为在检查策略时，每行都会评估该函数。如果性能成为问题，可以考虑优化函数或使用缓存策略。

🌐 Be aware that calling functions directly in RLS policies can impact database performance, as the function is evaluated for each row when the policy is checked. Consider optimizing your function or using caching strategies if performance becomes an issue.

用你实际的 IP 地址替换 '123.123.123.123'。

🌐 Replace '123.123.123.123' with your actual IP address.

禁用解释 #

🌐 Disabling explain

使用完 explain() 方法后，要禁用它，请执行以下 SQL 命令：

🌐 To disable the explain() method after use, execute the following SQL commands:

1
-- disable explain
2
alter role authenticator
3
set pgrst.db_plan_enabled to 'false';
4
5
-- if you used the above pre-request
6
alter role authenticator
7
set pgrst.db_pre_request to '';
8
9
-- reload the config
10
notify pgrst, 'reload config';

启用 explain()#

使用 explain()#