Your app calls a Supabase endpoint that should return in about 12 milliseconds. Instead it takes over a second, sometimes two. You check the network tab, confirm the delay is real, and assume you're missing an index. So you run EXPLAIN ANALYZE in the SQL editor, following the standard advice in Supabase's own EXPLAIN documentation, and the plan looks fine. Fast, even.
That mismatch is one of the most common frustrations developers hit when they try to debug a Postgres query plan on a table protected by Row-Level Security. One Reddit thread even describes a PostgREST query going from roughly 12ms to 1.86 seconds, a 155x slowdown, purely from RLS overhead that never showed up when the query was tested from an admin connection.
If you've already read our breakdown of why Supabase RLS gets slow, you know indexing and function volatility are usually the culprits. What that article doesn't cover is how to actually watch it happen inside EXPLAIN ANALYZE instead of guessing. That's what this walkthrough is for.
By the end, you'll know how to mock a real tenant session before running EXPLAIN ANALYZE, read the RLS filter Postgres injects into the plan tree, and tell a missing index apart from a bad policy and a client-side N+1 problem, all from the same page of output. This matters even more once your schema has multiple tenants and roles instead of one privileged service account.
authenticated and injects request.jwt.claims, because postgres and service_role connections bypass RLS by default. Then check for a SubPlan node under your filter: if its loops count matches the table's row count, Postgres is calling a function like auth.uid() once per row instead of caching it as an InitPlan.
Table of Contents
- Why Standard EXPLAIN ANALYZE Advice Breaks Down Under RLS
- How to Mock Your Session Before You Debug a Postgres RLS Query Plan
- How PostgreSQL Injects RLS Policies Into the Query Plan
- Why Is Your RLS Policy Ignoring Indexes?
- SubPlan vs InitPlan: The Real Reason auth.uid() Kills Performance
- Is It RLS, a Missing Index, or an N+1 Problem?
- Quick Answers About Reading EXPLAIN ANALYZE for RLS Policies
- Frequently Asked Questions
Why Standard EXPLAIN ANALYZE Advice Breaks Down Under RLS
Simply put, running EXPLAIN ANALYZE from the Supabase SQL editor, DBeaver, or any admin connection string shows you a plan that never touched your RLS policies at all. Both the postgres superuser and the service_role Supabase gives you carry the BYPASSRLS attribute, and Postgres strips row security policies entirely for any role that has it.
That's the trap. You connect, run the query, watch it fly through an Index Scan in under a millisecond, and conclude the database is healthy. Meanwhile your production API, which authenticates real users through the anon or authenticated role, is grinding through a sequential scan because a policy the SQL editor never evaluated is now sitting on the hot path.
This gap between what admin tools show and what your API actually runs is why generic "how to read EXPLAIN ANALYZE" guides fall apart for RLS-protected tables. They aren't wrong, they're just answering a different question than the one you're actually asking.
How to Mock Your Session Before You Debug a Postgres RLS Query Plan
To see the plan your API actually runs, you need to make Postgres believe you're an unprivileged, authenticated tenant user before you call EXPLAIN. Do this inside a transaction so nothing you touch sticks around afterward.
- Open a transaction with
BEGIN;so any session changes stay scoped and reversible. - Downgrade with
SET LOCAL ROLE authenticated;, the same role PostgREST uses for logged-in requests. - Inject the claims your policies read using
set_config('request.jwt.claims', ...), matching thesuband any custom tenant claim your policies check. - Run
EXPLAIN (ANALYZE, BUFFERS)against the real query, never plain EXPLAIN, so you get physical I/O numbers alongside the plan. - Close with
ROLLBACK;, notCOMMIT;, since this is a read-only diagnostic session.
-- Debug an RLS-protected query as if a real tenant user made the request
BEGIN;
-- 1. Drop from postgres/service_role down to the actual API role
SET LOCAL ROLE authenticated;
-- 2. Inject the JWT claims your policies actually read
SELECT set_config(
'request.jwt.claims',
'{"sub": "11111111-1111-1111-1111-111111111111", "tenant_id": "org_demo"}',
true
);
-- 3. Run the real diagnostic, BUFFERS shows physical I/O too
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM invoices
WHERE tenant_id = 'org_demo';
-- 4. Always roll back, this is read-only
ROLLBACK;
Once you run this, the plan you get back is the plan your tenant actually experiences: sequential scans, injected filters, and all. Everything after this point in the article assumes you're reading a plan captured this way.
How PostgreSQL Injects RLS Policies Into the Query Plan
Simply put, Postgres treats an RLS policy as a hidden security barrier, silently AND-ing your policy's boolean expression onto whatever WHERE clause you actually wrote. You never see this in your SQL, but you will see it in the plan.
Say your table has a policy like USING (tenant_id = auth.uid()) and your application runs SELECT * FROM invoices WHERE status = 'open'. The plan won't show a clean Filter on just status. Instead you'll find something like Filter: ((status = 'open'::text) AND (tenant_id = auth.uid())) buried inside a Seq Scan, or the Index Cond of an Index Scan, your explicit condition and the injected security predicate stitched together with an AND.
Learning to spot that injected clause is the whole skill here. Once you can separate what you wrote from what Postgres added on your behalf, the rest of this walkthrough is just about figuring out why the added part is slow.
Why Is Your RLS Policy Ignoring Indexes?
Because the planner sometimes refuses to push your index condition down past the security barrier. Postgres enforces this on purpose: if it let a non-leakproof function run before the RLS filter, a carefully crafted query could infer the existence of hidden rows through side effects, like a deliberate division-by-zero error thrown before the filter ever excludes the row.
So if your policy calls a function Postgres can't prove is leakproof, the planner evaluates the security barrier against the whole table first and only applies your index conditions afterward. That's how a table with a perfectly good index on tenant_id still produces a full sequential scan the moment RLS is switched on. It isn't a bug, it's the planner protecting against data leakage, and your query plan pays the cost.
This is also why two visually similar policies can perform completely differently. A plain column comparison like tenant_id = auth.uid() is usually leakproof-friendly. A policy wrapped in a custom pl/pgsql permission function, or one built around a CASE expression, often isn't, and that's frequently the real reason "RLS won't use my index" even though the index objectively exists on the table.
Even when the planner does use your index, a single-column index on tenant_id often isn't the whole fix. If your EXPLAIN ANALYZE output shows a valid Index Cond next to a large "Rows Removed by Filter" number, that's the signal you need a composite index instead, covered in depth in our guide to composite indexes for RLS.
SubPlan vs InitPlan: The Real Reason auth.uid() Kills Performance
This is the single most damaging pattern in Supabase RLS performance, and it comes down to two nodes that look almost identical at a glance in EXPLAIN ANALYZE output: SubPlan and InitPlan.
When you write a policy like USING (user_id = auth.uid()), Postgres often can't prove the function's result stays constant for the whole query, so it attaches it as a correlated SubPlan. A SubPlan re-runs once per row the outer scan touches. Scan a million-row table and you'll call auth.uid() a million times. In the plan, this shows up as a SubPlan node nested under your scan, with a loops= value matching the row count of the node above it, and a total time wildly out of proportion to what the base scan alone would cost.
The fix is a small syntax change that gives the planner what it needs to prove the value is safe to cache: wrap the function call in a scalar subquery.
-- SubPlan: auth.uid() evaluated once per row (slow on large tables)
CREATE POLICY tenant_isolation ON invoices
USING (user_id = auth.uid());
-- InitPlan: wrapped in a scalar subquery, evaluated once total (fast)
CREATE POLICY tenant_isolation ON invoices
USING (user_id = (SELECT auth.uid()));
Re-run EXPLAIN ANALYZE with the mocked session from earlier in this article and you should see the SubPlan node disappear, replaced by an InitPlan that runs exactly once, caches the UUID, and applies it to every row from memory instead of re-executing the function. Developers on Supabase's own GitHub discussions have documented this exact behavior, noting that declaring a function STABLE alone doesn't force the caching, only the scalar subquery syntax does.
If EXPLAIN ANALYZE already confirmed a runaway SubPlan is your bottleneck, the fastest general-purpose fix is usually restructuring the policy with the tenant_id IN() pattern instead of calling a function per row, which sidesteps the SubPlan question entirely.
Is It RLS, a Missing Index, or an N+1 Problem?
From the outside, a missing index, a slow RLS policy, and a client-side N+1 loop all look identical: the endpoint is slow. EXPLAIN ANALYZE is what lets you tell them apart, and each one leaves a distinct signature.
| Suspected Culprit | EXPLAIN ANALYZE Signature | Diagnostic Interpretation |
|---|---|---|
| Missing Index | Seq Scan on the primary table, a high Rows Removed by Filter count, large shared read numbers in Buffers | The database is physically scanning far more rows than it returns. RLS evaluated cheaply, the retrieval path itself is the problem. |
| Inefficient RLS Policy | Index Scan or Seq Scan present, but a SubPlan node sits underneath with loops= matching the scanned row count | Data retrieval is fine. The database is re-running an authorization function or secondary lookup once for every row. |
| Application N+1 | Execution time in the low milliseconds, but the browser network tab shows a much longer total duration | The query and the policy are both healthy. The latency is dozens of sequential HTTP calls, not the database. |
Run through this matrix top to bottom before you touch a single index. It usually takes less than a minute and keeps you from optimizing a query that was never the actual bottleneck.
Quick Answers About Reading EXPLAIN ANALYZE for RLS Policies
What Causes a Slow EXPLAIN ANALYZE Plan Under RLS?
Simply put, a slow RLS-protected query is almost always a function or subquery inside the policy getting re-evaluated once per row instead of once per query. Postgres shows this as a SubPlan node with a loops= value matching the row count of the scan above it, instead of the InitPlan you'd get from a properly cached, row-independent value. This matters most on tables past a few hundred thousand rows, where the per-row cost compounds from microseconds into whole seconds.
EXPLAIN ANALYZE for RLS at a Glance
| Aspect | Details |
|---|---|
| Symptom | EXPLAIN ANALYZE looks fast in the SQL editor, production API calls are slow |
| Root Cause | postgres and service_role bypass RLS (BYPASSRLS), so the plan you saw never included the policy |
| Fix | Mock the authenticated role and request.jwt.claims before running EXPLAIN (ANALYZE, BUFFERS) |
| What to Look For | A SubPlan with loops= matching the row count, versus a one-time InitPlan |
| Applies To | Postgres 15+ and Supabase projects enforcing RLS through PostgREST-issued JWTs |
When Does This Apply?
This applies to any Supabase or Postgres project enforcing RLS through PostgREST, where the API role differs from the role you use to connect for debugging. If your app connects with a single privileged service account and handles authorization entirely in application code, this diagnostic flow doesn't apply, you aren't running policy-based security at the database layer.
Frequently Asked Questions
What is EXPLAIN (ANALYZE, BUFFERS) and why does RLS debugging need the BUFFERS option?
EXPLAIN ANALYZE runs the query and reports real timing and row counts. Adding BUFFERS reports shared hit and shared read counts, showing whether data came from cache or disk. For RLS debugging, BUFFERS helps you tell a slow function call apart from genuine physical I/O pressure.
Can I test RLS policies without a real logged-in user session?
Yes. Wrap your query in a transaction, run SET LOCAL ROLE authenticated, and call set_config('request.jwt.claims', ...) with the sub and any custom claims your policies check, then EXPLAIN ANALYZE the query and ROLLBACK. This simulates a real tenant session without touching a live account.
Does a LIMIT clause stop RLS from scanning the whole table?
No. Postgres still has to evaluate the RLS policy against every candidate row before it can confirm which ones satisfy your query, so a LIMIT 15 can still trigger evaluation of tens of thousands of rows if the underlying scan or policy isn't selective.
Why doesn't adding an index fix my slow RLS query?
If a SubPlan is re-running a function per row, indexing the scanned column won't help, the cost is in the function calls, not the scan itself. Check for SubPlan loops matching your row count before assuming the fix is indexing.
Is a SubPlan always a performance problem in EXPLAIN ANALYZE?
Not always. A SubPlan that runs once or a handful of times is fine. The red flag is a loops= value that scales with your table's row count, meaning Postgres is re-running it for every row the outer scan touches.
How do I know if a function in my RLS policy is STABLE?
Check pg_proc, or run \df+ on the function in psql, and look at its volatility. STABLE alone isn't enough to force caching in RLS though, you also need to wrap the call in a scalar subquery for Postgres to treat it as an InitPlan.
Do I need service_role to run EXPLAIN ANALYZE on RLS-protected tables?
You need elevated access to open the session, but you should immediately downgrade with SET LOCAL ROLE authenticated before running EXPLAIN. Running it directly as service_role bypasses RLS entirely and shows you a plan your real users never experience.
Conclusion
Reading EXPLAIN ANALYZE for an RLS-protected table isn't fundamentally different from reading any other query plan. You're still hunting for sequential scans, blown-out row estimates, and expensive nodes. The real difference is you have to earn an honest plan first, by mocking the session your real API actually uses, and then know the specific fingerprints RLS leaves behind: an injected AND clause inside your Filter, a security barrier blocking index pushdown, and above all, a SubPlan with a suspiciously high loop count.
Once you've confirmed a SubPlan is your bottleneck, the fix is usually mechanical: wrap the function in a scalar subquery, or restructure the policy so it never calls a function per row at all. Either way, you stop guessing and start fixing the node that's actually slow, which is the entire point of learning to debug a Postgres query plan instead of eyeballing dashboard graphs.
This diagnostic habit matters even more as your schema grows past a handful of tables, which is exactly the territory our complete guide to multi-tenant Supabase architecture covers in depth. Bookmark this page. You'll want the role-mocking script again the next time a query that looked fine locally turns slow in production.
