MongoDB tips: Performance Tuning, Index key direction MATTERS!
This is the query I am working to make it better,
Environment:
MongoDB 3.6.6
2019-09-25T11:30:17.696-0400 I COMMAND [conn19014149] command task.task command: find { find: "task", filter: { $and: [ { _id.boardId.location.correlation: "MN01" }, { $or: [ { _id.boardId.boardDate: new Date(1569384000000) }, { _id.boardId.boardDate: new Date(1569297600000), started: { $exists: true }, ended: { $exists: false }, shift.end: { $gte: new Date(1569384000000) } }, { _id.boardId.boardDate: new Date(1569470400000), started: { $exists: true }, ended: { $exists: false }, shift.start: { $lte: new Date(1569470400000) } } ] } ] }, $db: "task", $clusterTime: { clusterTime: Timestamp(1569425411, 1), signature: { hash: BinData(0, A8F704450DF4EF062ABB018B8BC3C175CE3CC62A), keyId: 6680100327825342465 } }, lsid: { id: UUID("683fbed5-1856-4181-b18f-d06db6b6cbb8") } } planSummary: IXSCAN { _id.boardId.boardDate: 1 }, IXSCAN { _id.boardId.boardDate: 1, personnelAssignments.assignedId.identifier: 1 }, IXSCAN { _id.boardId.boardDate: 1 } keysExamined:11588 docsExamined:11926 fromMultiPlanner:1 cursorExhausted:1 numYields:724 nreturned:62 reslen:50591 locks:{ Global: { acquireCount: { r: 1450 } }, Database: { acquireCount: { r: 725 } }, Collection: { acquireCount: { r: 725 } } } protocol:op_msg 590ms
Take out everything else not related the query we are tuning, we have the query
db.getSiblingDB("task").task.find({ $and: [ { "_id.boardId.location.correlation": "MN01" }, { $or: [ { "_id.boardId.boardDate": new Date(1569384000000) }, { "_id.boardId.boardDate": new Date(1569297600000), started: { $exists: true }, ended: { $exists: false }, "shift.end": { $gte: new Date(1569384000000) } }, { "_id.boardId.boardDate": new Date(1569470400000), started: { $exists: true }, ended: { $exists: false }, "shift.start": { $lte: new Date(1569470400000) } } ] } ] }).explain("executionStats")
Without running the query yet, by looking the fields used to search for the documents, we know that the field "_id.boardId.boardDate" is the most selective, along with "shift.start", luckily they are being used within the $or operator, which grants us to utilize multiple index for this search, so initially the shape of index I chose to create were
{"_id.boardId.boardDate":1,"shift.start":-1 } && {"_id.boardId.boardDate":1,"shift.end":-1 }
I chose to do -1 for shift.start and shift.end were mainly due to how they are being searched as in range search, after thought it over the second time, it wasn't the right decision to do, especially not with this query.
I had an index
{"_id.boardId.boardDate" : -1, "equipmentAssignments.assignedId" : 1}
from another index that I was tuning, this will come into picture as I explained further
This is the executionStats of the query
------------
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 62,
"executionTimeMillis" : 420,
"totalKeysExamined" : 11594,
"totalDocsExamined" : 11932,
"executionStages" : {
"stage" : "FETCH",
"filter" : {
"_id.boardId.location.correlation" : {
"$eq" : "MN01"
}
},
"nReturned" : 62,
"executionTimeMillisEstimate" : 30,
"works" : 11598,
"advanced" : 62,
"needTime" : 11534,
"needYield" : 0,
"saveState" : 906,
"restoreState" : 906,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 5812,
"alreadyHasObj" : 338,
"inputStage" : {
"stage" : "OR",
"nReturned" : 5812,
"executionTimeMillisEstimate" : 10,
"works" : 11597,
"advanced" : 5812,
"needTime" : 5784,
"needYield" : 0,
"saveState" : 906,
"restoreState" : 906,
"isEOF" : 1,
"invalidates" : 0,
"dupsTested" : 5812,
"dupsDropped" : 0,
"recordIdsForgotten" : 0,
"inputStages" : [
{
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"shift.start" : {
"$lte" : ISODate("2019-09-26T04:00:00Z")
}
},
{
"started" : {
"$exists" : true
}
},
{
"$nor" : [
{
"ended" : {
"$exists" : true
}
}
]
}
]
},
"nReturned" : 0,
"executionTimeMillisEstimate" : 0,
"works" : 3,
"advanced" : 0,
"needTime" : 2,
"needYield" : 0,
"saveState" : 906,
"restoreState" : 906,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 2,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 2,
"executionTimeMillisEstimate" : 0,
"works" : 3,
"advanced" : 2,
"needTime" : 0,
"needYield" : 0,
"saveState" : 906,
"restoreState" : 906,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"_id.boardId.boardDate" : -1,
"equipmentAssignments.assignedId" : 1
},
"indexName" : "_id.boardId.boardDate_-1_equipmentAssignments.assignedId_1",
-------------
My initial reaction was like... huh? why was the index including field "equipmentAssignments.assignedId" picked over my indexes with much better coverage chose over the index that included a field that has nothing to do with what I am trying to search. Luckily it didn't take long for me to figure this one out as the sort direction I chose was 1 and -1.
{"_id.boardId.boardDate":-1,"shift.start":-1 } && {"_id.boardId.boardDate":-1,"shift.end":-1 }
executionStats after putting in new index with better sort direction for the query
----------------------------
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 62,
"executionTimeMillis" : 224,
"totalKeysExamined" : 6645,
"totalDocsExamined" : 6983,
"executionStages" : {
"stage" : "FETCH",
"filter" : {
"_id.boardId.location.correlation" : {
"$eq" : "MN01"
}
},
"nReturned" : 62,
"executionTimeMillisEstimate" : 20,
"works" : 6649,
"advanced" : 62,
"needTime" : 6585,
"needYield" : 0,
"saveState" : 519,
"restoreState" : 519,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 5812,
"alreadyHasObj" : 338,
"inputStage" : {
"stage" : "OR",
"nReturned" : 5812,
"executionTimeMillisEstimate" : 0,
"works" : 6648,
"advanced" : 5812,
"needTime" : 835,
"needYield" : 0,
"saveState" : 519,
"restoreState" : 519,
"isEOF" : 1,
"invalidates" : 0,
"dupsTested" : 5812,
"dupsDropped" : 0,
"recordIdsForgotten" : 0,
"inputStages" : [
{
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"shift.start" : {
"$lte" : ISODate("2019-09-26T04:00:00Z")
}
},
{
"started" : {
"$exists" : true
}
},
{
"$nor" : [
{
"ended" : {
"$exists" : true
}
}
]
}
]
},
"nReturned" : 0,
"executionTimeMillisEstimate" : 0,
"works" : 3,
"advanced" : 0,
"needTime" : 2,
"needYield" : 0,
"saveState" : 519,
"restoreState" : 519,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 2,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 2,
"executionTimeMillisEstimate" : 0,
"works" : 3,
"advanced" : 2,
"needTime" : 0,
"needYield" : 0,
"saveState" : 519,
"restoreState" : 519,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"_id.boardId.boardDate" : -1,
"shift.end" : -1
},
"indexName" : "_id.boardId.boardDate_-1_shift.end_-1",
"isMultiKey" : false,
"multiKeyPaths" : {
"_id.boardId.boardDate" : [ ],
"shift.end" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"_id.boardId.boardDate" : [
"[new Date(1569470400000), new Date(1569470400000)]"
],
"shift.end" : [
"[MaxKey, MinKey]"
]
},
"keysExamined" : 2,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
},
{
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"started" : {
"$exists" : true
}
},
{
"$nor" : [
{
"ended" : {
"$exists" : true
}
}
]
}
]
},
"nReturned" : 338,
"executionTimeMillisEstimate" : 0,
"works" : 1170,
"advanced" : 338,
"needTime" : 831,
"needYield" : 0,
"saveState" : 519,
"restoreState" : 519,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 1169,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 1169,
"executionTimeMillisEstimate" : 0,
"works" : 1170,
"advanced" : 1169,
"needTime" : 0,
"needYield" : 0,
"saveState" : 519,
"restoreState" : 519,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"_id.boardId.boardDate" : -1,
"shift.end" : -1
},
"indexName" : "_id.boardId.boardDate_-1_shift.end_-1",
"isMultiKey" : false,
"multiKeyPaths" : {
"_id.boardId.boardDate" : [ ],
"shift.end" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"_id.boardId.boardDate" : [
"[new Date(1569297600000), new Date(1569297600000)]"
],
"shift.end" : [
"[new Date(9223372036854775807), new Date(1569384000000)]"
]
},
"keysExamined" : 1169,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
},
{
"stage" : "IXSCAN",
"nReturned" : 5474,
"executionTimeMillisEstimate" : 0,
"works" : 5475,
"advanced" : 5474,
"needTime" : 0,
"needYield" : 0,
"saveState" : 519,
"restoreState" : 519,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"_id.boardId.boardDate" : -1,
"shift.end" : -1
},
"indexName" : "_id.boardId.boardDate_-1_shift.end_-1",
"isMultiKey" : false,
"multiKeyPaths" : {
"_id.boardId.boardDate" : [ ],
"shift.end" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"_id.boardId.boardDate" : [
"[new Date(1569384000000), new Date(1569384000000)]"
],
"shift.end" : [
"[MaxKey, MinKey]"
]
},
"keysExamined" : 5474,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
----------------------------------
This is certainly better in all front, after gone to a coffee break and back, immediately realizing a better index patterns. :) Yes breaks are important :))
{"_id.boardId.boardDate":-1,"shift.start":-1,"_id.boardId.location.correlation":1} && {"_id.boardId.boardDate":-1,"shift.end":-1,"_id.boardId.location.correlation":1 }
These new pair of index gave us near perfect return, with the keyExamined and DocExamined almost identical, not to mentioned the number of docs returned is less than 2:1 if using keyExamined:nReturned
executionStats
----------------------------
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 62,
"executionTimeMillis" : 11,
"totalKeysExamined" : 108,
"totalDocsExamined" : 82,
"executionStages" : {
"stage" : "FETCH",
"nReturned" : 62,
"executionTimeMillisEstimate" : 0,
"works" : 109,
"advanced" : 62,
"needTime" : 45,
"needYield" : 0,
"saveState" : 8,
"restoreState" : 8,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 62,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "OR",
"nReturned" : 62,
"executionTimeMillisEstimate" : 0,
"works" : 108,
"advanced" : 62,
"needTime" : 45,
"needYield" : 0,
"saveState" : 8,
"restoreState" : 8,
"isEOF" : 1,
"invalidates" : 0,
"dupsTested" : 62,
"dupsDropped" : 0,
"recordIdsForgotten" : 0,
"inputStages" : [
{
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"started" : {
"$exists" : true
}
},
{
"$nor" : [
{
"ended" : {
"$exists" : true
}
}
]
}
]
},
"nReturned" : 0,
"executionTimeMillisEstimate" : 0,
"works" : 24,
"advanced" : 0,
"needTime" : 23,
"needYield" : 0,
"saveState" : 8,
"restoreState" : 8,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 20,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 20,
"executionTimeMillisEstimate" : 0,
"works" : 24,
"advanced" : 20,
"needTime" : 3,
"needYield" : 0,
"saveState" : 8,
"restoreState" : 8,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"_id.boardId.boardDate" : -1,
"shift.end" : -1,
"_id.boardId.location.correlation" : 1
},
"indexName" : "_id.boardId.boardDate_-1_shift.end_-1__id.boardId.location.correlation_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"_id.boardId.boardDate" : [ ],
"shift.end" : [ ],
"_id.boardId.location.correlation" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"_id.boardId.boardDate" : [
"[new Date(1569297600000), new Date(1569297600000)]"
],
"shift.end" : [
"[new Date(9223372036854775807), new Date(1569384000000)]"
],
"_id.boardId.location.correlation" : [
"[\"MN01\", \"MN01\"]"
]
},
"keysExamined" : 24,
"seeks" : 4,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
},
{
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"started" : {
"$exists" : true
}
},
{
"$nor" : [
{
"ended" : {
"$exists" : true
}
}
]
}
]
},
"nReturned" : 0,
"executionTimeMillisEstimate" : 0,
"works" : 2,
"advanced" : 0,
"needTime" : 1,
"needYield" : 0,
"saveState" : 8,
"restoreState" : 8,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 0,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 0,
"executionTimeMillisEstimate" : 0,
"works" : 2,
"advanced" : 0,
"needTime" : 1,
"needYield" : 0,
"saveState" : 8,
"restoreState" : 8,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"_id.boardId.boardDate" : -1,
"shift.start" : -1,
"_id.boardId.location.correlation" : 1
},
"indexName" : "_id.boardId.boardDate_-1_shift.start_-1__id.boardId.location.correlation_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"_id.boardId.boardDate" : [ ],
"shift.start" : [ ],
"_id.boardId.location.correlation" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"_id.boardId.boardDate" : [
"[new Date(1569470400000), new Date(1569470400000)]"
],
"shift.start" : [
"[new Date(1569470400000), true)"
],
"_id.boardId.location.correlation" : [
"[\"MN01\", \"MN01\"]"
]
},
"keysExamined" : 2,
"seeks" : 2,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
},
{
"stage" : "IXSCAN",
"nReturned" : 62,
"executionTimeMillisEstimate" : 0,
"works" : 82,
"advanced" : 62,
"needTime" : 19,
"needYield" : 0,
"saveState" : 8,
"restoreState" : 8,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"_id.boardId.boardDate" : -1,
"shift.start" : -1,
"_id.boardId.location.correlation" : 1
},
"indexName" : "_id.boardId.boardDate_-1_shift.start_-1__id.boardId.location.correlation_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"_id.boardId.boardDate" : [ ],
"shift.start" : [ ],
"_id.boardId.location.correlation" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"_id.boardId.boardDate" : [
"[new Date(1569384000000), new Date(1569384000000)]"
],
"shift.start" : [
"[MaxKey, MinKey]"
],
"_id.boardId.location.correlation" : [
"[\"MN01\", \"MN01\"]"
]
},
"keysExamined" : 82,
"seeks" : 20,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
]
}
}
------------------------
Conclusion:
1. Sort direction matters, thinks about how the data are stored and how they are being used
2. Coffee break is as important as first point. :)
Environment:
MongoDB 3.6.6
2019-09-25T11:30:17.696-0400 I COMMAND [conn19014149] command task.task command: find { find: "task", filter: { $and: [ { _id.boardId.location.correlation: "MN01" }, { $or: [ { _id.boardId.boardDate: new Date(1569384000000) }, { _id.boardId.boardDate: new Date(1569297600000), started: { $exists: true }, ended: { $exists: false }, shift.end: { $gte: new Date(1569384000000) } }, { _id.boardId.boardDate: new Date(1569470400000), started: { $exists: true }, ended: { $exists: false }, shift.start: { $lte: new Date(1569470400000) } } ] } ] }, $db: "task", $clusterTime: { clusterTime: Timestamp(1569425411, 1), signature: { hash: BinData(0, A8F704450DF4EF062ABB018B8BC3C175CE3CC62A), keyId: 6680100327825342465 } }, lsid: { id: UUID("683fbed5-1856-4181-b18f-d06db6b6cbb8") } } planSummary: IXSCAN { _id.boardId.boardDate: 1 }, IXSCAN { _id.boardId.boardDate: 1, personnelAssignments.assignedId.identifier: 1 }, IXSCAN { _id.boardId.boardDate: 1 } keysExamined:11588 docsExamined:11926 fromMultiPlanner:1 cursorExhausted:1 numYields:724 nreturned:62 reslen:50591 locks:{ Global: { acquireCount: { r: 1450 } }, Database: { acquireCount: { r: 725 } }, Collection: { acquireCount: { r: 725 } } } protocol:op_msg 590ms
Take out everything else not related the query we are tuning, we have the query
db.getSiblingDB("task").task.find({ $and: [ { "_id.boardId.location.correlation": "MN01" }, { $or: [ { "_id.boardId.boardDate": new Date(1569384000000) }, { "_id.boardId.boardDate": new Date(1569297600000), started: { $exists: true }, ended: { $exists: false }, "shift.end": { $gte: new Date(1569384000000) } }, { "_id.boardId.boardDate": new Date(1569470400000), started: { $exists: true }, ended: { $exists: false }, "shift.start": { $lte: new Date(1569470400000) } } ] } ] }).explain("executionStats")
Without running the query yet, by looking the fields used to search for the documents, we know that the field "_id.boardId.boardDate" is the most selective, along with "shift.start", luckily they are being used within the $or operator, which grants us to utilize multiple index for this search, so initially the shape of index I chose to create were
{"_id.boardId.boardDate":1,"shift.start":-1 } && {"_id.boardId.boardDate":1,"shift.end":-1 }
I chose to do -1 for shift.start and shift.end were mainly due to how they are being searched as in range search, after thought it over the second time, it wasn't the right decision to do, especially not with this query.
I had an index
{"_id.boardId.boardDate" : -1, "equipmentAssignments.assignedId" : 1}
from another index that I was tuning, this will come into picture as I explained further
This is the executionStats of the query
------------
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 62,
"executionTimeMillis" : 420,
"totalKeysExamined" : 11594,
"totalDocsExamined" : 11932,
"executionStages" : {
"stage" : "FETCH",
"filter" : {
"_id.boardId.location.correlation" : {
"$eq" : "MN01"
}
},
"nReturned" : 62,
"executionTimeMillisEstimate" : 30,
"works" : 11598,
"advanced" : 62,
"needTime" : 11534,
"needYield" : 0,
"saveState" : 906,
"restoreState" : 906,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 5812,
"alreadyHasObj" : 338,
"inputStage" : {
"stage" : "OR",
"nReturned" : 5812,
"executionTimeMillisEstimate" : 10,
"works" : 11597,
"advanced" : 5812,
"needTime" : 5784,
"needYield" : 0,
"saveState" : 906,
"restoreState" : 906,
"isEOF" : 1,
"invalidates" : 0,
"dupsTested" : 5812,
"dupsDropped" : 0,
"recordIdsForgotten" : 0,
"inputStages" : [
{
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"shift.start" : {
"$lte" : ISODate("2019-09-26T04:00:00Z")
}
},
{
"started" : {
"$exists" : true
}
},
{
"$nor" : [
{
"ended" : {
"$exists" : true
}
}
]
}
]
},
"nReturned" : 0,
"executionTimeMillisEstimate" : 0,
"works" : 3,
"advanced" : 0,
"needTime" : 2,
"needYield" : 0,
"saveState" : 906,
"restoreState" : 906,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 2,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 2,
"executionTimeMillisEstimate" : 0,
"works" : 3,
"advanced" : 2,
"needTime" : 0,
"needYield" : 0,
"saveState" : 906,
"restoreState" : 906,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"_id.boardId.boardDate" : -1,
"equipmentAssignments.assignedId" : 1
},
"indexName" : "_id.boardId.boardDate_-1_equipmentAssignments.assignedId_1",
-------------
My initial reaction was like... huh? why was the index including field "equipmentAssignments.assignedId" picked over my indexes with much better coverage chose over the index that included a field that has nothing to do with what I am trying to search. Luckily it didn't take long for me to figure this one out as the sort direction I chose was 1 and -1.
{"_id.boardId.boardDate":-1,"shift.start":-1 } && {"_id.boardId.boardDate":-1,"shift.end":-1 }
executionStats after putting in new index with better sort direction for the query
----------------------------
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 62,
"executionTimeMillis" : 224,
"totalKeysExamined" : 6645,
"totalDocsExamined" : 6983,
"executionStages" : {
"stage" : "FETCH",
"filter" : {
"_id.boardId.location.correlation" : {
"$eq" : "MN01"
}
},
"nReturned" : 62,
"executionTimeMillisEstimate" : 20,
"works" : 6649,
"advanced" : 62,
"needTime" : 6585,
"needYield" : 0,
"saveState" : 519,
"restoreState" : 519,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 5812,
"alreadyHasObj" : 338,
"inputStage" : {
"stage" : "OR",
"nReturned" : 5812,
"executionTimeMillisEstimate" : 0,
"works" : 6648,
"advanced" : 5812,
"needTime" : 835,
"needYield" : 0,
"saveState" : 519,
"restoreState" : 519,
"isEOF" : 1,
"invalidates" : 0,
"dupsTested" : 5812,
"dupsDropped" : 0,
"recordIdsForgotten" : 0,
"inputStages" : [
{
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"shift.start" : {
"$lte" : ISODate("2019-09-26T04:00:00Z")
}
},
{
"started" : {
"$exists" : true
}
},
{
"$nor" : [
{
"ended" : {
"$exists" : true
}
}
]
}
]
},
"nReturned" : 0,
"executionTimeMillisEstimate" : 0,
"works" : 3,
"advanced" : 0,
"needTime" : 2,
"needYield" : 0,
"saveState" : 519,
"restoreState" : 519,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 2,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 2,
"executionTimeMillisEstimate" : 0,
"works" : 3,
"advanced" : 2,
"needTime" : 0,
"needYield" : 0,
"saveState" : 519,
"restoreState" : 519,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"_id.boardId.boardDate" : -1,
"shift.end" : -1
},
"indexName" : "_id.boardId.boardDate_-1_shift.end_-1",
"isMultiKey" : false,
"multiKeyPaths" : {
"_id.boardId.boardDate" : [ ],
"shift.end" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"_id.boardId.boardDate" : [
"[new Date(1569470400000), new Date(1569470400000)]"
],
"shift.end" : [
"[MaxKey, MinKey]"
]
},
"keysExamined" : 2,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
},
{
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"started" : {
"$exists" : true
}
},
{
"$nor" : [
{
"ended" : {
"$exists" : true
}
}
]
}
]
},
"nReturned" : 338,
"executionTimeMillisEstimate" : 0,
"works" : 1170,
"advanced" : 338,
"needTime" : 831,
"needYield" : 0,
"saveState" : 519,
"restoreState" : 519,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 1169,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 1169,
"executionTimeMillisEstimate" : 0,
"works" : 1170,
"advanced" : 1169,
"needTime" : 0,
"needYield" : 0,
"saveState" : 519,
"restoreState" : 519,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"_id.boardId.boardDate" : -1,
"shift.end" : -1
},
"indexName" : "_id.boardId.boardDate_-1_shift.end_-1",
"isMultiKey" : false,
"multiKeyPaths" : {
"_id.boardId.boardDate" : [ ],
"shift.end" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"_id.boardId.boardDate" : [
"[new Date(1569297600000), new Date(1569297600000)]"
],
"shift.end" : [
"[new Date(9223372036854775807), new Date(1569384000000)]"
]
},
"keysExamined" : 1169,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
},
{
"stage" : "IXSCAN",
"nReturned" : 5474,
"executionTimeMillisEstimate" : 0,
"works" : 5475,
"advanced" : 5474,
"needTime" : 0,
"needYield" : 0,
"saveState" : 519,
"restoreState" : 519,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"_id.boardId.boardDate" : -1,
"shift.end" : -1
},
"indexName" : "_id.boardId.boardDate_-1_shift.end_-1",
"isMultiKey" : false,
"multiKeyPaths" : {
"_id.boardId.boardDate" : [ ],
"shift.end" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"_id.boardId.boardDate" : [
"[new Date(1569384000000), new Date(1569384000000)]"
],
"shift.end" : [
"[MaxKey, MinKey]"
]
},
"keysExamined" : 5474,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
----------------------------------
This is certainly better in all front, after gone to a coffee break and back, immediately realizing a better index patterns. :) Yes breaks are important :))
{"_id.boardId.boardDate":-1,"shift.start":-1,"_id.boardId.location.correlation":1} && {"_id.boardId.boardDate":-1,"shift.end":-1,"_id.boardId.location.correlation":1 }
These new pair of index gave us near perfect return, with the keyExamined and DocExamined almost identical, not to mentioned the number of docs returned is less than 2:1 if using keyExamined:nReturned
executionStats
----------------------------
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 62,
"executionTimeMillis" : 11,
"totalKeysExamined" : 108,
"totalDocsExamined" : 82,
"executionStages" : {
"stage" : "FETCH",
"nReturned" : 62,
"executionTimeMillisEstimate" : 0,
"works" : 109,
"advanced" : 62,
"needTime" : 45,
"needYield" : 0,
"saveState" : 8,
"restoreState" : 8,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 62,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "OR",
"nReturned" : 62,
"executionTimeMillisEstimate" : 0,
"works" : 108,
"advanced" : 62,
"needTime" : 45,
"needYield" : 0,
"saveState" : 8,
"restoreState" : 8,
"isEOF" : 1,
"invalidates" : 0,
"dupsTested" : 62,
"dupsDropped" : 0,
"recordIdsForgotten" : 0,
"inputStages" : [
{
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"started" : {
"$exists" : true
}
},
{
"$nor" : [
{
"ended" : {
"$exists" : true
}
}
]
}
]
},
"nReturned" : 0,
"executionTimeMillisEstimate" : 0,
"works" : 24,
"advanced" : 0,
"needTime" : 23,
"needYield" : 0,
"saveState" : 8,
"restoreState" : 8,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 20,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 20,
"executionTimeMillisEstimate" : 0,
"works" : 24,
"advanced" : 20,
"needTime" : 3,
"needYield" : 0,
"saveState" : 8,
"restoreState" : 8,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"_id.boardId.boardDate" : -1,
"shift.end" : -1,
"_id.boardId.location.correlation" : 1
},
"indexName" : "_id.boardId.boardDate_-1_shift.end_-1__id.boardId.location.correlation_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"_id.boardId.boardDate" : [ ],
"shift.end" : [ ],
"_id.boardId.location.correlation" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"_id.boardId.boardDate" : [
"[new Date(1569297600000), new Date(1569297600000)]"
],
"shift.end" : [
"[new Date(9223372036854775807), new Date(1569384000000)]"
],
"_id.boardId.location.correlation" : [
"[\"MN01\", \"MN01\"]"
]
},
"keysExamined" : 24,
"seeks" : 4,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
},
{
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"started" : {
"$exists" : true
}
},
{
"$nor" : [
{
"ended" : {
"$exists" : true
}
}
]
}
]
},
"nReturned" : 0,
"executionTimeMillisEstimate" : 0,
"works" : 2,
"advanced" : 0,
"needTime" : 1,
"needYield" : 0,
"saveState" : 8,
"restoreState" : 8,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 0,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 0,
"executionTimeMillisEstimate" : 0,
"works" : 2,
"advanced" : 0,
"needTime" : 1,
"needYield" : 0,
"saveState" : 8,
"restoreState" : 8,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"_id.boardId.boardDate" : -1,
"shift.start" : -1,
"_id.boardId.location.correlation" : 1
},
"indexName" : "_id.boardId.boardDate_-1_shift.start_-1__id.boardId.location.correlation_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"_id.boardId.boardDate" : [ ],
"shift.start" : [ ],
"_id.boardId.location.correlation" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"_id.boardId.boardDate" : [
"[new Date(1569470400000), new Date(1569470400000)]"
],
"shift.start" : [
"[new Date(1569470400000), true)"
],
"_id.boardId.location.correlation" : [
"[\"MN01\", \"MN01\"]"
]
},
"keysExamined" : 2,
"seeks" : 2,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
},
{
"stage" : "IXSCAN",
"nReturned" : 62,
"executionTimeMillisEstimate" : 0,
"works" : 82,
"advanced" : 62,
"needTime" : 19,
"needYield" : 0,
"saveState" : 8,
"restoreState" : 8,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"_id.boardId.boardDate" : -1,
"shift.start" : -1,
"_id.boardId.location.correlation" : 1
},
"indexName" : "_id.boardId.boardDate_-1_shift.start_-1__id.boardId.location.correlation_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"_id.boardId.boardDate" : [ ],
"shift.start" : [ ],
"_id.boardId.location.correlation" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"_id.boardId.boardDate" : [
"[new Date(1569384000000), new Date(1569384000000)]"
],
"shift.start" : [
"[MaxKey, MinKey]"
],
"_id.boardId.location.correlation" : [
"[\"MN01\", \"MN01\"]"
]
},
"keysExamined" : 82,
"seeks" : 20,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
]
}
}
------------------------
Conclusion:
1. Sort direction matters, thinks about how the data are stored and how they are being used
2. Coffee break is as important as first point. :)
Comments
Post a Comment