[SERVER-12792] incorrect nscannedObjects for explain for index intersection Created: 19/Feb/14  Updated: 14/Apr/16  Resolved: 26/Jan/15

Status: Closed
Project: Core Server
Component/s: Querying
Affects Version/s: 2.5.5
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Asya Kamsky Assignee: David Storch
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
duplicates SERVER-10448 Revamp explain() formatting Closed
Backwards Compatibility: Fully Compatible
Operating System: ALL
Participants:

 Description   

There is an index on city and on state:

geo@local(2.5.5) > db.zips.find({city:"HAZARD"}).explain()
{
	"cursor" : "BtreeCursor city_1",
	"isMultiKey" : false,
	"n" : 2,
	"nscannedObjects" : 2,
	"nscanned" : 2,
	"nscannedObjectsAllPlans" : 2,
	"nscannedAllPlans" : 2,
	"scanAndOrder" : false,
	"indexOnly" : false,
	"nYields" : 0,
	"nChunkSkips" : 0,
	"millis" : 0,
	"indexBounds" : {
		"city" : [
			[
				"HAZARD",
				"HAZARD"
			]
		]
	},
	"server" : "asyasmacbook.local:27017"
}
geo@local(2.5.5) > db.zips.find({state:"CA"}).explain()
{
	"cursor" : "BtreeCursor state_1",
	"isMultiKey" : false,
	"n" : 1523,
	"nscannedObjects" : 1523,
	"nscanned" : 1523,
	"nscannedObjectsAllPlans" : 1523,
	"nscannedAllPlans" : 1523,
	"scanAndOrder" : false,
	"indexOnly" : false,
	"nYields" : 11,
	"nChunkSkips" : 0,
	"millis" : 3,
	"indexBounds" : {
		"state" : [
			[
				"CA",
				"CA"
			]
		]
	},
	"server" : "asyasmacbook.local:27017"
}
geo@local(2.5.5) > db.zips.find({city:"HAZARD",state:"CA"}).explain()
{
	"cursor" : "Complex Plan",
	"n" : 1,
	"nscannedObjects" : 0,
	"nscanned" : 1525,
	"nscannedObjectsAllPlans" : 0,
	"nscannedAllPlans" : 1525,
	"nYields" : 11,
	"nChunkSkips" : 0,
	"millis" : 1,
	"server" : "asyasmacbook.local:27017"
}
geo@local(2.5.5) >



 Comments   
Comment by David Storch [ 26/Jan/15 ]

Fixed by the explain improvements done under SERVER-10448, so I'm resolving as a duplicate. See below for a repro against 2.6, and a demonstration that this is fixed in master.

Setup

t = db.t
t.drop()
for (var i = 0; i < 100; i++) { t.insert({a: i, b: 1}); }
WriteResult({ "nInserted" : 1 })
t.ensureIndex({a: 1})
t.ensureIndex({b: 1})

2.6.7

> t.find({a: 20}).explain()
{
	"cursor" : "BtreeCursor a_1",
	"isMultiKey" : false,
	"n" : 1,
	"nscannedObjects" : 1,
	"nscanned" : 1,
	"nscannedObjectsAllPlans" : 1, // CORRECT: nscannedObjects==n
	"nscannedAllPlans" : 1,
	"scanAndOrder" : false,
	"indexOnly" : false,
	"nYields" : 0,
	"nChunkSkips" : 0,
	"millis" : 0,
	"indexBounds" : {
		"a" : [
			[
				20,
				20
			]
		]
	},
	"server" : "dstorch-desktop:27017",
	"filterSet" : false
}
> t.find({b: 1}).explain()
{
	"cursor" : "BtreeCursor b_1",
	"isMultiKey" : false,
	"n" : 100,
	"nscannedObjects" : 100, // CORRECT: nscannedObjects==n
	"nscanned" : 100,
	"nscannedObjectsAllPlans" : 100,
	"nscannedAllPlans" : 100,
	"scanAndOrder" : false,
	"indexOnly" : false,
	"nYields" : 0,
	"nChunkSkips" : 0,
	"millis" : 0,
	"indexBounds" : {
		"b" : [
			[
				1,
				1
			]
		]
	},
	"server" : "dstorch-desktop:27017",
	"filterSet" : false
}
> db.adminCommand({setParameter: 1, internalQueryForceIntersectionPlans: true})
{ "was" : false, "ok" : 1 }
> t.find({a: 20, b: 1}).explain()
{
	"cursor" : "Complex Plan",
	"n" : 1,
	"nscannedObjects" : 0, // WRONG: nscannedObjects < n
	"nscanned" : 22,
	"nscannedObjectsAllPlans" : 3,
	"nscannedAllPlans" : 25,
	"nYields" : 0,
	"nChunkSkips" : 0,
	"millis" : 0,
	"server" : "dstorch-desktop:27017",
	"filterSet" : false
}

3.0.0-rc6

