Why is my Supabase query taking forever and how do I add an index to speed it up?

A slow Supabase query almost always means a missing database index. Learn how to spot it and add the right index in a few minutes, no DBA required.

Why Is My Supabase Query So Slow, and How Do I Add an Index?

6 min read

If a Supabase query that felt instant on your laptop now takes seconds in production, the cause is almost always a missing index. Postgres is reading every row in the table to find the ones you asked for, and that gets slower with every user you add. The fix is usually one line of SQL and takes a few minutes.

Why this happens

When you filter or sort on a column (where user_id = ..., order by created_at), Postgres needs a fast way to find the matching rows. An index is that shortcut: a sorted lookup structure, like the index at the back of a book. Without one, Postgres does a "sequential scan," reading the whole table top to bottom. On the 20 rows you had while building, that is invisible. On the 50,000 rows you have after a week of real users, it is a spinner.

Your AI agent wrote the query that worked, and it did work. Nobody asked it to predict which columns you would filter on at scale, so it did not add indexes. The query is correct. It is just unindexed, and that only shows up once there is real data behind it.

How to check

In the Supabase dashboard, open the SQL Editor and run your slow query with explain analyze in front of it:
```
explain analyze
select * from messages where conversation_id = 'abc' order by created_at;
```
Read the output. If you see Seq Scan on messages and a large rows removed by filter number, Postgres is reading the whole table. That is your missing index. If you see Index Scan, that query is already fine.
Note the Execution Time at the bottom. Write it down so you can compare after adding the index.
Repeat for the queries behind your slowest pages. The usual culprits are the columns in your where and order by clauses.

The fix

Index the column you filter on. In the SQL Editor:
```
create index concurrently if not exists messages_conversation_id_idx
  on messages (conversation_id);
```
concurrently lets the index build without locking the table, so live users are not blocked while it runs.
If you filter and sort together, index both columns in one index, in that order:
```
create index concurrently if not exists messages_conv_created_idx
  on messages (conversation_id, created_at);
```
This serves where conversation_id = ... order by created_at in a single step.
Re-run explain analyze. You should now see Index Scan instead of Seq Scan, and the execution time should drop from hundreds or thousands of milliseconds to single digits.
Do this for your foreign keys. Any column holding another table's id (user_id, project_id, order_id) is almost always worth an index, because you will filter and join on it constantly.

The trap to avoid

Do not index every column "to be safe." Each index makes writes (inserts and updates) a little slower and takes up space, and an unused index just costs you without helping. Add indexes for the columns you actually filter, sort, or join on, confirmed by explain analyze, not on a hunch. And resist "fixing" slowness by fetching everything into your app and filtering in JavaScript. That moves the whole table over the network and is far slower than letting an indexed Postgres do its job.

Where this fits

A missing index is invisible in every demo and obvious the moment real data arrives, which is exactly when your first beta users show up. It often travels with two siblings: a page firing hundreds of separate queries and a list loading thousands of rows at once. The free Readiness Report runs against your real project and flags the queries that will crawl under load before your users feel them. If you would rather have the indexes and query fixes handed to you in order, that is what the Finishing Pass is for.