[SERVER-6542] query optimizer does not consider query plans for $and:[$or:[conditions]] Created: 20/Jul/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: Arie Grapa Assignee: Aaron Staple
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

any


Issue Links:
Duplicate
duplicates SERVER-3327 use indexes for nested $or clauses Closed
Operating System: ALL
Participants:

 Description   

I have 2 sets of "ored" conditions because I need all rows that meet conditions A or B or C, and that also meet conditions D or E or F.
Since I can only have a single $or statement, I use this:
$or:[

{A}

,

{B}

,

{C}

],$and[{$or:[

{D}

,

{E}

,

{F}

]}]
This returns the correct results, and by the way, uses indexes on A, B and C and runs in parallel, which is fantastic (good job!!!).
However, if my indexes on D,E and F are even better choices than A,B and C, let's say they have better selectivity, the query optimizer completely ignores them as viable plans. Moreover, there is no way for me to give a hint to use them, because hint can only take a single index. Even if I had a combined index on D_E_F and passed it as a hint, it would not help because the query on E and on F would not be able to use it.

I think we need either a mechanism to specify multiple first level $ors (I know JS does not make it easy) or a smarter query optimizer that considers more alternatives, even if they are hidden inside an $and.



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

Hi Arie - This is SERVER-3327.

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