2

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.

  • What specific versions of MongoDB server and mongos are you using? Can you edit your question to include the output of explain(true) for the query via mongos? – Stennie Jan 12 at 1:52
  • I was assuming that you would get a notification about my edit but I am not so sure. Just a ping in case you did not get notified. @Stennie – Julien 15 hours ago

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.