-
Type: Bug
-
Resolution: Unresolved
-
Priority: 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
- is related to
-
SERVER-22622 Improve $lookup explain to indicate query plan on the "from" collection
- Needs Scheduling