Skip to content
← All guides

Production readiness

Slow Supabase queries in a growing app: finding the missing indexes

By Jan Lipmann, appienceChecked against sources on 11 min read

When a table is small, Postgres can read every row on every request and nobody notices. As the table grows, the same query reads more and more rows, and screens that were instant start to crawl or time out. Below: where Supabase shows your slowest queries, the SQL that finds tables read row by row and foreign keys without an index, and how to add the right indexes safely on a live app.

SupabasePostgreSQL

What you are seeing

Missing indexes do not show up on launch day. The app was fast with test data and a handful of users, and nothing in the code changed. Signs that the database has started reading whole tables:

  • A list, dashboard or search screen that used to load in a second now takes several, and it gets slower week by week as data grows.
  • Requests fail with canceling statement due to statement timeout (Postgres error code 57014). On the date of checking (2026-11-21), Supabase's docs listed default timeouts of 3 seconds for the anon role and 8 seconds for authenticated (Timeouts) - so a query that got slower over time eventually stops returning at all.
  • The Performance Advisor in the Supabase dashboard reports "Unindexed foreign keys" (lint 0001) or "Slow security policy detected" (lint 0003) (Database Advisors).
  • Your oldest and heaviest users complain first. Their lists are the longest, so a query that reads all of their rows suffers most.
  • The app is slow only when signed in, while the same query in the SQL Editor is fast. The SQL Editor runs as postgres, which bypasses Row Level Security on Supabase (Row Level Security), so it skips the policy check your users go through.

How to check it yourself

Do this on your own project only. Every step below only reads: nothing changes in your data or your schema. The SQL runs in the Supabase dashboard under SQL Editor. The examples use a table called orders with a user_id column; use your own table and column names.

1. Open the Performance Advisor

In the dashboard, go to Advisors > Performance Advisor (Database Advisors). The findings that point to missing or wasted indexes:

  • 0001_unindexed_foreign_keys - a column that points to another table has no index, so joins and filters on it read the whole table (lint 0001).
  • 0003_auth_rls_initplan - a policy calls auth.uid() (or auth.jwt()) in a way that runs it once for every row instead of once per query (lint 0003).
  • 0005_unused_index and 0009_duplicate_index - indexes that cost write time and storage but help no query (lint 0005, lint 0009). Not the cause of a slow screen, but worth knowing before you add more.

2. Find the queries that take the most time

Supabase's Query Performance report (under Observability in the dashboard) lists the queries your database spends the most time on. Select a query and open the Indexes tab: it uses the index_advisor extension to suggest an index and show the estimated cost before and after (index_advisor). The same data is available in SQL from pg_stat_statements (pg_stat_statements):

select round(total_exec_time::numeric / 1000, 1) as total_seconds,
       calls,
       round(mean_exec_time::numeric, 1) as avg_ms,
       round(max_exec_time::numeric, 1) as max_ms,
       rows,
       left(query, 300) as query
from pg_stat_statements
where query not ilike '%pg_stat_statements%'
order by total_exec_time desc
limit 20;
Start at the top: a query that takes 40 ms but runs 100,000 times costs more than one that takes 2 seconds once. If you get relation "pg_stat_statements" does not exist, enable the extension under Database > Extensions (Extensions). The numbers add up from the last statistics reset, not for a fixed period (Inspect the database).

Queries your app sends through supabase-js are built by Supabase's Data API, so their text looks longer than the code you wrote. Look for your table and column names in them. With the Supabase CLI, supabase inspect db outliers --remote shows the same ranking for the linked project (CLI reference).

3. Find tables that are read row by row

select relname as table_name,
       n_live_tup as approx_rows,
       seq_scan as full_table_scans,
       seq_tup_read as rows_read_by_full_scans,
       coalesce(idx_scan, 0) as index_scans
