Uploaded image for project: 'Mender'
  1. Mender
  2. MEN-5521

[deployments] avoid "SCAN AND SORT" in deployments/next, again

    XMLWordPrintable

    Details

      Description

      The deployments service is querying the deployments collection as follows:

      db.devices.find({"deviceid": "7c5902e2-e995-4892-bf8e-fa98d687726e", "status": {"$in": [2304, 1536, 1792, 2048, 768, 1024, 1280]}}).sort({"created": 1}).explain()
      

      This is performing a SCAN AND SORT:

      {{ "queryPlanner" : { "plannerVersion" : 1, "namespace" : "deployment_service-5b20d68a5e9101000136629e.devices", "indexFilterSet" : false, "parsedQuery" : { "$and" : [ { "deviceid" : { "$eq" : "7c5902e2-e995-4892-bf8e-fa98d687726e" } }, { "status" : { "$in" : [ 768, 1024, 1280, 1536, 1792, 2048, 2304 ] } } ] }, "queryHash" : "36E90F4C", "planCacheKey" : "67A33E7C", "winningPlan" : { "stage" : "SORT", "sortPattern" : { "created" : 1 }, "memLimit" : 104857600, "type" : "simple", "inputStage" : { "stage" : "FETCH", "inputStage" : { "stage" : "IXSCAN", "keyPattern" : { "deviceid" : 1, "status" : 1 }, "indexName" : "devicesIdWithStatus", "isMultiKey" : false, "multiKeyPaths" : { "deviceid" : [ ], "status" : [ ] }, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "deviceid" : [ "[\"7c5902e2-e995-4892-bf8e-fa98d687726e\", \"7c5902e2-e995-4892-bf8e-fa98d687726e\"]" ], "status" : [ "[768.0, 768.0]", "[1024.0, 1024.0]", "[1280.0, 1280.0]", "[1536.0, 1536.0]", "[1792.0, 1792.0]", "[2048.0, 2048.0]", "[2304.0, 2304.0]" ] } } } }, "rejectedPlans" : [ { "stage" : "FETCH", "inputStage" : { "stage" : "IXSCAN", "keyPattern" : { "deviceid" : 1, "created" : 1, "status" : 1 }, "indexName" : "devicesIdWithCreatedStatus", "isMultiKey" : false, "multiKeyPaths" : { "deviceid" : [ ], "created" : [ ], "status" : [ ] }, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "deviceid" : [ "[\"7c5902e2-e995-4892-bf8e-fa98d687726e\", \"7c5902e2-e995-4892-bf8e-fa98d687726e\"]" ], "created" : [ "[MinKey, MaxKey]" ], "status" : [ "[768.0, 768.0]", "[1024.0, 1024.0]", "[1280.0, 1280.0]", "[1536.0, 1536.0]", "[1792.0, 1792.0]", "[2048.0, 2048.0]", "[2304.0, 2304.0]" ] } } } ] }, "serverInfo" : { "host" : "atlas-2onxqm-shard-00-02.hgobb.mongodb.net", "port" : 27017, "version" : "4.4.12", "gitVersion" : "51475a8c4d9856eb1461137e7539a0a763cc85dc" }, "ok" : 1, "$clusterTime" : { "clusterTime" : Timestamp(1646892669, 139), "signature" : { "hash" : BinData(0,"QvQfJgDuM8Lj+QbhbQWgQk4D0ts="), "keyId" : NumberLong("7026345168001302529") } }, "operationTime" : Timestamp(1646892669, 139)}
      

      The results are sorted by the "created" field.

      We should optimize FindOldestDeploymentForDeviceIDWithStatuses and FindLatestDeploymentForDeviceIDWithStatuses, these are similar queries which are doing SCAN AND ORDER.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              alfrunes Alf-Rune Siqveland
              Reporter:
              tranchitella Fabio Tranchitella
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Zendesk Support