-
Type: New Feature
-
Resolution: Fixed
-
Priority: Major - P3
-
Affects Version/s: None
-
Component/s: None
-
Labels:None
-
Fully Compatible
A query like this:
db.telemetry.aggregate([ {$match: {"meta.sensorId”: 9}}, // <======= Note the $match here! {$sort: {"meta.sensorId”: 1, "timestamp”: -1}}, {$group: { _id: "$metadata.sensorId", ts: {$last: "$timestamp"}, temp: {$last: "$temp"} }}])
Should be translated into this:
MongoDB Enterprise > db.system.buckets.telemetry.explain("executionStats").aggregate([ ... {$match:{"meta.sensorId": 9}}, // <======= Just carry the $match forward and translate the rest as usual. ... {$sort: {"meta.sensorId": 1, "control.max.timestamp": -1}}, ... {$group: { ... _id: "$meta.sensorId", ... bucket: {$first: "$_id"}, ... }}, ... {$lookup: { ... from: "system.buckets.telemetry", ... foreignField: "_id", ... localField: "bucket", ... as: "bucket_data", ... pipeline:[ ... {$_internalUnpackBucket: { ... timeField:"timestamp", ... metaField:"tags", ... bucketMaxSpanSeconds:NumberInt("60") ... }}, ... {$sort: {"timestamp": -1}}, ... {$limit:1} ... ] ... }}, ... {$unwind: "$bucket_data"}, ... {$replaceWith:{ ... _id: "$_id", ... timestamp: "$bucket_data.timestamp", ... temp: "$bucket_data.temp" ... }} ... ]); { "explainVersion" : "1", "stages" : [ { "$cursor" : { "queryPlanner" : { "namespace" : "test.system.buckets.telemetry", "indexFilterSet" : false, "parsedQuery" : { "meta.sensorId" : { "$eq" : 9 } }, "queryHash" : "C44A5C82", "planCacheKey" : "282D66F1", "maxIndexedOrSolutionsReached" : false, "maxIndexedAndSolutionsReached" : false, "maxScansToExplodeReached" : false, "winningPlan" : { "stage" : "FETCH", "inputStage" : { "stage" : "DISTINCT_SCAN", "keyPattern" : { "meta.sensorId" : 1, "control.max.timestamp" : -1, "control.min.timestamp" : -1 }, "indexName" : "metadata.sensorId_1_timestamp_-1", "isMultiKey" : false, "multiKeyPaths" : { "meta.sensorId" : [ ], "control.max.timestamp" : [ ], "control.min.timestamp" : [ ] }, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "meta.sensorId" : [ "[9.0, 9.0]" ], "control.max.timestamp" : [ "[MaxKey, MinKey]" ], "control.min.timestamp" : [ "[MaxKey, MinKey]" ] } } }, "rejectedPlans" : [ ] },
This should work for all variations of +- indexes.
Notice that the plan includes a DISTINCT_SCAN with range precisely limited to the matched range.