Performance: to-one relation filter generates slow COUNT subquery instead of EXISTS (sibling of #2440)

[evgenovalov]

Description

Sibling regression to #2440. ZenStack v3 generates a correlated COUNT(*) > 0 subquery for to-one relation filters (where: { relation: { <field>: value } } and where: { relation: { is: { ... } } }). This executes the aggregate once per parent row and prevents PostgreSQL from converting it into a semi-join.

PR #2455 fixed the same issue for { some }, { none }, and { every } in buildToManyRelationFilter. The equivalent buildToOneRelationFilter (same file, packages/orm/src/client/crud/dialects/base-dialect.ts:330-416) was not touched and still emits COUNT.

Generated SQL (actual)

SELECT "id" FROM "tag_category"
WHERE EXISTS (                                       -- ← fast path from PR #2455
  SELECT 1 FROM "tag" AS "$$t1"
  WHERE "tag_category"."id" = "$$t1"."tag_category_id"
    AND EXISTS (                                     -- ← fast path from PR #2455
      SELECT 1 FROM "product_tag_link" AS "$$t2"
      WHERE "$$t1"."id" = "$$t2"."tag_id"
        AND (                                        -- ← THE SLOW PATH, still count > 0
          SELECT count(1) FROM "product_site" AS "$$t3"
          WHERE "$$t3"."id" = "$$t2"."product_site_id"
            AND "$$t3"."site_id" = $1
            AND "$$t3"."is_used" = true
        ) > 0
    )
);

The outer .some filters use EXISTS. The inner productSite: { siteId: ..., isUsed: true } is a to-one relation filter and is still wrapped in (select count(1) …) > 0.

Expected SQL

-- all three layers use EXISTS and can be converted to semi-joins
AND EXISTS (
  SELECT 1 FROM "product_site" AS "$$t3"
  WHERE "$$t3"."id" = "$$t2"."product_site_id"
    AND "$$t3"."site_id" = $1
    AND "$$t3"."is_used" = true
)

Reproduction

const schema = `
model TagCategory {
  id   Int   @id @default(autoincrement())
  tags Tag[]
}
model Tag {
  id            Int              @id @default(autoincrement())
  tagCategory   TagCategory?     @relation(fields: [tagCategoryId], references: [id])
  tagCategoryId Int?
  productTagLinks ProductTagLink[]
}
model ProductSite {
  id              Int              @id @default(autoincrement())
  siteId          Int
  isUsed          Boolean
  productTagLinks ProductTagLink[]
}
model ProductTagLink {
  tagId         Int
  productSiteId Int
  tag           Tag         @relation(fields: [tagId],         references: [id])
  productSite   ProductSite @relation(fields: [productSiteId], references: [id])
  @@id([productSiteId, tagId])
}
`;

await db.tagCategory.findMany({
  where: {
    tags: {
      some: {
        productTagLinks: {
          some: {
            productSite: { siteId: 849, isUsed: true },  // ← to-one filter, slow
          },
        },
      },
    },
  },
});

Performance comparison

Production data, ~2.6M product_site rows, ~8.5M product_tag_link rows, filter selects 8,807 product_sites, 12 resulting tag_categories. PostgreSQL 16, EXPLAIN (ANALYZE, BUFFERS).

Shape	Plan	Execution time
Current (count(1) > 0 on inner to-one)	Correlated SubPlan fires 1,072,403 times	2099 ms
Fixed (EXISTS on inner to-one)	Parallel Hash Semi Join on product_site_site_id_id_idx	49-96 ms

Same data, same indexes, same filters — only the SQL generation changes. Speedup: ~22-43×.

Workaround

Rephrase the to-one filter as a scalar IN against the FK after a pre-fetch:

const productSites = await db.productSite.findMany({
  where: { siteId: 849, isUsed: true },
  select: { id: true },
});
await db.tagCategory.findMany({
  where: {
    tags: {
      some: {
        productTagLinks: {
          some: { productSiteId: { in: productSites.map(p => p.id) } },
        },
      },
    },
  },
});

Adds a roundtrip and falls over on high-cardinality IN lists.

Proposed fix

Apply the same pattern PR #2455 used for buildToManyRelationFilter to buildToOneRelationFilter: replace the count(1) > 0 / = 0 comparisons with EXISTS / NOT EXISTS via the existing buildExistsExpression helper. Diff is contained to packages/orm/src/client/crud/dialects/base-dialect.ts; MySQL's buildExistsExpression override already exists so the derived-table wrapping carries over.

I'm opening a PR with the change and a regression test modeled after tests/regression/test/issue-2440.test.ts.

Environment

• ZenStack: @zenstackhq/orm@3.5.6
• Database: PostgreSQL 16
• Dialect: postgresql (Kysely backend)