MongoDB nested/include query slow

[foghina]

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

[janpio]

Thanks for the nice investigation @foghina 👍

[sebastiansas1]

Hi all. Do you guys have any updates on this?

I can confirm there's a significant delay when using include with MongoDB.

It is significantly impacting our production environment. Simple queries with include are taking on average 10x more time to complete.

[rjzheng]

Happening to us as well, it wouldn't be usable at scale.

[rjzheng]

I did some digging and this is what I found:
Prisma uses $expr and $in and indexes cannot be used for that combination in Mongo. Although it may eventually be supported, it does not at this point. Because of that, the query would scan every document in the collection.

If only Prisma uses $expr and $or + $eq instead, it'll be able to use indexes and speed up queries by magnitudes

I'm not sure if this would give Prisma a head start or not but we are currently going to convert all of our queries to not use include or connect, just very simple queries and if we need complex lookups, we'll just use aggregateRaw to write our own aggregation. It's really bad DX but can't do much about it :(

[EricVentor]

My issue is related, but isn't for includes. When trying to do a query with Prisma using in where the values were ids, Mongo was doing a table scan.

Slow solution:

where.id = {
  in: ids.map((id) => id),
};

In order to use the index for each id I did:

where.OR = ids.map((id) => ({ id: { equals: id} }));

[macrozone]

select has the same problem.

i think this is a very serious issue and should be tackled with high priority.

[offchan42]

My issue is related, but isn't for includes. When trying to do a query with Prisma using in where the values were ids, Mongo was doing a table scan.

Slow solution:

where.id = {
  in: ids.map((id) => id),
};

In order to use the index for each id I did:

where.OR = ids.map((id) => ({ id: { equals: id} }));

Yep, it's because of the $expr $and $in operator.
Here is another example from me. This code is also slow even if there's index on the key:

export function getCreationsFromShareTokens(shareTokens: string[]) {
  return prisma.creation.findMany({
    where: {
      shareToken: { in: shareTokens },
    },
  })
}

[Rieranthony]

Any news on this specific problem? I feel like mongodb isn't really being taken care of on prisma anymore

[laplab]

This performance problem is fixed in Prisma 5.14.0