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_10alter role authenticator _10set pgrst.db_plan_enabled to 'true';_10_10-- reload the config_10notify pgrst, 'reload config';
Using explain()#
To get the execution plan of a query, you can chain the explain() method to a Supabase query:
_10const { data, error } = await supabase_10 .from('countries')_10 .select()_10 .explain()
Example data#
To illustrate, consider the following setup of a countries table:
_11create table countries (_11 id int8 primary key,_11 name text_11);_11_11insert into countries_11 (id, name)_11values_11 (1, 'Afghanistan'),_11 (2, 'Albania'),_11 (3, 'Algeria');
Expected response#
The response would typically look like this:
_10Aggregate (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:
_15create or replace function filter_plan_requests()_15returns void as $$_15declare_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_15begin_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;_15end; $$ language plpgsql;_15alter role authenticator set pgrst.db_pre_request to 'filter_plan_requests';_15notify 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_10alter role authenticator _10set pgrst.db_plan_enabled to 'false';_10_10-- if you used the above pre-request_10alter role authenticator _10set pgrst.db_pre_request to '';_10_10-- reload the config_10notify pgrst, 'reload config';