Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-37290

Avoid FETCH stage with $elemmatch if using covered index and multiKeyPaths is correct

    XMLWordPrintableJSON

Details

    • Improvement
    • Status: Closed
    • Major - P3
    • Resolution: Works as Designed
    • 3.6.8
    • None
    • None

    Description

      Hi,

      I've a performance issue with a collection with almost 4M documents, because the find (or $match on aggregation) is executing a FETCH when I use the $elemmatch. Even if the index is working well and returning only the correct documents. 
      The FETCH is not necessary because on version 3.6 the multiKeyPaths is working and makes the $elemmatch query to filter well on date ranges. So I think MongoDB should avoid executing the FETCH since it causes a huge performance drop.

       

      here is an example of the query planner:

       

      {
          "queryPlanner" : {
              "plannerVersion" : 1,
              "namespace" : "mp.userdocuments",
              "indexFilterSet" : false,
              "parsedQuery" : {
                  "$and" : [ 
                      {
                          "userActs" : {
                              "$elemMatch" : {
                                  "publishedDate2" : {
                                      "$elemMatch" : {
                                          "$lte" : ISODate("2018-10-01T18:31:48.000Z"),
                                          "$gte" : ISODate("2017-01-01T18:31:48.000Z")
                                      }
                                  }
                              }
                          }
                      }, 
                      {
                          "isArchived" : {
                              "$eq" : false
                          }
                      }, 
                      {
                          "user" : {
                              "$eq" : ObjectId("556efbaaef2541ec0d80a1ba")
                          }
                      }
                  ]
              },
              "winningPlan" : {
                  "stage" : "FETCH",
                  "filter" : {
                      "userActs" : {
                          "$elemMatch" : {
                              "publishedDate2" : {
                                  "$elemMatch" : {
                                      "$lte" : ISODate("2018-10-01T18:31:48.000Z"),
                                      "$gte" : ISODate("2017-01-01T18:31:48.000Z")
                                  }
                              }
                          }
                      }
                  },
                  "inputStage" : {
                      "stage" : "IXSCAN",
                      "keyPattern" : {
                          "user" : 1,
                          "isArchived" : 1,
                          "userActs.publishedDate2" : 1
                      },
                      "indexName" : "user_1_isArchived_1_userActs.publishedDate2_1",
                      "isMultiKey" : true,
                      "multiKeyPaths" : {
                          "user" : [],
                          "isArchived" : [],
                          "userActs.publishedDate2" : [ 
                              "userActs", 
                              "userActs.publishedDate2"
                          ]
                      },
                      "isUnique" : false,
                      "isSparse" : false,
                      "isPartial" : false,
                      "indexVersion" : 2,
                      "direction" : "forward",
                      "indexBounds" : {
                          "user" : [ 
                              "[ObjectId('556efbaaef2541ec0d80a1ba'), ObjectId('556efbaaef2541ec0d80a1ba')]"
                          ],
                          "isArchived" : [ 
                              "[false, false]"
                          ],
                          "userActs.publishedDate2" : [ 
                              "[new Date(1483295508000), new Date(1538418708000)]"
                          ]
                      }
                  }
              },
              "rejectedPlans" : []
          },
          "executionStats" : {
              "executionSuccess" : true,
              "nReturned" : 13033,
              "executionTimeMillis" : 175,
              "totalKeysExamined" : 15228,
              "totalDocsExamined" : 13033,
              "executionStages" : {
                  "stage" : "FETCH",
                  "filter" : {
                      "userActs" : {
                          "$elemMatch" : {
                              "publishedDate2" : {
                                  "$elemMatch" : {
                                      "$lte" : ISODate("2018-10-01T18:31:48.000Z"),
                                      "$gte" : ISODate("2017-01-01T18:31:48.000Z")
                                  }
                              }
                          }
                      }
                  },
                  "nReturned" : 13033,
                  "executionTimeMillisEstimate" : 152,
                  "works" : 15229,
                  "advanced" : 13033,
                  "needTime" : 2195,
                  "needYield" : 0,
                  "saveState" : 119,
                  "restoreState" : 119,
                  "isEOF" : 1,
                  "invalidates" : 0,
                  "docsExamined" : 13033,
                  "alreadyHasObj" : 0,
                  "inputStage" : {
                      "stage" : "IXSCAN",
                      "nReturned" : 13033,
                      "executionTimeMillisEstimate" : 41,
                      "works" : 15229,
                      "advanced" : 13033,
                      "needTime" : 2195,
                      "needYield" : 0,
                      "saveState" : 119,
                      "restoreState" : 119,
                      "isEOF" : 1,
                      "invalidates" : 0,
                      "keyPattern" : {
                          "user" : 1,
                          "isArchived" : 1,
                          "userActs.publishedDate2" : 1
                      },
                      "indexName" : "user_1_isArchived_1_userActs.publishedDate2_1",
                      "isMultiKey" : true,
                      "multiKeyPaths" : {
                          "user" : [],
                          "isArchived" : [],
                          "userActs.publishedDate2" : [ 
                              "userActs", 
                              "userActs.publishedDate2"
                          ]
                      },
                      "isUnique" : false,
                      "isSparse" : false,
                      "isPartial" : false,
                      "indexVersion" : 2,
                      "direction" : "forward",
                      "indexBounds" : {
                          "user" : [ 
                              "[ObjectId('556efbaaef2541ec0d80a1ba'), ObjectId('556efbaaef2541ec0d80a1ba')]"
                          ],
                          "isArchived" : [ 
                              "[false, false]"
                          ],
                          "userActs.publishedDate2" : [ 
                              "[new Date(1483295508000), new Date(1538418708000)]"
                          ]
                      },
                      "keysExamined" : 15228,
                      "seeks" : 1,
                      "dupsTested" : 15228,
                      "dupsDropped" : 2195,
                      "seenInvalidated" : 0
                  }
              }
          },
          "serverInfo" : {
              "host" : "ML-Lenovo",
              "port" : 27017,
              "version" : "3.6.8",
              "gitVersion" : "6bc9ed599c3fa164703346a22bad17e33fa913e4"
          },
          "ok" : 1
      }
      

      So, as you can see, from 23K documents (for this user id), the filter correctly returns 13033 rows, and then the FETCH is executed and also returns 13033, because it is doing the same filter again, so the result will not be different.

       

      Here is an example of a document:

       

      {
          "_id" : ObjectId("5b4d188ca958130c528f21ac"),
          "user" : ObjectId("556efbaaef2541ec0d80a1ba"),
          "isArchived" : false,
          "userActs" : [ 
              {
                  "publishedDate2" : [ 
                      ISODate("2016-02-15T05:00:00.000Z")
                  ]
              }, 
              {
                  "publishedDate2" : [ 
                      ISODate("2016-03-07T05:00:00.000Z")
                  ]
              }, 
              {
                  "publishedDate2" : [ 
                      ISODate("2016-10-04T04:00:00.000Z")
                  ]
              }, 
              {
                  "publishedDate2" : [ 
                      ISODate("2016-10-13T04:00:00.000Z")
                  ]
              }, 
              {
                  "publishedDate2" : [ 
                      ISODate("2017-01-04T05:00:00.000Z")
                  ]
              }, 
              {
                  "publishedDate2" : [ 
                      ISODate("2018-03-05T05:00:00.000Z")
                  ]
              }
          ],
          "__v" : 6
      }
      

       

       

      The index is:

      {
          "user" : 1,
          "isArchived" : 1,
          "userActs.publishedDate2" : 1
      }
      

      The execution of the FETCH stage is killing the performance.
      Can we fix this and avoiding the FETCH stage when the index isMultiKey and the fields on $elemMatch are covered by the index and are in the multiKeyPaths?

      Otherwise, I guess, I'll need to change my structure to not use array? (not good since we would need to duplicate a lot of data).

      Thanks

      Attachments

        Issue Links

          Activity

            People

              kelsey.schubert@mongodb.com Kelsey Schubert
              marcelolopes Marcelo Lopes
              Votes:
              0 Vote for this issue
              Watchers:
              8 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: