# Query performance and EXPLAIN

> Find and fix slow queries in a SupaHost project: read EXPLAIN ANALYZE plans, test queries under RLS as a real user, use pg_stat_statements, and apply the usual fixes.

Source: https://supahost.app/docs/database/query-performance · Updated 2026-11-11

All projects on an instance share one Postgres server, its CPU and its memory. A single slow query in one project can make the others slower too, so it pays to find and fix slow queries early.

SupaHost doesn't offer Studio's Reports pages or a log explorer, so you diagnose performance with SQL. The tools below all work in Studio's SQL editor, through the MCP server's `execute_sql`, or over psql on Business.

## Read a plan with EXPLAIN

`explain` shows how Postgres intends to run a query. `explain analyze` actually runs it and reports real timings and row counts. Add `buffers` to see how much data was read.

```sql
explain (analyze, buffers)
select id, number, total
from public.invoices
where customer_id = 812
order by issued_on desc
limit 20;
```

A slow plan might look like this:

```
Limit  (actual time=148.2..148.3 rows=20 loops=1)
  ->  Sort  (actual time=148.2..148.2 rows=20 loops=1)
        Sort Key: issued_on DESC
        ->  Seq Scan on invoices  (actual time=0.03..147.9 rows=311 loops=1)
              Filter: (customer_id = 812)
              Rows Removed by Filter: 402118
Planning Time: 0.2 ms
Execution Time: 148.4 ms
```

What to look for:

- **`Seq Scan` with a large "Rows Removed by Filter"**: Postgres read the whole table to find a few rows. An index on the filter column usually fixes it.
- **Estimated rows far from actual rows**: the planner is working from stale statistics. Run `analyze public.invoices;`.
- **`Sort` on many rows** before a `Limit`: an index matching the `order by` lets Postgres read rows already sorted.
- **Nested loops with high `loops=` counts**: often a missing index on a join column.

For this query, a composite index covers the filter and the sort:

```sql
create index invoices_customer_issued_idx
  on public.invoices (customer_id, issued_on desc);
```

Run the `explain` again and you should see an `Index Scan` and a time measured in fractions of a millisecond. More on choosing indexes in [Indexes](/docs/database/indexes).

`explain analyze` really executes the statement. For an `update` or `delete`, wrap it in a transaction and roll back:

```sql
begin;
explain analyze delete from public.invoices where issued_on < '2020-01-01';
rollback;
```

## Test the query the way your app runs it

Queries from supabase-js run as the `anon` or `authenticated` role with your RLS policies applied. Studio's SQL editor runs as `postgres`, which skips those policies, so a query that's fast in Studio can be slow from the app. Reproduce the real conditions inside a transaction:

```sql
begin;
set local role authenticated;
set local request.jwt.claims = '{"sub": "6f1c2a7e-0d43-4b8e-9a51-3c2f7d9e8b10", "role": "authenticated"}';

explain (analyze, buffers)
select * from public.invoices order by issued_on desc limit 20;

rollback;
```

Use a real user id from `auth.users`. The plan now includes the policy's conditions. If a policy calls `auth.uid()` directly, wrap it as `(select auth.uid())` so Postgres evaluates it once per query instead of once per row. [RLS performance best practices](/kb/rls-performance) covers this and other policy patterns.

## Find the slowest queries with pg_stat_statements

`pg_stat_statements` records every distinct query shape with its call count and total time. It's part of the Supabase Postgres image. Check it's enabled in your project's database and create it if not:

```sql
select extname from pg_extension where extname = 'pg_stat_statements';

create extension if not exists pg_stat_statements with schema extensions;
```

Then list the queries that use the most total time, which is usually where fixes pay off most:

```sql
select
  calls,
  round(total_exec_time::numeric, 1) as total_ms,
  round(mean_exec_time::numeric, 2) as mean_ms,
  rows,
  left(query, 120) as query
from extensions.pg_stat_statements
where dbid = (select oid from pg_database where datname = current_database())
order by total_exec_time desc
limit 15;
```

The `dbid` filter matters on SupaHost: the statistics cover the whole Postgres server, and every project is its own database on that server. If the view returns a permission error, see [insufficient privilege on pg_stat_statements](/kb/pg-stat-statements-privilege).

After you fix something, reset the counters so you can measure the change: `select extensions.pg_stat_statements_reset();` (this may also need extra privileges).

## Spot what's running right now

`pg_stat_activity` shows live sessions. This finds queries in your project's database that have been running for more than five seconds:

```sql
select pid, usename, state, now() - query_start as running_for, left(query, 100) as query
from pg_stat_activity
where datname = current_database()
  and state <> 'idle'
  and now() - query_start > interval '5 seconds'
order by running_for desc;
```

To stop one, `select pg_cancel_backend(<pid>);` cancels the query and keeps the connection. If queries are waiting on each other, see [Finding queries blocked by locks](/kb/blocked-queries).

## Common fixes

| Symptom | Usual fix |
|---|---|
| Sequential scan on a large table | Add an index on the filter or join column |
| Slow only through the Data API | Index the RLS policy columns; wrap `auth.uid()` in `(select ...)` |
| Big `offset` values getting slower | Use keyset pagination: `where issued_on < $last order by issued_on desc limit 20` |
| Wrong row estimates | `analyze` the table; check autovacuum is keeping up |
| `select *` on wide rows | Select only the columns you need |
| Slow database function | Profile it as shown in [Running EXPLAIN ANALYZE on functions](/kb/explain-analyze-functions) |

If the whole instance is slow rather than one query, check [High CPU usage on your instance](/kb/high-cpu) and [High memory or swap usage](/kb/high-memory). Every project shares the plan's CPU and RAM, so a larger plan also helps; see [Plans and limits](/docs/platform/plans).

## Related

- [Indexes](/docs/database/indexes)
- [Connections and timeouts](/docs/database/connections-and-timeouts)
- [Canceling statement due to statement timeout](/kb/statement-timeout)
- Postgres reference: [Using EXPLAIN](https://www.postgresql.org/docs/17/using-explain.html)