$in operator is an equality or a range?

Hi community,

While I’m working on index I came across with a “strange” behavior of index.
I’ve a simple query:

{
	$and[	
		{
		   “a”:1
		},
		{
		  “b”:
			{$in:[3,4,5]}
		}
	]
	$sort:{
		{ d: 1}
	}
}

Following the ESR rule written on the mongoDB official doc, my index is:

{a:1, d:1, b:1}. ($in considered as range)

But, when I execute the query it is used another index that is:

{a:1, b:1, d:1}. ($in considered as equality)

I’ve already checked the score with the explain() and the second index is better than the first one.

To write my index with the $in operator I followed the little paragraph on the Official Doc that says:

$in can be an equality operator or a range operator. When $in is used alone, it is an equality operator that does a series of equality matches. $in acts like a range operator when it is used with .sort() .

So, my question is:

The line: " $in acts like a range operator when it is used with .sort()" means that the same field used in $in must be used in $sort operator to consider $in as a range operator in the index?

Thanks in advance

Hi @Luciano_Bigiotti and welcome to MongoDB community forums!!

could you please help me understand the above statement on how the the second index was performing better than that first index defined above. Also, what is the meaning of the scores here ?

I tried to replicate the same in my local environment and here is my understanding:

Case 1:
when index is a_1_c_1_b_1 and the query is db.sample.find({a:1,c:{$in:[1,2,3]}}).sort({c:1}), in this case according to the ESR rule, $in is using the index for range. Hence the field values, a follows the Equality and c follows the Range in the ESR rule.
The following is explained in the below part of the explain():

      stage: 'FETCH',
      inputStage: {
        stage: 'IXSCAN',
        keyPattern: { a: 1, c: 1, b: 1 },
        indexName: 'a_1_c_1_b_1',
        isMultiKey: false,
        multiKeyPaths: { a: [], c: [], b: [] },
        direction: 'forward',
        indexBounds: {
          a: [ '[1, 1]' ],
          c: [ '[1, 1]', '[2, 2]', '[3, 3]' ],
          b: [ '[MinKey, MaxKey]' ]
        }
      }
    },

Case 2: Now, consider the same index a_1_c_1_b_1 and the query is db.sample.find({a:1,b:{$in:[1,2,3]}}).sort({c:1}). Here the $in is used as equality operator as MongoDB will look for exact matches of the values within the $in array.
The explain output here:

winningPlan: {
      stage: 'FETCH',
      inputStage: {
        stage: 'IXSCAN',
        keyPattern: { a: 1, c: 1, b: 1 },
        indexName: 'a_1_c_1_b_1',
        isMultiKey: false,
        multiKeyPaths: { a: [], c: [], b: [] },
        direction: 'forward',
        indexBounds: {
          a: [ '[1, 1]' ],
          c: [ '[MinKey, MaxKey]' ],
          b: [ '[1, 1]', '[2, 2]', '[3, 3]' ]
        }
      }
    },

shows that $in is being used a the equality operator in this case.

Case 3: Now consider a case where index is a_1_c_1_b_1 and the query is db.sample.find({a:1,c:{$in:[1,2,3]}}).sort({b:1}), the $in here is used as the equality operator as shown in case 2 and the sort merge stage will be used in this case.

The explain out for this is shown as:

winningPlan: {
      stage: 'FETCH',
      inputStage: {
        stage: 'SORT_MERGE',
        sortPattern: { b: 1 },
        inputStages: [
          {
            stage: 'IXSCAN',
            keyPattern: { a: 1, c: 1, b: 1 },
            indexName: 'a_1_c_1_b_1',
            isMultiKey: false,
            multiKeyPaths: { a: [], c: [], b: [] },
            indexVersion: 2,
            direction: 'forward',
            indexBounds: {
              a: [ '[1, 1]' ],
              c: [ '[1, 1]' ],
              b: [ '[MinKey, MaxKey]' ]
            }
          },
          {
            stage: 'IXSCAN',
            keyPattern: { a: 1, c: 1, b: 1 },
            indexName: 'a_1_c_1_b_1',
            isMultiKey: false,
            multiKeyPaths: { a: [], c: [], b: [] },
            indexVersion: 2,
            direction: 'forward',
            indexBounds: {
              a: [ '[1, 1]' ],
              c: [ '[2, 2]' ],
              b: [ '[MinKey, MaxKey]' ]
            }
          },
          {
            stage: 'IXSCAN',
            keyPattern: { a: 1, c: 1, b: 1 },
            indexName: 'a_1_c_1_b_1',
            isMultiKey: false,
            multiKeyPaths: { a: [], c: [], b: [] },
            indexVersion: 2,
            direction: 'forward',
            indexBounds: {
              a: [ '[1, 1]' ],
              c: [ '[3, 3]' ],
              b: [ '[MinKey, MaxKey]' ]
            }
          }
        ]
      }
    },

P.S. I have intentionally removed a few fields of the explain output to showcase only relevant fields.
Please feel free to reach out in case of any further questions.

Warm regards
Aasawari

1 Like