Performance findings: unindexed predicates and bare auth.uid()

Unindexed predicates become sequential scans

An RLS predicate is evaluated against every candidate row the planner considers. When the column it filters on has no index, each query degrades into a sequential scan of the whole table, which is the most common reason a Supabase bill grows faster than traffic does.

Sequential scans under RLS punish twice: the predicate runs for every caller on every query, so background jobs, internal dashboards and end users all pay, and the cost tracks table growth rather than feature growth.

The rule reports one finding per table and column even when several policies reference the same column, because the fix is one index, and three alerts for one fix would be three times the noise. Only valid indexes satisfy the check, so an invalid build lingering after a failed concurrent creation leaves the finding standing. Remediation arrives as CREATE INDEX CONCURRENTLY IF NOT EXISTS, sized to avoid a write lock and shaped to run outside a transaction.

Bare auth.uid() is re-evaluated per row

Writing auth.uid() directly inside a policy asks Postgres to evaluate it once per candidate row. Wrapping the same call as a subquery, (SELECT auth.uid()), lets the planner hoist it into an InitPlan evaluated once per statement, with identical semantics and a very different cost curve.

An InitPlan is the planner's name for a subquery evaluated once with its result reused across rows. Hoisting turns a million evaluations of a function call into one lookup, which is why the wrapped form is the standard idiom in Supabase material generally.

-- Before: evaluated once per candidate row
USING (user_id = auth.uid())

-- After: hoisted into an InitPlan, evaluated once per statement
USING (user_id = (SELECT auth.uid()))

Magnitude, stated honestly

Where the pattern holds generically, large tables, per-row calls, predicates evaluated millions of times, the improvement is routinely ten to a hundred times, which is the honest range for this class of fix rather than a best case dressed up as a promise.

The range is wide because the multiplier depends on how much work each avoided evaluation carried: cheap calls multiply less, heavyweight lookups multiply more. What does not vary is the direction. On a thousand-row table the absolute saving is trivial, and the rule fires anyway, because structure predicts trajectory rather than today's pain.

Ordering the fixes

Index first, wrap second: the index removes a whole-table scan, the wrapper removes per-row overhead, and together they restore the planner's room to work, since per-row function calls can inhibit optimisations of their own. Confirm with EXPLAIN ANALYZE on a representative query before and after; the plan should show an index scan replacing the sequential one and the wrapped call appearing as an init-time node.

Remember that the predicate runs whether or not your application already filtered by the same column: RLS is evaluated for every query the policy covers, so an index that looks redundant next to your own WHERE clause is rarely redundant to the policy. The usual culprits behind missing indexes are ordinary ones, foreign-key-style columns created without a constraint and bulk-loaded tables whose indexes never got added back.

Related questions

Do these rules ever fire on healthy schemas?

They fire on structure, not opinion: a referenced column with no valid index, or an auth call outside a subquery. Both conditions are objectively present or absent in the catalog snapshot.

Is the hundred-fold figure guaranteed?

No figure is guaranteed, and none should be. Ten to a hundred times is the range observed where the pattern holds generically; your workload is the authority, and EXPLAIN ANALYZE settles it in minutes.