[SERVER-802] Query optimization is doing a big scan for searches involves $in Created: 22/Mar/10  Updated: 12/Jul/16  Resolved: 16/Jul/10

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

Type: Bug Priority: Major - P3
Reporter: Alex Dong Assignee: Aaron Staple
Resolution: Done Votes: 1
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

Mac OSX 10.5.8
MongoDB: 1.3.3


Attachments: File index_check8.js    
Issue Links:
Depends
depends on SERVER-371 optimize compound index multiple ranges Closed
depends on SERVER-1026 check $in speed Closed
Participants:

 Description   

As described in http://bit.ly/bURxwn

> db.hits.findOne()

{ "_id":ObjectId("4ba5a0409a59dc523d0a81dc"),
"date": 1510,
"user": 21176417,
"word": 1,
"list": [994953325, 2000523166]
}
> db.hits.count()
8663354
> print(db.hits.totalIndexSize())
1279240064
> db.hits.find({d: {$gte:1500, $lte:1510}, w: {$in:[275, 317]} }).explain()

{
"cursor" : "BtreeCursor d_1_w_1_u_1",
"startKey" : {
"d" : 1500,
"w" : 275,
"u" :

{ "$minElement" : 1 }


},
"endKey" : {
"d" : 1510,
"w" : 317,
"u" :

{ "$maxElement" : 1 }


},
"nscanned" : 2866468,
"n" : 5033,
"millis" : 44465,
"allPlans" : [
{
"cursor" : "BtreeCursor d_1_w_1_u_1",
"startKey" : {
"d" : 1500,
"w" : 275,
"u" :

{ "$minElement" : 1 }


},
"endKey" : {
"d" : 1510,
"w" : 317,
"u" :

{ "$maxElement" : 1 }


}
},
{
"cursor" : "BasicCursor",
"startKey" : {
},
"endKey" : {
}
}
]
}

Eliot replies: right now its not looking all all the sub-regions, just going from the front to back we can optimize at some point



 Comments   
Comment by auto [ 16/Jul/10 ]

Author:

{'login': 'astaple', 'name': 'Aaron', 'email': 'aaron@10gen.com'}

Message: SERVER-802 update explain format
http://github.com/mongodb/mongo/commit/94fab2e73fcdf50033b245ba7a9dd0174f253c27

Comment by auto [ 16/Jul/10 ]

Author:

{'login': 'astaple', 'name': 'Aaron', 'email': 'aaron@10gen.com'}

Message: SERVER-802 range / in skipping
http://github.com/mongodb/mongo/commit/5371a5e44870d111c730371b9b8aaaec36b27b1d

Comment by Aaron Staple [ 22/Mar/10 ]

We'll fix this, but for now you can get better index bounds if the $in key is first in your index – eg an index on

{w:1,d:1}
Comment by Alex Dong [ 22/Mar/10 ]

Here is a test case for this case. Thanks a lot.

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