[SERVER-801] query optimizer should consider full b-tree scans for certain queries Created: 21/Mar/10  Updated: 14/Apr/16  Resolved: 27/Jan/15

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

Type: Improvement Priority: Minor - P4
Reporter: Dwight Merriman Assignee: Unassigned
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Related
is related to SERVER-2109 Query optimizer should pick plans tha... Closed
Backwards Compatibility: Fully Compatible
Participants:

 Description   

on a query such as :

> t.find(

{ q : /.*cde.*/ }

).explain()

and other similar things, the query optimizer should consider using an index on

{ q : 1 }

if it exists. the entire index would have to be scanned, but that will in some cases still be much faster than a table scan. (this is assuming a "covered index" approach - that iwll have to be verified too)

i think a hint works with the above now?



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

2.4 and 2.6 versions will answer this query using an index scan, e.g.:

> db.version()
2.4.11
> t.ensureIndex({q: 1})
> t.find( { q : /.*cde.*/ } ).explain()
{
	"cursor" : "BtreeCursor q_1 multi",
	"isMultiKey" : false,
	"n" : 0,
	"nscannedObjects" : 0,
	"nscanned" : 0,
	"nscannedObjectsAllPlans" : 0,
	"nscannedAllPlans" : 0,
	"scanAndOrder" : false,
	"indexOnly" : false,
	"nYields" : 0,
	"nChunkSkips" : 0,
	"millis" : 0,
	"indexBounds" : {
		"q" : [
			[
				"",
				{
 
				}
			],
			[
				/.*cde.*/,
				/.*cde.*/
			]
		]
	},
	"server" : "MacBook-Pro:27017"
}

With a projection, the plan will be covered:

> t.find( { q : /.*cde.*/ }, { _id: 0, q: 1 } ).explain()
{
	"cursor" : "BtreeCursor q_1 multi",
	"isMultiKey" : false,
	"n" : 0,
	"nscannedObjects" : 0,
	"nscanned" : 0,
	"nscannedObjectsAllPlans" : 0,
	"nscannedAllPlans" : 0,
	"scanAndOrder" : false,
	"indexOnly" : true, // Indicates that the plan is covered.
	"nYields" : 0,
	"nChunkSkips" : 0,
	"millis" : 0,
	"indexBounds" : {
		"q" : [
			[
				"",
				{
 
				}
			],
			[
				/.*cde.*/,
				/.*cde.*/
			]
		]
	},
	"server" : "MacBook-Pro:27017"
}

Generated at Thu Feb 08 02:55:11 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.