Mongodb execution stats

Krishna_Sai_Manda · 2024-05-23T06:50:39.691Z

What are the different stages in explain(“executionStats”), there are multiple stages like inner stage, input stage, outer stage, seek, ixseek, limit, ixscan, nlj etc…
There is nothing mentioned about all these stages in documentation.
Can someone please help me understand what each stage does and how to see the ordering of the stages

Peter_Hubbard · 2024-05-23T11:53:08.482Z

Does this help? https://www.mongodb.com/docs/manual/reference/explain-results/

Krishna_Sai_Manda · 2024-05-24T05:03:21.568Z

Hi @Peter_Hubbard, thanks for your reply, but I couldn’t find about all these stages in the link you’ve shared.
I am trying to analyse the performance difference between 2 queries, it has all the stages I mentioned above, I want to know all the differences between nlj, seek, branch, ixseek, unwind, unique and few more
And what’s the difference between inner and outer stages and what are the stats related to slots

Peter_Hubbard · 2024-05-24T07:26:17.783Z

Can you post an example of what you are seeing and what you want to know more about?

Krishna_Sai_Manda · 2024-05-24T10:41:03.927Z

{
“nReturned”: 1,
“executionTimeMillis”: 3357,
“totalKeysExamined”: 96,
“totalDocsExamined”: 1,
“executionStages”: {
“stage”: “SINGLE_SHARD”,
“nReturned”: 1,
“executionTimeMillis”: 3357,
“totalKeysExamined”: 96,
“totalDocsExamined”: 1,
“totalChildMillis”: NumberLong(3354),
“shards”: [
{
“shardName”: “mongo-shareddb”,
“executionSuccess”: true,
“nReturned”: 1,
“executionTimeMillis”: 3354,
“totalKeysExamined”: 96,
“totalDocsExamined”: 1,
“executionStages”: {
“stage”: “limit”,
“planNodeId”: 3,
“nReturned”: 1,
“executionTimeMillisEstimate”: 41,
“opens”: 1,
“closes”: 2,
“saveState”: 1,
“restoreState”: 1,
“isEOF”: 1,
“limit”: 25,
“inputStage”: {
“stage”: “nlj”,
“planNodeId”: 2,
“nReturned”: 1,
“executionTimeMillisEstimate”: 41,
“opens”: 1,
“closes”: 2,
“saveState”: 1,
“restoreState”: 1,
“isEOF”: 1,
“totalDocsExamined”: 1,
“totalKeysExamined”: 96,
“collectionScans”: 0,
“collectionSeeks”: 1,
“indexScans”: 0,
“indexSeeks”: 1,
“indexesUsed”: [
“group.name_1_category_1_organization_1”,
“group.name_1_category_1_organization_1”
],
“innerOpens”: 1,
“innerCloses”: 1,
“outerProjects”: ,
“outerCorrelated”: [
NumberLong(20),
NumberLong(21),
NumberLong(22),
NumberLong(23),
NumberLong(24)
],
“outerStage”: {
“stage”: “unique”,
“planNodeId”: 1,
“nReturned”: 1,
“executionTimeMillisEstimate”: 41,
“opens”: 1,
“closes”: 2,
“saveState”: 1,
“restoreState”: 1,
“isEOF”: 1,
“dupsTested”: 1,
“dupsDropped”: 0,
“keySlots”: [
NumberLong(20)
],
“inputStage”: {
“stage”: “branch”,
“planNodeId”: 1,
“nReturned”: 1,
“executionTimeMillisEstimate”: 41,
“opens”: 1,
“closes”: 2,
“saveState”: 1,
“restoreState”: 1,
“isEOF”: 1,
“numTested”: 1,
“thenBranchOpens”: 1,
“thenBranchCloses”: 1,
“elseBranchOpens”: 0,
“elseBranchCloses”: 0,
“filter”: "s25 ",
“thenSlots”: [
NumberLong(5),
NumberLong(7),
NumberLong(8),
NumberLong(9),
NumberLong(10)
],
“elseSlots”: [
NumberLong(11),
NumberLong(17),
NumberLong(18),
NumberLong(19),
NumberLong(10)
],
“outputSlots”: [
NumberLong(20),
NumberLong(21),
NumberLong(22),
NumberLong(23),
NumberLong(24)
],
“thenStage”: {
“stage”: “ixscan_generic”,
“planNodeId”: 1,
“nReturned”: 1,
“executionTimeMillisEstimate”: 41,
“opens”: 1,
“closes”: 1,
“saveState”: 1,
“restoreState”: 1,
“isEOF”: 1,
“indexName”: “group.name_1_category_1_organization_1”,
“keysExamined”: 96,
“seeks”: 96,
“numReads”: 97,
“indexKeySlot”: 9,
“recordIdSlot”: 5,
“snapshotIdSlot”: 7,
“indexIdentSlot”: 8,
“outputSlots”: ,
“indexKeysToInclude”: “00000000000000000000000000000000”
},
“elseStage”: {
“stage”: “nlj”,
“planNodeId”: 1,
“nReturned”: 0,
“executionTimeMillisEstimate”: 0,
“opens”: 0,
“closes”: 0,
“saveState”: 1,
“restoreState”: 1,
“isEOF”: 0,
“totalDocsExamined”: 0,
“totalKeysExamined”: 0,
“collectionScans”: 0,
“collectionSeeks”: 0,
“indexScans”: 0,
“indexSeeks”: 0,
“indexesUsed”: [
“group.name_1_category_1_organization_1”
],
“innerOpens”: 0,
“innerCloses”: 0,
“outerProjects”: ,
“outerCorrelated”: [
NumberLong(12),
NumberLong(13)
],
“outerStage”: {
“stage”: “project”,
“planNodeId”: 1,
“nReturned”: 0,
“executionTimeMillisEstimate”: 0,
“opens”: 0,
“closes”: 0,
“saveState”: 1,
“restoreState”: 1,
“isEOF”: 0,
“projections”: {
“12”: "getField(s15, "l") ",
“13”: "getField(s15, "h") "
},
“inputStage”: {
“stage”: “unwind”,
“planNodeId”: 1,
“nReturned”: 0,
“executionTimeMillisEstimate”: 0,
“opens”: 0,
“closes”: 0,
“saveState”: 1,
“restoreState”: 1,
“isEOF”: 0,
“inputSlot”: 14,
“outSlot”: 15,
“outIndexSlot”: 16,
“preserveNullAndEmptyArrays”: 0,
“inputStage”: {
“stage”: “limit”,
“planNodeId”: 1,
“nReturned”: 0,
“executionTimeMillisEstimate”: 0,
“opens”: 0,
“closes”: 0,
“saveState”: 1,
“restoreState”: 1,
“isEOF”: 0,
“limit”: 1,
“inputStage”: {
“stage”: “coscan”,
“planNodeId”: 1,
“nReturned”: 0,
“executionTimeMillisEstimate”: 0,
“opens”: 0,
“closes”: 0,
“saveState”: 1,
“restoreState”: 1,
“isEOF”: 0
}
}
}
},
“innerStage”: {
“stage”: “ixseek”,
“planNodeId”: 1,
“nReturned”: 0,
“executionTimeMillisEstimate”: 0,
“opens”: 0,
“closes”: 0,
“saveState”: 1,
“restoreState”: 1,
“isEOF”: 0,
“indexName”: “group.name_1_category_1_organization_1”,
“keysExamined”: 0,
“seeks”: 0,
“numReads”: 0,
“indexKeySlot”: 19,
“recordIdSlot”: 11,
“snapshotIdSlot”: 17,
“indexIdentSlot”: 18,
“outputSlots”: ,
“indexKeysToInclude”: “00000000000000000000000000000000”,
“seekKeyLow”: "s12 ",
“seekKeyHigh”: "s13 "
}
}
}
},
“innerStage”: {
“stage”: “limit”,
“planNodeId”: 2,
“nReturned”: 1,
“executionTimeMillisEstimate”: 0,
“opens”: 1,
“closes”: 1,
“saveState”: 1,
“restoreState”: 1,
“isEOF”: 1,
“limit”: 1,
“inputStage”: {
“stage”: “seek”,
“planNodeId”: 2,
“nReturned”: 1,
“executionTimeMillisEstimate”: 0,
“opens”: 1,
“closes”: 1,
“saveState”: 1,
“restoreState”: 1,
“isEOF”: 0,
“numReads”: 1,
“recordSlot”: 26,
“recordIdSlot”: 27,
“seekKeySlot”: 20,
“snapshotIdSlot”: 21,
“indexIdentSlot”: 22,
“indexKeySlot”: 23,
“indexKeyPatternSlot”: 24,
“fields”: ,
“outputSlots”:
}
}
}
}
}
]
}
}