[SERVER-3327] use indexes for nested $or clauses Created: 24/Jun/11  Updated: 12/Jul/16  Resolved: 23/Jan/14

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

Type: Improvement Priority: Major - P3
Reporter: Aaron Staple Assignee: hari.khalsa@10gen.com
Resolution: Done Votes: 16
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Depends
Duplicate
is duplicated by SERVER-6542 query optimizer does not consider que... Closed
is duplicated by SERVER-5338 Strange results with identical but di... Closed
Related
related to SERVER-9180 Nested $or queries seem to not use in... Closed
related to SERVER-3192 allow nesting $or within $and Closed
related to SERVER-8790 Introduce composable "stages" in quer... Closed
Participants:

 Comments   
Comment by hari.khalsa@10gen.com [ 23/Jan/14 ]

This falls out of index intersection.

For example, for the query

t.find({$and: [{$or:[{a:1}, {b:2}]}, {$or: [{c:3}, {d:4}]}]})

with indices over each of : a, b, c, d

we generate the plan

FETCH
---fetched = 1
---sortedByDiskLoc = 0
---getSort = []
---Child:
------KEEP_MUTATIONS
---------filter:
                $and
                    $or
                        a == 1.0
                        b == 2.0
                    $or
                        c == 3.0
                        d == 4.0
---------fetched = 0
---------sortedByDiskLoc = 0
---------getSort = []
---------Child:
------------AND_HASH
---------------fetched = 0
---------------sortedByDiskLoc = 0
---------------getSort = []
---------------Child 0:
---------------OR
------------------fetched = 0
------------------sortedByDiskLoc = 0
------------------getSort = []
------------------Child 0:
---------------------IXSCAN
------------------------keyPattern = { a: 1.0 }
------------------------direction = 1
------------------------bounds = field #0['a']: [1.0, 1.0]
------------------------fetched = 0
------------------------fetched = 0
------------------------sortedByDiskLoc = 1
------------------------getSort = [{ a: 1 }, ]
 
------------------Child 1:
---------------------IXSCAN
------------------------keyPattern = { b: 1.0 }
------------------------direction = 1
------------------------bounds = field #0['b']: [2.0, 2.0]
------------------------fetched = 0
------------------------fetched = 0
------------------------sortedByDiskLoc = 1
------------------------getSort = [{ b: 1 }, ]
 
---------------Child 1:
---------------OR
------------------fetched = 0
------------------sortedByDiskLoc = 0
------------------getSort = []
------------------Child 0:
---------------------IXSCAN
------------------------keyPattern = { c: 1.0 }
------------------------direction = 1
------------------------bounds = field #0['c']: [3.0, 3.0]
------------------------fetched = 0
------------------------fetched = 0
------------------------sortedByDiskLoc = 1
------------------------getSort = [{ c: 1 }, ]
 
------------------Child 1:
---------------------IXSCAN
------------------------keyPattern = { d: 1.0 }
------------------------direction = 1
------------------------bounds = field #0['d']: [4.0, 4.0]
------------------------fetched = 0
------------------------fetched = 0
------------------------sortedByDiskLoc = 1
------------------------getSort = [{ d: 1 }, ]

Comment by Grant Hall [ 22/Jan/14 ]

Hi Ian,

It has been a long time since this has been updated. Any news on this issue? We are experiencing this issue and would like to know what the status is.

Thanks!

Comment by Ian Whalen (Inactive) [ 21/Sep/12 ]

drapeko we are still intending on doing this, although we have not yet planned it for a specific upcoming release.

Comment by drapeko [ 21/Sep/12 ]

Are you guys still going to implement this?

Comment by Aaron Staple [ 25/Dec/11 ]

Hi Folks - We do plan to implement this, though I don't have any info on when that will happen other than what appears in the summary at the top of this ticket. I would like to note a couple of short term workarounds here in case they are useful for people.

Let's say A, B, C, and D are sub-queries. If you have a query like:
{$and:[{$or:[A,B]},{$or:[C,D]}]}

One workaround is to rewrite the query as follows:
{$or:[{$and:[A,C]},{$and:[A,D]},{$and:[B,C]},{$and:[B,D]}]}

Another workaround, which will allow you to utilize indexes for one of your $or's, is this:
{$or:

{[A,B]}

,{$and:[{$or:[C,D]}]}}

Comment by Nader Akhnoukh [ 15/Nov/11 ]

Hi everyone, is there a planned release for this? Support for nested ors within and is great and was needed by many people (#1089 && #3192), but is of little use in any real environment without indexes.

Comment by Thomas DeMille [ 14/Nov/11 ]

Hi, In my opinion this is a huge miss,

Consider an RDBMS if you couldn't write this fairly common query without inducing a full table scan:

Select * from foo where (A = 1 or B=2 ) and (C=1 or D=2)

It's a really common scenario.

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