# Query plans with explain()

See whether Postgres scans a whole table or uses an index for a slow Data API query.

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](https://supabase.com/dashboard/project/_/observability/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](https://supabase.com/docs/guides/database/query-optimization#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](#production-use-with-pre-request-protection).

## Enabling `explain()`

On a production project, set up [pre-request protection](#production-use-with-pre-request-protection) before you enable `explain()`.

Run the following SQL using the [SQL Editor](https://supabase.com/dashboard/project/_/sql) in the Supabase Dashboard or with `psql` over a direct connection. To connect with `psql`, see [Direct connection](https://supabase.com/docs/guides/database/connecting-to-postgres#direct-connection).

```sql
alter role authenticator
set pgrst.db_plan_enabled to 'true';

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.

   ```sql
   create table instruments (
     id int8 primary key,
     name text
   );

   insert into instruments
     (id, name)
   values
     (1, 'violin'),
     (2, 'viola'),
     (3, 'cello');
   ```

2. Chain `explain()` to a query:

   ```ts
   const { data, error } = await supabase
     .from('instruments')
     .select()
     .explain()
   ```

   `data` holds the plan:

   ```markdown
   Aggregate  (cost=33.34..33.36 rows=1 width=112)
     ->  Limit  (cost=0.00..18.33 rows=1000 width=40)
           ->  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'`:

```ts
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](https://supabase.com/docs/reference/javascript/explain). 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](https://supabase.com/docs/guides/database/postgres/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:

   ```sql
   select coalesce(d.datname, 'all databases') as database, s.setconfig
   from pg_db_role_setting s
   left join pg_database d on d.oid = s.setdatabase
   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.

   ```sql
   create or replace function public.filter_plan_requests()
   returns void
   set search_path = ''
   as $$
   declare
     headers   json := current_setting('request.headers', true)::json;
     client_ip text := coalesce(headers->>'cf-connecting-ip', '');
     accept    text := lower(coalesce(headers->>'accept', ''));
     your_ip   text := '123.123.123.123';
   begin
     if accept like '%application/vnd.pgrst.plan%' and client_ip != your_ip then
       raise insufficient_privilege using
         message = 'Not allowed to use application/vnd.pgrst.plan';
     end if;
   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:

     ```sql
     alter role authenticator set pgrst.db_pre_request to 'public.filter_plan_requests';
     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.

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

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.

**Example:**

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:

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

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

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

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

**Performance consideration:**

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.

## Disabling `explain()`

When you're done, disable `explain()`:

```sql
alter role authenticator
set pgrst.db_plan_enabled to 'false';

notify pgrst, 'reload config';
```

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

```sql
alter role authenticator
reset pgrst.db_pre_request;

notify pgrst, 'reload config';
```

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