from pg_stat_user_tables
where schemaname = 'public'
order by seq_tup_read desc
limit 20;
A full table scan ("sequential scan") reads every row. Many full scans on a small table are fine. A table with tens of thousands of rows at the top of this list, with few index scans, is where an index is likely missing (PostgreSQL: Cumulative Statistics).

4. Find foreign keys without an index

This is the same check as Performance Advisor lint 0001, written out so you can see the column names. It follows the logic of Supabase's own lint (lint source): a foreign key counts as indexed when a valid index starts with its columns.

with foreign_keys as (
  select c.conrelid,
         c.conrelid::regclass as table_name,
         c.conname as foreign_key,
         c.conkey,
         (select string_agg(a.attname, ', ' order by k.ord)
            from unnest(c.conkey) with ordinality as k(attnum, ord)
            join pg_attribute a on a.attrelid = c.conrelid and a.attnum = k.attnum) as columns
  from pg_constraint c
  join pg_namespace n on n.oid = c.connamespace
  where c.contype = 'f'
    and n.nspname = 'public'
)
select fk.table_name, fk.foreign_key, fk.columns
from foreign_keys fk
where not exists (
  select 1
  from pg_index i
  where i.indrelid = fk.conrelid
    and i.indisvalid
    and (string_to_array(i.indkey::text, ' ')::smallint[])[1:array_length(fk.conkey, 1)] = fk.conkey
)
order by fk.table_name::text, fk.foreign_key;
Every row is a column like orders.user_id or messages.chat_id that your app almost certainly filters or joins on, with no index behind it.

5. Check the columns your policies filter on

A Row Level Security policy is an extra condition on every query, checked against each row the query reads (Row Level Security performance). The column it compares (usually user_id, owner_id or organisation_id) needs an index too. List your existing indexes and compare them with your policies:

select tablename, indexname, indexdef
from pg_indexes
where schemaname = 'public'
order by tablename, indexname;

select tablename, policyname, cmd, qual, with_check
from pg_policies
where schemaname = 'public'
  and concat_ws(' ', qual, with_check) ~ 'auth\.(uid|jwt|role|email)\(\)'
  and concat_ws(' ', qual, with_check) !~* 'select auth\.(uid|jwt|role|email)\(\)'
order by tablename, policyname;
The first query lists every index. A column counts as indexed only if it is the first column of a btree index: a composite primary key (team_id, user_id) does not index user_id (Add indexes). The second lists policies that call auth.uid() without wrapping it in select - the pattern lint 0003 flags. A policy that uses both forms can slip past this filter, so read the qual column of your busiest tables yourself.

6. See how Postgres runs one slow query, as a real user

explain shows the plan Postgres picks; explain analyze also runs the query and reports the real time (PostgreSQL: EXPLAIN). To see what a signed-in user's request costs, including the policy, switch to the authenticated role with a test user's id, as Supabase's own RLS performance guide does (Row Level Security performance). Take a user id from Authentication > Users and rewrite the query to match one your app runs:

begin;
set local role authenticated;
set local request.jwt.claims = '{"role": "authenticated", "sub": "TEST-USER-UUID"}';

explain (analyze, buffers)
select id, status, total, created_at
from public.orders
where user_id = 'TEST-USER-UUID'
order by created_at desc
limit 20;

rollback;
explain analyze really executes the statement. That is harmless for a select; never run it on an insert, update or delete outside a transaction you roll back (PostgreSQL: EXPLAIN). SQL Editor may show only the result of the last statement: if you see "Success. No rows returned", select the lines from begin; through the explain statement, run only that selection, then run rollback;.

What to look for in the output:

  • Seq Scan on orders with a large Rows Removed by Filter: Postgres read the whole table to find a few rows. Supabase's guide calls Rows Removed by Filter the signal to watch (Row Level Security performance).
  • A Sort step above the scan: Postgres had to sort every matching row to return the first 20. An index in the same order can return them directly (PostgreSQL: Indexes and ORDER BY).
  • Index Scan using ... or Index Only Scan: an index is already being used. If this query is still slow, the problem is elsewhere (too many rows returned, a heavy join, or a slow function).
  • Execution Time at the bottom: the number to compare before and after a fix.

