[SERVER-20155] Query for null filters before index scan Created: 27/Aug/15  Updated: 10/Sep/15  Resolved: 10/Sep/15

Status: Closed
Project: Core Server
Component/s: Querying
Affects Version/s: 3.0.2
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Matthew Spence Assignee: Ramon Fernandez Marina
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
duplicates SERVER-18653 Answering "equality to null" predicat... Closed
Operating System: ALL
Steps To Reproduce:

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.

Participants:

 Description   

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



 Comments   
Comment by J Rassi [ 27/Aug/15 ]

This is indeed a dup of SERVER-18653. The index scan is happening "first", and each index key returned by the index scan is fetched before returning to the user. Index scans generated by "equality to null" predicates are slightly different from other equality predicates in that they generate INEXACT_FETCH bounds (which means that the predicate needs to be re-checked after the fetch); SERVER-18653 has an explanation as to why.

Comment by Ramon Fernandez Marina [ 27/Aug/15 ]

This does look expected as per SERVER-18653, but I can't quite wrap my head around it. Intuitively I would expect the index scan to happen first for performance reasons, but not everything in query is as intuitive as one would expect

jason.rassi, david.storch, can you confirm whether this is a dup of SERVER-18653 and therefore expected behavior?

Thanks,
Ramón.

Generated at Thu Feb 08 03:53:20 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.