1

I have an index on field f1. {f1:1}

Running an explain on the following query

db.coll.find()

says that the winning plan is a CollScan (expected)

However, for db.coll.find().sort({f1:1})

the explain says

"stage" : "FETCH",
            "inputStage" : {
                "stage" : "IXSCAN",

I have trouble understanding this because even though the index is used for sorting , shouldn't it have done a collection scan to fetch the documents that needed to be sorted?

Thank you

1
3

I have trouble understanding this because even though the index is used for sorting , shouldn't it have done a collection scan to fetch the documents that needed to be sorted?

If you fully iterate both of your queries they will end up fetching all documents from the collection, but they take different approaches in terms of query planning and execution.

A collection scan means an index was not used and documents were retrieved in natural order (which is the undefined "order of documents on disk"). An index scan indicates an index was used regardless of whether that stage returns some or all documents in a collection.

If you review the full query explain output for a collection scan you should see totalKeysExamined is 0 and totalDocsExamined equal to the number of documents returned.

db.coll.find().explain('executionStats')

The winningPlan for your first query should be a simple COLLSCAN stage:

    "winningPlan" : {
        "stage" : "COLLSCAN",
        "direction" : "forward"
    }

db.coll.find().sort({f1:1}).explain('executionStats')

In your second query example the index supports the requested sort order, so you should see totalKeysExamined and totalDocsExamined are equal to the number of documents returned (assuming that f1 is not not a multikey (array) index).

The winningPlan for this should be an index scan (IXSCAN) which iterates the f1 index in sorted order to fetch matching documents. You should see the IXSCAN as the inputStage for a FETCH:

    "winningPlan" : {
        "stage" : "FETCH",
        "inputStage" : {
            "stage" : "IXSCAN",
            "keyPattern" : {
                "f1" : 1
            },
            ...
    }

If the winning plan did not support the requested sort order, you would still see an IXSCAN or COLLSCAN but it would be part of an in-memory SORT stage (limited to ~32MB of documents):

    "winningPlan" : {
        "stage" : "SORT",
        "sortPattern" : {
            "f1" : 1
        },
        "inputStage" : {
            "stage" : "SORT_KEY_GENERATOR",
            "inputStage" : {
                "stage" : "FETCH",
                "inputStage" : {
                    "stage" : "IXSCAN",
                    ... 
                },
                ...
            }
        }
    }

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.