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

Sparse index is not used to filter {field: {$ne: null}} queries

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major - P3
    • Resolution: Works as Designed
    • Affects Version/s: 2.6.0-rc2
    • Fix Version/s: None
    • Component/s: Querying
    • Labels:
    • Operating System:
      ALL
    • Steps To Reproduce:
      Hide

      > db.foo.drop()
      true
      > db.foo.ensureIndex({bar: 1}, {sparse: 1})
      {
      	"createdCollectionAutomatically" : true,
      	"numIndexesBefore" : 1,
      	"numIndexesAfter" : 2,
      	"ok" : 1
      }
      > db.foo.find({bar: {$ne: null}}).explain()
      {
      	"cursor" : "BasicCursor",
      	"isMultiKey" : false,
      	"n" : 0,
      	"nscannedObjects" : 0,
      	"nscanned" : 0,
      	"nscannedObjectsAllPlans" : 0,
      	"nscannedAllPlans" : 0,
      	"scanAndOrder" : false,
      	"indexOnly" : false,
      	"nYields" : 0,
      	"nChunkSkips" : 0,
      	"millis" : 0,
      	"server" : "lucien.lic.songza.com:27017",
      	"filterSet" : false
      }
      > db.foo.find({bar: {$exists: true}}).explain()
      {
      	"cursor" : "BtreeCursor bar_1",
      	"isMultiKey" : false,
      	"n" : 0,
      	"nscannedObjects" : 0,
      	"nscanned" : 0,
      	"nscannedObjectsAllPlans" : 0,
      	"nscannedAllPlans" : 0,
      	"scanAndOrder" : false,
      	"indexOnly" : false,
      	"nYields" : 0,
      	"nChunkSkips" : 0,
      	"millis" : 0,
      	"indexBounds" : {
      		"bar" : [
      			[
      				{
      					"$minElement" : 1
      				},
      				{
      					"$maxElement" : 1
      				}
      			]
      		]
      	},
      	"server" : "lucien.lic.songza.com:27017",
      	"filterSet" : false
      }

      Show
      > db.foo.drop() true > db.foo.ensureIndex({bar: 1}, {sparse: 1}) { "createdCollectionAutomatically" : true, "numIndexesBefore" : 1, "numIndexesAfter" : 2, "ok" : 1 } > db.foo.find({bar: {$ne: null}}).explain() { "cursor" : "BasicCursor", "isMultiKey" : false, "n" : 0, "nscannedObjects" : 0, "nscanned" : 0, "nscannedObjectsAllPlans" : 0, "nscannedAllPlans" : 0, "scanAndOrder" : false, "indexOnly" : false, "nYields" : 0, "nChunkSkips" : 0, "millis" : 0, "server" : "lucien.lic.songza.com:27017", "filterSet" : false } > db.foo.find({bar: {$exists: true}}).explain() { "cursor" : "BtreeCursor bar_1", "isMultiKey" : false, "n" : 0, "nscannedObjects" : 0, "nscanned" : 0, "nscannedObjectsAllPlans" : 0, "nscannedAllPlans" : 0, "scanAndOrder" : false, "indexOnly" : false, "nYields" : 0, "nChunkSkips" : 0, "millis" : 0, "indexBounds" : { "bar" : [ [ { "$minElement" : 1 }, { "$maxElement" : 1 } ] ] }, "server" : "lucien.lic.songza.com:27017", "filterSet" : false }

      Description

      In previous version of mongo a sparse index on a field could not be used to find those documents that only had the indexed field. Take the following collection:

      db.foo.ensureIndex({bar: 1}, {sparse: 1})

      If one were to naively write a query to find all documents that had the "bar" property one would start with:

      db.foo.find({bar: {$exists: true}})

      In mongo 2.4 and below this particular query would result in a full collection scan with a BasicCursor. For an application in which "bar" would never be null this particular query could be rewritten as:

      db.foo.find({bar: {$ne: null}})

      This query would use the sparse index. It appears that while mongo 2.5 can now correctly use the sparse index for the $exists: true query it will fail to use any index for the {$ne: null} variety unless an explicit hint is given.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              david.storch David Storch
              Reporter:
              michael@songza.com Michael Henson
              Participants:
              Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: