-
Type: Bug
-
Resolution: Duplicate
-
Priority: Major - P3
-
None
-
Affects Version/s: None
-
Component/s: Querying
-
ALL
Firstly, Assume my schema is the below:
{ "_id" : ObjectId("51548ccdc77b96c10579b54f"), "key" : { "val" : 557 }, "list" : [ { "A" : 696, "B" : 688, "C" : 743 }, { "A" : 743, "B" : 696, "C" : 688 }, { "A" : 688, "B" : 743, "C" : 696 } ] }
Second, assume my indexes are the below:
db.test.ensureIndex(
{ "key.val" : 1 } );
db.test.ensureIndex(
);
db.test.ensureIndex(
);
Now consider the query below:
db.test.find( { "$or" : [ { "key.val" : 123456789} , { "list" : { "$elemMatch" : { "$or" : [ { "A" : 696} , { "B" : 696 } ] , "C" : 743}} } ] } )
What this is saying is find me all docs where key.val = 12345679 OR ( ( list.A = 696 OR list.B = 696) AND C = 743).
If I execute this query on my DB I get the following explain output.
> db.test.find( { "$or" : [ { "key.val" : 123456789} , { "list" : { "$elemMatch" : { "$or" : [ { "A" : 696} , { "B" : 696 } ] , "C" : 743}} } ] } ).explain() { "cursor" : "BasicCursor", "isMultiKey" : false, "n" : 4, "nscannedObjects" : 100000, "nscanned" : 100000, "nscannedObjectsAllPlans" : 100000, "nscannedAllPlans" : 100000, "scanAndOrder" : false, "indexOnly" : false, "nYields" : 1, "nChunkSkips" : 0, "millis" : 117, "indexBounds" : { }, "server" : "Oz-Olivo-MacBook-Pro.local:27017" }
The results show that this DOES NOT use an index. But does correctly return the 4 matching documents in my DB. At the very least, it should be using the val index for the first part of the OR if not all 3 indexes for the different OR clauses. Why?
The four matching documents are below:
> db.test.find( { "$or" : [ { "key.val" : 123456789} , { "list" : { "$elemMatch" : { "$or" : [ { "A" : 696} , { "B" : 696 } ] , "C" : 743}} } ] } ).pretty() { "_id" : ObjectId("51548ccdc77b96c10579b54f"), "key" : { "val" : 557 }, "list" : [ { "A" : 696, "B" : 688, "C" : 743 }, { "A" : 743, "B" : 696, "C" : 688 }, { "A" : 688, "B" : 743, "C" : 696 } ] } { "_id" : ObjectId("515599fc23a957b837c8a24d"), "key" : { "val" : 708 }, "list" : [ { "A" : 743, "B" : 647, "C" : 696 }, { "A" : 696, "B" : 743, "C" : 647 }, { "A" : 647, "B" : 696, "C" : 743 } ] } { "_id" : ObjectId("515599fc23a957b837c8ac95"), "key" : { "val" : 505 }, "list" : [ { "A" : 264, "B" : 696, "C" : 743 }, { "A" : 743, "B" : 264, "C" : 696 }, { "A" : 696, "B" : 743, "C" : 264 } ] } { "_id" : ObjectId("515599fc23a957b837c8b30a"), "key" : { "val" : 849 }, "list" : [ { "A" : 323, "B" : 696, "C" : 743 }, { "A" : 743, "B" : 323, "C" : 696 }, { "A" : 696, "B" : 743, "C" : 323 } ] }
Alternatively, I thought this query could be rewritten as
db.test.find({ "$or" : [ { "key.val" : 123456789} , { "list" : { "$elemMatch" : { "A" : 696 , "C" : 743 }}} , { "list" : { "$elemMatch" : { "B" : 696 , "C" :743 }}} ]})
What this is saying is find me all docs where key.val = 12345679 OR ( list.A = 696 AND C = 743 ) OR (list.B = 696 AND C = 743). Which should be semantically equivalent to the above.
When I run the explain on this query I get:
> db.test.find({ "$or" : [ { "key.val" : 123456789} , { "list" : { "$elemMatch" : { "A" : 696 , "C" : 743 }}} ,{ "list" : { "$elemMatch" : { "B" : 696 , "C" :743 }}} ]}).explain() { "clauses" : [ { "cursor" : "BtreeCursor key.val_1", "isMultiKey" : false, "n" : 0, "nscannedObjects" : 0, "nscanned" : 0, "nscannedObjectsAllPlans" : 0, "nscannedAllPlans" : 0, "scanAndOrder" : false, "indexOnly" : false, "nYields" : 0, "nChunkSkips" : 0, "millis" : 0, "indexBounds" : { "key.val" : [ [ 123456789, 123456789 ] ] } }, { "cursor" : "BtreeCursor list.A_1", "isMultiKey" : true, "n" : 4, "nscannedObjects" : 297, "nscanned" : 297, "nscannedObjectsAllPlans" : 594, "nscannedAllPlans" : 594, "scanAndOrder" : false, "indexOnly" : false, "nYields" : 0, "nChunkSkips" : 0, "millis" : 1, "indexBounds" : { "list.A" : [ [ 696, 696 ] ] } }, { "cursor" : "BtreeCursor list.B_1", "isMultiKey" : true, "n" : 0, "nscannedObjects" : 297, "nscanned" : 297, "nscannedObjectsAllPlans" : 594, "nscannedAllPlans" : 594, "scanAndOrder" : false, "indexOnly" : false, "nYields" : 0, "nChunkSkips" : 0, "millis" : 1, "indexBounds" : { "list.B" : [ [ 696, 696 ] ] } } ], "n" : 4, "nscannedObjects" : 594, "nscanned" : 594, "nscannedObjectsAllPlans" : 1188, "nscannedAllPlans" : 1188, "millis" : 3, "server" : "Oz-Olivo-MacBook-Pro.local:27017" }
This DOES use the indexes and finds 4 matches.
Why does query 1 not use any index while query 2 does?
I've uploaded my initial data load script in case it can be of any help. You may have to change the values for A, B, and C in the queries to find anything reasonable though, as the values are randomly generated
- is related to
-
SERVER-3327 use indexes for nested $or clauses
- Closed