MongoDB Execution Plan analysis Detailed (1)

Body Queryplanner

Queryplanner is the default mode for the current version of explain, and Queryplanner mode does not actually query queries, but instead performs a plan analysis and selects winning plan for the query statement.

{"Queryplanner": {"plannerversion": 1, "namespace": "Game_db.game_user",                                "Indexfilterset": false, "Parsedquery": {"W": { "$eq": 1}, "Winningplan": {"stage                                ":" FETCH "," Inputstage ": {" stage ":" IXSCAN ",  "Keypattern": {"W": 1, "n":                                1}, "IndexName": "W_1_n_1", "Ismultikey": false, "direction": "Forward", "Indexbound                                 S ": {" w ": [" [1.0, 1.0] "       ], "n": ["[Minkey, Maxkey]"                ]                                }                        }                },                                "Rejectedplans": [{"Stage": "FETCH",                                        "Inputstage": {"stage": "IXSCAN",                                                "Keypattern": {"W": 1,                                        "V": 1}, "IndexName": "W_1_v_1",                                         "Ismultikey": false, "direction": "Forward",                                  "Indexbounds": {"W": [                      "[1.0, 1.0]"],                                                "V": ["[Minkey, Maxkey]"                ]                                        }                                }                        } ]        },

Let's look at the various return meanings of the queryplanner pattern.

Explain.queryplanner

The return of the Queryplanner.

Explain.queryPlanner.namespace

As the name implies, this value returns the table queried by the query.

Explain.queryPlanner.indexFilterSet

Whether there is a indexfilter for the query (which is explained in detail later in this article).

Explain.queryPlanner.winningPlan

The details of the optimal execution plan returned by the query optimizer for this query.

Explain.queryPlanner.winningPlan.stage

The stage of the optimal execution plan, where the return is fetch, can be understood as retrieving the specific document by returning the index position (there are several patterns in the stage, which will be explained later in this article).

Explain.queryPlanner.winningPlan.inputStage

Explain.queryPlanner.winningPlan.stage's child stage, here is Ixscan, indicates that the index scanning is being carried out.

Explain.queryPlanner.winningPlan.keyPattern

The index content scanned, here is w:1 and N:1.

Explain.queryPlanner.winningPlan.indexName

Index selected by winning plan.

Explain.queryPlanner.winningPlan.isMultiKey

Whether it is Multikey, where the return is false, if the index is based on an array, this will be true.

Explain.queryPlanner.winningPlan.direction

The query order, here is forward, if the. Sort ({w:-1}) will display backward.

Explain.queryPlanner.winningPlan.indexBounds

Winningplan the index range scanned, where the query condition is w:1, using the index is a joint index W and N, so w is [1.0,1.0] and N is not specified in the query conditions, it is [Minkey,maxkey].

Explain.queryPlanner.rejectedPlans

The detailed return of other execution plans (non-optimal and reject by the query optimizer), where the specific information is the same as the return of the Winningplan, so do not repeat it here.

Executionstats

The return of Executionstats is more like this:

  "Executionstats": {"executionsuccess": True, "nreturned": 29861, "Execu                Tiontimemillis ": 23079," totalkeysexamined ": 29861," totaldocsexamined ": 29861,                        "Executionstages": {"stage": "FETCH", "nreturned": 29861, "Executiontimemillisestimate": 22685, "Works": 29862, "Adva nced ": 29861," Needtime ": 0," Needfetch ": 0," Save State ": 946," restorestate ": 946," IsEOF ": 1," INV                        Alidates ": 0," docsexamined ": 29861," Alreadyhasobj ": 0, "Inputstage": {"stage": "IXSCAN", "nreturned": 29                    861,            "Executiontimemillisestimate": "Works": 29862,                                "Advanced": 29861, "Needtime": 0, "Needfetch": 0,                                "SaveState": 946, "restorestate": 946,                                        "IsEOF": 1, "invalidates": 0, "Keypattern": {                                "W": 1, "n": 1                                }, "IndexName": "W_1_n_1", "Ismultikey": false,                                        "Direction": "Forward", "Indexbounds": {                                        "W": ["[1.0, 1.0]"                      ],                  "N": ["[Minkey, Maxkey]"                                ]}, "keysexamined": 29861, "dupstested": 0, "dupsdropped": 0, "Seeninvalidat Ed ": 0," matchtested ": 0}},

Executionstats mode, we mainly need to note that the return has the following several

Executionstats.executionsuccess

Whether the execution succeeds

executionstats.nreturned

Number of returned bars for the query

Executionstats.executiontimemillis

Overall execution time

executionstats.totalkeysexamined

Number of index scans

executionstats.totaldocsexamined

Number of document scans

The above several very good understanding, we do not detail here, later in the case will have the analysis.

ExecutionStats.executionStages.stage

Here is the fetch to scan for documents

executionStats.executionStages.nReturned

Because it is a fetch, the value here is consistent with executionstats.nreturned

executionStats.executionStages.docsExamined

Consistent with executionstats.totaldocsexamined

Executionstats.inputstage in the same way as the above understanding

There are also some documents that are not described in returns such as:

"Works": 29862,

"Advanced": 29861,

"IsEOF": 1,

These values will be initialized at the beginning of the Explan:

Mongo/src/mongo/db/exec/plan_stats.h

struct CommonStats {    CommonStats(const char* type)        : stageTypeStr(type),          works(0),          yields(0),          unyields(0),          invalidates(0),          advanced(0),          needTime(0),          needYield(0),          executionTimeMillis(0),          isEOF(false) {}

Take works as an example, see the source code found, each operation will add 1, and the execution time will be recorded in the Executiontimemillis.

Mongo/src/mongo/db/exec/fetch.cpp

         ++_commonStats.works;        // Adds the amount of time taken by work() to executionTimeMillis.        ScopedTimer timer(&_commonStats.executionTimeMillis);

And at the end of the query Eof,works will add 1,advanced not add.

Mongo/src/mongo/db/exec/eof.cpp

PlanStage::StageState EOFStage::work(WorkingSetID* out) {    ++_commonStats.works;    // Adds the amount of time taken by work() to executionTimeMillis.    ScopedTimer timer(&_commonStats.executionTimeMillis);    return PlanStage::IS_EOF;}

So the normal return works will be 1 more than nreturned, when IsEOF is true (1):

Mongo/src/mongo/db/exec/eof.cpp

bool EOFStage::isEOF() {    return true;}unique_ptr<PlanStageStats> EOFStage::getStats() {    _commonStats.isEOF = isEOF();    return make_unique<PlanStageStats>(_commonStats, STAGE_EOF);}

The return value of advanced is +1 when hit, and does not increase when skip,eof:

Mongo/src/mongo/db/exec/skip.cpp

if (PlanStage::ADVANCED == status) {        // If we‘re still skipping results...        if (_toSkip > 0) {            // ...drop the result.            --_toSkip;            _ws->free(id);            ++_commonStats.needTime;            return PlanStage::NEED_TIME;        }        *out = id;        ++_commonStats.advanced;        return PlanStage::ADVANCED;

MongoDB Execution Plan analysis Detailed (1)