Performance: where { relation: { some } } generates slow correlated COUNT subquery instead of EXISTS

[uzarsalan]

Description

When using findMany with a relation filter where: { relation: { some: { ... } } }, ZenStack v3 generates a correlated subquery using COUNT > 0 that executes once per row in the parent table. This causes severe performance degradation on large tables.

Generated SQL (actual)

SELECT * FROM "canonical_requests"
WHERE (
  SELECT "$count" FROM (
    SELECT count(*) AS "$count"
    FROM "order_requisites" AS "$$t5"
    WHERE "canonical_requests"."id" = "$$t5"."canonical_request_id"
      AND ("$$t5"."requisite_type" = $1 AND "$$t5"."value" = $2)
  ) AS "$sub"
) > 0
ORDER BY "source_created_at" DESC

For a table with 928,701 rows, this executes the subquery ~928k times = ~20 seconds response time.

Expected SQL (optimal)

SELECT * FROM "canonical_requests"
WHERE EXISTS (
  SELECT 1 FROM "order_requisites"
  WHERE "canonical_request_id" = "canonical_requests"."id"
    AND "requisite_type" = $1
    AND "value" = $2
)
ORDER BY "source_created_at" DESC

With a proper index on (requisite_type, value), EXISTS executes in < 1ms.

Reproduction

// Slow: generates correlated COUNT subquery
const results = await db.canonical_requests.findMany({
  where: {
    requisites: { some: { requisite_type: 'phone', value: '79788808687' } }
  }
})

Workaround:

// Fast: two-step query using indexed lookup
const requisiteRows = await db.order_requisites.findMany({
  where: { requisite_type: 'phone', value: '79788808687' },
  select: { canonical_request_id: true }
})

const results = await db.canonical_requests.findMany({
  where: { id: { in: requisiteRows.map(r => r.canonical_request_id) } }
})

Performance comparison

Approach	Table size	Time
where: { relation: { some } }	928k rows	~20 seconds
WHERE EXISTS (...)	928k rows	< 1ms
Workaround (two-step)	928k rows	~50ms

Environment

• ZenStack: v3.x (Kysely backend)
• Database: PostgreSQL (Supabase)
• Table: 928,701 rows

pg_stat_statements confirmation

mean_exec_time: 19,738ms
max_exec_time: 24,044ms
calls: 8

The issue appears to be in how ZenStack translates Prisma-compatible { some: { ... } } filters to SQL. Using COUNT > 0 instead of EXISTS prevents the query planner from short-circuiting on the first match and forces a full correlated scan.

[ymc9]

Fixed in 3.4.5