[SERVER-64020] Last point on time-series optimization M7: $match(meta) prefix Created: 25/Feb/22  Updated: 29/Oct/23  Resolved: 15/Mar/22

Status: Closed
Project: Core Server
Component/s: None
Affects Version/s: None
Fix Version/s: 6.0.0-rc0

Type: New Feature Priority: Major - P3
Reporter: Steve Tarzia Assignee: Alya Berciu
Resolution: Fixed Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Backwards Compatibility: Fully Compatible
Participants:

 Description   

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. 



 Comments   
Comment by Steve Tarzia [ 15/Mar/22 ]

This was done already and tested in SERVER-64235

Generated at Thu Feb 08 05:59:16 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.