Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-16169

$elemMatch with nested $or doesn't use index on 2.4

    XMLWordPrintableJSON

Details

    • Icon: Bug Bug
    • Resolution: Won't Fix
    • Icon: Minor - P4 Minor - P4
    • None
    • 2.4.12
    • Querying
    • None
    • Fully Compatible
    • ALL

    Description

      It's technically possible to nest an $or under $elemMatch with the intent of finding array elements that match at least one criteria. However, in 2.4 the index is not used for that search. The new query engine on 2.6.5 handles the case as expected.

      Here is a repro from 2.4:

      db.ggg.drop()
      db.ggg.insert({party:[{a:5,b:10},{a:3,b:2}]})
      db.ggg.insert({party:[{a:5,b:10},{a:4,b:3}]})
      db.ggg.insert({party:[{a:5,b:0},{a:4,b:3}]})
      db.ggg.insert({party:[{a:1,b:0},{a:4,b:3}]})
      db.ggg.insert({party:[{a:0,b:1},{a:2,b:0}]})
      db.ggg.ensureIndex({"party.a":1,"party.b":1})

      Output from 2.4:

      > db.ggg.find({ "party": { $elemMatch: { $or: [ {  "a" : 5,  "b" : 10 },{  "a" : 3,  "b" : 2 } ]} } }).explain()
      {
      	"cursor" : "BasicCursor",
      	"isMultiKey" : false,
      	"n" : 2,
      	"nscannedObjects" : 5,
      	"nscanned" : 5,
      	"nscannedObjectsAllPlans" : 5,
      	"nscannedAllPlans" : 5,
      	"scanAndOrder" : false,
      	"indexOnly" : false,
      	"nYields" : 0,
      	"nChunkSkips" : 0,
      	"millis" : 0,
      	"indexBounds" : {
       
      	},
      	"server" : "AD-MAC10G.local:27017"
      }

      Output from 2.6 for comparison:

      db.ggg.find({ "party": { $elemMatch: { $or: [ {  "a" : 5,  "b" : 10 },{  "a" : 3,  "b" : 2 } ]} } }).explain()
      {
      	"clauses" : [
      		{
      			"cursor" : "BtreeCursor party.a_1_party.b_1",
      			"isMultiKey" : true,
      			"n" : 2,
      			"nscannedObjects" : 2,
      			"nscanned" : 2,
      			"scanAndOrder" : false,
      			"indexOnly" : false,
      			"nChunkSkips" : 0,
      			"indexBounds" : {
      				"party.a" : [
      					[
      						5,
      						5
      					]
      				],
      				"party.b" : [
      					[
      						10,
      						10
      					]
      				]
      			}
      		},
      		{
      			"cursor" : "BtreeCursor party.a_1_party.b_1",
      			"isMultiKey" : true,
      			"n" : 1,
      			"nscannedObjects" : 1,
      			"nscanned" : 1,
      			"scanAndOrder" : false,
      			"indexOnly" : false,
      			"nChunkSkips" : 0,
      			"indexBounds" : {
      				"party.a" : [
      					[
      						3,
      						3
      					]
      				],
      				"party.b" : [
      					[
      						2,
      						2
      					]
      				]
      			}
      		}
      	],
      	"cursor" : "QueryOptimizerCursor",
      	"n" : 2,
      	"nscannedObjects" : 3,
      	"nscanned" : 3,
      	"nscannedObjectsAllPlans" : 3,
      	"nscannedAllPlans" : 3,
      	"scanAndOrder" : false,
      	"nYields" : 0,
      	"nChunkSkips" : 0,
      	"millis" : 0,
      	"server" : "AD-MAC10G.local:37017",
      	"filterSet" : false
      }

      Attachments

        Activity

          People

            david.storch@mongodb.com David Storch
            alex.komyagin@mongodb.com Alexander Komyagin
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: