1

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?

0

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.

  • 1
    Thanks, however in your example you are getting the IXSCAN step on a find operation, where it makes no sense to use a DISTINCT_SCAN. However in my case both steps are returned from a distinct operation. – Tzach Nov 15 '17 at 8:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.