# Supabase RLS performance optimization: measure slow policies with EXPLAIN, and make them 100× faster with (select) wrapping, indexes, TO, and JWT

> Supabase (PostgreSQL) row-level security (RLS) gets slow even written correctly. Fold per-row evaluation into an InitPlan with the (select) wrap of auth.uid(), index the policy columns, narrow roles with TO, rewrite JOINs into set references, and erase lookups with security definer functions and JWT claims — 6 optimizations compliant with the official benchmarks, explained with real code and an EXPLAIN ANALYZE measurement procedure.

- Published: 2026-06-28
- Author: 友田 陽大
- Tags: Supabase, RLS, PostgreSQL, パフォーマンス, B2B SaaS
- URL: https://tomodahinata.com/en/blog/supabase-rls-performance-optimization-select-wrap-index-guide
- Category: Databases & RLS
- Pillar guide: https://tomodahinata.com/en/blog/supabase-production-guide-nextjs-rls-realtime-edge-functions

## Key points

- An RLS policy is an implicit WHERE added to every query, so a slow predicate hits all rows. Optimize not by guessing but by identifying the heavy predicate with EXPLAIN ANALYZE, then apply in order of effectiveness.
- The biggest move is wrapping auth.uid() with (select auth.uid()). An uncorrelated scalar subquery is evaluated once as an InitPlan, erasing the per-row call (official bench 179ms→9ms).
- Index the columns the policy references (user_id, tenant_id), and narrow roles with TO authenticated. The full-table scan without an index and the policy evaluation that even runs for anon disappear.
- Rewrite JOINs in the policy into set references (team_id in (select ...)), and offload complex authorization to a security definer function in the private schema, wrapping it with (select). Put permissions in the JWT's app_metadata and the lookup itself disappears.
- Figures are Supabase's official synthetic bench and environment-dependent. After optimizing, always re-verify with pgTAP that allow/deny isn't broken — a fast but leaky RLS is the worst regression.

---

