Mongodb – MONGOS uselessly scanning indexed documents when counting on shards

mongodbsharding

I have a simple collection with 40M documents distributed over 2 shards. The documents contain a field named COUNTRY with a sparse index on it.

db.contacts.count({ "COUNTRY": "US" })

I am trying to count documents with a specific value for COUNTRY. When counting on the shards directly the plan looks correct and uses COUNT_SCAN with the index, returns 6M docs on each shard without loading them.

2019-01-11T16:03:02.643+0000 I COMMAND  [conn102] command segmentation.contacts appName: "MongoDB Shell" command: count { count: "contacts", query: { COUNTRY: "US" }, fields: {}, lsid: { id: UUID("60be11b5-6299-416b-9ead-8c58fd3656b8") }, $clusterTime: { clusterTime: Timestamp(1547221085, 2), signature: { hash: BinData(0, 0000000000000000000000000000000000000000), keyId: 0 } }, $db: "segmentation" } planSummary: COUNT_SCAN { COUNTRY: 1 } keysExamined:6923922 docsExamined:0 numYields:54093 reslen:340 locks:{ Global: { acquireCount: { r: 54094 } }, Database: { acquireCount: { r: 54094 } }, Collection: { acquireCount: { r: 54094 } } } protocol:op_msg 2747ms

Running the exact same query on MONGOS, the count is distributed to the shards but I can see in the logs of each shard that the plan changed and it used IDX_SCAN instead and loaded all the documents which was much slower.

2019-01-11T16:04:13.104+0000 I COMMAND  [conn111] command segmentation.contacts appName: "MongoDB Shell" command: count { count: "contacts", query: { COUNTRY: "US" }, allowImplicitCollectionCreation: false, shardVersion: [ Timestamp(474, 1),ObjectId('5c37917aa0f162a86b270897') ], lsid: { id: UUID("9be8fb16-f158-4099-bf25-a3a0c97a33c2"), uid: BinData(0, E3B0C44298FC1C149AFBF4C8996FB92427AE41E4649B934CA495991B7852B855) }, $clusterTime: { clusterTime: Timestamp(1547222609, 1), signature: { hash: BinData(0, 1F79735A80E06C35800DE9CDC4E92608B2759F1F), keyId: 6644928741854150685 } }, $client: { application: { name: "MongoDB Shell" }, driver: { name: "MongoDB Internal Client", version: "4.0.5" }, os: { type: "Linux", name: "Ubuntu", architecture: "x86_64", version: "18.04" }, mongos: { host: "ip-192-168-169-237:27019", client: "127.0.0.1:59430", version: "4.0.5" } }, $configServerState: { opTime: { ts: Timestamp(1547222609, 1), t: 1 } }, $db: "segmentation" } planSummary: IXSCAN { COUNTRY: 1 } keysExamined:6923921 docsExamined:6923921 numYields:54093 reslen:320 locks:{ Global: { acquireCount: { r: 54094 } }, Database: { acquireCount: { r: 54094 } }, Collection: { acquireCount: { r: 54094 } } } protocol:op_msg 34784ms

Can someone explain me why the plan changed or why the docs have to be scanned ? The performance impact is huge…

Here is the explain plan from mongos:

