Why Supabase RLS queries are slow (and how to fix)

TL;DR — Your Supabase RLS queries are slow because the policy predicate is evaluated per row as the table is scanned. Three changes fix most of it:

1. Wrap auth.uid() in a subquery: using ( (select auth.uid()) = user_id ). This turns one JSON parse per row into one per statement.
2. Index the columns your policy filters on: create index on items (user_id);. An unindexed policy column means a sequential scan on every query.
3. Scope the policy to a role: add to authenticated so Postgres skips the policy entirely for anon.

None of these weaken your policy — RLS still enforces access on every row. They only change how the planner evaluates the same predicate. Measure the difference with EXPLAIN ANALYZE, run as the actual request role.

---

Why are my Supabase RLS queries so slow?

Here's the mechanism that explains everything below. An RLS policy is not a gate that runs once before your query. Postgres rewrites the policy's USING expression into an implicit predicate, AND-s it onto your query, and evaluates it as the table is scanned — conceptually once per candidate row, exactly like an extra WHERE clause the planner injected.

So every per-row cost inside a policy — a function call, a JSON parse, a subquery, a join — gets multiplied by the number of rows the scan touches. Supabase says this directly: RLS is "especially" costly "for queries that scan every row in a table — like many select operations, including those using limit, offset, and ordering."

On a 100K-row table that difference is the gap between a query that returns in single-digit milliseconds and one that takes several seconds.

The InitPlan mechanism: why wrapping auth.uid() matters

auth.uid() and auth.jwt() are SQL functions that read the request JWT out of current_setting('request.jwt.claims') and parse JSON from it. Written bare in a policy, the call sits in the scan's filter expression and Postgres re-evaluates it at every row where its value is needed.

Before — auth.uid() re-runs per row:

create policy "select_own" on public.items
for select
to authenticated
using ( auth.uid() = user_id );

After — wrap it in a scalar sub-select:

create policy "select_own" on public.items
for select
to authenticated
using ( (select auth.uid()) = user_id );

Wrapping the call as (select auth.uid()) makes it an uncorrelated subquery — it references nothing from the outer row. Postgres therefore plans it as an InitPlan: a sub-SELECT that, by definition, only needs to run once because it has no dependency on the surrounding query level. It executes once at statement start and its scalar result is substituted as a constant into the scan filter. One JSON parse per statement instead of one per row.

Supabase's own phrasing: wrapping the function "causes an initPlan to be run by the Postgres optimizer, which allows it to 'cache' the results per-statement, rather than calling the function on each row."

Supabase's published tests (100K-row table) show auth.uid() = user_id going from 179 ms to 9 ms after wrapping, and heavier role checks going from 11,000 ms to 7 ms.

Hard constraint: this only works when the wrapped expression does not depend on the current row's columns. auth.uid() and auth.jwt() qualify; a function that must read a per-row column does not and must not be hoisted. For compound conditions, wrap each call:

using ( (select is_admin()) or (select auth.uid()) = user_id );

If you're not sure your policies are even correct before optimizing them, start with the complete guide to Supabase Row Level Security — a fast policy that returns the wrong rows is worse than a slow correct one.

Index the column your policy filters on

The policy predicate ... = user_id is only cheap if user_id is indexed. Without an index, the injected predicate forces a sequential scan that reads and filters every row, on every query, for every user.

create index items_user_id_idx on public.items using btree (user_id);

Supabase's tests show this alone taking a query from 171 ms to under 0.1 ms. Their guidance: "add indexes on any columns used within the Policies which are not already indexed (or primary keys)."

When the policy touches more than one column, use a composite index, equality columns first:

create index items_team_user_idx on public.items using btree (team_id, user_id);

And when the policy only ever cares about a subset of rows, a partial index keeps it small:

create index items_active_user_idx on public.items (user_id)
where deleted_at is null;

Postgres uses a partial index only when it can prove the query's WHERE implies the index predicate, so pair it with a mirrored query filter (below) that includes the same condition.

Scope the policy TO authenticated

A policy created without a TO clause defaults to PUBLIC, so its predicate runs for every role — including anon, whose auth.uid() is null and can never satisfy (select auth.uid()) = user_id. That's guaranteed-wasted work on every row.

create policy "select_own" on public.items
for select
to authenticated                       -- role gate
using ( (select auth.uid()) = user_id );

Naming the role lets Postgres skip the policy entirely for anon — execution stops at the role check. Security is unchanged: anon simply matches no policy and sees nothing. Supabase's tests show an anon request against protected data going from 170 ms to under 0.1 ms. Never rely on auth.uid() alone to rule out anon — gate the role with TO.

Add an explicit query filter that mirrors the policy

RLS restricts what a query returns; it does not restrict what the planner scans. If the client sends a bare select * from items, the only usable predicate is the policy's own. Duplicating the predicate in the query gives the planner an obvious, sargable condition to drive the index:

// Before — relies on RLS alone
const { data } = await supabase.from('items').select();

// After — mirror the policy predicate so the planner uses the index
const { data } = await supabase.from('items').select().eq('user_id', userId);

Supabase's tests show this going from 171 ms to 9 ms. This is a planner hint, not a security control — if the client passes the wrong userId, RLS still blocks the rows. The mirror can only narrow access, never widen it.

