|
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" :
{
"$eq" : ObjectId("60fe62de551f6014f5ff9af5")
}
},
{
"salaryMonth" :
{
"$eq" : "2022-09"
}
}
]
},
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" :
{
"companyId" : 1,
"salaryMonth" : 1,
"employeeId" : 1
}
,
"indexName" : "companyId_1_salaryMonth_1_employeeId_1",
"isMultiKey" : false,
"multiKeyPaths" :
{
"companyId" : [ ],
"salaryMonth" : [ ],
"employeeId" : [ ]
}
,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" :
{
"companyId" : [
"[ObjectId('60fe62de551f6014f5ff9af5'), ObjectId('60fe62de551f6014f5ff9af5')]"
],
"salaryMonth" : [
"[\"2022-09\", \"2022-09\"]"
],
"employeeId" : [
"[MinKey, MaxKey]"
]
}
}
},
"rejectedPlans" : [
{
"stage" : "FETCH",
"filter" : {
"companyId" :
{
"$eq" : ObjectId("60fe62de551f6014f5ff9af5")
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" :
{
"salaryMonth" : 1
}
,
"indexName" : "salaryMonth_1",
"isMultiKey" : false,
"multiKeyPaths" :
{
"salaryMonth" : [ ]
}
,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" :
{
"salaryMonth" : [
"[\"2022-09\", \"2022-09\"]"
]
}
}
},
{
"stage" : "FETCH",
"filter" : {
"salaryMonth" :
{
"$eq" : "2022-09"
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" :
{
"companyId" : 1
}
,
"indexName" : "companyId_1",
"isMultiKey" : false,
"multiKeyPaths" :
{
"companyId" : [ ]
}
,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" :
{
"companyId" : [
"[ObjectId('60fe62de551f6014f5ff9af5'), ObjectId('60fe62de551f6014f5ff9af5')]"
]
}
}
}
]
},
"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" :
{
"companyId" : 1,
"salaryMonth" : 1,
"employeeId" : 1,
}
,
"indexName" : "companyId_1_salaryMonth_1_employeeId_1",
"isMultiKey" : false,
"multiKeyPaths" :
{
"companyId" : [ ],
"salaryMonth" : [ ],
"employeeId" : [ ]
}
,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" :
{
"companyId" : [
"[ObjectId('60fe62de551f6014f5ff9af5'), ObjectId('60fe62de551f6014f5ff9af5')]"
],
"salaryMonth" : [
"[\"2022-09\", \"2022-09\"]"
],
"employeeId" : [
"[MinKey, MaxKey]"
]
}
,
"keysExamined" : 88078,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
},
"serverInfo" :
{
"host" : "izwz9j7ffkk3uyach8aflfz",
"port" : 27017,
"version" : "3.4.10",
"gitVersion" : "078f28920cb24de0dd479b5ea6c66c644f6326e9"
}
,
"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": [
{
"companyId": ObjectId("60fe62de551f6014f5ff9af5")
}
,
{
"salaryMonth": "2022-09"
}
]
},
"sort":
{
"_id": 1
}
,
"projection":
{
"employeeId": 1,
"price": 1,
"amount": 1
}
,
"returnKey": false,
"showRecordId": false
},
"cursorid": 70514389445,
"keysExamined": 29726623,
"docsExamined": 29726623,
"fromMultiPlanner": true,
"replanned": true,
"numYield": 251263,
"locks": {
"Global": {
"acquireCount":
{
"r": NumberLong(502528)
}
},
"Database": {
"acquireCount":
{
"r": NumberLong(251264)
}
},
"Collection": {
"acquireCount":
{
"r": NumberLong(251264)
}
}
},
"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":
{
"employeeId": 1,
"price": 1,
"amount": 1
}
,
"inputStage": {
"stage": "FETCH",
"filter": {
"$and": [{
"companyId":
{
"$eq": ObjectId("60fe62de551f6014f5ff9af5")
}
}, {
"salaryMonth":
{
"$eq": "2022-09"
}
}
]
},
"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":
{
"_id": 1
}
,
"indexName": "id",
"isMultiKey": false,
"multiKeyPaths":
{
"_id": []
}
,
"isUnique": true,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "forward",
"indexBounds":
{
"_id": ["[MinKey, MaxKey]"]
}
,
"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",
"db": "cowboy"
}
],
"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": [
{
"companyId": ObjectId("60fe62de551f6014f5ff9af5")
}
,
{
"salaryMonth": "2022-08"
}
]
},
"sort":
{
"_id": 1
}
,
"projection":
{
"employeeId": 1,
"price": 1,
"amount": 1
}
,
"returnKey": false,
"showRecordId": false
},
"cursorid": 71019107321,
"keysExamined": 71678,
"docsExamined": 71678,
"hasSortStage": true,
"numYield": 563,
"locks": {
"Global": {
"acquireCount":
{
"r": NumberLong(1128)
}
},
"Database": {
"acquireCount":
{
"r": NumberLong(564)
}
},
"Collection": {
"acquireCount":
{
"r": NumberLong(564)
}
}
},
"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":
{
"employeeId": 1,
"price": 1,
"amount": 1
}
,
"inputStage": {
"stage": "SORT",
"nReturned": 101,
"executionTimeMillisEstimate": 413,
"works": 71781,
"advanced": 101,
"needTime": 71680,
"needYield": 0,
"saveState": 564,
"restoreState": 563,
"isEOF": 0,
"invalidates": 0,
"sortPattern":
{
"_id": 1
}
,
"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":
{
"companyId": 1,
"salaryMonth": 1,
"employeeId": 1
}
,
"indexName": "companyId_1_salaryMonth_1_employeeId_1",
"isMultiKey": false,
"multiKeyPaths":
{
"companyId": [],
"salaryMonth": [],
"employeeId": []
}
,
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "forward",
"indexBounds":
{
"companyId": ["[ObjectId('60fe62de551f6014f5ff9af5'), ObjectId('60fe62de551f6014f5ff9af5')]"],
"salaryMonth": ["[\"2022-08\", \"2022-08\"]"],
"employeeId": ["[MinKey, MaxKey]"]
}
,
"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",
"db": "cowboy"
}
],
"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!
|