Does MongoDB stages should avoid FETCH when INDEX is matching?

Question

According to the docs, MongoDB should skip the FETCH stage when an index covers a query.

If I correctly understood, this sentences explain this behavior:

Covered Queries When an index covers a query, MongoDB can both match the query conditions and return the results using only the index keys; i.e. MongoDB does not need to examine documents from the collection to return the results.

When an index covers a query, the explain result has an IXSCAN stage that is not a descendant of a FETCH stage, and in the executionStats, the totalDocsExamined is 0.

In earlier versions of MongoDB, cursor.explain() returned the indexOnly field to indicate whether the index covered a query. (https://docs.mongodb.com/manual/reference/explain-results/)

And this

With this in place, the query takes less than 2 Ms. Because the index ‘covered’ the query, MongoDB was able to match the query conditions and return the results using only the index keys; without even needing to examine documents from the collection to return the results. (if you see an IXSCAN stage that is not a child of a FETCH stage, in the execution plan then the index ‘covered’ the query.) (https://studio3t.com/knowledge-base/articles/mongodb-index-strategy/)

But in a test scenario it doesn't occur:

Example to test:

    db.Test.insert({"Field1":"data on field1: 1","Field2":"data on field2: 1"});
    db.Test.insert({"Field1":"data on field1: 2","Field2":"data on field2: 2"});
    db.Test.insert({"Field1":"data on field1: 3","Field2":"data on field2: 3"});
    db.Test.insert({"Field1":"data on field1: 4","Field2":"data on field2: 4"});
    db.Test.insert({"Field1":"data on field1: 5","Field2":"data on field2: 5"});
    db.Test.insert({"Field1":"data on field1: 6","Field2":"data on field2: 6"});

After I created an index to Field2.

db.Test.createIndex({"Field2":1})

And then I query the collection:

db.Test.find({"Field2":"data on field2: 5"}).explain("executionStats");

I expected a stage IDXSCAN that isn't child of a FETCH stage. But the output is like this:

[...]
"winningPlan" : {
            "stage" : "FETCH",
            "inputStage" : {
                "stage" : "IXSCAN",
                "keyPattern" : {
                    "Campo2" : 1.0
                },
                "indexName" : "Field2_1",
                "isMultiKey" : false,
                "multiKeyPaths" : {
                    "Campo2" : []
                },
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "forward",
                "indexBounds" : {
                    "Field2" : [ 
                        "[\"data on field2: 5", \"data on field2: 5\"]"
                    ]
                }
            }
        },
[...]

There are two stages: one "stage" : "FETCH", and its child "stage" : "IXSCAN",.

Can anyone explain what I am misunderstanding?

*** ABOUT PROJECTION

When running query with projection

"winningPlan" : {
            "stage" : "PROJECTION",
            "transformBy" : {
                "Campo2" : 1.0
            },
            "inputStage" : {
                "stage" : "FETCH",
                "inputStage" : {
                    "stage" : "IXSCAN",
                    "keyPattern" : {
                        "Field2" : 1.0
                    },
                    "indexName" : "Field2_1",
                    "isMultiKey" : false,
                    "multiKeyPaths" : {
                        "Campo2" : []
                    },
                    "isUnique" : false,
                    "isSparse" : false,
                    "isPartial" : false,
                    "indexVersion" : 2,
                    "direction" : "forward",
                    "indexBounds" : {
                        "Field2" : [ 
                            "[\"data on field2: 5", \"data on field2: 5\"]"
                        ]
                    }
                }
            }
        },

With Adam's answer: it worked!

I realized that projection shouldn't include "_id" to avoid FETCH.

Adam Harrison · Accepted Answer · 2019-10-24 20:35:54Z

6

Your query doesn't specify a projection, meaning that it will return all fields in the document. This means that the { Field2: 1 } index does not cover the query, since it only contains a single field.

The following query should be fully covered and should not have a FETCH stage. Note that the projection is explicitly excluding the _id field, as this will be included in the projection unless specified otherwise:

    db.Test.find(
      {"Field2":"data on field2: 5"},
      {"Field2" : 1, "_id" : 0 }
    ).explain("executionStats");

Output:

{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "foo.Test",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "Field2" : {
                "$eq" : "data on field2: 5"
            }
        },
        "winningPlan" : {
            "stage" : "PROJECTION",
            "transformBy" : {
                "Field2" : 1,
                "_id" : 0
            },
            "inputStage" : {
                "stage" : "IXSCAN",
                "keyPattern" : {
                    "Field2" : 1
                },
                "indexName" : "Field2_1",
                "isMultiKey" : false,
                "multiKeyPaths" : {
                    "Field2" : [ ]
                },
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "forward",
                "indexBounds" : {
                    "Field2" : [
                        "[\"data on field2: 5\", \"data on field2: 5\"]"
                    ]
                }
            }
        },
        "rejectedPlans" : [ ]
    },
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 1,
        "executionTimeMillis" : 1,
        "totalKeysExamined" : 1,
        "totalDocsExamined" : 0,
        "executionStages" : {
            "stage" : "PROJECTION",
            "nReturned" : 1,
            "executionTimeMillisEstimate" : 0,
            "works" : 2,
            "advanced" : 1,
            "needTime" : 0,
            "needYield" : 0,
            "saveState" : 0,
            "restoreState" : 0,
            "isEOF" : 1,
            "invalidates" : 0,
            "transformBy" : {
                "Field2" : 1,
                "_id" : 0
            },
            "inputStage" : {
                "stage" : "IXSCAN",
                "nReturned" : 1,
                "executionTimeMillisEstimate" : 0,
                "works" : 2,
                "advanced" : 1,
                "needTime" : 0,
                "needYield" : 0,
                "saveState" : 0,
                "restoreState" : 0,
                "isEOF" : 1,
                "invalidates" : 0,
                "keyPattern" : {
                    "Field2" : 1
                },
                "indexName" : "Field2_1",
                "isMultiKey" : false,
                "multiKeyPaths" : {
                    "Field2" : [ ]
                },
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "forward",
                "indexBounds" : {
                    "Field2" : [
                        "[\"data on field2: 5\", \"data on field2: 5\"]"
                    ]
                },
                "keysExamined" : 1,
                "seeks" : 1,
                "dupsTested" : 0,
                "dupsDropped" : 0,
                "seenInvalidated" : 0
            }
        }
    },
    "serverInfo" : {
    ...
    },
    "ok" : 1,
    ...
}

edited Oct 24, 2019 at 20:35

answered Oct 24, 2019 at 20:04

Adam Harrison

3,4212 gold badges20 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Rafael Over a year ago

I thought that, but not. The output when query has projection: "stage" : "PROJECTION", ... "stage" : "FETCH", // [NOT CHILD] "stage" : "IXSCAN", // [CHILD]

Rafael Over a year ago

are you using any collations?

barrypicker Over a year ago

@Rafael, did you run the same query with 'executionStats' that Adam provided?

Rafael Over a year ago

Thanks... it worked I realized that projection shouldn't include "_id" to avoid FETCH.

Rafael Over a year ago

Now it becomes clearly... the index hasn't "_id", then it won't be covered.

|

Collectives™ on Stack Overflow

Does MongoDB stages should avoid FETCH when INDEX is matching?

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related