Query plans with explain()

When a Data API query, such as a supabase-js call, is slow, see how Postgres runs it. A query plan shows the steps Postgres takes to answer a query: whether it scans the whole table or uses an index, and how many rows it expects each step to handle. Chain explain() to any query, including rpc() calls and writes, to get its plan instead of its data.

Use explain() when a query is slower than you expect, or after you add an index, to check that Postgres uses the index.

To find which queries are slow in the first place, use the Query Performance page. For a query you run in SQL rather than through the Data API, use explain directly, as described in Analyze the query plan.

explain() is disabled by default, because a plan reveals details of your database structure and operations. Enable it in a non-production environment. To use it in production, restrict it to your own IP address.

Enabling explain()#

On a production project, set up pre-request protection before you enable explain().

Run the following SQL using the SQL Editor in the Supabase Dashboard or with psql over a direct connection. To connect with psql, see Direct connection.

1
alter role authenticator
2
set pgrst.db_plan_enabled to 'true';
3
4
notify pgrst, 'reload config';

Using explain()#

To get the plan of a query:

1. To follow along with sample data, create an instruments table. To use your own table, skip this step.

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

2. Chain explain() to a query:

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

   data holds the plan:

   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)

The plan is in text format by default. To get it as JSON, pass format: 'json':

1
const { data, error } = await supabase.from('instruments').select().explain({ format: 'json' })

For every option explain() accepts, such as analyze and verbose, see the explain() reference. With analyze, Postgres runs the query to measure it, so analyze on a write changes your data. If the plan shows a policy filter that removes most of the table, see Row Level Security performance.

Production use with pre-request protection#

To use explain() in production, restrict it with a pre-request function that rejects plan requests from every IP address except yours. PostgREST runs one pre-request function per project, so check whether yours already has one.

To restrict explain() to your IP address:

1. Check whether your project already uses a pre-request function:

   1
   select coalesce(d.datname, 'all databases') as database, s.setconfig
   2
   from pg_db_role_setting s
   3
   left join pg_database d on d.oid = s.setdatabase
   4
   where s.setrole = 'authenticator'::regrole;

   If any row includes pgrst.db_pre_request set to a function name, your project already has one. If both rows set one, PostgREST uses the row for your database. If no row includes pgrst.db_pre_request, or it has no function name, your project has none. On a self-hosted project, also check PostgREST's PGRST_DB_PRE_REQUEST environment variable.

2. Create the filter function. Replace '123.123.123.123' with your IP address.

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

   The filter reads the client IP from the cf-connecting-ip header, which the Supabase Platform sets on every request. On a self-hosted project, read the IP from a header that your proxy overwrites on every request.

3. Run the filter on every request:

   • If your project has no pre-request function, set the filter as the pre-request function:

     1
     alter role authenticator set pgrst.db_pre_request to 'public.filter_plan_requests';
     2
     notify pgrst, 'reload config';

   • If your project already has one, call public.filter_plan_requests() from that function instead, so the existing function keeps running.

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

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

Disabling explain()#

When you're done, disable explain():

1
alter role authenticator
2
set pgrst.db_plan_enabled to 'false';
3
4
notify pgrst, 'reload config';

If you set public.filter_plan_requests as the pre-request function, remove it too:

1
alter role authenticator
2
reset pgrst.db_pre_request;
3
4
notify pgrst, 'reload config';

If you called public.filter_plan_requests() from your own pre-request function, remove the call from that function instead.