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

Different index behavior for 1 and -1 direction indexes

    • Type: Icon: Bug Bug
    • Resolution: Done
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: 2.4.6, 2.4.7
    • Component/s: Index Maintenance
    • Labels:
      None
    • ALL

      I have collection with about 4MM documents. There are two multikey indexes on two fields that are not present in all documents. The two indexes have the second field in a different direction.
      i.e.

      db.test_index.ensureIndex({"a.b" : 1, "a.c" : 1})
      db.test_index.ensureIndex({"a.b" : 1, "a.c" : -1})
      

      The result of doing a find, where the fields are null, against each index is dramatically different. (1 object scanned vs 1,055,153)

      > db.test_index.find({"a.b": null, "a.c": null }, {"a.b": 1, "a.c": 1 }).hint({"a.b": 1, "a.c": 1 }).limit(1).explain()
      {
      	"cursor" : "BtreeCursor a.b_1_a.c_1",
      	"isMultiKey" : true,
      	"n" : 1,
      	"nscannedObjects" : 1,
      	"nscanned" : 1,
      	"nscannedObjectsAllPlans" : 1,
      	"nscannedAllPlans" : 1,
      	"scanAndOrder" : false,
      	"indexOnly" : false,
      	"nYields" : 0,
      	"nChunkSkips" : 0,
      	"millis" : 0,
      	"indexBounds" : {
      		"a.b" : [
      			[
      				null,
      				null
      			]
      		],
      		"a.c" : [
      			[
      				{
      					"$minElement" : 1
      				},
      				{
      					"$maxElement" : 1
      				}
      			]
      		]
      	}
      }
      
      > db.test_index.find({"a.b": null, "a.c": null }, {"a.b": 1, "a.c": 1 }).hint({"a.b": 1, "a.c": -1 }).limit(1).explain()
      {
      	"cursor" : "BtreeCursor a.b_1_a.c_-1",
      	"isMultiKey" : true,
      	"n" : 1,
      	"nscannedObjects" : 1055153,
      	"nscanned" : 1055153,
      	"nscannedObjectsAllPlans" : 1055153,
      	"nscannedAllPlans" : 1055153,
      	"scanAndOrder" : false,
      	"indexOnly" : false,
      	"nYields" : 0,
      	"nChunkSkips" : 0,
      	"millis" : 177775,
      	"indexBounds" : {
      		"a.b" : [
      			[
      				null,
      				null
      			]
      		],
      		"a.c" : [
      			[
      				{
      					"$maxElement" : 1
      				},
      				{
      					"$minElement" : 1
      				}
      			]
      		]
      	}
      }
      

      The following is a short script to reproduce. After running this the 1 index will show one object scanned, while the -1 index will show two.

      db.test_index.ensureIndex({"a.b" : 1, "a.c" : 1})
      db.test_index.ensureIndex({"a.b" : 1, "a.c" : -1})
      db.test_index.insert(
      [
      {
      	"_id" : ObjectId("5198fb0acfe8202c49ab389d"),
      	"a" : [
      		{
      			"c" : ISODate("2013-08-17T22:14:40Z"),
      		},
      		{
      			"b" : ObjectId("5175284b50e71b43f29cd447"),
      			"c" : ISODate("2013-08-17T22:15:18Z")
      		}
      	]
      },
      { "_id" : ObjectId("4ec0cb4eb2a44b0a3bf6102d") },
      {
      	"_id" : ObjectId("4ec9f21100ad3b3ee3ca927a"),
      	"a" : [
      		{
      			"b" : ObjectId("4f6c377f00aa296ff90001f3"),
      			"c" : ISODate("2012-08-05T13:52:49Z")
      		}
      	]
      }
      ]
      )
      
      db.test_index.find({ "a.b" : null, "a.c" : null }, { "a.b" : 1, "a.c" : 1 }).hint({ "a.b" : 1, "a.c" : 1 }).limit(-1).explain()
      db.test_index.find({ "a.b" : null, "a.c" : null }, { "a.b" : 1, "a.c" : 1 }).hint({ "a.b" : 1, "a.c" : -1 }).limit(-1).explain()
      

            Assignee:
            david.storch@mongodb.com David Storch
            Reporter:
            cory.mintz@mongodb.com Cory Mintz
            Votes:
            0 Vote for this issue
            Watchers:
            8 Start watching this issue

              Created:
              Updated:
              Resolved: