Optimizing $lookup Performance Using the Power of Indexing

1
db.genres.insertMany([
2
    { "_id": 0, "genre": "Comedy" },
3
    { "_id": 1, "genre": "Drama" },
4
    { "_id": 2, "genre": "Action" },
5
    { "_id": 3, "genre": "Romance" },
6
    { "_id": 4, "genre": "Adventure" },
7
    { "_id": 5, "genre": "Family" },
8
    { "_id": 6, "genre": "Fantasy" },
9
    { "_id": 7, "genre": "Thriller" }
10
])

1
    db.movies.insertMany([
2
    { "_id": 0, "title": "Billy Madison", "genre_codes": [0], "year": 1995, "directors": ["Tamra Davis"] },
3
    { "_id": 1, "title": "Happy Gilmore", "genre_codes": [0, 5], "year": 1996, "directors": ["Dennis Dugan"] },
4
    { "_id": 2, "title": "The Waterboy", "genre_codes": [0, 5], "year": 1998, "directors": ["Frank Coraci"] },
5
    { "_id": 3, "title": "Big Daddy", "genre_codes": [0, 5], "year": 1999, "directors": ["Dennis Dugan"] },
6
    { "_id": 4, "title": "Little Nicky", "genre_codes": [0], "year": 2000, "directors": ["Steven Brill"] },
7
    { "_id": 5, "title": "Mr. Deeds", "genre_codes": [0], "year": 2002, "directors": ["Dennis Dugan"] },
8
    { "_id": 6, "title": "50 First Dates", "genre_codes": [0, 3], "year": 2004, "directors": ["Peter Segal"] },
9
    { "_id": 7, "title": "The Longest Yard", "genre_codes": [0, 2], "year": 2005, "directors": ["Peter Segal"] },
10
    { "_id": 8, "title": "Grown Ups", "genre_codes": [0, 5], "year": 2010, "directors": ["Dennis Dugan"] },
11
    { "_id": 9, "title": "Just Go with It", "genre_codes": [0, 3], "year": 2011, "directors": ["Dennis Dugan"] },
12
    { "_id": 10, "title": "Blended", "genre_codes": [0, 5], "year": 2014, "directors": ["Frank Coraci"] },
13
    { "_id": 11, "title": "The Do-Over", "genre_codes": [0, 2], "year": 2016, "directors": ["Steven Brill"] },
14
    { "_id": 12, "title": "Murder Mystery", "genre_codes": [0, 7], "year": 2019, "directors": ["Kyle Newacheck"] },
15
    { "_id": 13, "title": "You Are So Not Invited to My Bat Mitzvah", "genre_codes": [0], "year": 2023, "directors": ["Sammi Cohen"] },
16
    { "_id": 14, "title": "Punch-Drunk Love", "genre_codes": [1], "year": 2002, "directors": ["Paul Thomas Anderson"] },
17
    { "_id": 15, "title": "Reign Over Me", "genre_codes": [1], "year": 2007, "directors": ["Mike Binder"] },
18
    { "_id": 16, "title": "Funny People", "genre_codes": [1], "year": 2009, "directors": ["Judd Apatow"] },
19
    { "_id": 17, "title": "Uncut Gems", "genre_codes": [1, 7], "year": 2019, "directors": ["Josh Safdie", "Benny Safdie"] },
20
    { "_id": 18, "title": "Bedtime Stories", "genre_codes": [5, 6], "year": 2008, "directors": ["Adam Shankman"] },
21
    { "_id": 19, "title": "Hotel Transylvania", "genre_codes": [5, 6], "year": 2012, "directors": ["Genndy Tartakovsky"] },
22
    { "_id": 20, "title": "Pixels", "genre_codes": [0, 2], "year": 2015, "directors": ["Chris Columbus"] }
23
])

1
{
2
    "_id": 1,
3
    "genre": "Drama",
4
    "movies": [
5
      { "title": "Funny People", "year": 2009 },
6
      { "title": "Punch-Drunk Love", "year": 2002 },
7
      { "title": "Reign Over Me", "year": 2007 },
8
      { "title": "Uncut Gems", "year": 2019 }
9
    ]
10
}
11
    
12
{
13
    "_id": 0,
14
    "genre": "Comedy",
15
    "movies": [
16
      { "title": "50 First Dates", "year": 2004 },
17
      { "title": "Big Daddy", "year": 1999 },
18
      { "title": "Billy Madison", "year": 1995 },
19
      { "title": "Blended", "year": 2014 },
20
      { "title": "Grown Ups", "year": 2010 },
21
      { "title": "Happy Gilmore", "year": 1996 },
22
      { "title": "Just Go with It", "year": 2011 },
23
      { "title": "Little Nicky", "year": 2000 },
24
      { "title": "Mr. Deeds", "year": 2002 },
25
      { "title": "Murder Mystery", "year": 2019 },
26
      { "title": "Pixels", "year": 2015 },
27
      { "title": "The Do-Over", "year": 2016 },
28
      { "title": "The Longest Yard", "year": 2005 },
29
      { "title": "The Waterboy", "year": 1998 },
30
      { "title": "You Are So Not Invited to My Bat Mitzvah", "year": 2023 }
31
    ]
32
}

1
   db.genres.aggregate([
2
    { "$match": { "genre": { "$in": [ "Comedy", "Drama" ] } } },
3
    {
4
        "$lookup": {
5
            "from": "movies",
6
            "let": { "genre_id": "$_id" },
7
            "pipeline": [
8
                { "$match": { "$expr": { "$in": [ "$$genre_id", "$genre_codes" ] } } },
9
                { "$project": { _id: 0, "title": 1, "year":1 } }
10
            ],
11
            "as": "movies"
12
        }
13
    }
14
]).explain("allPlansExecution")

1
executionStats: {
2
          nReturned: 2,
3
          totalKeysExamined: 0,
4
          totalDocsExamined: 8,
5
          executionStages: {
6
            stage: 'COLLSCAN',
7
            filter: { genre: { '$in': [ 'Comedy', 'Drama' ] } },
8
            nReturned: 2,
9
	    ...
10
"totalDocsExamined": 42,
11
"totalKeysExamined": 0,
12
"collectionScans": 2,
13
"indexesUsed": [],
14
"nReturned": 2

1
db.genres.createIndex({ "genre": 1 })
2
db.movies.createIndex({ "genre_codes": 1 })

1
db.genres.aggregate([
2
    { "$match": { "genre": { "$in": [ "Comedy", "Drama" ] } } },
3
    {
4
        "$lookup": {
5
            "from": "movies",
6
            "let": { "genre_id": "$_id" },
7
            "pipeline": [
8
                { "$match": { "$expr": { "$in": [ "$$genre_id", "$genre_codes" ] } } },
9
                { "$project": { _id: 0, "title": 1, "year":1 } }
10
            ],
11
            "as": "movies"
12
        }
13
    }
14
]).explain("allPlansExecution")

1
"executionStats": {
2
          "nReturned": 2,
3
          "totalKeysExamined": 3,
4
          "totalDocsExamined": 2,
5
              "stage": "IXSCAN",
6
              "nReturned": 2,
7
              "indexName": "genre_1",
8
              ...
9
"totalDocsExamined": 42,
10
"totalKeysExamined": 0,
11
"collectionScans": 2,
12
"indexesUsed": [],
13
"nReturned": 2,

1
db.genres.aggregate([
2
  { $match: { genre: { $in: ["Comedy", "Drama"] } } },
3
  {
4
    $lookup: {
5
      from: "movies",
6
      localField: "_id",
7
      foreignField: "genre_codes",
8
      let: {},
9
      pipeline: [{ $project: { _id: 0, title: 1, year: 1 } }],
10
      as: "movies"
11
    }
12
  }
13
]).explain("allPlansExecution")

1
"executionStats": {
2
          "nReturned": 2,
3
          "totalKeysExamined": 3,
4
          "totalDocsExamined": 2,
5
          "stage": "IXSCAN",
6
              "nReturned": 2,
7
              "indexName": "genre_1",
8
              ...
9
"totalDocsExamined": 19,
10
"totalKeysExamined": 19,
11
"collectionScans": 0,
12
"indexesUsed": ["genre_codes_1"],
13
"nReturned": 2

1
db.movies.createIndex({ "genre_codes":1, "title":1, "year":1})
2
3
"totalDocsExamined": 0,
4
"totalKeysExamined": 19,
5
"collectionScans": 0,
6
"indexesUsed": [ "genre_codes_1_title_1_year_1" ],
7
"nReturned": 2,