Cardinality planner

The planner decides how relation-heavy reads are executed. The fastest strategy depends on cardinality, not just query shape: a profile include behaves very differently from posts.comments.likes.

Strategies

• flat joins — low-depth includes with predictable fan-out
• correlated subqueries — small bounded child includes
• where-in segmented loading — large to-many includes: fetch parents, thenWHERE fk IN (...) batches, stitched in memory. Composite foreign keys use row-value tuple IN.

A naive join strategy can explode row counts on deep trees (User → Posts → Comments → Likes). The planner keeps read amplification under control while preserving Prisma-compatible result shapes.

Planner stats

During generation, prisma-sql can collect artifacts into planner.generated.ts:RELATION_STATS, MODEL_STATS, ROUNDTRIP_ROW_EQUIVALENT, JSON_ROW_FACTOR.

MODEL_STATS powers the large to-many include guard: when a child table is large and the parent result is estimated small, queries switch to where-in segmented loading instead of correlated per-parent subqueries.

Without stats (or with known: false) the guard is inactive for that model. Queries still work correctly, but relation-heavy reads may not pick the optimal strategy.

Best setup

1) Model real cardinality

@unique on one-to-one foreign keys tells the planner the relation is bounded; indexes on one-to-many foreign keys make segmented loading cheap.

model Profile {
  id     Int  @id @default(autoincrement())
  userId Int  @unique
  user   User @relation(fields: [userId], references: [id])
}

2) Index every foreign key used in includes and relation filters

model Comment {
  id        Int      @id @default(autoincrement())
  postId    Int
  createdAt DateTime @default(now())
  published Boolean  @default(false)

  post Post @relation(fields: [postId], references: [id])

  @@index([postId])
  @@index([postId, createdAt])
  @@index([postId, published])
}

3) Prefer deterministic nested ordering

const users = await prisma.user.findMany({
  include: {
    posts: {
      orderBy: { createdAt: 'desc' },
      take: 5,
    },
  },
})

Unbounded nested includes are supported — capping with take is a product decision, not a correctness requirement.

Deployment without database access at build time

Skip collection during build:

PRISMA_SQL_SKIP_PLANNER=true npx prisma generate

Collect after deployment, when the database is reachable:

npx prisma-sql-collect-stats \
  --output dist/prisma/generated/sql/planner.generated.js \
  --prisma-client dist/prisma/generated/client/index.js

Load collected stats at runtime:

import { loadExternalPlannerStats } from 'prisma-sql'

if (loadExternalPlannerStats('/data/planner-stats/planner.generated.js')) {
  console.log('stats loaded')
}

Incremental collection

• freshness window: skip if existing file is younger than PRISMA_SQL_STATS_MAX_AGE_MS (default 24h)
• fast mode (default): PostgreSQL catalog statistics, falls back to precise mode if stale
• precise mode: full relation cardinality queries via PRISMA_SQL_STATS_MODE=precise
• slow edges reuse cached stats (PRISMA_SQL_SLOW_EDGE_MS) and re-measure after PRISMA_SQL_STALE_EDGE_HOURS
• per-edge timeout via PRISMA_SQL_EDGE_TIMEOUT_MS; total timeout via PRISMA_SQL_PLANNER_TIMEOUT_MS

By default collection does not run ANALYZE. Set PRISMA_SQL_ANALYZE=1 to refresh PostgreSQL catalog statistics during collection — avoid it where generation should not mutate database state.

Verify the planner helps

const prisma = basePrisma.$extends(
  speedExtension({
    postgres: sql,
    debug: true,
    onQuery: (info) => {
      console.log(info.model, info.method, info.duration + 'ms')
      console.log(info.sql)
    },
  }),
)

Healthy signs: small parent pages stay small in latency; bounded child includes stay predictable; high-fanout includes avoid correlated per-parent scans.