[SERVER-23675] Count with filter is considerable slower with partial match on compound index without index hint Created: 13/Apr/16  Updated: 13/Apr/16  Resolved: 13/Apr/16

Status: Closed
Project: Core Server
Component/s: Performance
Affects Version/s: 3.2.4
Fix Version/s: None

Type: Bug Priority: Minor - P4
Reporter: Denis Tomasevic Assignee: Kelsey Schubert
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
duplicates SERVER-22133 COUNT_SCAN plans are not generated pr... Closed
Operating System: ALL
Participants:

 Description   

Collection has several compound indexes that start with same field.
When using count() with a filter on the first field in a compound index, there is a huge operation time difference for execution with or without index hint, despite the fact that mongodb uses same index in both cases.

1.Case: Filtered count with index hint
Explain:

db.transaction.explain().count({"data.systemId":"TEST"},{hint:"data.systemId_1.0_data.timeReceived_-1.0"})
{
        "queryPlanner" : {
                "plannerVersion" : 1,
                "namespace" : "test.transaction",
                "indexFilterSet" : false,
                "parsedQuery" : {
                        "data.systemId" : {
                                "$eq" : "TEST"
                        }
                },
                "winningPlan" : {
                        "stage" : "COUNT",
                        "inputStage" : {
                                "stage" : "COUNT_SCAN",
                                "keyPattern" : {
                                        "data.systemId" : 1,
                                        "data.timeReceived" : -1
                                },
                                "indexName" : "data.systemId_1.0_data.timeReceived_-1.0",
                                "isMultiKey" : false,
                                "isUnique" : false,
                                "isSparse" : false,
                                "isPartial" : false,
                                "indexVersion" : 1
                        }
                },
                "rejectedPlans" : [ ]
        },
        "serverInfo" : {
                "host" : "test-db1",
                "port" : 27017,
                "version" : "3.2.4",
                "gitVersion" : "e2ee9ffcf9f5a94fad76802e28cc978718bb7a30"
        },
        "ok" : 1
}

Statistics from system.profile:

{ 
    "op" : "command", 
    "ns" : "test.transaction", 
    "command" : {
        "count" : "transaction", 
        "query" : {
            "data.systemId" : "TEST"
        }, 
        "hint" : "data.systemId_1.0_data.timeReceived_-1.0", 
        "fields" : {
 
        }
    }, 
    "keyUpdates" : NumberInt(0), 
    "writeConflicts" : NumberInt(0), 
    "numYield" : NumberInt(32602), 
    "locks" : {
        "Global" : {
            "acquireCount" : {
                "r" : NumberLong(65206)
            }
        }, 
        "Database" : {
            "acquireCount" : {
                "r" : NumberLong(32603)
            }
        }, 
        "Collection" : {
            "acquireCount" : {
                "r" : NumberLong(32603)
            }
        }
    }, 
    "responseLength" : NumberInt(47), 
    "protocol" : "op_command", 
    "millis" : NumberInt(1104), 
    "execStats" : {
 
    }, 
    "ts" : ISODate("2016-04-13T11:29:46.541+0000"), 
    "client" : "127.0.0.1", 
    "allUsers" : [
        {
            "user" : "root", 
            "db" : "admin"
        }
    ], 
    "user" : "root@admin"
}

2.Case: Filtered count without index hint
Explain:

db.transaction.explain().count({"data.systemId":"TEST"})
{
        "queryPlanner" : {
                "plannerVersion" : 1,
                "namespace" : "test.transaction",
                "indexFilterSet" : false,
                "parsedQuery" : {
                        "data.systemId" : {
                                "$eq" : "TEST"
                        }
                },
                "winningPlan" : {
                        "stage" : "COUNT",
                        "inputStage" : {
                                "stage" : "COUNT_SCAN",
                                "keyPattern" : {
                                        "data.systemId" : 1,
                                        "data.timeReceived" : -1
                                },
                                "indexName" : "data.systemId_1.0_data.timeReceived_-1.0",
                                "isMultiKey" : false,
                                "isUnique" : false,
                                "isSparse" : false,
                                "isPartial" : false,
                                "indexVersion" : 1
                        }
                },
                "rejectedPlans" : [ ]
        },
        "serverInfo" : {
                "host" : "test-db1",
                "port" : 27017,
                "version" : "3.2.4",
                "gitVersion" : "e2ee9ffcf9f5a94fad76802e28cc978718bb7a30"
        },
        "ok" : 1
}

Statistics from system.profile:

{ 
    "op" : "command", 
    "ns" : "test.transaction", 
    "command" : {
        "count" : "transaction", 
        "query" : {
            "data.systemId" : "TEST"
        }, 
        "fields" : {
 
        }
    }, 
    "keyUpdates" : NumberInt(0), 
    "writeConflicts" : NumberInt(0), 
    "numYield" : NumberInt(32603), 
    "locks" : {
        "Global" : {
            "acquireCount" : {
                "r" : NumberLong(65208)
            }
        }, 
        "Database" : {
            "acquireCount" : {
                "r" : NumberLong(32604)
            }
        }, 
        "Collection" : {
            "acquireCount" : {
                "r" : NumberLong(32604)
            }
        }
    }, 
    "responseLength" : NumberInt(47), 
    "protocol" : "op_command", 
    "millis" : NumberInt(5370), 
    "execStats" : {
 
    }, 
    "ts" : ISODate("2016-04-13T11:28:24.711+0000"), 
    "client" : "127.0.0.1", 
    "allUsers" : [
        {
            "user" : "root", 
            "db" : "admin"
        }
    ], 
    "user" : "root@admin"
}

Results above are from collection with 7M records, filter above matches 4M.



 Comments   
Comment by Kelsey Schubert [ 13/Apr/16 ]

Hi denist,

Thank you for reporting this behavior. This issue has been previously identified in SERVER-22133 and the fix will be included in MongoDB 3.4.

For additional information about this issue please review Dave's comment, which includes a repro and a description of the behavior.

Kind regards,
Thomas

Generated at Thu Feb 08 04:04:09 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.