To get the plan of a request made through supabase-js instead, Supabase offers .explain() on queries. It is disabled by default because a plan reveals your database structure; Supabase's docs describe how to enable it and how to restrict it to your own IP address on a production project (Query plans with explain()).

Why it happens

An index works like the index at the back of a book: instead of reading every page to find a word, Postgres looks it up and jumps straight to the rows it needs. Without one, the only way to answer where user_id = ... is to read every row and throw away the ones that do not match. With 500 rows, that takes no time. With 500,000, the same query reads a thousand times more data, on every request, for every user.

  • Postgres creates indexes automatically for primary keys and unique constraints (PostgreSQL: Constraints). That covers lookups by id and little else.
  • It does not create an index for a foreign key. Postgres's docs say so directly: declaring a foreign key "does not automatically create an index on the referencing columns" (PostgreSQL: Constraints). A schema generated by an AI tool can have user_id uuid references auth.users on every table and no index on any of them, and the app still works - only more slowly as data grows.
  • Row Level Security adds a filter to every query. Postgres checks the policy against each row the query scans, so the cost grows with the rows read (Row Level Security performance). A policy on an unindexed user_id turns every read into a full table scan, even when the app asks for one row.
  • A policy written as auth.uid() = user_id can call auth.uid() once per row. Writing it as (select auth.uid()) = user_id lets Postgres call it once per query and reuse the result (lint 0003).
  • Queries with no filter of their own. A screen that runs supabase.from("orders").select() and relies on the policy to narrow it down gives Postgres less to plan with. Supabase recommends adding the same filter in the client query, even though it repeats the policy (Row Level Security performance).
  • Lists sorted by date. order by created_at desc limit 20 with no matching index means Postgres collects all matching rows and sorts them before returning 20 (PostgreSQL: Indexes and ORDER BY).
  • Search boxes using ilike '%word%'. A regular btree index cannot help with a pattern that starts with %; the pg_trgm extension's GIN and GiST indexes support LIKE and ILIKE searches (PostgreSQL: pg_trgm).

One more reason this hides until later: Postgres may ignore an index on a small table, because reading a few pages from start to end is faster than using an index (Managing indexes). An index you add during development may look useless in a plan and start working only once the table grows.

How to fix it

Warning: A plain create index blocks inserts, updates and deletes on the table until the index is built (PostgreSQL: CREATE INDEX). On a live app, use create index concurrently as shown below. Try each change on a Supabase branch or a copy of the project first, and keep the final SQL in a migration (step 6).

1. Index every foreign key the app filters or joins on

For each row from check 4, create one index on the foreign key column. concurrently builds the index without blocking writes; it takes longer and does more work in total, and it cannot run inside a transaction block (PostgreSQL: CREATE INDEX).

create index concurrently if not exists orders_user_id_idx
on public.orders using btree (user_id);
Run each create index concurrently on its own, one statement per run. If you get CREATE INDEX CONCURRENTLY cannot run inside a transaction block, the tool wrapped it in a transaction: run it from psql over a direct connection (Direct connection), or, for a small table at a quiet time, leave out concurrently.

On a large table the build can take minutes. On the date of checking (2026-11-21), Supabase's docs listed a 2-minute default cap for the postgres role (Timeouts). If the build is cancelled, connect with psql over a direct connection, raise the limit for that session only with set statement_timeout = '10min';, and run the create index concurrently again. Supabase's docs note that the session setting does not work in the dashboard (Timeouts).

A failed concurrent build leaves an "invalid" index behind: Postgres ignores it for queries but still updates it on every write. Postgres recommends dropping it and building it again (PostgreSQL: CREATE INDEX). After each build, check:

select indexrelid::regclass as index_name,
       indrelid::regclass as table_name
