Mongodb – What’s the difference between DISTINCT_SCAN and IXSCAN in explain output

distinctexplainmongodbmongodb-3.4

In Mongo 3.4 explain("executionStats").distinct(...), for some queries
the winning plan contains an IXSCAN step, and for other queries it contains an DISTINCT_SCAN step.

What is the difference between the two? Is one of them faster than the other?

Best Answer

They are different stages for different purposes. I believe DISTINCT_SCAN is only present when there is an index in the field. An IXSCAN stage basically means that the query planner scans an index as one of the stages.

For example:

> db.test.createIndex({a:1})
{
  "createdCollectionAutomatically": true,
  "numIndexesBefore": 1,
  "numIndexesAfter": 2,
  "ok": 1
}

Doing find() on an indexed field will trigger the presence of an IXSCAN stage:

> db.test.explain().find({a:1})
{
  ...
    "winningPlan": {
      "stage": "FETCH",
      "inputStage": {
        "stage": "IXSCAN",
        "keyPattern": {
          "a": 1
        },
        "indexName": "a_1",
        ...

While doing distinct() on the indexed field will trigger the DISTINCT_SCAN stage:

> db.test.explain().distinct('a')
{
  ...
    "winningPlan": {
      "stage": "PROJECTION",
      "transformBy": {
        "_id": 0,
        "a": 1
      },
      "inputStage": {
        "stage": "DISTINCT_SCAN",
        "keyPattern": {
          "a": 1
        },
        "indexName": "a_1",
        ...

Note that in both cases, the index {a: 1} is used. Without the presence of the index, you will see a COLLSCAN stage instead.

One is not "faster" than the other. They're different stages serving different purposes.

Related Question