{
"queryPlanner" : {
    "mongosPlannerVersion" : 1,
    "winningPlan" : {
        "stage" : "SHARD_MERGE",
        "shards" : [
            {
                "shardName" : "segmentation-rs",
                "connectionString" : "segmentation-rs/192.168.140.69:27017",
                "serverInfo" : {
                    "host" : "ip-192-168-140-69",
                    "port" : 27017,
                    "version" : "4.0.5",
                    "gitVersion" : "3739429dd92b92d1b0ab120911a23d50bf03c412"
                },
                "plannerVersion" : 1,
                "namespace" : "segmentation.contacts",
                "indexFilterSet" : false,
                "parsedQuery" : {
                    "COUNTRY" : {
                        "$eq" : "US"
                    }
                },
                "winningPlan" : {
                    "stage" : "COUNT",
                    "inputStage" : {
                        "stage" : "SHARDING_FILTER",
                        "inputStage" : {
                            "stage" : "FETCH",
                            "inputStage" : {
                                "stage" : "IXSCAN",
                                "keyPattern" : {
                                    "COUNTRY" : 1
                                },
                                "indexName" : "COUNTRY_1",
                                "isMultiKey" : false,
                                "multiKeyPaths" : {
                                    "COUNTRY" : [ ]
                                },
                                "isUnique" : false,
                                "isSparse" : true,
                                "isPartial" : false,
                                "indexVersion" : 2,
                                "direction" : "forward",
                                "indexBounds" : {
                                    "COUNTRY" : [
                                        "[\"US\", \"US\"]"
                                    ]
                                }
                            }
                        }
                    }
                },
                "rejectedPlans" : [ ]
            },
            {
                "shardName" : "segmentation-rs2",
                "connectionString" : "segmentation-rs2/192.168.152.98:27017",
                "serverInfo" : {
                    "host" : "ip-192-168-152-98",
                    "port" : 27017,
                    "version" : "4.0.5",
                    "gitVersion" : "3739429dd92b92d1b0ab120911a23d50bf03c412"
                },
                "plannerVersion" : 1,
                "namespace" : "segmentation.contacts",
                "indexFilterSet" : false,
                "parsedQuery" : {
                    "COUNTRY" : {
                        "$eq" : "US"
                    }
                },
                "winningPlan" : {
                    "stage" : "COUNT",
                    "inputStage" : {
                        "stage" : "SHARDING_FILTER",
                        "inputStage" : {
                            "stage" : "FETCH",
                            "inputStage" : {
                                "stage" : "IXSCAN",
                                "keyPattern" : {
                                    "COUNTRY" : 1
                                },
                                "indexName" : "COUNTRY_1",
                                "isMultiKey" : false,
                                "multiKeyPaths" : {
                                    "COUNTRY" : [ ]
                                },
                                "isUnique" : false,
                                "isSparse" : true,
                                "isPartial" : false,
                                "indexVersion" : 2,
                                "direction" : "forward",
                                "indexBounds" : {
                                    "COUNTRY" : [
                                        "[\"US\", \"US\"]"
                                    ]
                                }
                            }
                        }
                    }
                },
                "rejectedPlans" : [ ]
            }
        ]
    }
},
"executionStats" : {
    "nReturned" : 0,
    "executionTimeMillis" : 34224,
    "totalKeysExamined" : 13844662,
    "totalDocsExamined" : 13844662,
    "executionStages" : {
        "stage" : "SHARD_MERGE",
        "nReturned" : 0,
        "executionTimeMillis" : 34224,
        "totalKeysExamined" : 13844662,
        "totalDocsExamined" : 13844662,
        "totalChildMillis" : NumberLong(63271),
        "shards" : [
            {
                "shardName" : "segmentation-rs",
                "executionSuccess" : true,
                "executionStages" : {
                    "stage" : "COUNT",
                    "nReturned" : 0,
                    "executionTimeMillisEstimate" : 27860,
                    "works" : 6923922,
                    "advanced" : 0,
                    "needTime" : 6923921,
                    "needYield" : 0,
                    "saveState" : 54093,
                    "restoreState" : 54093,
                    "isEOF" : 1,
                    "invalidates" : 0,
                    "nCounted" : 6923921,
                    "nSkipped" : 0,
                    "inputStage" : {
                        "stage" : "SHARDING_FILTER",
                        "nReturned" : 6923921,
                        "executionTimeMillisEstimate" : 27410,
                        "works" : 6923922,
                        "advanced" : 6923921,
                        "needTime" : 0,
                        "needYield" : 0,
                        "saveState" : 54093,
                        "restoreState" : 54093,
                        "isEOF" : 1,
                        "invalidates" : 0,
                        "chunkSkips" : 0,
                        "inputStage" : {
                            "stage" : "FETCH",
                            "nReturned" : 6923921,
                            "executionTimeMillisEstimate" : 16480,
                            "works" : 6923922,
                            "advanced" : 6923921,
                            "needTime" : 0,
                            "needYield" : 0,
                            "saveState" : 54093,
                            "restoreState" : 54093,
                            "isEOF" : 1,
                            "invalidates" : 0,
                            "docsExamined" : 6923921,
                            "alreadyHasObj" : 0,
                            "inputStage" : {
                                "stage" : "IXSCAN",
                                "nReturned" : 6923921,
                                "executionTimeMillisEstimate" : 3500,
                                "works" : 6923922,
                                "advanced" : 6923921,
                                "needTime" : 0,
                                "needYield" : 0,
                                "saveState" : 54093,
                                "restoreState" : 54093,
                                "isEOF" : 1,
                                "invalidates" : 0,
                                "keyPattern" : {
                                    "COUNTRY" : 1
                                },
                                "indexName" : "COUNTRY_1",
                                "isMultiKey" : false,
                                "multiKeyPaths" : {
                                    "COUNTRY" : [ ]
                                },
                                "isUnique" : false,
                                "isSparse" : true,
                                "isPartial" : false,
                                "indexVersion" : 2,
                                "direction" : "forward",
                                "indexBounds" : {
                                    "COUNTRY" : [
                                        "[\"US\", \"US\"]"
                                    ]
                                },
                                "keysExamined" : 6923921,
                                "seeks" : 1,
                                "dupsTested" : 0,
                                "dupsDropped" : 0,
                                "seenInvalidated" : 0
                            }
                        }
                    }
                }
            },
            {
                "shardName" : "segmentation-rs2",
                "executionSuccess" : true,
                "executionStages" : {
                    "stage" : "COUNT",
                    "nReturned" : 0,
                    "executionTimeMillisEstimate" : 33180,
                    "works" : 6920742,
                    "advanced" : 0,
                    "needTime" : 6920741,
                    "needYield" : 0,
                    "saveState" : 54068,
                    "restoreState" : 54068,
                    "isEOF" : 1,
                    "invalidates" : 0,
                    "nCounted" : 6920741,
                    "nSkipped" : 0,
                    "inputStage" : {
                        "stage" : "SHARDING_FILTER",
                        "nReturned" : 6920741,
                        "executionTimeMillisEstimate" : 32500,
                        "works" : 6920742,
                        "advanced" : 6920741,
                        "needTime" : 0,
                        "needYield" : 0,
                        "saveState" : 54068,
                        "restoreState" : 54068,
                        "isEOF" : 1,
                        "invalidates" : 0,
                        "chunkSkips" : 0,
                        "inputStage" : {
                            "stage" : "FETCH",
                            "nReturned" : 6920741,
                            "executionTimeMillisEstimate" : 21430,
                            "works" : 6920742,
                            "advanced" : 6920741,
                            "needTime" : 0,
                            "needYield" : 0,
                            "saveState" : 54068,
                            "restoreState" : 54068,
                            "isEOF" : 1,
                            "invalidates" : 0,
                            "docsExamined" : 6920741,
                            "alreadyHasObj" : 0,
                            "inputStage" : {
                                "stage" : "IXSCAN",
                                "nReturned" : 6920741,
                                "executionTimeMillisEstimate" : 3610,
                                "works" : 6920742,
                                "advanced" : 6920741,
                                "needTime" : 0,
                                "needYield" : 0,
                                "saveState" : 54068,
                                "restoreState" : 54068,
                                "isEOF" : 1,
                                "invalidates" : 0,
                                "keyPattern" : {
                                    "COUNTRY" : 1
                                },
                                "indexName" : "COUNTRY_1",
                                "isMultiKey" : false,
                                "multiKeyPaths" : {
                                    "COUNTRY" : [ ]
                                },
                                "isUnique" : false,
                                "isSparse" : true,
                                "isPartial" : false,
                                "indexVersion" : 2,
                                "direction" : "forward",
                                "indexBounds" : {
                                    "COUNTRY" : [
                                        "[\"US\", \"US\"]"
                                    ]
                                },
                                "keysExamined" : 6920741,
                                "seeks" : 1,
                                "dupsTested" : 0,
                                "dupsDropped" : 0,
                                "seenInvalidated" : 0
                            }
                        }
                    }
                }
            }
        ]
    },
    "allPlansExecution" : [
        {
            "shardName" : "segmentation-rs",
            "allPlans" : [ ]
        },
        {
            "shardName" : "segmentation-rs2",
            "allPlans" : [ ]
        }
    ]
},
"ok" : 1,
"operationTime" : Timestamp(1547463575, 1),
"$clusterTime" : {
    "clusterTime" : Timestamp(1547463575, 1),
    "signature" : {
        "hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
        "keyId" : NumberLong(0)
    }
}
}

This is MongoDB 4.0.5 (on both shards, config and mongos) installed from the official ubuntu repos.

Best Answer

Because of sharding, when automatically moving a chunk for balancing, old data may remain in a shard for a while, until it gets asynchronously deleted. So when you are doing a count, the database counts how many documents fall under ({ "COUNTRY": "US" }) for each chunk individually, and it needs to do a INDEX_SCAN. If it would do it for the whole collection, you would get a COUNT_SCAN, but it may show a wrong number of documents.

Related Question