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

Inefficient index bounds applied to $or query

    • Type: Icon: Bug Bug
    • Resolution: Duplicate
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: 2.6.3, 2.6.4
    • Component/s: Querying
    • None
    • ALL
    • Hide

      Create one or more documents with three fields: a, b, c
      Create two indexes {a:1, b:1} and {a:1, c:1}
      Attempt query on a while including an $or on b and c.

      > db.or.find()
      { "_id" : ObjectId("5410beba0bae8df4a3526ffd"), "a" : 1, "b" : 1, "c" : 3 }
      > db.or.getIndexKeys()
      [ { "_id" : 1 }, { "a" : 1, "b" : 1 }, { "a" : 1, "c" : 1 } ]
      > db.or.find({a:1, $or:[{b:1},{c:2}]}).explain(true)
      {
      	"cursor" : "BtreeCursor a_1_b_1",
      	"isMultiKey" : false,
      	"n" : 1,
      	"nscannedObjects" : 1,
      	"nscanned" : 1,
      	"nscannedObjectsAllPlans" : 2,
      	"nscannedAllPlans" : 2,
      	"scanAndOrder" : false,
      	"indexOnly" : false,
      	"nYields" : 0,
      	"nChunkSkips" : 0,
      	"millis" : 0,
      	"indexBounds" : {
      		"a" : [
      			[
      				1,
      				1
      			]
      		],
      		"b" : [
      			[
      				{
      					"$minElement" : 1
      				},
      				{
      					"$maxElement" : 1
      				}
      			]
      		]
      	},
      	"allPlans" : [
      		{
      			"cursor" : "BtreeCursor a_1_b_1",
      			"isMultiKey" : false,
      			"n" : 1,
      			"nscannedObjects" : 1,
      			"nscanned" : 1,
      			"scanAndOrder" : false,
      			"indexOnly" : false,
      			"nChunkSkips" : 0,
      			"indexBounds" : {
      				"a" : [
      					[
      						1,
      						1
      					]
      				],
      				"b" : [
      					[
      						{
      							"$minElement" : 1
      						},
      						{
      							"$maxElement" : 1
      						}
      					]
      				]
      			}
      		},
      		{
      			"cursor" : "BtreeCursor a_1_c_1",
      			"isMultiKey" : false,
      			"n" : 1,
      			"nscannedObjects" : 1,
      			"nscanned" : 1,
      			"scanAndOrder" : false,
      			"indexOnly" : false,
      			"nChunkSkips" : 0,
      			"indexBounds" : {
      				"a" : [
      					[
      						1,
      						1
      					]
      				],
      				"c" : [
      					[
      						{
      							"$minElement" : 1
      						},
      						{
      							"$maxElement" : 1
      						}
      					]
      				]
      			}
      		}
      	],
      	"server" : XXX",
      	"filterSet" : false,
      	"stats" : {
      		"type" : "KEEP_MUTATIONS",
      		"works" : 3,
      		"yields" : 0,
      		"unyields" : 0,
      		"invalidates" : 0,
      		"advanced" : 1,
      		"needTime" : 0,
      		"needFetch" : 0,
      		"isEOF" : 1,
      		"children" : [
      			{
      				"type" : "FETCH",
      				"works" : 2,
      				"yields" : 0,
      				"unyields" : 0,
      				"invalidates" : 0,
      				"advanced" : 1,
      				"needTime" : 0,
      				"needFetch" : 0,
      				"isEOF" : 1,
      				"alreadyHasObj" : 0,
      				"forcedFetches" : 0,
      				"matchTested" : 1,
      				"children" : [
      					{
      						"type" : "IXSCAN",
      						"works" : 1,
      						"yields" : 0,
      						"unyields" : 0,
      						"invalidates" : 0,
      						"advanced" : 1,
      						"needTime" : 0,
      						"needFetch" : 0,
      						"isEOF" : 1,
      						"keyPattern" : "{ a: 1.0, b: 1.0 }",
      						"boundsVerbose" : "field #0['a']: [1.0, 1.0], field #1['b']: [MinKey, MaxKey]",
      						"isMultiKey" : 0,
      						"yieldMovedCursor" : 0,
      						"dupsTested" : 0,
      						"dupsDropped" : 0,
      						"seenInvalidated" : 0,
      						"matchTested" : 0,
      						"keysExamined" : 1,
      						"children" : [ ]
      					}
      				]
      			}
      		]
      	}
      }
      
      Show
      Create one or more documents with three fields: a, b, c Create two indexes {a:1, b:1} and {a:1, c:1} Attempt query on a while including an $or on b and c. > db.or.find() { "_id" : ObjectId( "5410beba0bae8df4a3526ffd" ), "a" : 1, "b" : 1, "c" : 3 } > db.or.getIndexKeys() [ { "_id" : 1 }, { "a" : 1, "b" : 1 }, { "a" : 1, "c" : 1 } ] > db.or.find({a:1, $or:[{b:1},{c:2}]}).explain( true ) { "cursor" : "BtreeCursor a_1_b_1" , "isMultiKey" : false , "n" : 1, "nscannedObjects" : 1, "nscanned" : 1, "nscannedObjectsAllPlans" : 2, "nscannedAllPlans" : 2, "scanAndOrder" : false , "indexOnly" : false , "nYields" : 0, "nChunkSkips" : 0, "millis" : 0, "indexBounds" : { "a" : [ [ 1, 1 ] ], "b" : [ [ { "$minElement" : 1 }, { "$maxElement" : 1 } ] ] }, "allPlans" : [ { "cursor" : "BtreeCursor a_1_b_1" , "isMultiKey" : false , "n" : 1, "nscannedObjects" : 1, "nscanned" : 1, "scanAndOrder" : false , "indexOnly" : false , "nChunkSkips" : 0, "indexBounds" : { "a" : [ [ 1, 1 ] ], "b" : [ [ { "$minElement" : 1 }, { "$maxElement" : 1 } ] ] } }, { "cursor" : "BtreeCursor a_1_c_1" , "isMultiKey" : false , "n" : 1, "nscannedObjects" : 1, "nscanned" : 1, "scanAndOrder" : false , "indexOnly" : false , "nChunkSkips" : 0, "indexBounds" : { "a" : [ [ 1, 1 ] ], "c" : [ [ { "$minElement" : 1 }, { "$maxElement" : 1 } ] ] } } ], "server" : XXX", "filterSet" : false , "stats" : { "type" : "KEEP_MUTATIONS" , "works" : 3, "yields" : 0, "unyields" : 0, "invalidates" : 0, "advanced" : 1, "needTime" : 0, "needFetch" : 0, "isEOF" : 1, "children" : [ { "type" : "FETCH" , "works" : 2, "yields" : 0, "unyields" : 0, "invalidates" : 0, "advanced" : 1, "needTime" : 0, "needFetch" : 0, "isEOF" : 1, "alreadyHasObj" : 0, "forcedFetches" : 0, "matchTested" : 1, "children" : [ { "type" : "IXSCAN" , "works" : 1, "yields" : 0, "unyields" : 0, "invalidates" : 0, "advanced" : 1, "needTime" : 0, "needFetch" : 0, "isEOF" : 1, "keyPattern" : "{ a: 1.0, b: 1.0 }" , "boundsVerbose" : "field #0[ 'a' ]: [1.0, 1.0], field #1[ 'b' ]: [MinKey, MaxKey]" , "isMultiKey" : 0, "yieldMovedCursor" : 0, "dupsTested" : 0, "dupsDropped" : 0, "seenInvalidated" : 0, "matchTested" : 0, "keysExamined" : 1, "children" : [ ] } ] } ] } }

      Previous versions of mongo seemed good about assembling index bounds that included fields external and internal to an $or clause. This does not currently appear to be the case for the relatively simple query structure:

      {a:1, $or: [

      {b:1}

      ,

      {c:1}

      ]}

            Assignee:
            Unassigned Unassigned
            Reporter:
            ericsedor Eric Sedor
            Votes:
            2 Vote for this issue
            Watchers:
            6 Start watching this issue

              Created:
              Updated:
              Resolved: