.. a Performance Bug

The same query, on identical data, ran in well under half a second in one environment and over a second in another. The query plan looked the same. The difference wasn't the query at all.

Row-level security policies that re-check a permission condition per row are correctness features that also carry a performance cost — and that cost is easy to misdiagnose as "the query is slow" when the real cause is "the security layer is re-verifying something on every row, every time."

The policy in question gated visibility on a sensitive-data flag, checked through a lookup back to a parent record for every row returned. Measuring the actual physical work involved (buffer reads, not just elapsed time) showed the version with the policy active did several times more physical work than the same query without it — for logically identical output. The policy wasn't wrong. It was correct and expensive, and nobody had measured the expense until the phase it was hiding inside had already been chased as a "slow query" problem for weeks.

The fix had two parts, and both were necessary:

• Push the same visibility logic into the application layer, so the correct rows get filtered explicitly by the calling code instead of relying on the database to re-check on every row at query time.
• Once the application layer is provably doing the same check, move the query to run under a role that skips the redundant per-row policy entirely — because paying for the same check twice, once in the database and once in application code, is pure waste.

This only works safely if the application-side check is verified to match the security policy's logic exactly — cutting the safety net without confirming the replacement net is at least as strong defeats the entire point.

A "show only records the current user is allowed to see" filter, if enforced both by a database policy and by application code that already computes the same answer, is being paid for twice. Enforce it once, deliberately, and skip the other.

Remember: when a query's cost doesn't line up with its apparent complexity, check what's wrapped around the query — a security policy, an audit trigger, a view — before assuming the query itself is the problem.