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

Query for null filters before index scan

XMLWordPrintableJSON

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

      If I create an index: 

      { a: 1, b: 1 }

      And then perform query like so:

      { a: 'something', b: null }

      or

      { a: 'something', b: { $exists: false }

      My query plan looks like this:

      {
	"queryPlanner" : {
		...
		"winningPlan" : {
			"stage" : "FETCH",
			"filter" : {
				"b" : {
					"$eq" : null
				}
			},
			"inputStage" : {
				"stage" : "IXSCAN",
				"keyPattern" : {
					"a" : 1,
					"b" : 1
				},
				"indexName" : "a_1_b_1",
				"isMultiKey" : false,
				"direction" : "forward",
				"indexBounds" : {
					"a" : [
						"['something', 'something']"
					],
					"b" : [
						"[\"null\", \"null\"]"
					]
				}
			}
		},
		...
	},
	...
}

      This performs badly, I'm assuming because of the filter on the initial fetch. If I remove that by updating all my records with

      { b: null }

      , to have

      { b: "null" }

      (note the string) and now query for the string null rather than actual null, the plan looks like this.

      {
	"queryPlanner" : {
		...
		"winningPlan" : {
			"stage" : "FETCH",
			"inputStage" : {
				"stage" : "IXSCAN",
				"keyPattern" : {
					"a" : 1,
					"b" : 1
				},
				"indexName" : "a_1_b_1",
				"isMultiKey" : false,
				"direction" : "forward",
				"indexBounds" : {
					"a" : [
						"[\"something\", \"something\"]"
					],
					"b" : [
						"[\"null\", \"null\"]"
					]
				}
			}
		},
		...
	},
	...
}

      The filter is missing and the query now performs as expected.

      Show
      If I create an index: { a: 1, b: 1 } And then perform query like so: { a: 'something' , b: null } or { a: 'something' , b: { $exists: false } My query plan looks like this: { "queryPlanner" : { ... "winningPlan" : { "stage" : "FETCH" , "filter" : { "b" : { "$eq" : null } }, "inputStage" : { "stage" : "IXSCAN" , "keyPattern" : { "a" : 1, "b" : 1 }, "indexName" : "a_1_b_1" , "isMultiKey" : false , "direction" : "forward" , "indexBounds" : { "a" : [ "[ 'something' , 'something' ]" ], "b" : [ "[\" null \ ", \" null \ "]" ] } } }, ... }, ... } This performs badly, I'm assuming because of the filter on the initial fetch. If I remove that by updating all my records with { b: null } , to have { b: "null" } (note the string) and now query for the string null rather than actual null, the plan looks like this. { "queryPlanner" : { ... "winningPlan" : { "stage" : "FETCH" , "inputStage" : { "stage" : "IXSCAN" , "keyPattern" : { "a" : 1, "b" : 1 }, "indexName" : "a_1_b_1" , "isMultiKey" : false , "direction" : "forward" , "indexBounds" : { "a" : [ "[\" something\ ", \" something\ "]" ], "b" : [ "[\" null \ ", \" null \ "]" ] } } }, ... }, ... } The filter is missing and the query now performs as expected.

      Querying for null values looks like it is filtering records with fetch before hitting the index, even when the null field is part of the index.

      This stack overflow question seems to be related:

      http://stackoverflow.com/questions/30319805/mongodb-extremely-slow-at-counting-null-values-or-exists-false

            Assignee:
            ramon.fernandez@mongodb.com Ramon Fernandez Marina
            Reporter:
            matt@trak.io Matthew Spence
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

              Created:
              Updated:
              Resolved: