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

Query on compound multi-key indexes lost performance between 2.4.6 and 2.5.5-pre-

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major - P3
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 2.6.0-rc0
    • Component/s: Querying
    • Labels:
      None
    • Operating System:
      ALL

      Description

      Set up the environment using the following:

      for (var i = 0; i < 1000; i++) { db.foo.insert({x: {a: 'y', b: [i, 3*i, "wassup yo"]}}); }
      db.foo.ensureIndex({a:1, b:1})

      In 2.4.6, performant behavior:

      > db.foo.find({'a': 'y', 'b': 244}).explain()
      {
      	"cursor" : "BtreeCursor a_1_b_1",
      	"isMultiKey" : true,
      	"n" : 2,
      	"nscannedObjects" : 2,
      	"nscanned" : 2,
      	"nscannedObjectsAllPlans" : 2,
      	"nscannedAllPlans" : 2,
      	"scanAndOrder" : false,
      	"indexOnly" : false,
      	"nYields" : 0,
      	"nChunkSkips" : 0,
      	"millis" : 0,
      	"indexBounds" : {
      		"a" : [
      			[
      				"y",
      				"y"
      			]
      		],
      		"b" : [
      			[
      				244,
      				244
      			]
      		]
      	},
      	"server" : "Kestrel.local:27017"
      }

      In 2.5.5-pre-, huge slowdown:

      > db.foo.find({'a': 'y', 'b': 244}).explain()
      {
      	"cursor" : "BtreeCursor a_1_b_1",
      	"isMultiKey" : true,
      	"n" : 1,
      	"nscannedObjects" : 1000,
      	"nscanned" : 2999,
      	"nscannedObjectsAllPlans" : 1099,
      	"nscannedAllPlans" : 3098,
      	"scanAndOrder" : false,
      	"indexOnly" : false,
      	"nYields" : 23,
      	"nChunkSkips" : 0,
      	"millis" : 7,
      	"indexBounds" : {
      		"a" : [
      			[
      				"y",
      				"y"
      			]
      		],
      		"b" : [
      			[
      				{
      					"$minElement" : 1
      				},
      				{
      					"$maxElement" : 1
      				}
      			]
      		]
      	},
      	"server" : "Kestrel.local:27017"
      }

      It's unclear which of these we should expect to see, based on SERVER-7959 and other incidents. However, the performance slowdown is going to surprise a lot of users. (Also, pretty sure that the functionality we use is encouraged by our docs/training.)

      However, not 100% sure if the 2.4.6 functionality lost results. Bears further investigation.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              david.storch David Storch
              Reporter:
              amalia.hawkins@10gen.com Amalia Hawkins
              Participants:
              Votes:
              0 Vote for this issue
              Watchers:
              8 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: