[SERVER-8753] Query optimizer doesn't use hashed key in a query with sorting on a different indexed value Created: 27/Feb/13  Updated: 07/Mar/14  Resolved: 27/Feb/13

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

Type: Bug Priority: Major - P3
Reporter: Thomas Adam Assignee: Unassigned
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
duplicates SERVER-3071 Index Intersection Closed
Related
Operating System: ALL
Participants:

 Description   

Hi,

if I execute a query like this:

db.entries.find(

{"u.$id":ObjectId("5124aa20dfee8fb462469370")}

).sort({_id:1})

leads to a full table scan, because he don't use my hashed index on "u.$id".
If I execute with .hint("u.$id_hashed") it works like expected.

See discussion here: https://groups.google.com/d/topic/mongodb-user/ngxI9vkv8Y4/discussion



 Comments   
Comment by Aaron Staple [ 27/Feb/13 ]

Current specced query optimizer behavior is that a non btree index cursor will only be considered for a query if there are no candidate btree cursors. This is described in SERVER-4531 (that ticket predates hash indexes and just describes geo indexes). In both of the examples above, there is a candidate btree cursor available causing the hash index to be ignored.

In the first example the _id:1 index is a candidate btree index, preventing use of a hashed index on 'u.$id'.
In the second example the y:1 index is a candidate btree index, preventing use of the hashed index on x:1

I filed SERVER-8768 to represent adding query optimizer support for speculatively evaluating btree and non btree index cursors simultaneously.

Comment by Asya Kamsky [ 27/Feb/13 ]

I was able to reproduce this without sharding involved.
Create hashed index on x and unhashed index on y:

> db.idx.find({x:5}).sort({y:1}).hint({x:'hashed'}).explain(true)
{
	"cursor" : "BtreeCursor x_hashed",
	"isMultiKey" : false,
	"n" : 10,
	"nscannedObjects" : 10,
	"nscanned" : 10,
	"nscannedObjectsAllPlans" : 10,
	"nscannedAllPlans" : 10,
	"scanAndOrder" : true,
	"indexOnly" : false,
	"nYields" : 0,
	"nChunkSkips" : 0,
	"millis" : 0,
	"indexBounds" : {
		"x" : [
			[
				NumberLong("4470791281878691347"),
				NumberLong("4470791281878691347")
			]
		]
	},
	"allPlans" : [
		{
			"cursor" : "BtreeCursor x_hashed",
			"n" : 10,
			"nscannedObjects" : 10,
			"nscanned" : 10,
			"indexBounds" : {
				"x" : [
					[
						NumberLong("4470791281878691347"),
						NumberLong("4470791281878691347")
					]
				]
			}
		}
	],
	"server" : "asyasmacbook.local:27017"
}
> db.idx.find({x:5}).sort({y:1}).explain(true)
{
	"cursor" : "BtreeCursor y_1",
	"isMultiKey" : false,
	"n" : 10,
	"nscannedObjects" : 100,
	"nscanned" : 100,
	"nscannedObjectsAllPlans" : 200,
	"nscannedAllPlans" : 200,
	"scanAndOrder" : false,
	"indexOnly" : false,
	"nYields" : 0,
	"nChunkSkips" : 0,
	"millis" : 0,
	"indexBounds" : {
		"y" : [
			[
				{
					"$minElement" : 1
				},
				{
					"$maxElement" : 1
				}
			]
		]
	},
	"allPlans" : [
		{
			"cursor" : "BtreeCursor y_1",
			"n" : 10,
			"nscannedObjects" : 100,
			"nscanned" : 100,
			"indexBounds" : {
				"y" : [
					[
						{
							"$minElement" : 1
						},
						{
							"$maxElement" : 1
						}
					]
				]
			}
		},
		{
			"cursor" : "BasicCursor",
			"n" : 10,
			"nscannedObjects" : 100,
			"nscanned" : 100,
			"indexBounds" : {
				
			}
		}
	],
	"oldPlan" : {
		"cursor" : "BtreeCursor y_1",
		"indexBounds" : {
			"y" : [
				[
					{
						"$minElement" : 1
					},
					{
						"$maxElement" : 1
					}
				]
			]
		}
	},
	"server" : "asyasmacbook.local:27017"
}

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