Speeding up slow queries with indexes

What you see

A query or supabase-js call that used to be fast now takes hundreds of milliseconds or more, and gets slower as the table grows. Sometimes it ends with canceling statement due to statement timeout.

Why it happens

Without a suitable index, Postgres answers a filter by reading every row (a sequential scan). That's fine for a few thousand rows and slow for a few million. An index is a sorted structure over one or more columns that lets Postgres jump straight to the matching rows, and often return them already sorted.

How to fix it

1. Find the slow query. If you know it from your app, translate the supabase-js call into SQL. For example:

   const { data } = await supabase
     .from('bookings')
     .select('id, starts_at, guest_name')
     .eq('venue_id', 42)
     .gte('starts_at', new Date().toISOString())
     .order('starts_at')
     .limit(50)
   

   is roughly:

   select id, starts_at, guest_name
   from public.bookings
   where venue_id = 42 and starts_at >= now()
   order by starts_at
   limit 50;
   

   To find slow queries you don't know about, use pg_stat_statements as described in High CPU usage on your instance.

2. Look at the plan:

   explain analyze
   select id, starts_at, guest_name
   from public.bookings
   where venue_id = 42 and starts_at >= now()
   order by starts_at
   limit 50;
   

   Warning signs: Seq Scan on bookings with a large Rows Removed by Filter, or a Sort step over many rows. Compare the Execution Time before and after each change.

3. Add an index that matches the query. Put equality filters first and the range or sort column last:

   create index concurrently if not exists bookings_venue_starts_idx
     on public.bookings (venue_id, starts_at);
   

   concurrently builds the index without blocking writes. It can't run inside a transaction, so run it as its own statement.

4. Run explain analyze again. You should see Index Scan using bookings_venue_starts_idx and no separate sort. If the planner still picks a sequential scan, refresh its statistics with analyze public.bookings;.

Indexes worth checking

• Columns used by RLS policies. A policy like using ((select auth.uid()) = user_id) runs on every query, so user_id needs an index:

  create index concurrently if not exists bookings_user_id_idx on public.bookings (user_id);
  

• Foreign key columns. Postgres doesn't index them automatically, and joins and cascading deletes need them.

• Partial indexes for queries that always filter the same way, which keeps the index small:

  create index concurrently if not exists bookings_pending_idx
    on public.bookings (starts_at) where status = 'pending';
  

• Text search and JSONB need GIN indexes rather than B-tree. See Full-text search and JSON and JSONB columns.

Don't over-index

Every index slows down writes and takes disk space that counts toward your database quota. Find indexes that are never used:

select relname as table_name, indexrelname as index_name, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) as size
from pg_stat_user_indexes
where idx_scan = 0
order by pg_relation_size(indexrelid) desc;

Drop them with drop index concurrently if they're not backing a primary key or unique constraint.

On SupaHost

• Run these in Studio's SQL editor (Dashboard → your project → Database tab → Open Studio). MCP apply_migration wraps each migration in a transaction, so create index concurrently fails there. Run concurrent builds as a single statement in Studio, or with psql through the SSH tunnel on Business, and keep plain create index in migrations for new, empty tables.
• All projects share the instance's CPU, so fixing one project's slow query can speed up the others too.

Related

• Indexes
• Query performance and EXPLAIN
• Running EXPLAIN ANALYZE on functions
• RLS performance best practices