> t.find({a: 20}).explain("executionStats")
{
...
	"executionStats" : {
		"executionSuccess" : true,
		"nReturned" : 1,
		"executionTimeMillis" : 0,
		"totalKeysExamined" : 1,
		"totalDocsExamined" : 1, // CORRECT: totalDocsExamined==nReturned
		"executionStages" : {
			"stage" : "FETCH",
			"nReturned" : 1,
			"executionTimeMillisEstimate" : 0,
			"works" : 2,
			"advanced" : 1,
			"needTime" : 0,
			"needFetch" : 0,
			"saveState" : 0,
			"restoreState" : 0,
			"isEOF" : 1,
			"invalidates" : 0,
			"docsExamined" : 1,
			"alreadyHasObj" : 0,
			"inputStage" : {
				"stage" : "IXSCAN",
				"nReturned" : 1,
				"executionTimeMillisEstimate" : 0,
				"works" : 2,
				"advanced" : 1,
				"needTime" : 0,
				"needFetch" : 0,
				"saveState" : 0,
				"restoreState" : 0,
				"isEOF" : 1,
				"invalidates" : 0,
				"keyPattern" : {
					"a" : 1
				},
				"indexName" : "a_1",
				"isMultiKey" : false,
				"direction" : "forward",
				"indexBounds" : {
					"a" : [
						"[20.0, 20.0]"
					]
				},
				"keysExamined" : 1,
				"dupsTested" : 0,
				"dupsDropped" : 0,
				"seenInvalidated" : 0,
				"matchTested" : 0
			}
		}
	},
...
}
> t.find({b: 1}).explain("executionStats")
{
...
	"executionStats" : {
		"executionSuccess" : true,
		"nReturned" : 100,
		"executionTimeMillis" : 2,
		"totalKeysExamined" : 100,
		"totalDocsExamined" : 100, // CORRECT: totalDocsExamined==nReturned
		"executionStages" : {
			"stage" : "FETCH",
			"nReturned" : 100,
			"executionTimeMillisEstimate" : 0,
			"works" : 101,
			"advanced" : 100,
			"needTime" : 0,
			"needFetch" : 0,
			"saveState" : 0,
			"restoreState" : 0,
			"isEOF" : 1,
			"invalidates" : 0,
			"docsExamined" : 100,
			"alreadyHasObj" : 0,
			"inputStage" : {
				"stage" : "IXSCAN",
				"nReturned" : 100,
				"executionTimeMillisEstimate" : 0,
				"works" : 100,
				"advanced" : 100,
				"needTime" : 0,
				"needFetch" : 0,
				"saveState" : 0,
				"restoreState" : 0,
				"isEOF" : 1,
				"invalidates" : 0,
				"keyPattern" : {
					"b" : 1
				},
				"indexName" : "b_1",
				"isMultiKey" : false,
				"direction" : "forward",
				"indexBounds" : {
					"b" : [
						"[1.0, 1.0]"
					]
				},
				"keysExamined" : 100,
				"dupsTested" : 0,
				"dupsDropped" : 0,
				"seenInvalidated" : 0,
				"matchTested" : 0
			}
		}
	},
...
}
> db.adminCommand({setParameter: 1, internalQueryForceIntersectionPlans: true})
{ "was" : false, "ok" : 1 }
> t.find({a: 20, b: 1}).explain("executionStats")
{
...
	"executionStats" : {
		"executionSuccess" : true,
		"nReturned" : 1,
		"executionTimeMillis" : 2,
		"totalKeysExamined" : 22,
		"totalDocsExamined" : 1, // CORRECT: totalDocsExamined==nReturned
		"executionStages" : {
			"stage" : "FETCH",
			"nReturned" : 1,
			"executionTimeMillisEstimate" : 0,
			"works" : 23,
			"advanced" : 1,
			"needTime" : 21,
			"needFetch" : 0,
			"saveState" : 0,
			"restoreState" : 0,
			"isEOF" : 1,
			"invalidates" : 0,
			"docsExamined" : 1,
			"alreadyHasObj" : 0,
			"inputStage" : {
				"stage" : "KEEP_MUTATIONS",
				"nReturned" : 1,
				"executionTimeMillisEstimate" : 0,
				"works" : 23,
				"advanced" : 1,
				"needTime" : 21,
				"needFetch" : 0,
				"saveState" : 0,
				"restoreState" : 0,
				"isEOF" : 1,
				"invalidates" : 0,
				"inputStage" : {
					"stage" : "AND_SORTED",
					"nReturned" : 1,
					"executionTimeMillisEstimate" : 0,
					"works" : 23,
					"advanced" : 1,
					"needTime" : 21,
					"needFetch" : 0,
					"saveState" : 0,
					"restoreState" : 0,
					"isEOF" : 1,
					"invalidates" : 0,
					"flagged" : 0,
					"matchTested" : 0,
					"failedAnd_0" : 0,
					"failedAnd_1" : 0,
					"inputStages" : [
						{
							"stage" : "IXSCAN",
							"nReturned" : 1,
							"executionTimeMillisEstimate" : 0,
							"works" : 2,
							"advanced" : 1,
							"needTime" : 0,
							"needFetch" : 0,
							"saveState" : 0,
							"restoreState" : 0,
							"isEOF" : 1,
							"invalidates" : 0,
							"keyPattern" : {
								"a" : 1
							},
							"indexName" : "a_1",
							"isMultiKey" : false,
							"direction" : "forward",
							"indexBounds" : {
								"a" : [
									"[20.0, 20.0]"
								]
							},
							"keysExamined" : 1,
							"dupsTested" : 0,
							"dupsDropped" : 0,
							"seenInvalidated" : 0,
							"matchTested" : 0
						},
						{
							"stage" : "IXSCAN",
							"nReturned" : 21,
							"executionTimeMillisEstimate" : 0,
							"works" : 21,
							"advanced" : 21,
							"needTime" : 0,
							"needFetch" : 0,
							"saveState" : 0,
							"restoreState" : 0,
							"isEOF" : 0,
							"invalidates" : 0,
							"keyPattern" : {
								"b" : 1
							},
							"indexName" : "b_1",
							"isMultiKey" : false,
							"direction" : "forward",
							"indexBounds" : {
								"b" : [
									"[1.0, 1.0]"
								]
							},
							"keysExamined" : 21,
							"dupsTested" : 0,
							"dupsDropped" : 0,
							"seenInvalidated" : 0,
							"matchTested" : 0
						}
					]
				}
			}
		}
	},
...
}

Comment by Asya Kamsky [ 19/Feb/14 ]

since it's not a covered query nscannedObjects lower bound is n.

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