COLLSCAN is faster than IXSCAN

[Dennis Melzer]

Hello community,

i inserted 80.000.000 entries to a collection. The structure is the same like in the documentation example: http://mongodb.github.io/mongo-java-driver/3.4/driver/getting-started/quick-start/

Document doc = new Document("name", "MongoDB")
                .append("type", "database")
                .append("count", 1)
                .append("versions", Arrays.asList("v3.2", "v3.0", "v2.6"))
                .append("info", new Document("x", 203).append("y", 102));

If i use no index the winning plan and executionStats looks like. The query take 37seconds.

       "winningPlan" : {

            "stage" : "COLLSCAN", 

            "filter" : {

                "name" : {

                    "$eq" : "MongoDB"

                }

            }, 

            "direction" : "forward"

        }, 

        "rejectedPlans" : [

        ]

    }, 

    "executionStats" : {

        "executionSuccess" : true, 

        "nReturned" : 81850335.0, 

        "executionTimeMillis" : 37297.0, 

        "totalKeysExamined" : 0.0, 

        "totalDocsExamined" : 81850335.0, 

Now i would except, if i use a index, the query time is the same or lower.

but the winning and executionStats looks like:

       "winningPlan" : {

            "stage" : "FETCH", 

            "inputStage" : {

                "stage" : "IXSCAN", 

                "keyPattern" : {

                    "name" : 1.0

                }, 

                "indexName" : "name_1", 

                "isMultiKey" : false, 

                "multiKeyPaths" : {

                    "name" : [

                    ]

                }, 

                "isUnique" : false, 

                "isSparse" : false, 

                "isPartial" : false, 

                "indexVersion" : 2.0, 

                "direction" : "forward", 

                "indexBounds" : {

                    "name" : [

                        "[\"MongoDB\", \"MongoDB\"]"

                    ]

                }

            }

        }, 

        "rejectedPlans" : [

        ]

    }, 

    "executionStats" : {

        "executionSuccess" : true, 

        "nReturned" : 81850335.0, 

        "executionTimeMillis" : 80096.0, 

        "totalKeysExamined" : 81850335.0, 

        "totalDocsExamined" : 81850335.0, 

        "executionStages" : {

Does anyone know why?

Best regards,Dennis

[Rob Moore]

The slow down is caused by the extra level of indirection for the index without any benefit.

Normally, an index provides a performance benefit because you can limit the number of records that need to be inspected. In this case you have a single value in the database for the indexed field (name always equals "MongoDB") so the index scan still results in looking at every single document. Worse, you have to look at every single index record and then fetch every single document based on that index record. Even worse, the order of the index records and the order of the document records may not be the same so while the index record scan is likely sequential the document fetch is likely to be random (aka slow).

For the COLLSCAN you perform a single sequential read of the documents in the collection. Not indirection, No overhead so it is going to be faster.

Did that make sense?

Rob.