First, let me state the most important thing outright. **An RLS (row-level security) policy is an "implicit `WHERE` clause" automatically added to every query against the table** ([Supabase: Row Level Security](https://supabase.com/docs/guides/database/postgres/row-level-security)). So if the predicate (the contents of `USING` / `WITH CHECK`) is slow, that slowness **rides equally on every query and every row that touches that table.** The way one policy is written can decide the whole app's latency — this is why RLS performance can't be taken lightly.

And the tricky part is that **RLS gets slow even written "correctly."** The seemingly innocuous predicate `auth.uid() = user_id` suddenly becomes 100× slower in production once row count grows. The cause isn't a mistake in the authorization logic but **a query-plan problem: PostgreSQL evaluates that predicate "per row."**

This article explains the **6 RLS performance optimizations** Supabase (PostgreSQL) officially presents, with query-plan-level reasons for "why it works" and a procedure to **measure in your own environment with `EXPLAIN ANALYZE`.** The material is my single-handedly-built [real-time match-recording app with simultaneous multi-user editing](/case-studies/realtime-sports-scoring-app) (an Expo + Next.js + Supabase monorepo), where I **enable RLS on all 69 tables and operate about 280 policies** in production. At this scale of policies, the difference of one way of writing directly ties to perceived speed.

> **This article's position**: **how to design** RLS (multi-tenant separation, `tenant_id`, membership) is in the [Supabase RLS production-design guide](/blog/supabase-rls-production-multi-tenancy-patterns), the **correctness** of `USING` and `WITH CHECK` is in the [write-bypass article](/blog/supabase-rls-with-check-using-write-bypass-guide), and the **tests** that stop regressions are in the [pgTAP article](/blog/supabase-rls-testing-pgtap-policy-regression-guide). This article doesn't overlap with those and concentrates only on **"how to make correctly-written RLS fast."** It's a performance talk on the premise that design, correctness, and tests are in place.

> **honesty contract**: the benchmark figures in the text (179ms→9ms, etc.) are **measured values on the synthetic tables Supabase's official documentation presents.** They likely **won't reproduce** with your row counts, distribution, hardware, and index situation. Read the figures as a guide to "the order (digits) of effect" and always **measure with your own data** using the `EXPLAIN ANALYZE` below. The specs are based on [Supabase official](https://supabase.com/docs/guides/database/postgres/row-level-security) and [PostgreSQL official](https://www.postgresql.org/docs/current/ddl-rowsecurity.html) (both as of June 2026).

---

## 0. Why RLS gets slow: in 3 lines

Before optimizing, decompose the slowness into three. Grasp this and the 6 measures become visible not as "scattered tricks" but as "prescriptions for three diseases."

| The true identity of slowness | What's happening | Effective measure |
| --- | --- | --- |
| **per-row function evaluation** | `auth.uid()`, etc., is called per row | §2 `(select)` wrap |
| **absence of an index** | the policy predicate induces a full scan | §3 index, §5 JOIN reduction, §6 functionize |
| **wasteful policy evaluation** | the predicate even runs for unrelated roles | §4 `TO` specification, §7 JWT |

The PostgreSQL official defines the evaluation order of policy expressions like this — **"this expression is evaluated for each row *before* conditions or functions from the user's query"** ([PostgreSQL: Row Security Policies](https://www.postgresql.org/docs/current/ddl-rowsecurity.html)). That is, the RLS predicate runs on **the innermost hot path.** So if this is slow, no other optimization works. The exception is only `leakproof` (guaranteed not to leak information) functions, in which case the optimizer can apply the user condition before the RLS check.

The point is the order. **First measure (§1), then in order of effect (§2→§7).** Erecting indexes by guesswork is a bad move, in RLS as in ordinary query tuning.

---

## 1. First measure: EXPLAIN ANALYZE and pg_stat_statements

Before saying "RLS is slow," grasp **which policy's which predicate is how many ms** in numbers. Since RLS works transparently, run a normal `EXPLAIN ANALYZE` and the policy **appears in the execution plan as a filter condition.**

### 1-1. Measure by impersonating an authenticated user

RLS results change by "who runs it." Measuring with `service_role` (RLS bypass) is meaningless. **Impersonate the `authenticated` role and put `sub` (the user ID) in `request.jwt.claims`** before measuring.

```sql
-- セッションを「特定の認証済みユーザー」に偽装して計測する
begin;
  -- このトランザクションだけ authenticated ロールで実行する
  set local role authenticated;
  -- auth.uid() が返すユーザーIDを注入する（本番のJWTと同じ経路）
  set local request.jwt.claims = '{"sub":"00000000-0000-0000-0000-000000000001","role":"authenticated"}';

  explain (analyze, buffers, verbose)
  select * from public.matches where game_id = '...';
rollback; -- 計測は副作用を残さない（冪等）
```

Closing with `rollback` is **so the measurement doesn't change state no matter how many times it runs** (idempotency). A line like `Filter: (matches.user_id = auth.uid())` appears in the plan, and looking at the `Rows Removed by Filter` below it and the actual time tells you how much work the policy is doing.

### 1-2. Spot "per-row evaluation" in the execution plan

Grasp, in plan terms, what changes before and after optimization.

- **The slow form**: the predicate is applied to all rows as a `Filter`, and `auth.uid()` is called per row. `Seq Scan` + a large `Rows Removed by Filter`.
- **The fast form**: `(select auth.uid())` appears once at the head of the plan as `InitPlan 1`, and the body can be **fetched by index** like `Index Cond: (user_id = $0)`.

So the goal is clear — **move the predicate from `Filter` (per row) to `Index Cond` (one index lookup).** The 6 below are all means to that.

### 1-3. In production, find "the slowness that matters" with pg_stat_statements

A single `EXPLAIN` is for hypothesis verification. In production, find "which query's cumulative cost is large" with `pg_stat_statements` (Supabase enables it by default).

```sql
select calls, mean_exec_time, total_exec_time, query
from pg_stat_statements
order by total_exec_time desc
limit 20;
```

The query whose `mean_exec_time × calls = total_exec_time` is large is the target with the highest optimization ROI. **Fix "a somewhat-slow query called a lot" before "a query that's slow once"** — this is a tuning iron rule beyond RLS.

---

## 2. The strongest move: wrap `auth.uid()` with `(select auth.uid())`

This has the biggest effect and the lowest cost. Just **wrap the function call in the policy with an uncorrelated scalar subquery `(select ...)`.**

```sql
-- ❌ 遅い：auth.uid() が行ごとに評価される
create policy "rls_select" on public.test_table
  for select to authenticated
  using ( auth.uid() = user_id );

-- ✅ 速い：(select auth.uid()) は一度だけ評価され、InitPlanに畳まれる
create policy "rls_select" on public.test_table
  for select to authenticated
  using ( (select auth.uid()) = user_id );
```

In the official benchmark, this one-character-level difference is **179ms → 9ms (about 95% improvement).** In an example with a heavy function like `is_admin()`, **11,000ms → 7ms**; in an example involving a security definer function, a difference of orders of magnitude is reported: **178,000ms → 12ms** ([Supabase: RLS Performance](https://supabase.com/docs/guides/database/postgres/row-level-security)).

### Why it works: InitPlan, "constant folding"

`auth.uid()` is, in substance, a function that reads the user ID from `current_setting('request.jwt.claims')`. Its value is **the same throughout one query execution** — it doesn't change per row.

Yet PostgreSQL, if `auth.uid()` is written bare in the policy predicate, dutifully re-calls it for each row. **Wrap it in a subquery as `(select auth.uid())`** and PostgreSQL recognizes it as an expression uncorrelated with the outer rows (an uncorrelated subquery) and **evaluates it once at the start of execution as an `InitPlan`, reusing the result like a constant.** In the plan it appears at the head as `InitPlan 1 (returns $0)`, and the body just references `$0` (the evaluated constant).

This is less "caching" than **constant folding.** So as a side effect, if `user_id` has an index, `user_id = $0` can be **fetched by index**, drawing out §3's effect too.

> **Applicability condition (important)**: this optimization is correct only when **the result of the wrapped expression is the same for all rows.** `auth.uid()`, `auth.jwt()`, and functions depending on the current user are OK. Conversely, **don't wrap an expression containing a value that depends on the row, like `row.column`, with `(select ...)`** — the meaning changes. Remember to fold only "values constant per user."

---

## 3. Index the columns the policy references

An RLS policy is an implicit `WHERE`. Then, **just like an ordinary query, the columns used for narrowing need an index.** If you narrow by `user_id = (select auth.uid())`, index `user_id`.

```sql
create index if not exists idx_test_table_user_id
  on public.test_table using btree (user_id);
```

In the official bench, **171ms → under 0.1ms (about 99.9% improvement).** With the predicate in a form that can ride on `Index Cond` (per §2) plus an index, it can be **fetched in one index lookup**, so it becomes dramatically faster.

Index-design guidance in practice:

- **Single-tenant ownership**: a btree single-column index on `user_id`.
- **Multi-tenant**: `tenant_id` becomes the common predicate of all policies, so place `tenant_id` at the **head of a composite index** (e.g., `(tenant_id, created_at)`). The query's `order by created_at` can be served by the index too.
- **membership table** (used heavily in §5): a composite index `(user_id, team_id)` and a `(team_id)` index for reverse lookup.

> Indexes aren't omnipotent. An index on a low-cardinality column with many writes (e.g., a boolean like `is_active`) can be not worth the update cost. **Always confirm "did it change to an `Index Scan`" with §1's `EXPLAIN ANALYZE`** before keeping it. The details of PostgreSQL core index design are left to the 'PostgreSQL core / performance design' cluster.

---

## 4. Narrow roles with `TO` and erase wasteful policy evaluation

Without writing `TO authenticated` on a policy, that policy is **evaluated for all roles including `anon` (unauthenticated).** Even an unauthenticated user's request runs the `auth.uid()` comparison, wastefully slowing it.

```sql
-- ❌ TO なし：anon のリクエストでもこの述語が評価される
create policy "rls_select" on public.rls_test
  using ( (select auth.uid()) = user_id );

-- ✅ TO authenticated：認証済みにだけ評価され、anon は即スキップ
create policy "rls_select" on public.rls_test
  for select to authenticated
  using ( (select auth.uid()) = user_id );
```

In the official bench, in the case where `anon` accesses, **170ms → under 0.1ms (about 99.8% improvement).** Since this policy doesn't apply to `anon` from the start, the predicate evaluation itself disappears.

This is a good case where **performance and correctness coincide.** As an RLS design principle, **making "who it runs as (`anon` / `authenticated` / `service_role`)" explicit on all policies** is the pattern recommended in the [production-design guide](/blog/supabase-rls-production-multi-tenancy-patterns) too. The habit of writing `TO` works for both speed and safety.

---

## 5. Rewrite JOINs in the policy into "set references"

Write complex authorization — "is this user a member of the team this row belongs to?" — as a **table join (JOIN)** in the policy, and that join runs **per row of the target table**, becoming sharply slow.

```sql
-- ❌ 遅い：source(対象表) と target(team_user) を結合している
create policy "rls_select" on public.test_table
  for select to authenticated
  using (
    (select auth.uid()) in (
      select user_id from team_user
      where team_user.team_id = test_table.team_id  -- 外側の行に相関＝行ごとに実行
    )
  );

-- ✅ 速い：先に「自分が所属するteam_idの集合」を作り、行はそれに含まれるか確認するだけ
create policy "rls_select" on public.test_table
  for select to authenticated
  using (
    team_id in (
      select team_id from team_user
      where user_id = (select auth.uid())  -- 外側に相関しない＝一度だけ実行
    )
  );
```

In the official bench, **9,000ms → 20ms (about 99.8% improvement).**

### Why it works: cut the correlation

The first form, because the subquery references `test_table.team_id`, is **correlated with the outer rows** and re-fetches `team_user` per row (N lookups).

The latter, because the subquery depends only on `(select auth.uid())`, is **uncorrelated with the outside and executed once**, building "the set of team IDs I belong to." After that it just checks with `in` whether each row's `team_id` is in that set. `O(N joins)` changes to `O(1 set construction + N membership checks)`. **Not "JOIN the target table with the authorization table" but "fetch the authorization data as a set first"** — this is the most important rewrite pattern in RLS.

---

## 6. Offload complex authorization to a `security definer` function

Multi-stage authorization that even `in (select ...)` can't fully express (roles, time-limited permissions, item-level disclosure, etc.) is confined to a **`security definer` function.** This function runs not with the caller's privileges but with **the function owner's privileges and internally bypasses RLS**, so the policy's predicate evaluation completes there.

```sql
-- 認可ロジックを private スキーマ（API非公開）の関数に閉じ込める
create or replace function private.has_team_access(target_team_id uuid)
returns boolean
language sql
security definer
set search_path = ''            -- search_path注入を塞ぐ（必須）
stable                          -- 同一トランザクション内で結果が変わらないと明示
as $$
  select exists (
    select 1 from public.team_user
    where user_id = (select auth.uid())
      and team_id = target_team_id
  );
$$;

revoke execute on function private.has_team_access(uuid) from anon, authenticated;
grant  execute on function private.has_team_access(uuid) to authenticated;

-- ポリシーからは (select ...) で包んで呼ぶ（§2の最適化を効かせる）
create policy "rls_select" on public.test_table
  for select to authenticated
  using ( (select private.has_team_access(team_id)) );
```

In the official bench, an extreme improvement of **178,000ms → 12ms** is reported for the case of wrapping a security definer function with `(select ...)`.

But `security definer` is also **a blade that bypasses RLS.** Always keep these 3.

1. **Don't put it in a public schema.** As the Supabase official explicitly says, you must not create a security definer function in an "Exposed schema (a schema published in the API settings)." Put it in a non-public schema like `private`.
2. **Always add `set search_path = ''`** and fully-qualify the objects inside the function like `public.team_user`. Neglecting this becomes an injection path where a malicious `search_path` makes it grab a different object.
3. **Least privilege.** Strip `execute` from `anon` and grant it only to the necessary role.

The design to not make this function **a hole that slips past RLS**, and the pitfall of `search_path`, are detailed in the [security definer function and search_path article](/blog/supabase-security-definer-function-search-path-guide). A function introduced for performance becoming a hole in authorization defeats the purpose, so be sure to read it together.

---

## 7. A higher-tier optimization: erase the lookup itself with JWT claims

Up to here was about "making the DB query fast." The last one changes the idea — **don't query the DB at all.**

If a user's team membership or role doesn't change often, **put it in the JWT's `app_metadata`** and the policy can **judge with only the token contents without querying the DB at all.** Even the lookup to the `team_user` table (§5, §6) disappears.

```sql
-- JWTのapp_metadataに格納された teams 配列で判定する（DBルックアップなし）
create policy "user is in team" on public.my_table
  for select to authenticated
  using (
    team_id in (
      select jsonb_array_elements_text(
        (select auth.jwt() -> 'app_metadata' -> 'teams')
      )::uuid
    )
  );
```

There are two important notes.

- **Use `app_metadata` (not `user_metadata`).** [Supabase official](https://supabase.com/docs/guides/database/postgres/row-level-security) explicitly states that `raw_user_metadata` is **writable by the user themselves**, so **it must not be used for authorization**; for authorization, use the **non-writable `raw_app_metadata`.** Get this wrong and it becomes a fatal hole where users can escalate their own permissions.
- **The trade-off with token freshness.** A JWT is a snapshot at issue time. Even after leaving a team, the old permission remains until the token expires (until refresh). **Use DB lookups (§5/§6) for permissions needing immediate revocation, and JWT for permissions that change slowly** — this is the classic trade-off of performance and consistency.

Similarly, requiring MFA (multi-factor authentication) strength with RLS is an application of JWT claims. With a `restrictive` (AND-combined) policy, you can enforce "no update unless `aal2`" on all paths.

```sql
-- 二要素認証を通したセッションでなければ更新を拒否する（全UPDATEにANDで効く）
create policy "require mfa for updates" on public.profiles
  as restrictive            -- 既存のpermissiveポリシーとANDで結合される
  for update to authenticated
  using ( (select auth.jwt()->>'aal') = 'aal2' );
```

A `restrictive` policy is combined with other `permissive` policies (OR-combined) with **AND** ([PostgreSQL official](https://www.postgresql.org/docs/current/ddl-rowsecurity.html)). "Adding permission" is permissive, "constraining the whole" is restrictive — fix **absolutely-non-removable conditions** like tenant boundaries and MFA requirements with restrictive.

---

## 8. The order of production rollout: apply in order of effect, protect with tests

Lining up the 6 (+JWT) optimizations in order of high ROI is as follows. Apply from the top in order and confirm the effect each time with §1's `EXPLAIN ANALYZE` — that's the shortest path.

| Priority | Optimization | Cost | Effect (digit of official bench) | When it works |
| --- | --- | --- | --- | --- |
| 1 | `(select auth.uid())` wrap | tiny | ~100× | almost all policies |
| 2 | index the policy column | small | ~1000× | tables with many rows |
| 3 | explicit `TO authenticated` | tiny | ~1000× on the anon path | mixed public + authenticated |
| 4 | JOIN→set-reference rewrite | medium | ~hundreds× | membership authorization |
| 5 | security definer functionize | medium | orders of magnitude for multi-stage authorization | complex authorization |
| 6 | JWT-claim-ize | medium | lookup disappears | slowly-changing permissions |

And there's **something to always do last.**

> **Re-verify: is the now-fast RLS still correct?**

Performance optimization is the act of rewriting predicates. The `(select)` wrap, JOIN rewrite, and functionize always carry the risk of **inadvertently changing the meaning of authorization.** An RLS that's "fast but lets others' rows be seen" is far more dangerous than a slow one. So before and after optimization, run the automated tests that check **both "what should be allowed is allowed" and "what should be denied is denied"** as shown in the [pgTAP article](/blog/supabase-rls-testing-pgtap-policy-regression-guide), and stop regressions in CI. **Performance and correctness — you can't ship to production with only one.**

In my [real-time match-recording app](/case-studies/realtime-sports-scoring-app), I wove these 6 into all 280 policies from the design stage, verified allow/deny with pgTAP, and run a screen where multiple people simultaneously touch the same data with different views at a practical latency. RLS isn't "done when written" or "done when made fast"; it endures production only with the 3-set of **design → performance → verification.**

---

## Conclusion: RLS slowness is "a disease of the execution plan," not a mistake in authorization

- An RLS policy is **an implicit WHERE added to every query.** A slow predicate hits all rows, so performance is part of the design.
- The true identity of slowness is three — **per-row function evaluation, absence of an index, wasteful policy evaluation.** The 6 optimizations are prescriptions for these three.
- The biggest move is the **`(select auth.uid())` wrap.** An uncorrelated scalar subquery is folded into an InitPlan, per-row evaluation disappears, and it rides on an index too.
- Apply **indexes, explicit `TO`, JOIN set-referencing, security definer functions, and JWT-claim-izing** in order of effect while measuring with `EXPLAIN ANALYZE`.
- Figures are environment-dependent. **After optimizing, re-verify allow/deny with pgTAP** and absolutely don't produce a "fast but leaky" regression.

### Primary sources (always confirm the latest)

- [Supabase: Row Level Security (including the performance section)](https://supabase.com/docs/guides/database/postgres/row-level-security)
- [PostgreSQL: Row Security Policies](https://www.postgresql.org/docs/current/ddl-rowsecurity.html)
- [PostgreSQL: CREATE POLICY](https://www.postgresql.org/docs/current/sql-createpolicy.html)
- [PostgreSQL: EXPLAIN](https://www.postgresql.org/docs/current/sql-explain.html)