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';