PostgreSQL: policy SQL blocks index use (uuid column cast, count(1) > 0 instead of EXISTS, relation == auth() subquery)

[sidharthv96]

Summary

On PostgreSQL, three patterns in the policy SQL generated by @zenstackhq/orm / @zenstackhq/plugin-policy 3.9.2 prevent Postgres from using indexes to evaluate access policies. A read guarded only by a policy therefore scans the whole table and runs correlated subqueries per row. On our production data these reads took 1.3–9.5 s under load and saturated the connection pool.

Each pattern has an equivalent SQL form that is index-friendly. The measurements below come from a synthetic table set: 20,000 teams, 20,000 team members, and 60,000 projects.

1. auth() comparisons cast the uuid column instead of the value

model User {
  id String @id @default(uuid()) @db.Uuid
  @@auth()
}

model Membership {
  id     String @id @default(uuid()) @db.Uuid
  teamID String @db.Uuid
  userID String @db.Uuid
  @@index([userID])
  @@allow('read', userID == auth().id)
}

compiles to

cast("Membership"."userID" as text) = $1

Casting the column means no index on it can serve the predicate, so every policy check on a uuid foreign key becomes a sequential scan. Comparing natively, as "Membership"."userID" = $1::uuid, uses the index. Measured on a 20,000-row table with a btree index on the uuid column:

SQL	Plan	Time
cast(col as text) = $1	Seq Scan	3.5 ms
col = $1::uuid	Index Only Scan	0.01 ms

I think this comes from the #2394 fix, #2532. In plugin-policy/dist/index.mjs, getFieldDefFromFieldRef returns undefined for an auth().x member, because it has no branch for a call receiver. The Postgres buildComparison in orm/dist/index.mjs then sees a @db.Uuid field def on one side only, and casts that side to text. If the auth() member resolved to the auth model's field def, both sides would be uuid, and no cast would be needed. Where a cast is unavoidable, casting the parameter keeps the column indexable. #2396 took that approach.

2. Collection predicates compile to count(1) > 0 instead of EXISTS

model Team {
  id      String       @id @default(uuid()) @db.Uuid
  members Membership[]
  @@allow('read', members?[userID == auth().id])
}

compiles to

(select count(1) > 0 from "Membership" where "Team"."id" = "Membership"."teamID" and ...)

Measured on 20,000 teams, with an indexed uuid column in the member predicate:

Postgres can't turn a correlated scalar count subquery into a semi-join, so it evaluates the subquery once per Team row. EXISTS can be planned as a semi-join that starts from the indexed side, and it stops at the first match:

SQL	Plan	Time
(select count(1) > 0 ...)	Seq Scan on Team, subquery per row	21 ms
exists (select 1 ...)	Index Scan on the member index, then Team pkey	0.02–0.04 ms

Relation filters in where (some) already compile to exists (select 1 ...). Using the same shape for ?[...], with NOT EXISTS for ![...] and ^[...], would make policies plan as well as explicit filters. The count comes from the predicateResult construction in the collection-predicate transform, FunctionNode.create("count", ...), in plugin-policy/dist/index.mjs.

3. relation == auth() adds a per-row subquery on the auth model

@@allow('read', orgMember.user == auth())

compiles to

$1 = cast((select (select "id" from "User" where "OrganisationMember"."userID" = "User"."id")
  from "OrganisationMember" where "TeamMember"."orgMemberID" = "OrganisationMember"."id") as text)

When the relation's foreign key references the auth model's id, this is equivalent to orgMember.userID == auth().id, which drops the inner User lookup:

Form	Time
orgMember.user == auth()	55 ms
orgMember.userID == auth().id	15 ms, still a sequential scan because of pattern 1

We worked around this by rewriting every policy to compare foreign keys. Having the compiler do the rewrite would help everyone who writes the relation form.

Combined effect

A policy-only project.findMany({ where: { ownerID: null } }), where visibility comes from team.members?[...], took 420 ms on the synthetic data. The same read with an explicit team: { members: { some: { orgMemberID: { in: [...] } } } } filter took 0.35 ms and returned the same rows, because that filter compiles to EXISTS with a native uuid comparison. The explicit filter is our workaround; fixing patterns 1 and 2 would make policy-only reads plan like this without it.

Environment

@zenstackhq/orm 3.9.2, @zenstackhq/plugin-policy 3.9.2, @zenstackhq/cli 3.9.2, PostgreSQL (Supabase), pg driver.

[sidharthv96]

Our DB CPU use after the user == auth() to userID == auth().id optimisation. Initial CPU is ~50%, since this is after we had to double our DB capacity due to CPU always being close to 95%.

[ymc9]

Hey @sidharthv96 , I see this can cause quite big adverse performance. For the first two issues you listed, I've made some local experiments and published a "@canary" build (v3.9.7-beta.1). Could you help try it out?