# Speeding up slow queries with indexes

> Find slow queries, read EXPLAIN ANALYZE, and add the right B-tree, composite or partial index without locking your table. Worked example with a bookings app on SupaHost.

Source: https://supahost.app/kb/improve-query-performance · Updated 2026-11-11

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

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

   ```sql
   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](/kb/high-cpu).

2. **Look at the plan:**

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

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

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

  ```sql
  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](/docs/database/full-text-search) and [JSON and JSONB columns](/docs/database/json).

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

```sql
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](/docs/database/indexes)
- [Query performance and EXPLAIN](/docs/database/query-performance)
- [Running EXPLAIN ANALYZE on functions](/kb/explain-analyze-functions)
- [RLS performance best practices](/kb/rls-performance)