Prepare for the PgBouncer and IPv4 deprecations on 26th January 2024

Database

Debugging performance issues

Debug slow-running queries using the Postgres execution planner.

explain() is a method that provides the Postgres EXPLAIN execution plan of a query. It is a powerful tool for debugging slow queries and understanding how Postgres will execute a given query. This feature is applicable to any query, including those made through rpc() or write operations.

Enabling explain()

explain() is disabled by default to protect sensitive information about your database structure and operations. We recommend using explain() in a non-production environment. Run the following SQL to enable explain():


_10
-- enable explain
_10
alter role authenticator
_10
set pgrst.db_plan_enabled to 'true';
_10
_10
-- reload the config
_10
notify pgrst, 'reload config';

Using explain()

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


_10
const { data, error } = await supabase
_10
.from('countries')
_10
.select()
_10
.explain()

Example data

To illustrate, consider the following setup of a countries table:


_11
create table countries (
_11
id int8 primary key,
_11
name text
_11
);
_11
_11
insert into countries
_11
(id, name)
_11
values
_11
(1, 'Afghanistan'),
_11
(2, 'Albania'),
_11
(3, 'Algeria');

Expected response

The response would typically look like this:


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

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

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:


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

Replace '123.123.123.123' with your actual IP address.

Disabling explain

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


_10
-- disable explain
_10
alter role authenticator
_10
set pgrst.db_plan_enabled to 'false';
_10
_10
-- if you used the above pre-request
_10
alter role authenticator
_10
set pgrst.db_pre_request to '';
_10
_10
-- reload the config
_10
notify pgrst, 'reload config';