MongoDB Query Performance Analysis (explain)

This chapter introduces MongoDB query performance analysis, similar to SQL's explain, MongoDB also supports explain to analyze the performance of query statements.

Basic Usage

By calling the explain function, you can obtain the analysis result

// Analysis result of the find method
db.collection.find({}).explain();

// Analysis result of the aggregate method
db.collection.explain().aggregate([]);

explain has three modes:

• queryPlanner (default)
• executionStats
• allPlansExecution

Explanation:

• Using queryPlanner only lists all possible execution plans, does not execute the actual statement, and displays the winningPlan plan that has already won.
• Using executionStats executes only the winningPlan plan and outputs the result.
• Using allPlansExecution executes all plans and outputs the results.

Usage of different modes

// executionStats mode, simply pass parameters to the explain function
db.collection.find({}).explain('executionStats');

// allPlansExecution mode
db.collection.find({}).explain('allPlansExecution');

Explanation of explain content

queryPlanner Content

The following content is the default content returned by explain, ignoring non-key information

{
	"queryPlanner" : {
		"plannerVersion" : 1,
		"namespace" : "test.orders",
		"indexFilterSet" : false, // Key metric, whether index filtering data is used
		"winningPlan" : {
			"stage" : "COLLSCAN",  // Key metric, stage phase name. Each phase has phase-specific information, e.g., COLLSCAN represents scanning the entire collection content
			"direction" : "forward"
		},
		"rejectedPlans" : [ ]
	}
	...
}

Stage phase types are as follows:

• COLLSCAN: Full table scan
• IXSCAN: Index scan
• FETCH: Retrieve specific documents based on the index
• SHARD_MERGE: Merging data returned by each shard
• SORT: Indicates sorting in memory
• LIMIT: Limit the number of returns
• SKIP: Skip using
• IDHACK: Query for _id
• SHARDING_FILTER: Query sharded data through mongos
• COUNT: Count operations using db.coll.explain().count() or similar
• COUNTSCAN: Stage return when count is not using an index for count
• COUNT_SCAN: Stage return when count is using an index for count
• SUBPLA: Stage return for $or queries not using indexes
• TEXT: Stage return when querying using full-text indexes
• PROJECTION: Return stage for limiting returned fields

executionStats Content

The following content is the content returned in executionStats mode, ignoring non-key information

{
	"executionStats" : {
		"executionSuccess" : true,
		"nReturned" : 5,  // Number of returned documents
		"executionTimeMillis" : 0, // Execution time
		"totalKeysExamined" : 0, // How many indexes are scanned
		"totalDocsExamined" : 5, // Total number of scanned documents
		"executionStages" : {
			"stage" : "COLLS CAN", // Stage type, COLLSCAN means scanning the entire table
			"nReturned" : 5,
			"executionTimeMillisEstimate" : 0,
			"works" : 7,
			"advanced" : 5,
			"needTime" : 1,
			"needYield" : 0,
			"saveState" : 0,
			"restoreState" : 0,
			"isEOF" : 1,
			"direction" : "forward",
			"docsExamined" : 5
		}
	}
}

Query Optimization Ideas

• Use indexes as much as possible
• The fewer documents scanned, the better