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

Index used by whole collection scanned

    XMLWordPrintableJSON

Details

    • Icon: Question Question
    • Resolution: Done
    • Icon: Major - P3 Major - P3
    • None
    • 3.0.2
    • Querying, WiredTiger
    • None

    Description

      Hello,

      I have a compound index on a collection:

      	{
      		"v" : 1,
      		"key" : {
      			"sdk.id" : 1,
      			"sdk.createdAt" : 1,
      			"sdk.updatedAt" : 1,
      			"deviceInfo.isTablet" : 1
      		},
      		"name" : "sdk.id_1_sdk.createdAt_1_sdk.updatedAt_1_deviceInfo.isTablet_1",
      		"ns" : "company.Device"
      	}
      

      The documents contains an array of subdocument, stored in 'sdk' property.

      When I try to query the collection on the sdk.id field, I have the following result:

      db.Device.explain(1).find({'sdk.id': 'id1'});
      {
      	"queryPlanner" : {
      		"plannerVersion" : 1,
      		"namespace" : "company.Device",
      		"indexFilterSet" : false,
      		"parsedQuery" : {
      			"sdk.id" : {
      				"$eq" : "id1"
      			}
      		},
      		"winningPlan" : {
      			"stage" : "PROJECTION",
      			"transformBy" : {
      				"sdk.id" : 1
      			},
      			"inputStage" : {
      				"stage" : "FETCH",
      				"inputStage" : {
      					"stage" : "IXSCAN",
      					"keyPattern" : {
      						"sdk.id" : 1,
      						"sdk.createdAt" : 1,
      						"sdk.updatedAt" : 1,
      						"deviceInfo.isTablet" : 1
      					},
      					"indexName" : "sdk.id_1_sdk.createdAt_1_sdk.updatedAt_1_deviceInfo.isTablet_1",
      					"isMultiKey" : true,
      					"direction" : "forward",
      					"indexBounds" : {
      						"sdk.id" : [
      							"[\"id1\", \"id1\"]"
      						],
      						"sdk.createdAt" : [
      							"[MinKey, MaxKey]"
      						],
      						"sdk.updatedAt" : [
      							"[MinKey, MaxKey]"
      						],
      						"deviceInfo.isTablet" : [
      							"[MinKey, MaxKey]"
      						]
      					}
      				}
      			}
      		},
      		"rejectedPlans" : [ ]
      	},
      	"executionStats" : {
      		"executionSuccess" : true,
      		"nReturned" : 569733,
      		"executionTimeMillis" : 3435,
      		"totalKeysExamined" : 569733,
      		"totalDocsExamined" : 569733,
      		"executionStages" : {
      			"stage" : "PROJECTION",
      			"nReturned" : 569733,
      			"executionTimeMillisEstimate" : 3331,
      			"works" : 569734,
      			"advanced" : 569733,
      			"needTime" : 0,
      			"needFetch" : 0,
      			"saveState" : 4452,
      			"restoreState" : 4452,
      			"isEOF" : 1,
      			"invalidates" : 0,
      			"transformBy" : {
      				"sdk.id" : 1
      			},
      			"inputStage" : {
      				"stage" : "FETCH",
      				"nReturned" : 569733,
      				"executionTimeMillisEstimate" : 2231,
      				"works" : 569734,
      				"advanced" : 569733,
      				"needTime" : 0,
      				"needFetch" : 0,
      				"saveState" : 4452,
      				"restoreState" : 4452,
      				"isEOF" : 1,
      				"invalidates" : 0,
      				"docsExamined" : 569733,
      				"alreadyHasObj" : 0,
      				"inputStage" : {
      					"stage" : "IXSCAN",
      					"nReturned" : 569733,
      					"executionTimeMillisEstimate" : 940,
      					"works" : 569733,
      					"advanced" : 569733,
      					"needTime" : 0,
      					"needFetch" : 0,
      					"saveState" : 4452,
      					"restoreState" : 4452,
      					"isEOF" : 1,
      					"invalidates" : 0,
      					"keyPattern" : {
      						"sdk.id" : 1,
      						"sdk.createdAt" : 1,
      						"sdk.updatedAt" : 1,
      						"deviceInfo.isTablet" : 1
      					},
      					"indexName" : "sdk.id_1_sdk.createdAt_1_sdk.updatedAt_1_deviceInfo.isTablet_1",
      					"isMultiKey" : true,
      					"direction" : "forward",
      					"indexBounds" : {
      						"sdk.id" : [
      							"[\"id1\", \"id1\"]"
      						],
      						"sdk.createdAt" : [
      							"[MinKey, MaxKey]"
      						],
      						"sdk.updatedAt" : [
      							"[MinKey, MaxKey]"
      						],
      						"deviceInfo.isTablet" : [
      							"[MinKey, MaxKey]"
      						]
      					},
      					"keysExamined" : 569733,
      					"dupsTested" : 569733,
      					"dupsDropped" : 0,
      					"seenInvalidated" : 0,
      					"matchTested" : 0
      				}
      			}
      		},
      		"allPlansExecution" : [ ]
      	},
      	"serverInfo" : {
      		"host" : "replicaset-primary",
      		"port" : 27000,
      		"version" : "3.0.2",
      		"gitVersion" : "6201872043ecbbc0a4cc169b5482dcf385fc464f"
      	},
      	"ok" : 1
      }
      

      I can't find any info on isEOF and other values such as saveState etc. to debug it. We are using WiredTiger and replica set was setted up on AWS using MMS.
      The same query was very fast before the WiredTiger switch

      Can someone give us some insight on the explain above ?
      Regards,
      Jonathan

      Attachments

        Activity

          People

            ramon.fernandez@mongodb.com Ramon Fernandez Marina
            jmuller@dial-once.com Jonathan Muller
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: