Poor performance on query with sort by two fields and a huge $or condition

Ops and Admin
,
1

I need some help on boosting performance of my query, which is used for a pagination purposes.
I’m curious if I could create an appropriate index for the query (probably with avoiding sort_merge somehow), but I wasn’t able to find an index to not face with merge sorting since each subquery in the $or condition has its own greater than condition for an _id field and I can’t get rid of it since it’s a big part of bussiness logic.

I created the following index for the query:
groupId: 1, isVisible: 1, isVisibleForPagination: 1, score: -1, _id: 1, type: -1

performance with the index:
“nReturned” : 20.0,
“executionTimeMillis” : 235.0,
“totalKeysExamined” : 85098.0,
“totalDocsExamined” : 20.0

Additional info, isVisible and isVisibleForPagination truthy in most cases and a real query contains 50 subqueries in $or condition and all fields are the same within or conditions except of groupId and _id

stages:
“sortPattern” : {
“score” : -1.0,
“_id” : -1.0
},
and 50 scans of index mentioned below.

Here is the query, but with 5 or conditions instead of 50. So, can anyone help finding a great index for the usecase or I should have done a migration to mark if the doc is greater than the id to create an additional field (the _id condition is a static for the whole group)?:

collection.find({
  "$or": [
    {
      "groupId": "ObjectId(\"552d1d24dc1c586b09d2d051\")",
      "isVisible": false,
      "isVisibleForPagination": true,
      "type": {
        "$nin": [
          5,
          10
        ]
      },
      "score": {
        "$gt": 0
      },
      "_id": {
        "$gt": "ObjectId(\"65368d780000000000000000\")"
      }
    },
    {
      "groupId": "ObjectId(\"5af4501a0a592c0014cbceae\")",
      "isVisible": false,
      "isVisibleForPagination": true,
      "type": {
        "$nin": [
          5,
          10
        ]
      },
      "score": {
        "$gt": 0
      },
      "_id": {
        "$gt": "ObjectId(\"652c52d00000000000000000\")"
      }
    },
    {
      "groupId": "ObjectId(\"5f5b5d305c2ecc001dd170f1\")",
      "isVisible": false,
      "isVisibleForPagination": true,
      "type": {
        "$nin": [
          5,
          10
        ]
      },
      "score": {
        "$gt": 0
      },
      "_id": {
        "$gt": "ObjectId(\"652ea4780000000000000000\")"
      }
    },
    {
      "groupId": "ObjectId(\"6017c6612e2fc21280381692\")",
      "isVisible": false,
      "isVisibleForPagination": true,
      "type": {
        "$nin": [
          5,
          10
        ]
      },
      "score": {
        "$gt": 0
      },
      "_id": {
        "$gt": "ObjectId(\"652ea4780000000000000000\")"
      }
    },
    {
      "groupId": "ObjectId(\"603e4e3f825ea5002321b20e\")",
      "isVisible": false,
      "isVisibleForPagination": true,
      "type": {
        "$nin": [
          5,
          10
        ]
      },
      "score": {
        "$gt": 0
      },
      "_id": {
        "$gt": "ObjectId(\"652ea4780000000000000000\")"
      }
    }
  ]
}).sort({ score: -1, _id: -1 }).limit(20)
2

The first thing would be to factor to common conditions so they are not repeated. It would make the whole thing easier to understand.

The $nin is not very selective so it is usually slower than a $in of the complementary values.

You could replace the normal compound index with a partial index that could have

{ isVisible:false, isVisibleForPagination:true, score:{$gt:0} }

as the partialFilterExpression.