Why IXSCAN not enough?

Working with Data
,
12

Hi @Anteaus

I think you’re hitting on a peculiarity of a multikey index, especially when paired with an $elemMatch :slight_smile:

Let me give an example. Suppose we’re searching for a specific element in an array using $elemMatch:

> db.test.find( {arr: { $elemMatch: {field:123} } })
[
  { _id: 2, arr: [ { field: 123 } ] }
]

Quite straightforward. The query returns one document.

However if we remove the $elemMatch:

> db.test.find( {arr:{field:123}} )
[
  { _id: 1, arr: { field: 123 } },
  { _id: 2, arr: [ { field: 123 } ] }
]

There are actually two documents matching that criteria. One with arr as an array, and one as a subdocument.

Now if we direct our attention to a very subtle sentence in the documentation for $elemMatch:

The $elemMatch operator matches documents that contain an array field with at least one element that matches all the specified query criteria.

The keyword here is “documents that contain an array field”. In other words, a query using $elemMatch must return a set of documents where the field is an array. This is why in the first example it returns only one document, while on the second example it returns all matching documents, array or otherwise.

Therefore the FETCH stage is needed in this case, because MongoDB can’t tell for sure that a field is an array for 100% of the documents in a collection, or if there are some documents that could match the query criteria, but is not an array. $elemMatch mandates that it must be an array, so it needs to check the actual document. This fact is not visible from the index alone.

Best regards
Kevin

3 Likes