Find slow queries: pg_stat_statements, explain analyze, inspect db

The answer. Slowness has a short list of causes in a Supabase project (a policy evaluated per row, a foreign key without an index, a filter on an unindexed column, a count on a large table) and a short list of tools that name them. pg_stat_statements aggregates every statement shape with call counts and total time; the dashboard's Query Performance report is a view over it, sorted by the queries that cost the most in aggregate rather than the slowest single run. For one query, explain (analyze, buffers) shows the actual plan with row counts and timings; from the client, .explain({ analyze: true }) on a builder does the same through PostgREST, policies included. The index_advisor extension proposes indexes for a given statement, and the CLI's supabase inspect db commands print the usual DBA checklists (outliers, long-running queries, bloat, cache hit ratio, unused and duplicate indexes) without writing SQL.

The pattern.

npx supabase inspect db outliers --linked            # top statements by total time (pg_stat_statements)
npx supabase inspect db long-running-queries --linked
npx supabase inspect db index-usage --linked         # tables read mostly by seq scan
npx supabase inspect db unused-indexes --linked

explain (analyze, buffers) select id, title from public.tickets where tenant = 'packt' order by created_at desc limit 20;
-- want: Index Scan using tickets_tenant_created_idx, and an InitPlan for auth.uid(), not a Filter on every row
select * from index_advisor('select id from public.tickets where assignee = 7');   -- create extension index_advisor

Watch out.

explain analyze runs the query; wrap a write in begin; … rollback; before explaining it.
Numbers vary with load and cache state. Compare plans (Seq Scan vs Index Scan, estimated vs actual rows), not milliseconds from one run.
An index makes writes slower and takes space; the unused-index report exists so the "add an index" reflex has a counterweight.