Uploaded image for project: 'Compass '
  1. Compass
  2. COMPASS-7062

Query Performance Explain Summary for Aggregation Pipelines with $lookup is inaccurate with pre-SBE Execution Plans

    • Type: Icon: Bug Bug
    • Resolution: Unresolved
    • Priority: Icon: Minor - P4 Minor - P4
    • None
    • Affects Version/s: 1.39.0
    • Component/s: Explain
    • Environment:
      OS: Mac (M1) OSX and Windows
      node.js / npm versions: Bundled with Compass
      Additional info: Compass 1.39.0, MongoDB Replica Set 6.0.7, however it should be reproduceable in any MongoDB Replica Set pre-SBE.
    • 3

      Problem Statement/Rationale

      Running an explain plan on an aggregation pipeline with a $match and a $lookup shows incorrect results on MongoDB 6+ when the pipeline can not be run using the new SBE. It will only show the keys and documents examined by the first $match stage.

      This scenario shows that the query might be efficient (for example, returning 4 documents, examining 4) because it is hiding the internal $lookup information, that might be doing COLLSCAN.

      Please be sure to attach relevant logs with any sensitive data redacted.
      No logs seem to be necessary for reproducing this issue. All required information is attached as a mongodump and in the `Steps to Reproduce` section.

      Steps to Reproduce

      1. Start a new replica set with MongoDB 6.0.X, it can be created both in Atlas or a local environment. It has been reproduced in both environments.

      2. Restore the provided mongodump with the following command:

      mongorestore --gzip --archive=document-examined-mismatch.tar.gz

      3. Connect to the cluster using Compass.

      4. Open the embedded mongosh, and run the following command to disable SBE:

      db.adminCommand({ setParameter: "*", internalQueryForceClassicEngine: true })

      5. Run the following aggregation pipeline explain plan in the 'document-examined-mismatch
      .source' collection:

       [
        {
          $match:
            {
              name: "Barbie",
            },
        },
        {
          $lookup:
            {
              from: "comment",
              localField: "name",
              foreignField: "from",
              as: "comments",
            },
        },
        {
          $sortByCount:
            {
              $avg: "$comments.rating",
            },
        },
      ]

      The explain plan will show that it only examines 1 document:

      However, the explain plan shows that it examines 1 document during the $match, and 3 documents during the $lookup:

      "stage": "FETCH",
      ...
      "docsExamined": 1,
      ...
      "$lookup":
      { "from": "comment", "as": "comments", "localField": "name", "foreignField": "from" }
      ,
      "totalDocsExamined": 3,

      The complete explain plan is attached.

      Expected Results

      What do you expect to happen?

      A correct and consistent behaviour both pre-SBE and SBE-based explain plans. In SBE-based, the information is accurate (4 documents examined), however, pre-SBE, only shows 1 document examined.

      Actual Results

      Compass is not recognising the $lookup, and not showing in the summary the examined documents nor the COLLSCANS.

      Additional Notes

        1. document-examined-mismatch.tar.gz
          0.6 kB
          Kevin Mas Ruiz
        2. explain-plan.json
          6 kB
          Kevin Mas Ruiz
        3. Screenshot 2023-07-31 at 14.41.37.png
          160 kB
          Kevin Mas Ruiz

            Assignee:
            Unassigned Unassigned
            Reporter:
            kevin.mas@mongodb.com Kevin Mas Ruiz
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Created:
              Updated: