Question 249294 in Mongodb Question posted in
The official documentation can be found here.

mongodb not using index unless field is specified in $match

compound index

{
  A: 1,
  B: 1
}

In this query, the compound index is not used:

db.user.aggregate([
  { $match: {
      B: { $gt: 100 }
  } },
  {
    $sort: {
      A: 1
    }
  },
  {
    $limit: 1000
  }
])

I need do this in order to get it to use the index:

db.user.aggregate([
  { $match: {
      A: { $exists: true }
      B: { $gt: 100 }
  } },
  {
    $sort: {
      A: 1
    }
  },
  {
    $limit: 1000
  }
])

Adding A: { $exists: true } is unnecessary because all documents will have this field. I thought that mongodb was smart enough to use an index to sort?

Tags:

2

Answers


  1. 0

    The order of the index fields matter. Indexes are read left to right.

    The order of the indexed fields impacts the effectiveness of a compound index. Compound indexes contain references to documents according to the order of the fields in the index. To create efficient compound indexes, follow the ESR (Equality, Sort, Range) rule.

    Taken from MongoDB compound index documentation on field order.

    $sort will use the index if is used in the first stage, or if the $sort stage is only preceded by a $match stage (documentation).

    Creating a new index with the following order should fix your problem:

    {
  "B": 1,
  "A": 1
}

    This is of course assuming that your index is not already sorted correctly.

    Login or Signup to reply.
  2. 0

    The claim as written seems incorrect or incomplete. When I attempt to reproduce this on version 6.0.1 I see the index being used* as opposed to a collection scan:

    > db.version()
6.0.1
> db.user.createIndex({
...           "A": 1,
...           "B": 1
...         })
A_1_B_1
> db.user.aggregate([
...   {
.....     $match: {
.......       B: {
.........         $gt: 100
.........       }
.......     }
.....   },
...   {
.....     $sort: {
.......       A: 1
.......     }
.....   },
...   {
.....     $limit: 1000
.....   }
... ]).explain().queryPlanner.winningPlan
{
  stage: 'LIMIT',
  limitAmount: 1000,
  inputStage: {
    stage: 'FETCH',
    filter: { B: { '$gt': 100 } },
    inputStage: {
      stage: 'IXSCAN',
      keyPattern: { A: 1, B: 1 },
      indexName: 'A_1_B_1',
      isMultiKey: false,
      multiKeyPaths: { A: [], B: [] },
      isUnique: false,
      isSparse: false,
      isPartial: false,
      indexVersion: 2,
      direction: 'forward',
      indexBounds: { A: [ '[MinKey, MaxKey]' ], B: [ '[MinKey, MaxKey]' ] }
    }
  }
}

    We can similarly see the index used in this mongoplayground demonstration.

    So more specific details about your situation would be required in order to advise further on the question as written. Is collation involved perhaps? But the general answer to your question is that MongoDB can (and does) use the index in the situation that was described which aligns with the documentation referenced in the other answerv

    *Notably the index is not used as efficiently as it could be, and so it is probably separately a good idea to include the $exists clause to improve performance anyway. It just shouldn’t strictly be required in order to get the index to be used in the first place.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search