Query performance and EXPLAIN

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.

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:

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.

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

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:

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 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:

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:

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.

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:

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.

Common fixes

If the whole instance is slow rather than one query, check High CPU usage on your instance and High memory or swap usage. Every project shares the plan's CPU and RAM, so a larger plan also helps; see Plans and limits.

Related

• Indexes
• Connections and timeouts
• Canceling statement due to statement timeout
• Postgres reference: Using EXPLAIN