[SERVER-6200] some $or queries not optimized properly Created: 25/Jun/12  Updated: 07/Mar/14  Resolved: 26/Feb/13

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

Type: Bug Priority: Major - P3
Reporter: Björn Karge Assignee: Aaron Staple
Resolution: Duplicate Votes: 1
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

all


Attachments: File queryutil.cpp.diff    
Issue Links:
Duplicate
duplicates SERVER-8743 add the ability for a query to match ... Closed
Operating System: ALL
Participants:

 Description   

The execution plan generated for certain $or queries is amendable:

> db.domain.ensureIndex(

{ key: 1 }

)
> db.domain.find( { $or: [
{ key:

{ $gte: 722156315770, $lt: 722156332548 }

}, { key:

{ $gte: 722156333548, $lt: 722156349325 }

}, { key:

{ $gte: 722156416434, $lt: 722156433211 }

},
{ key:

{ $in : [ -922337203685477, -230584300921369, -154023107256070, 115292150460684, 153726349067734, 461172215037663 ] }

}
] } ).explain();

===>

{ clauses: [
{ cursor: "BtreeCursor key_1", ..., indexBounds:

{ key: [ [ 722156315770, 722156332548 ] ] }

},
{ cursor: "BtreeCursor key_1", ..., indexBounds:

{ key: [ [ 722156333548, 722156349325 ] ] }

},
{ cursor: "BtreeCursor key_1", ..., indexBounds:

{ key: [ [ 722156416434, 722156433211 ] ] }

},
{ cursor: "BtreeCursor key_1 multi", ..., indexBounds:

{ key: [ [ -922337203685477, -922337203685477 ], [ -230584300921369, -230584300921369 ], [ -154023107256070, -154023107256070 ], [ 115292150460684, 115292150460684 ], [ 153726349067734, 153726349067734 ], [ 461172215037663, 461172215037663 ] ] }

} ],
...
}

This is according to the docs but slow, nonetheless. I would expect the following plan

{ cursor: "BtreeCursor key_1 multi", ..., indexBounds:

{ key: [ [ -922337203685477, -922337203685477 ], [ -230584300921369, -230584300921369 ], [ -154023107256070, -154023107256070 ], [ 722156315770, 722156332548 ], [ 722156333548, 722156349325 ], [ 722156416434, 722156433211 ], [ 115292150460684, 115292150460684 ], [ 153726349067734, 153726349067734 ], [ 461172215037663, 461172215037663 ] ] }

}

Which would almost halve the execution time (measured against a modified mongod where I patched the optimizer).

Instead of modifying the optimizer, an alternative could also be to extend $in to accept ranges as well.



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

Hi Bjorn - I filed a ticket for this SERVER-8743.

Comment by Björn Karge [ 21/Feb/13 ]

Added the patch I mentioned on
https://groups.google.com/forum/?fromgroups=#!topic/mongodb-user/Trn4fF0JMdM

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