Keep the predicate sargable: compare the indexed column directly to a constant or InitPlan value (user_id = (select auth.uid())), never wrap the indexed column in a function like lower(email) = ... or user_id::text = ... — that defeats the index.

Avoid joins inside the policy

If a policy subquery references the outer row (team_user.team_id = team_id, where team_id is the scanned row's column), it's correlated — it can't be hoisted to an InitPlan and instead runs as a per-row SubPlan, often against a second RLS-protected table. Rewrite so the subquery is uncorrelated:

-- Slow: correlated — subquery depends on the row's team_id, runs per row
using (
  (select auth.uid()) in (
    select user_id from team_user where team_user.team_id = team_id
  )
);

-- Fast: uncorrelated — build the user's team set once, test the row against it
using (
  team_id in (
    select team_id from team_user where user_id = (select auth.uid())
  )
);

Supabase's tests show the reordered version going from 9,000 ms to 20 ms. For heavier role or membership checks, push the lookup into a security definer function (which bypasses RLS on the join table) and call it wrapped:

create function private.is_admin()
returns boolean
language plpgsql
security definer
set search_path = ''          -- required: pin search_path
as $$
begin
  return exists (
    select 1 from private.user_roles
    where user_id = (select auth.uid()) and role = 'admin'
  );
end;
$$;

A security definer function must pin search_path and live in a non-exposed schema. The Postgres docs are explicit: set search_path to exclude schemas writable by untrusted users, with pg_temp searched last — use set search_path = '' and fully-qualify every reference, as above. This is why an over-broad or missing policy is such a common failure mode; if your rows aren't scoping to the current user at all, fix the RLS policy that isn't user-scoped before you tune it.

How do I measure RLS query performance?

If you run EXPLAIN ANALYZE as the table owner or postgres, RLS is bypassed and the plan lies — owners are exempt from RLS by default. You must impersonate the request role and supply the JWT claims the policy reads, inside a transaction you roll back:

begin;
  set local role authenticated;
  set local "request.jwt.claims" to
    '{"role":"authenticated","sub":"5950b438-b07c-4012-8190-6ce79e4bd8e5"}';

  explain (analyze, buffers, verbose)
  select count(*) from public.items;
rollback;

set local scopes both settings to the transaction, so rollback discards them cleanly. To test the anon path, use set local role anon and omit the claims. Execution time is the number to compare.

What to look for in the plan:

• InitPlan running the auth function once (fix worked) vs. Filter: (user_id = auth.uid()) re-run per row.
• Index Scan on your policy column vs. Seq Scan with a large Rows Removed by Filter.
• SubPlan with high loops= for a correlated policy subquery vs. a single InitPlan at loops=1.

You can also run it straight from the client via PostgREST's explain() modifier (enable it once with alter role authenticator set pgrst.db_plan_enabled to true; notify pgrst, 'reload config';):

const { data } = await supabase
  .from('items').select('*').eq('user_id', userId)
  .explain({ analyze: true });

And run Supabase Advisors → Performance, which flags auth_rls_initplan (unwrapped auth.*() in a policy), unindexed foreign keys and policy columns, and multiple permissive policies per role+action that all get evaluated.

Does GuardLayer measure RLS performance?

No — and it would be dishonest to claim otherwise. RLS slowness is a runtime, query-plan property. The only tools that can measure it are EXPLAIN ANALYZE (run as the real role, as above) and Supabase's Performance Advisor. A static scanner never sees your query plans, your row counts, or your execution times, so it can't tell you a policy is slow.

What GuardLayer checks is the security side: that RLS is actually enabled on your tables, and that your policies are genuinely user-scoped — not USING (true), not missing entirely. That matters here because a fast policy that returns the wrong rows is no good. Optimizing a broken policy just makes the leak faster.

There's one happy overlap. Wrapping (select auth.uid()) is primarily a performance fix, but it also makes the policy's intent clearer to read and review — so the same change that speeds up the scan tends to make the security review easier too. GuardLayer's job is to confirm the policy is correct and covering; making it fast is on you, EXPLAIN ANALYZE, and the Performance Advisor.

FAQ

Does wrapping auth.uid() change what rows the policy returns? No. (select auth.uid()) returns the same value as auth.uid() for the whole statement — it only changes how often Postgres evaluates it (once vs. per row). Access semantics are identical.

Why is auth.uid() STABLE but still re-evaluated per row? STABLE allows the optimizer to collapse multiple calls to one, particularly in an index-scan condition — but the Postgres docs only say "allows," not "guarantees," for a bare call in a sequential-scan filter. Wrapping it in a subquery removes the ambiguity and forces the once-per-statement InitPlan.

Do I still need indexes if I've wrapped auth.uid()? Yes. Wrapping fixes the function-call cost; the index fixes the scan cost. They're independent wins — Supabase measures each separately (179→9 ms for wrapping, 171→<0.1 ms for the index).

Is adding .eq('user_id', userId) in the client a security risk? No. RLS still enforces access regardless of what the client sends. The explicit filter can only narrow results to a subset the user is already allowed to see — it's a planner hint, not an access control.

Why does EXPLAIN ANALYZE show my query as fast when users report it's slow? You're probably running it as postgres or the table owner, who are exempt from RLS by default — so the plan omits the policy cost entirely. Re-run it inside a transaction with set local role authenticated and the JWT claims, as shown above.