MongoDB nested/include query slow #17142
Description
Bug description
It seems that, when using include with MongoDB, the generated query uses "in" instead of "eq", resulting in significantly slower queries.
How to reproduce
Relevant schema bits:
model Series {
id String @id @default(auto()) @map("_id") @db.ObjectId
imdbId String @unique
title Title
episodes Episode[]
@@index([title.primaryTitle])
}
model Episode {
id String @id @default(auto()) @map("_id") @db.ObjectId
imdbId String @unique
season Int?
episode Int?
title Title
Series Series @relation(fields: [seriesImdbId], references: [imdbId])
seriesImdbId String
@@index([seriesImdbId])
}
Now, say I have the imdbId for a series, and I want to fetch it + all the episodes. The following takes under 130ms to run on my dev machine:
console.time("my-query");
const series = await prisma.series.findUnique({
where: { imdbId: tconst },
});
const episodes = await prisma.episode.findMany({
where: { seriesImdbId: tconst },
});
console.timeEnd("my-query");
However, the following takes over 2.5s:
console.time("my-query");
const series = await prisma.series.findUnique({
where: { imdbId: tconst },
include: { episodes: true },
});
const episodes = series.episodes;
console.timeEnd("my-query");
Now, I've enabled query logging and saw the following:
Slow:
Query: db.Episode.aggregate([ { $match: { $expr: { $and: [ { $in: [ "$seriesImdbId", [ { $literal: "tt0106179", }, ], ], }, { $ne: [ "$seriesImdbId", "$$REMOVE", ], }, ], }, }, }, { $project: { _id: 1, imdbId: 1, season: 1, episode: 1, title.primaryTitle: 1, title.originalTitle: 1, title.isAdult: 1, title.startYear: 1, title.endYear: 1, title.runtimeMins: 1, title.genres: 1, title.rating.numVotes: 1, title.rating.avgRating: 1, seriesImdbId: 1, }, }, ])
Fast:
Query: db.Episode.aggregate([ { $match: { $expr: { $and: [ { $eq: [ "$seriesImdbId", { $literal: "tt0106179", }, ], }, { $ne: [ "$seriesImdbId", "$$REMOVE", ], }, ], }, }, }, { $project: { _id: 1, imdbId: 1, season: 1, episode: 1, title.primaryTitle: 1, title.originalTitle: 1, title.isAdult: 1, title.startYear: 1, title.endYear: 1, title.runtimeMins: 1, title.genres: 1, title.rating.numVotes: 1, title.rating.avgRating: 1, seriesImdbId: 1, }, }, ])
I think the key here is that the slow query uses operator $in, whereas the fast query uses $eq.
Expected behavior
The query using include should not take 20x longer.
Prisma information
included in repro
Environment & setup
- Fedora 37
- MongoDB 6.0
- Node v19.2.0
Prisma Version
prisma : 4.8.0
@prisma/client : 4.8.0
Current platform : rhel-openssl-3.0.x
Query Engine (Node-API) : libquery-engine d6e67a83f971b175a593ccc12e15c4a757f93ffe (at node_modules/@prisma/engines/libquery_engine-rhel-openssl-3.0.x.so.node)
Migration Engine : migration-engine-cli d6e67a83f971b175a593ccc12e15c4a757f93ffe (at node_modules/@prisma/engines/migration-engine-rhel-openssl-3.0.x)
Introspection Engine : introspection-core d6e67a83f971b175a593ccc12e15c4a757f93ffe (at node_modules/@prisma/engines/introspection-engine-rhel-openssl-3.0.x)
Format Binary : prisma-fmt d6e67a83f971b175a593ccc12e15c4a757f93ffe (at node_modules/@prisma/engines/prisma-fmt-rhel-openssl-3.0.x)
Format Wasm : @prisma/prisma-fmt-wasm 4.8.0-61.d6e67a83f971b175a593ccc12e15c4a757f93ffe
Default Engines Hash : d6e67a83f971b175a593ccc12e15c4a757f93ffe
Studio : 0.479.0