from pg_index
where not indisvalid;

-- For each index listed above:
drop index concurrently if exists public.orders_user_id_idx;
-- then run the create index concurrently statement again.
An empty first result means every index finished building. Run the drop index concurrently line only for an index the first query lists.

2. Match the index to the query: filter first, then sort

Most screens in an app are a list: "my orders, newest first, 20 per page". One index with the filter column first and the sort column second answers the whole query without reading or sorting the rest. A multi-column index is most efficient when the query filters on its leading (leftmost) columns (PostgreSQL: Multicolumn Indexes), and an index in the same order as order by ... limit lets Postgres return the first rows without scanning the remainder (PostgreSQL: Indexes and ORDER BY).

create index concurrently if not exists orders_user_id_created_at_idx
on public.orders using btree (user_id, created_at desc);
This one index serves where user_id = ... alone as well, because user_id comes first, and it satisfies the foreign key check from step 1. If you already built orders_user_id_idx, the Performance Advisor may later report it as unused; see step 7.

If a screen always shows a small subset, such as unpaid orders, a partial index covers only those rows and stays small (Managing indexes). Postgres uses it only when the query has the same condition:

create index concurrently if not exists orders_pending_created_at_idx
on public.orders using btree (created_at)
where status = 'pending';

Not sure which index a query needs? Paste it into index_advisor, which suggests single-column btree indexes and shows the estimated cost before and after (index_advisor):

create extension if not exists index_advisor cascade;

select index_statements, total_cost_before, total_cost_after, errors
from index_advisor('select id, total from public.orders where user_id = $1::uuid order by created_at desc limit 20');
Write values as $1, $2 with an explicit type ($1::uuid); the advisor reports an error when it cannot work out a parameter's type (index_advisor). It only suggests single-column indexes, so for a filter plus a sort, the two-column index above may still be the better choice. Supabase also notes that a recommended index may not be used once created (Managing indexes) - check with explain (step 8).

3. Make policies cheap: wrap auth.uid() in select

For each policy from check 5, rewrite the condition so auth.uid() is called once per query. The result is the same; only the cost changes (lint 0003). alter policy replaces the condition in place, so the policy never disappears in between (PostgreSQL: ALTER POLICY):

alter policy "Users can read their own orders"
on public.orders
using ( (select auth.uid()) = user_id );
Use the exact policy name from pg_policies and keep the rest of the condition as it is. For insert and update policies, change the with check ( ... ) part the same way. Make sure the column the policy compares (user_id here) has an index from step 1.

While you are there, check that each policy names its role (to authenticated), so it does not run for signed-out requests at all (Specify roles in your policies). Policies that look up a team or organisation membership have their own performance patterns; see our multi-tenant RLS guide.

4. Filter in the app's queries, not only in the policy

Repeat the policy's condition in the query and ask only for the rows and columns the screen shows. Supabase recommends the extra filter because Postgres can use it to build a better plan (Row Level Security performance):

const {
  data: { user },
} = await supabase.auth.getUser();

if (!user) {
  throw new Error("Not signed in");
}

const { data: orders, error } = await supabase
  .from("orders")
  .select("id, status, total, created_at")
  .eq("user_id", user.id)
  .order("created_at", { ascending: false })
  .limit(20);
The filter is for speed, not security: anyone can send a query without it, so the policy must still decide what each user may read.

5. Refresh the planner's statistics after big imports

Postgres chooses between a full scan and an index using statistics about each table. Autovacuum normally keeps them up to date (PostgreSQL: ANALYZE), and Supabase suggests running analyze periodically (Query optimization). After importing a large batch of rows, run it yourself so the next plans are based on the new size:

analyze public.orders;

6. Put the indexes into a migration

Indexes created by hand in the SQL Editor exist only in that project. If your app is rebuilt from migrations (a staging copy, a branch, a new region), they disappear. Create a migration file with the Supabase CLI (CLI reference):

