-
Type: Bug
-
Resolution: Done
-
Priority: Major - P3
-
None
-
Affects Version/s: 3.4.10
-
Component/s: None
-
None
-
Server Triage
-
ALL
I create compound index:
db.dailyWork.createIndex({"companyId": 1, "salaryMonth":1, "employeeId":1})
and, I RUN command explain's result is good:
db.dailyWork.find({"companyId" : ObjectId("60fe62de551f6014f5ff9af5"),"salaryMonth" : "2022-09"}).explain("executionStats")
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "cowboy.dailyWork",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"companyId" :
},
{
"salaryMonth" :
}
]
},
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" :
,
"indexName" : "companyId_1_salaryMonth_1_employeeId_1",
"isMultiKey" : false,
"multiKeyPaths" :
,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" :
}
},
"rejectedPlans" : [
{
"stage" : "FETCH",
"filter" : {
"companyId" :
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" :
,
"indexName" : "salaryMonth_1",
"isMultiKey" : false,
"multiKeyPaths" :
,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" :
}
},
{
"stage" : "FETCH",
"filter" : {
"salaryMonth" :
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" :
,
"indexName" : "companyId_1",
"isMultiKey" : false,
"multiKeyPaths" :
,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" :
}
}
]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 88078,
"executionTimeMillis" : 183,
"totalKeysExamined" : 88078,
"totalDocsExamined" : 88078,
"executionStages" : {
"stage" : "FETCH",
"nReturned" : 88078,
"executionTimeMillisEstimate" : 151,
"works" : 88079,
"advanced" : 88078,
"needTime" : 0,
"needYield" : 0,
"saveState" : 691,
"restoreState" : 691,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 88078,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 88078,
"executionTimeMillisEstimate" : 30,
"works" : 88079,
"advanced" : 88078,
"needTime" : 0,
"needYield" : 0,
"saveState" : 691,
"restoreState" : 691,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" :
,
"indexName" : "companyId_1_salaryMonth_1_employeeId_1",
"isMultiKey" : false,
"multiKeyPaths" :
,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" :
,
"keysExamined" : 88078,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
},
"serverInfo" :
,
"ok" : 1
}
but I get different result from my node server. It's the same query condition:
{
"op": "query",
"ns": "cowboy.dailyWork",
"query": {
"find": "dailyWork",
"filter": {
"$and": [
,
{ "salaryMonth": "2022-09" } ]
},
"sort":
,
"projection":
,
"returnKey": false,
"showRecordId": false
},
"cursorid": 70514389445,
"keysExamined": 29726623,
"docsExamined": 29726623,
"fromMultiPlanner": true,
"replanned": true,
"numYield": 251263,
"locks": {
"Global": {
"acquireCount":
},
"Database": {
"acquireCount":
},
"Collection": {
"acquireCount":
}
},
"nreturned": 101,
"responseLength": 7476,
"protocol": "op_query",
"millis": 35504,
"planSummary": "IXSCAN { companyId: 1, salaryMonth: 1, employeeId: 1 }",
"execStats": {
"stage": "PROJECTION",
"nReturned": 101,
"executionTimeMillisEstimate": 27861,
"works": 29726623,
"advanced": 101,
"needTime": 29726522,
"needYield": 0,
"saveState": 251264,
"restoreState": 251263,
"isEOF": 0,
"invalidates": 0,
"transformBy":
,
"inputStage": {
"stage": "FETCH",
"filter": {
"$and": [{
"companyId":
}, {
"salaryMonth":
}
]
},
"nReturned": 101,
"executionTimeMillisEstimate": 27387,
"works": 29726623,
"advanced": 101,
"needTime": 29726522,
"needYield": 0,
"saveState": 251264,
"restoreState": 251263,
"isEOF": 0,
"invalidates": 0,
"docsExamined": 29726623,
"alreadyHasObj": 0,
"inputStage": {
"stage": "IXSCAN",
"nReturned": 29726623,
"executionTimeMillisEstimate": 9654,
"works": 29726623,
"advanced": 29726623,
"needTime": 0,
"needYield": 0,
"saveState": 251264,
"restoreState": 251263,
"isEOF": 0,
"invalidates": 0,
"keyPattern":
,
"indexName": "id",
"isMultiKey": false,
"multiKeyPaths":
,
"isUnique": true,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "forward",
"indexBounds":
,
"keysExamined": 29726623,
"seeks": 1,
"dupsTested": 0,
"dupsDropped": 0,
"seenInvalidated": 0
}
}
},
"ts": ISODate("2022-09-30T17:34:34.965Z"),
"client": "127.0.0.1",
"allUsers": [
],
"user": "cowboyUser@cowboy"
}
but more wired thing is, if the results item less than about 80000, the query is fast, the only differt condition is 'salaryMonth', the former one is '2022-09', below is '2020-08'. '2022-09' condition will find 88078 items, but '2022-08' will find 71678 items. These results is not far different, but first query is so slow(27+ sec), the 2rd qurey just less than 1 sec. The 2rd query as below:
{
"op": "query",
"ns": "cowboy.dailyWork",
"query": {
"find": "dailyWork",
"filter": {
"$and": [
,
{ "salaryMonth": "2022-08" } ]
},
"sort":
,
"projection":
,
"returnKey": false,
"showRecordId": false
},
"cursorid": 71019107321,
"keysExamined": 71678,
"docsExamined": 71678,
"hasSortStage": true,
"numYield": 563,
"locks": {
"Global": {
"acquireCount":
},
"Database": {
"acquireCount":
},
"Collection": {
"acquireCount":
}
},
"nreturned": 101,
"responseLength": 7872,
"protocol": "op_query",
"millis": 415,
"planSummary": "IXSCAN { companyId: 1, salaryMonth: 1, employeeId: 1 }",
"execStats": {
"stage": "CACHED_PLAN",
"nReturned": 101,
"executionTimeMillisEstimate": 413,
"works": 101,
"advanced": 101,
"needTime": 0,
"needYield": 0,
"saveState": 564,
"restoreState": 563,
"isEOF": 0,
"invalidates": 0,
"inputStage": {
"stage": "PROJECTION",
"nReturned": 101,
"executionTimeMillisEstimate": 413,
"works": 71781,
"advanced": 101,
"needTime": 71680,
"needYield": 0,
"saveState": 564,
"restoreState": 563,
"isEOF": 0,
"invalidates": 0,
"transformBy":
,
"inputStage": {
"stage": "SORT",
"nReturned": 101,
"executionTimeMillisEstimate": 413,
"works": 71781,
"advanced": 101,
"needTime": 71680,
"needYield": 0,
"saveState": 564,
"restoreState": 563,
"isEOF": 0,
"invalidates": 0,
"sortPattern":
,
"memUsage": 32000792,
"memLimit": 33554432,
"inputStage": {
"stage": "SORT_KEY_GENERATOR",
"nReturned": 71678,
"executionTimeMillisEstimate": 212,
"works": 71680,
"advanced": 71678,
"needTime": 1,
"needYield": 0,
"saveState": 564,
"restoreState": 563,
"isEOF": 1,
"invalidates": 0,
"inputStage": {
"stage": "FETCH",
"nReturned": 71678,
"executionTimeMillisEstimate": 170,
"works": 71679,
"advanced": 71678,
"needTime": 0,
"needYield": 0,
"saveState": 564,
"restoreState": 563,
"isEOF": 1,
"invalidates": 0,
"docsExamined": 71678,
"alreadyHasObj": 0,
"inputStage": {
"stage": "IXSCAN",
"nReturned": 71678,
"executionTimeMillisEstimate": 60,
"works": 71679,
"advanced": 71678,
"needTime": 0,
"needYield": 0,
"saveState": 564,
"restoreState": 563,
"isEOF": 1,
"invalidates": 0,
"keyPattern":
,
"indexName": "companyId_1_salaryMonth_1_employeeId_1",
"isMultiKey": false,
"multiKeyPaths":
,
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "forward",
"indexBounds":
,
"keysExamined": 71678,
"seeks": 1,
"dupsTested": 0,
"dupsDropped": 0,
"seenInvalidated": 0
}
}
}
}
}
},
"ts": ISODate("2022-09-30T17:52:33.762Z"),
"client": "127.0.0.1",
"allUsers": [
],
"user": "cowboyUser@cowboy"
}
I have enough RAM, the only different is result items amount. If the result amount more than 80000, the query become very slow. If you know what's going on, please tell me, thanks!