How to View Slow Queries in Supabase

The pg_stat_statements extension tracks execution statistics for every SQL query that runs against your database. It records the total execution time, number of calls, rows returned, and more. On most Supabase projects this extension is already enabled. If not, you can enable it from the SQL Editor. Once enabled, it begins collecting data immediately for all new queries.

1-- Enable the extension (if not already active)
2create extension if not exists pg_stat_statements;
3
4-- Verify it is enabled
5select * from pg_available_extensions where name = 'pg_stat_statements';

Once the extension is active, query it to find your slowest queries sorted by total execution time. The mean_exec_time column shows the average time per call, while total_exec_time shows the cumulative time spent on that query pattern. Focus on queries with high mean_exec_time and high calls — these are the biggest performance gains. The query text is normalized, with literal values replaced by $1, $2 parameters.

1-- Find the 10 slowest queries by average execution time
2select
3  round(mean_exec_time::numeric, 2) as avg_time_ms,
4  calls,
5  round(total_exec_time::numeric, 2) as total_time_ms,
6  rows,
7  query
8from pg_stat_statements
9order by mean_exec_time desc
10limit 10;

Once you identify a slow query, use EXPLAIN ANALYZE to see its execution plan. This shows you exactly how PostgreSQL processes the query: which indexes it uses (or does not use), whether it performs sequential scans on large tables, and where time is spent. The output includes the planned cost, actual execution time, and the number of rows processed at each step. Look for Seq Scan on large tables — this is the most common cause of slow queries.

1-- Run EXPLAIN ANALYZE on a query (uses real data, safe for SELECT)
2explain analyze
3select * from todos
4where user_id = 'some-uuid-here'
5and is_complete = false
6order by created_at desc;

The Supabase Dashboard includes a built-in Performance Advisor under the Database section. It automatically analyzes your query patterns and suggests indexes for the most common slow queries. Navigate to your project Dashboard, click on Database in the left sidebar, and look for the Performance or Advisors section. It shows unused indexes (wasting space), missing indexes (causing slow scans), and tables without RLS (security risk).

Based on the EXPLAIN ANALYZE output and Dashboard recommendations, create indexes on columns used in WHERE clauses, JOIN conditions, and ORDER BY. A btree index (the default) is suitable for equality and range comparisons. For text search, use GIN indexes. For JSON queries, use GIN on jsonb columns. Always test the query performance before and after adding the index by running EXPLAIN ANALYZE again.

1-- Add an index on user_id for the todos table
2create index idx_todos_user_id on public.todos using btree (user_id);
3
4-- Add a composite index for common query patterns
5create index idx_todos_user_complete on public.todos
6  using btree (user_id, is_complete);
7
8-- Verify the index is used
9explain analyze
10select * from todos
11where user_id = 'some-uuid-here'
12and is_complete = false;

After optimizing queries, reset the pg_stat_statements data to get a clean baseline. This lets you measure the impact of your changes and catch new slow queries as your application evolves. Schedule periodic reviews of slow queries as part of your maintenance routine, especially after adding new features that introduce new query patterns.

1-- Reset all statistics (requires superuser or pg_stat_statements_reset permission)
2select pg_stat_statements_reset();
3
4-- After some traffic, check again for new slow queries
5select
6  round(mean_exec_time::numeric, 2) as avg_time_ms,
7  calls,
8  query
9from pg_stat_statements
10where mean_exec_time > 100  -- queries slower than 100ms
11order by mean_exec_time desc
12limit 10;