Create an index over query with aggregate pipeline

Hello guys, I’ve been trying to create an index to the following query, but at the moment without any good results. I’ve tried to put the main properties as compound indexes and I’ve tried including the properties inside the $or operator as individuals together the main properties, it looks don’t work, so I hope you can give any clue, whichever info would be well received.

[
  {
    "$match": {
      "$and": [
        {
          "available": {
            "$in": [
              null,
              false
            ]
          }
        },
        {
          "newAcquisition": {
            "$in": [
              null,
              false
            ]
          }
        },
        {
          "$or": [
            {
              "product._id": "62d426209a80df0d3d9ca38e"
            },
            {
              "category._id": "6305339622cc505054e33353"
            },
            {
              "maker.company": "630fc3f4d28d4cead8db9054"
            }
          ]
        }
      ]
    }
  },
  {
    "$addFields": {
      "id": {
        "$toString": "$_id"
      }
    }
  },
  {
    "$sort": {
      "_id": -1
    }
  },
  {
    "$facet": {
      "data": [
        {
          "$count": "count"
        },
        {
          "$addFields": {
            "page": 1
          }
        },
        {
          "$addFields": {
            "limit": 25
          }
        }
      ],
      "docs": [
        {
          "$skip": 0
        },
        {
          "$limit": 25
        }
      ]
    }
  },
  {
    "$project": {
      "docs": 1,
      "total": {
        "$arrayElemAt": [
          "$data.total",
          0
        ]
      },
      "page": {
        "$arrayElemAt": [
          "$data.page",
          0
        ]
      },
      "limit": {
        "$arrayElemAt": [
          "$data.limit",
          0
        ]
      }
    }
  }
]

Read https://www.mongodb.com/community/forums/t/update-nested-objects-array-to-string-array/197588/2?u=steevej about storing _id as string (like product._id, category._id, …) instead of ObjectId for references.

Share the index you tried.

About flags, like your fields available and newAcquisition, I usually try to find meaningful alternative to Boolean flags. In the your case, I would use available_date and acquisition_date. But when I stick with Boolean I like to use partial index using the Boolean in my expression. For example, if most use-cases involve available:true, they would use a much smaller index.

You might gain by doing $sort:{_id:1} first because the $addFields might prevent the use of the _id:1 index, as in most case when documents are altered, no indexes can be used to $match or $sort.

In your final $project, you access $data.total, but you do not have such field in your data $facet