Why do multiple clauses in the $or operator messes up the 'text' index?

[Julius Šakalys]

I am seeking help from an index expert.

Please examine this query:

    db.getCollection('tasks').find({

      $text: {$search: "anything"},

      $or: [

        {tags: 'tag'},

        {'collaborators._id': 'id'}

      ]

    })

    .explain("executionStats");

The resulting explanation of this query states, that the winning plan is FETCH; whereas if I remove any one of the entries in the `$or` operator, then the winning plan becomes TEXT.

The expected behavior is that using this query, the runner would not end up using FETCH, because we have a huge database with big documents, so FETCH is killing our DB.

---

There are the following indexes:

    [

      //...

      {

        "key": {

          "_fts": "text",

          "_ftsx": 1,

          "collaborators._id": 1,

          "tags": 1

        },

        "weights": {

          "comments.text": 1,

          "title": 3

        },

        "language_override": "language",

        "default_language": "english",

        "textIndexVersion": 3

      },

      {

        "key": {

          "collaborators._id": 1

        },

      },

      {

        "key": {

          "tags": 1

        },

      },

      //...

    ]

I would really appreciate any help.

[Kevin Adistambha]

Hi Julius

I believe this is caused by two separate things:

1. Text index is a “special” index, similar to geo index
2. $or is a special operator that behaves slightly differently compared to e.g. $and

The page Text Indexes, especially the restrictions section describes how text indexes behaves in a different manner compared to typical compound index.

Also, the $or page contains an explanation about the behaviour of $or. Specifically:

When evaluating the clauses in the $or expression, MongoDB either performs a collection scan or, if all the clauses are supported by indexes, MongoDB performs index scans.

That is, a query such as

db.test.find({$or: [{a:1}, {b:1}]})

can be thought of like two queries of db.test.find({a:1}) and db.test.find({b:1}), where the results of both queries are combined. This is why a $or query can use two (or more) different indexes. In contrast, typical find() queries will only use one index.

In terms of your query, since you’re combining those two special characteristics in a single query, MongoDB cannot use the compound text index like a normal compound index. However, if you remove one of the $or term, the query can use the compound text index, since the query basically becomes {$text: {$search: ...}, field: ...} (i.e. the $or has no effect anymore).

Best regards
Kevin

​