supabase migration new add_missing_indexes
create index if not exists orders_user_id_created_at_idx
on public.orders using btree (user_id, created_at desc);

create index if not exists orders_pending_created_at_idx
on public.orders using btree (created_at)
where status = 'pending';
Without concurrently, because migration tools may run each file inside a transaction. On production, where you already built the indexes concurrently by hand, if not exists makes these lines do nothing. Use the same index names in both places.

7. Do not overdo it

Every index makes inserts, updates and deletes a little slower (Query optimization) and takes disk space. Add indexes for the queries the app actually runs, not for every column. A few weeks after the change, check the Performance Advisor for "Unused index" (lint 0005) and duplicate indexes (lint 0009). Before dropping one, Supabase advises confirming it is really unused, thinking about upcoming features, and testing the removal in staging first (lint 0005).

8. Verify

Run check 6 again for the same query and user. The plan should now show Index Scan using orders_user_id_created_at_idx (or your index's name) instead of Seq Scan, no separate Sort step, and a much lower Execution Time. Run check 4 again: it should return no rows for the tables you fixed. Then open the screen in the app while signed in as a user with a lot of data, and watch the query in the Query Performance report over the following days: its average time should drop and stay down.

When to get help

The steps above fix the most common kind of slow query: a filter or a sort on a column with no index. Get a second pair of eyes when:

  • The plan already shows an index scan, and the query is still slow. The cause is then something else: a heavy join, a function that runs per row, a policy that queries another table, or a screen that loads far more rows than it shows.
  • Your slowest queries come from database functions, triggers or views you did not write yourself, and you cannot tell what they do.
  • The tables are large enough that you are unsure an index build will finish, or a concurrent build keeps failing.
  • Search, reporting or filtering across many columns needs indexes other than btree (trigram, full-text, JSONB) and you need to know which.
  • The app is slow even though no single query stands out, and you suspect the number of requests per screen, polling or connections.
  • You are about to pay for a larger compute size and want to know first whether the queries, not the hardware, are the problem.

Sources

Primary documentation this guide was checked against. Platforms change - if something here no longer matches, the source wins.

Frequently asked questions

Why did my Supabase app get slow when nothing in the code changed?
Because the data changed. A query that reads every row of a table takes longer as the table grows, so a missing index becomes noticeable only after the app has been used for a while. Postgres does not index foreign key columns automatically, and an app can run for months without one.
Will a bigger compute size fix slow queries?
It can make every query somewhat faster, but a query that reads the whole table still reads the whole table, and it keeps slowing down as the table grows. Check the plan first: if it shows Seq Scan with a large Rows Removed by Filter, an index is the fix.
Is it safe to create an index on a live production table?
Use create index concurrently. A plain create index blocks writes to the table until it finishes; the concurrent version does not, but it takes longer and cannot run inside a transaction. If it fails, it leaves an invalid index that you drop and build again.
Should I add an index to every column?
No. Every index slows down inserts, updates and deletes and takes disk space. Index the columns your app filters, joins and sorts on, starting with the queries at the top of the Query Performance report, and check the Performance Advisor later for indexes nothing uses.
I created an index, but explain still shows Seq Scan. Why?
On a small table Postgres may decide that reading the whole table is faster, and switch to the index as the table grows. Also check that the query filters on the index's first column, that the build finished (no invalid index), and run analyze on the table if you recently imported a lot of data.
Why is the query fast in the SQL Editor and slow in the app?
The SQL Editor runs as postgres, which bypasses Row Level Security on Supabase, so it skips the policy check. Your users' requests run as authenticated with the policy added to every query. Test with set local role authenticated and a user id inside a transaction, as in check 6, to see the plan your users get.

Related guides

Fixed-price audit

Want someone to check the whole app, not just this?

A review of data security, production readiness and App Store readiness for apps built with AI tools. 900 USD, a ranked list of fixes within 5 business days.