How to understand and troubleshooting Explain command field(s)

Hi Team,

Can you please briefly explain as below fields how to troubleshooting real time when we run explain(executionStats) command.

"executionStats" : {
                "executionSuccess" : true,
                "nReturned" : 4,
                "executionTimeMillis" : 0,
                "totalKeysExamined" : 0,
                "totalDocsExamined" : 4,
                "executionStages" : {
                        "stage" : "PROJECTION_DEFAULT",
                        "nReturned" : 4,
                        "executionTimeMillisEstimate" : 0,
                        "works" : 6,
                        "advanced" : 4,
                        "needTime" : 1,
                        "needYield" : 0,
                        "saveState" : 0,
                        "restoreState" : 0,
                        "isEOF" : 1,
                        "transformBy" : {
                                "$recordId" : {
                                        "$meta" : "recordId"
                                }
                        },
                        "inputStage" : {
                                "stage" : "COLLSCAN",
                                "nReturned" : 4,
                                "executionTimeMillisEstimate" : 0,
                                "works" : 6,
                                "advanced" : 4,
                                "needTime" : 1,
                                "needYield" : 0,
                                "saveState" : 0,
                                "restoreState" : 0,
                                "isEOF" : 1,
                                "direction" : "forward",
                                "docsExamined" : 4
                        }
                }
        },

Hi @hari_dba

You might be interested in a couple of @Christopher_Harris 's MongoDB.live 2020 and 2021 sessions that talks about this topic:

Tips and Tricks for Query Performance: Let Us .explain() Them

.Explaining() Aggregation Performance

Best regards
Kevin

1 Like

Hi @hari_dba,

The talks that @kevinadi pointed to (thanks!) should indeed help give you a framework for thinking about explain output overall. This should be pretty general guidance which may align well with your generalized question. If helpful, we do also maintain an Explain Results documentation page which contains some details about many of the individual fields in the output represent.

The overall topic of troubleshooting real time issues is quite broad and examining explain output is just one subset of that process. If there is something more specific that you are curious about, please do let us know and we may be able to point you towards more specific resources. At a high level, some observations about the snippet shared are:

  • This was probably executed against a testing environment, as the collection contains just 4 documents.
  • The operation retrieved all of the documents in the collection rather than using an index to selectively return a subset.
  • The operation additionally requested that the $recordId of the documents be returned. This is very uncommon to see from an application.

Hope this helps!

Best,
Chris

I read documentation page an Explain Result but no example

If you know example let me know

and also how to to troubleshooting query execution on Production servers how will be taking action bases on above explain command all field(s) executionStats and allPlansExecution both are the differences.