The ticket said search was fast for admins and took nine seconds for tenants, with the same query text. EXPLAIN as the postgres superuser showed a tidy index scan at 40 ms. EXPLAIN as the application role showed a parallel sequential scan over 28 million rows and a Filter line full of expressions nobody had typed. The difference was row-level security: a policy that lives in the catalog, rewrites every plan that touches the table, and never appears in anyone's SQL. It was the strangest performance bug I have been assigned, precisely because nothing in the codebase was wrong.
The mechanics in one breath: on a table with ENABLE ROW LEVEL SECURITY, every query gets the applicable policies folded into it as quals — permissive policies OR'd together, restrictive policies AND'd on top — and the planner plans the rewritten query. That rewriting is invisible to the application, and it changes the optimization problem. A predicate you cannot see is still a predicate: it costs selectivity, it orders evaluation, and it can veto the index you were counting on.
Why is the same query fast as admin and slow as the tenant?
Because admins usually are not running the same query at all. The table owner bypasses RLS unless you ALTER TABLE ... FORCE ROW LEVEL SECURITY, and superusers and roles with BYPASSRLS skip it outright — so "it works when I run it" is the default, not a mystery. The tenant role's plan carries the policy quals, and those quals change everything downstream: estimated row counts, join order, index choice. pg_stat_statements quietly records the divergence if you split by role — the view has a userid column, and joining it to pg_roles is how you prove the same queryid is fast for one role and slow for another without arguing from anecdotes.
The version of this bug that costs weeks is the intermittent one: a policy whose qual is cheap for small tenants and catastrophic for your largest customer, because selectivity differs by two orders of magnitude and the plan was chosen for the average. The admin-versus-tenant split is the easy half of the diagnosis. The tenant-versus-tenant split is where you earn your money, and it only shows up when you look at plans per role with real parameter values.
How do non-LEAKPROOF functions break index usage under RLS?
Through evaluation ordering, and it is the single most common RLS performance bug I see. The planner refuses to evaluate any user-supplied expression that might leak data before the security quals have been applied — otherwise a malicious or careless function could exfiltrate rows the policy exists to hide, through error messages or timing channels. Only functions and operators marked LEAKPROOF are trusted to run early. The policy quals themselves are exempt — they are evaluated first by definition, so an index on the policy column still serves them — but a WHERE clause from your application that is built on a non-leakproof function gets deferred behind the barrier, and a deferred clause cannot be an index condition. Instead of an index scan driven by your predicate, you get a scan with it demoted to a Filter over every row the policy already passed.
The trap is that nearly nothing is leakproof by default: your own functions are not, whatever their volatility. The nine-second search from the opening was exactly this — a custom IMMUTABLE function in the application's WHERE clause, written years earlier, that the planner had to defer behind the policy quals, so the predicate that should have been an Index Cond ran as a filter over every row the policy passed. The fix is to audit the function for leak channels (no raising errors that echo input values, no dependency on an untrusted search_path) and then have a superuser ALTER FUNCTION ... LEAKPROOF. One marking took that nine-second search back to 60 ms, because the search predicate could finally drive the index again. The diagnosis is only visible in the plan: look for Filter lines containing your function where an Index Cond should be. If your predicate is being evaluated as a filter over millions of rows, you have found it.
What goes wrong with current_setting() and cached plans?
Two things, and they compound. First, current_setting is STABLE, not IMMUTABLE, and the subtlety cuts the opposite way from what people expect: the planner evaluates it once at plan time, so a freshly built plan is estimated against the real tenant value — but that estimate is only as fresh as the plan build. Second, plan caching freezes that estimate in place. With prepared statements or a driver using the extended protocol, PostgreSQL may switch to a generic plan after a few executions; that plan was estimated with whichever tenant value happened to be current when it was built, and then it serves every tenant. The plan that fits a 10,000-row tenant is a disaster for the 200-million-row tenant, and a generic plan hands the giant the dwarf's plan, or the reverse, depending on whose session built it.
The mitigations are unglamorous. Set plan_cache_mode = force_custom_plan for the sessions where tenant skew is extreme, so each execution is planned with the actual setting in hand. Prefer real parameters over GUCs where the architecture allows it, because a $1 placeholder at least participates in the custom-versus-generic decision honestly. And one behavior to know before it surprises you in a test: if the setting has never been set, a one-argument current_setting raises an error — unrecognized configuration parameter — rather than returning NULL. You get NULL only with the two-argument form, current_setting('app.tenant_id', true), and then the comparison is never true and the policy fails closed — zero rows. The error at least fails loud; the NULL version is the source of the classic "RLS returns nothing in staging" confusion.
How do you debug RLS plans without guessing?
Run EXPLAIN as the role that is actually slow — SET ROLE is the whole trick — and read the plan for filter lines and row-removal counts. A superuser's EXPLAIN is evidence about a different query. My checklist, in order: SET ROLE to the application role; set the tenant GUC the way the application would; EXPLAIN (ANALYZE, BUFFERS) the reported query; read every Filter and Rows Removed by Filter line; then inspect the policies themselves in pg_policies, and confirm relrowsecurity and relforcerowsecurity in pg_class for the table. Nine times in ten the answer is on that first plan.
SET ROLE app_tenant;
SET "app.tenant_id" = 'a1b2c3d4-0000-4000-8000-000000000042';
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title FROM documents
WHERE created_at >= now() - interval '7 days'
ORDER BY created_at DESC LIMIT 50;
RESET ROLE;
SELECT policyname, permissive, roles, qual
FROM pg_policies
WHERE schemaname = 'public' AND tablename = 'documents';
SELECT relrowsecurity, relforcerowsecurity
FROM pg_class WHERE relname = 'documents';Two more tools earn their place in the kit. SET row_security = off makes a restricted role raise an error instead of silently returning a policy-filtered subset — essential for exports and dump-style jobs, where a silent subset is a data-loss bug wearing a success message; pg_dump sets it for exactly that reason. And audit your policy list for permissive-policy sprawl: permissive policies are OR'd, so one broad policy quietly widens every other policy on the table. Teams add policies over time and nobody subtracts. A RESTRICTIVE policy expressing your hardest invariant — tenant match, always — turns that OR-sprawl back into a seatbelt.
Watching RLS hotspots with MonPG
MonPG monitors PostgreSQL in production today. It cannot see your policies, but it graphs the symptoms policies produce: sequential-scan share and row-throughput trends per table from pg_stat_user_tables, and mean execution time shifts in pg_stat_statements, so the deploy that added a non-leakproof function shows up as a seq-scan spike on exactly one table. The PostgreSQL monitoring surface covers how those counters are collected, and the EXPLAIN guide is the companion read for the per-role plan work above. RLS is worth the complexity for genuine multi-tenant isolation — just remember that every policy is a WHERE clause you wrote in a place nobody greps.