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

Last point on time-series optimization M7: $match(meta) prefix

    XMLWordPrintable

Details

    • New Feature
    • Status: Closed
    • Major - P3
    • Resolution: Fixed
    • None
    • 6.0.0-rc0
    • None
    • None
    • Fully Compatible

    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. 

      Attachments

        Activity

          People

            alya.berciu@mongodb.com Alya Berciu
            steve.tarzia@mongodb.com Steve Tarzia
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: