[SERVER-4326] Inclusivity of index bounds is not expressed in explain output Created: 18/Nov/11  Updated: 11/Jul/16  Resolved: 14/Feb/14

Status: Closed
Project: Core Server
Component/s: Index Maintenance, Querying
Affects Version/s: None
Fix Version/s: 2.6.0-rc0

Type: Improvement Priority: Major - P3
Reporter: Aaron Staple Assignee: Benety Goh
Resolution: Done Votes: 0
Labels: explain, query_triage
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Backwards Compatibility: Fully Compatible
Participants:

 Description   

> c.ensureIndex({a:1})
> c.find({a:{$gt:5}})
> c.find({a:{$gt:5}}).explain()
{
	"cursor" : "BtreeCursor a_1",
	"nscanned" : 0,
	"nscannedObjects" : 0,
	"n" : 0,
	"millis" : 7,
	"nYields" : 0,
	"nChunkSkips" : 0,
	"isMultiKey" : false,
	"indexOnly" : false,
	"indexBounds" : {
		"a" : [
			[
				5,
				1.7976931348623157e+308
			]
		]
	}
}
> c.find({a:{$gte:5}}).explain()
{
	"cursor" : "BtreeCursor a_1",
	"nscanned" : 0,
	"nscannedObjects" : 0,
	"n" : 0,
	"millis" : 0,
	"nYields" : 0,
	"nChunkSkips" : 0,
	"isMultiKey" : false,
	"indexOnly" : false,
	"indexBounds" : {
		"a" : [
			[
				5,
				1.7976931348623157e+308
			]
		]
	}
}

With an exclusive bound our btree scanning implementation can avoid scanning every key with a=5, while with an inclusive bound we must scan every key with a=5. However, there is no indication of this from the explain output. I usually mention that this is the case when doing new hire training, and recently it was raised specifically as a usability issue.



 Comments   
Comment by Benety Goh [ 14/Feb/14 ]

added boundsVerbose to the stats in explain

Comment by Githook User [ 14/Feb/14 ]

Author:

{u'username': u'benety', u'name': u'Benety Goh', u'email': u'benety@mongodb.com'}

Message: SERVER-4326 added boundsVerbose to index scan stats in explain
Branch: master
https://github.com/mongodb/mongo/commit/ec49a75b61297d5e266394d978d1a822fc1a41e9

Comment by Asya Kamsky [ 18/Oct/13 ]

It seems that explain already shows this:

> db.c.find({},{_id:0})
{ "a" : 1 }
{ "a" : 2 }
{ "a" : 5 }
{ "a" : 5 }
{ "a" : 5 }
{ "a" : 5 }
{ "a" : 6 }
{ "a" : 7 }
Fetched 8 record(s) in 2ms
test@local(2.5.3-pre-) > db.c.find({a:{$gt:5}}).explain().nscanned
2
test@local(2.5.3-pre-) > db.c.find({a:{$gte:5}}).explain().nscanned
6

In case of $gt it scanned only documents > 5 (2 of them) in case of $gte it scanned 4 more.

So there is indication of actual number of things scanned. So the "real" issue is that "Inclusivity of index bounds in not expressed in explain().indexBounds field. The rest of output of explain is fine and good.

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