Why this query is not covered query?

Working with Data
,
1

My collection has an index {a:1, b:1}, however the query

db.test.find({b:2}, {a:1,_id:0}).sort({a:1})

is not coverd (totalDocsExamined is the total number of docs in this collection). below is execution plan output. I understand the ESR rule, but even in that case, both a and b are in the same index tree, and should be able to serve: scan first (sort for free) and filter.
Why the engine is instead fetching value of b from disk ?

"explainVersion" : "1",
	"queryPlanner" : {
		"namespace" : "test.test",
		"indexFilterSet" : false,
		"parsedQuery" : {
			"b" : {
				"$eq" : 2
			}
		},
		"maxIndexedOrSolutionsReached" : false,
		"maxIndexedAndSolutionsReached" : false,
		"maxScansToExplodeReached" : false,
		"winningPlan" : {
			"stage" : "PROJECTION_SIMPLE",
			"transformBy" : {
				"a" : 1,
				"_id" : 0
			},
			"inputStage" : {
				"stage" : "FETCH",
				"filter" : {
					"b" : {
						"$eq" : 2
					}
				},
				"inputStage" : {
					"stage" : "IXSCAN",
					"keyPattern" : {
						"a" : 1,
						"b" : 1
					},
					"indexName" : "a_1_b_1",
					"isMultiKey" : false,
					"multiKeyPaths" : {
						"a" : [ ],
						"b" : [ ]
					},
					"isUnique" : false,
					"isSparse" : false,
					"isPartial" : false,
					"indexVersion" : 2,
					"direction" : "forward",
					"indexBounds" : {
						"a" : [
							"[MinKey, MaxKey]"
						],
						"b" : [
							"[MinKey, MaxKey]"
						]
					}
				}
			}
		},
		"rejectedPlans" : [ ]
	},
	"executionStats" : {
		"executionSuccess" : true,
		"nReturned" : 1,
		"executionTimeMillis" : 0,
		"totalKeysExamined" : 6,
		"totalDocsExamined" : 6,
		"executionStages" : {
			"stage" : "PROJECTION_SIMPLE",
			"nReturned" : 1,
			"executionTimeMillisEstimate" : 0,
			"works" : 7,
			"advanced" : 1,
			"needTime" : 5,
			"needYield" : 0,
			"saveState" : 0,
			"restoreState" : 0,
			"isEOF" : 1,
			"transformBy" : {
				"a" : 1,
				"_id" : 0
			},
			"inputStage" : {
				"stage" : "FETCH",
				"filter" : {
					"b" : {
						"$eq" : 2
					}
				},
				"nReturned" : 1,
				"executionTimeMillisEstimate" : 0,
				"works" : 7,
				"advanced" : 1,
				"needTime" : 5,
				"needYield" : 0,
				"saveState" : 0,
				"restoreState" : 0,
				"isEOF" : 1,
				"docsExamined" : 6,
				"alreadyHasObj" : 0,
				"inputStage" : {
					"stage" : "IXSCAN",
					"nReturned" : 6,
					"executionTimeMillisEstimate" : 0,
					"works" : 7,
					"advanced" : 6,
					"needTime" : 0,
					"needYield" : 0,
					"saveState" : 0,
					"restoreState" : 0,
					"isEOF" : 1,
					"keyPattern" : {
						"a" : 1,
						"b" : 1
					},
					"indexName" : "a_1_b_1",
					"isMultiKey" : false,
					"multiKeyPaths" : {
						"a" : [ ],
						"b" : [ ]
					},
					"isUnique" : false,
					"isSparse" : false,
					"isPartial" : false,
					"indexVersion" : 2,
					"direction" : "forward",
					"indexBounds" : {
						"a" : [
							"[MinKey, MaxKey]"
						],
						"b" : [
							"[MinKey, MaxKey]"
						]
					},
					"keysExamined" : 6,
					"seeks" : 1,
					"dupsTested" : 0,
					"dupsDropped" : 0
				}
			}
		}
	},
	"command" : {
		"find" : "test",
		"filter" : {
			"b" : 2
		},
		"sort" : {
			"a" : 1
		},
		"projection" : {
			"a" : 1,
			"_id" : 0
		},
		"$db" : "test"
	},
3

By the ESR rule your key pattern

does not match the query. Your Equality {b:2} is on b while your Sort is on a and your key pattern list a before b.

To make it covered, I think the index would then need to be {b:1,a:1}.

2 Likes
4

Yes i got that part. I use a:1 b:1 intentionally.

Though the equal comes after sort, both fields are in the same index. In that case i suppose mongo should be able to fetch value of a directly from index. But why its using disk instead?

Any reason behind this or its just not imolemented yet?

5

FETCH in explain plan does not mean read from disk. It means that the server cannot take the decision only with the index. The storage engine will read the document from disk only if it is not currently in RAM.

Why? I am not too sure and you should not worry about why the server is doing the wrong thing with the wrong index.

With the appropriate index b:1,a:1 you should get PROJECTION_COVERED with IXSCAN.

6

i think this is the key point. Probably it’s just how the logic is defined.