Mongoid winningPlan does not use compound index

I have a compound index as follows.

  index({ account_id: 1, is_private: 1, visible_in_list: 1, sent_at: -1, user_id: 1, status: 1, type: 1, 'tracking.last_opened_at' => -1 }, {name: 'email_page_index'})

Then I have a query with these exact fields,

selector:
{"account_id"=>BSON::ObjectId('id'), "is_private"=>false, "visible_in_list"=>{:$in=>[true, false]}, "status"=>{:$in=>["ok", "queued", "processing", "failed"]}, "sent_at"=>{"$lte"=>2021-03-22 15:29:18 UTC}, "tracking.last_opened_at"=>{"$gt"=>1921-03-22 15:29:18 UTC}, "user_id"=>BSON::ObjectId('id')}
options:  {:sort=>{"tracking.last_opened_at"=>-1}}

The winningPlan is the following

"inputStage": {
    "stage": "SORT_KEY_GENERATOR",
    "inputStage": {
      "stage": "FETCH",
      "filter": {
        "$and": [
          {
            "account_id": {
              "$eq": {
                "$oid": "objectid"
              }
            }
          },
          {
            "is_private": {
              "$eq": false
            }
          },
          {
            "sent_at": {
              "$lte": "2021-03-22T14:06:10.000Z"
            }
          },
          {
            "tracking.last_opened_at": {
              "$gt": "1921-03-22T14:06:10.716Z"
            }
          },
          {
            "status": {
              "$in": [
                "failed",
                "ok",
                "processing",
                "queued"
              ]
            }
          },
          {
            "visible_in_list": {
              "$in": [
                false,
                true
              ]
            }
          }
        ]
      },
      "inputStage": {
        "stage": "IXSCAN",
        "keyPattern": {
          "user_id": 1
        },
        "indexName": "user_id_1",
        "isMultiKey": false,
        "multiKeyPaths": {
          "user_id": []
        },.....

And the rejected plan has the compound index and forms as follows

"rejectedPlans": [
  {
    "stage": "FETCH",
    "inputStage": {
      "stage": "SORT",
      "sortPattern": {
        "tracking.last_opened_at": -1
      },
      "inputStage": {
        "stage": "SORT_KEY_GENERATOR",
        "inputStage": {
          "stage": "IXSCAN",
          "keyPattern": {
            "account_id": 1,
            "is_private": 1,
            "visible_in_list": 1,
            "sent_at": -1,
            "user_id": 1,
            "status": 1,
            "type": 1,
            "tracking.last_opened_at": -1
          },
          "indexName": "email_page_index",
          "isMultiKey": false,
          "multiKeyPaths": {
            "account_id": [],
            "is_private": [],
            "visible_in_list": [],
            "sent_at": [],
            "user_id": [],
            "status": [],
            "type": [],
            "tracking.last_opened_at": []
          },
          "isUnique": false,

The problem is that the winningPlan is slow, wouldn't be better if mongoid choose the compound index? Is there a way to force it? Also, how can I see the execution time for each separate STAGE?

Solution

I am posting some information that can help resolve the issue of performance and use an appropriate index. Please note this may not be the solution (and the issue is open to discussion).

...Also, how can I see the execution time for each separate STAGE?

For this, generate the query plan using the explain with the executionStats verbosity mode.

The problem is that the winningPlan is slow, wouldn't be better if mongoid choose the compound index? Is there a way to force it?

As posted the plans show a "stage": "SORT_KEY_GENERATOR", implying that the sort operation is being performed in the memory (that is not using an index for the sort). That would be one (or main) of the reasons for the slow performance. So, how to make the query and the sort use the index?

A single compound index can be used for a query with a filter+sort operations. That would be an efficient index and query. But, it requires that the compound index be defined in a certain way - some rules need to be followed. See this topic on Sort and Non-prefix Subset of an Index - as is the case in this post. I quote the example from the documentation for illustration:

Suppose there is a compound index: { a: 1, b: 1, c: 1, d: 1 } And, all the fields are used in a query with filter+sort. The ideal query is, to have a filter+sort as follows:

db.test.find( { a: "val1", b: "val2", c: 1949 } ).sort( { d: 1 })

Note the query filter has three fields with equality condition (there are no $gt, $lt, etc.). Then the query's sort has the last field d of the index. This is the ideal situation where the index will be used for the query''s filter as well as sort operations.

In your case, this cannot be applied from the posted query. So, to work towards a solution you may have to define a new index so as to take advantage of the rule Sort and Non-prefix Subset of an Index.

Is it possible? It depends upon your application and the use case. I have an idea like this and it may help. Create a compound index like the follows and see how it works:

account_id: 1, 
is_private: 1
visible_in_list: 1,
status: 1,
user_id: 1, 
'tracking.last_opened_at': -1

I think having a condition "tracking.last_opened_at"=>{"$gt"=>1921-03-22 15:29:18 UTC}, in the query''s filter may not help for the usage of the index.

Also, include some details like the version of the MongoDB server, the size of collection and some platform details. In general, query performance depends upon many factors, including, indexes, RAM memory, size and type of data, and the kind of operations on the data.

The ESR Rule: When using compound index for a query with multiple filter conditions and sort, sometimes the Equality Sort Range rule is useful for optimizing the query. See the following post with such a scenario: MongoDB - Index not being used when sorting and limiting on ranged query