Reading an explain Plan

explain takes three verbosity levels: queryPlanner (the default) shows the chosen plan without running it, executionStats runs it and reports what it did, allPlansExecution adds the rejected candidates' trial runs. Every number in this section came from the second.

The shape of an executionStats planJavaScript

const e = db.orders.find({ status: 'shipped', region: 'eu-west' })
  .sort({ createdAt: -1 }).limit(20).explain('executionStats');
print(Object.keys(e).join(', '));
print(JSON.stringify(e.executionStats.executionStages.inputStage.inputStage.indexBounds));

Output

explainVersion, queryPlanner, executionStats, queryShapeHash, command, serverInfo,
serverParameters, ok
{"status":["[\"shipped\", \"shipped\"]"],"region":["[\"eu-west\", \"eu-west\"]"],
 "createdAt":["[MaxKey, MinKey]"]}

The plan is a tree of stages and documents flow from the innermost stage outward: IXSCAN produces index keys, FETCH turns each into a document, LIMIT stops at twenty. Read it bottom-up and the bounds above become a sentence — walk status_1_region_1_createdAt_-1 where status is shipped and region is eu-west, newest first, and stop after twenty.

Four numbers under executionStats carry most of the meaning: totalKeysExamined, totalDocsExamined, nReturned and executionTimeMillis. Inside a stage, works counts units of work and needTime the ones that produced no document. Stage names tell the story before any number: COLLSCAN means no index, SORT a blocking in-memory sort, PROJECTION_COVERED that the collection was never touched, SORT_MERGE interleaved index ranges, EXPRESS_IXSCAN a fast path for point lookups. Aggregations explain the same way, through db.orders.explain('executionStats').aggregate([...]).

MENU