[SERVER-18360] Index used by whole collection scanned Created: 07/May/15  Updated: 07/May/15  Resolved: 07/May/15

Status: Closed
Project: Core Server
Component/s: Querying, WiredTiger
Affects Version/s: 3.0.2
Fix Version/s: None

Type: Question Priority: Major - P3
Reporter: Jonathan Muller Assignee: Ramon Fernandez Marina
Resolution: Done Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Participants:

 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



 Comments   
Comment by Jonathan Muller [ 07/May/15 ]

Ok thanks sorry about this

Comment by Ramon Fernandez Marina [ 07/May/15 ]

Thanks for your report jmuller@dial-once.com. Please note that the SERVER project is for reporting bugs or feature suggestions for the MongoDB server. For MongoDB-related support discussion please post on the mongodb-user group or Stack Overflow with the mongodb tag, where your question will reach a larger audience. A question like this involving more discussion would be best posted on the mongodb-user group.

Regards,
Ramón.

Comment by Jonathan Muller [ 07/May/15 ]

Also I'd like to edit the post to format de data and make it more readable but I can't find any editing option, is this possible?

Generated at Thu Feb 08 03:47:25 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.