Mongodb – Is MongoDB IXSCAN operator a scan or a seek

mongodbmongodb-3.4

It seems to me that the only operators I see in a MongoDB query plan are COLLSCAN or IXSCAN. The indexes being b-tree, I guess MongoDB can do index seek and index scan ? If so, how do we know that a seek id performed ?

For instance, in this plan (just a fragment) :

 "executionStats" : {
    "executionSuccess" : true,
    "nReturned" : 17,
    "executionTimeMillis" : 42,
    "totalKeysExamined" : 25359,
    "totalDocsExamined" : 1553,
    "executionStages" : {
        "stage" : "FETCH",
        "filter" : {
            "address.zipcode" : {
                "$eq" : "10075"
            }
        },
        "nReturned" : 17,
        "executionTimeMillisEstimate" : 30,
        "works" : 25360,
        "advanced" : 17,
        "needTime" : 25342,
        "needYield" : 0,
        "saveState" : 198,
        "restoreState" : 198,
        "isEOF" : 1,
        "invalidates" : 0,
        "docsExamined" : 1553,
        "alreadyHasObj" : 0,
        "inputStage" : {
            "stage" : "IXSCAN",
            "filter" : {
                "cuisine" : {
                    "$regex" : ".*alian",
                    "$options" : "i"
                }
            },
            "nReturned" : 1553,
            "executionTimeMillisEstimate" : 30,
            "works" : 25360,
            "advanced" : 1553,
            "needTime" : 23806,
            "needYield" : 0,
            "saveState" : 198,
            "restoreState" : 198,
            "isEOF" : 1,
            "invalidates" : 0,
            "keyPattern" : {
                "cuisine" : 1.0
            },
            "indexName" : "cuisine_1",
            "isMultiKey" : false,
            "multiKeyPaths" : {
                "cuisine" : []
            },
            "isUnique" : false,
            "isSparse" : false,
            "isPartial" : false,
            "indexVersion" : 2,
            "direction" : "forward",
            "indexBounds" : {
                "cuisine" : [ 
                    "[\"\", {})", 
                    "[/.*alian/i, /.*alian/i]"
                ]
            },
            "keysExamined" : 25359,
            "seeks" : 1,
            "dupsTested" : 0,
            "dupsDropped" : 0,
            "seenInvalidated" : 0
        }

I can guess it is a scan (I did a regexp search on purpose) :

"totalKeysExamined" : 25359,
"totalDocsExamined" : 1553,

but I see 

"seeks" : 1,

Is there any rule as how to interpret the plan, and why there is no IXSEEK operator ?

Best Answer

I guess MongoDB can do index seek and index scan ?

Yes MongoDB can do index seek and index scan, but it can do both during one fetch. That is what happened in your case. If started with a seek to the leftmost record of your index cuisine_1 and did a scan on 25359 keys. During the scan of 25359 keys 1553 key matched your search criteria. Index not being a covering one, it had to do a lookup (for other columns) from 1553 documents.

If so, how do we know that a seek id performed ?

It will always do a seek if it it using a index, I guess your question is when it is not doing a seek + scan but only seek?

IXSCAN means:

If the query planner selects an index, the explain result includes a IXSCAN stage. The stage includes information such as the index key pattern, direction of traversal, and index bounds.

If you see this, it will indicate a seek to one index key and no scan. But anything more than one will indicate a seek + scan.

  "nReturned" : 1,
  "totalKeysExamined" : 1,
Related Question