Compound index is not being used for aggregation match clause

Working with Data
, ,
1

Hello!

I have an aggregation query, for which I have match stage at the top and I want it to use the compound index on several fields.
Here is the whole query:

const query = Vote.aggregate([
      {
        $match: {
          "$and": [
            {
              "index": {
                "$eq": "5ecec41e1c3b9000e12c1225"
              }
            },
            {
              "auction.date": {
                "$lte": "2022-01-26T23:59:59.999Z"
              }
            },
            {
              "auction.date": {
                "$gte": "2021-11-26T00:00:00.000Z"
              }
            },
            {
              "is50PercentageOffAverage": {
                "$not": {
                  "$exists": true
                }
              }
            }
          ]
        },
      },
      {
        $sort: {
          createDate: -1,
        },
      },
      {
        $group: {
          _id: { id: '$auction._id' },
          auction: { $first: '$auction' },
          votes: {
            $push: {
              date: '$auction.date',
              createDate: '$createDate',
              closeValue: '$closeValue',
            },
          },
        },
      },
      {
        $project: {
          auction: 1,
          votes: { $slice: ['$votes', lastAuctionVotes] },
        },
      },
      {
        $project: {
          _id: 0,
          date: '$auction.date',
          count: { $size: '$votes' },
          averageClose: { $avg: '$votes.closeValue' },
        },
      },
      {
        $project: {
          date: 1,
          count: 1,
          averageClose: { $trunc: ['$averageClose', 2] },
        },
      },
      {
        $sort: {
          date: 1,
        },
      },
    ])

I want it to use the compound index on 3 fields, which you can see on this screen:

image
image2988×1528 306 KB

But it ends up using the wrong index for the fetch stage, here is the full log of explain command:

{
  "stages": [
    {
      "$cursor": {
        "queryPlanner": {
          "plannerVersion": 1,
          "namespace": "solex.votes",
          "indexFilterSet": false,
          "parsedQuery": {
            "$and": [
              {
                "index": {
                  "$eq": "5ecec41e1c3b9000e12c1225"
                }
              },
              {
                "auction.date": {
                  "$lte": "2022-01-26T23:59:59.999Z"
                }
              },
              {
                "auction.date": {
                  "$gte": "2021-11-26T00:00:00.000Z"
                }
              },
              {
                "is50PercentageOffAverage": {
                  "$not": {
                    "$exists": true
                  }
                }
              }
            ]
          },
          "queryHash": "80189EA4",
          "planCacheKey": "FECC47DB",
          "winningPlan": {
            "stage": "PROJECTION_SIMPLE",
            "transformBy": {
              "auction": 1,
              "closeValue": 1,
              "createDate": 1,
              "_id": 0
            },
            "inputStage": {
              "stage": "FETCH",
              "filter": {
                "$and": [
                  {
                    "index": {
                      "$eq": "5ecec41e1c3b9000e12c1225"
                    }
                  },
                  {
                    "auction.date": {
                      "$lte": "2022-01-26T23:59:59.999Z"
                    }
                  },
                  {
                    "auction.date": {
                      "$gte": "2021-11-26T00:00:00.000Z"
                    }
                  },
                  {
                    "is50PercentageOffAverage": {
                      "$not": {
                        "$exists": true
                      }
                    }
                  }
                ]
              },
              "inputStage": {
                "stage": "IXSCAN",
                "keyPattern": {
                  "createDate": 1
                },
                "indexName": "createDate",
                "isMultiKey": false,
                "multiKeyPaths": {
                  "createDate": []
                },
                "isUnique": false,
                "isSparse": false,
                "isPartial": false,
                "indexVersion": 2,
                "direction": "backward",
                "indexBounds": {
                  "createDate": [
                    "[MaxKey, MinKey]"
                  ]
                }
              }
            }
          },
          "rejectedPlans": [
            {
              "stage": "SORT",
              "sortPattern": {
                "createDate": -1
              },
              "memLimit": 104857600,
              "type": "simple",
              "inputStage": {
                "stage": "PROJECTION_SIMPLE",
                "transformBy": {
                  "auction": 1,
                  "closeValue": 1,
                  "createDate": 1,
                  "_id": 0
                },
                "inputStage": {
                  "stage": "FETCH",
                  "filter": {
                    "$and": [
                      {
                        "is50PercentageOffAverage": {
                          "$not": {
                            "$exists": true
                          }
                        }
                      },
                      {
                        "index": {
                          "$eq": "5ecec41e1c3b9000e12c1225"
                        }
                      }
                    ]
                  },
                  "inputStage": {
                    "stage": "IXSCAN",
                    "keyPattern": {
                      "auction.date": 1
                    },
                    "indexName": "auctionDate",
                    "isMultiKey": false,
                    "multiKeyPaths": {
                      "auction.date": []
                    },
                    "isUnique": false,
                    "isSparse": false,
                    "isPartial": false,
                    "indexVersion": 2,
                    "direction": "forward",
                    "indexBounds": {
                      "auction.date": [
                        "[new Date(1637884800000), new Date(1643241599999)]"
                      ]
                    }
                  }
                }
              }
            },
            {
              "stage": "SORT",
              "sortPattern": {
                "createDate": -1
              },
              "memLimit": 104857600,
              "type": "simple",
              "inputStage": {
                "stage": "PROJECTION_SIMPLE",
                "transformBy": {
                  "auction": 1,
                  "closeValue": 1,
                  "createDate": 1,
                  "_id": 0
                },
                "inputStage": {
                  "stage": "FETCH",
                  "filter": {
                    "is50PercentageOffAverage": {
                      "$not": {
                        "$exists": true
                      }
                    }
                  },
                  "inputStage": {
                    "stage": "IXSCAN",
                    "keyPattern": {
                      "auction.date": 1,
                      "index": 1,
                      "is50PercentageOffAverage": 1
                    },
                    "indexName": "agg",
                    "isMultiKey": false,
                    "multiKeyPaths": {
                      "auction.date": [],
                      "index": [],
                      "is50PercentageOffAverage": []
                    },
                    "isUnique": false,
                    "isSparse": false,
                    "isPartial": false,
                    "indexVersion": 2,
                    "direction": "forward",
                    "indexBounds": {
                      "auction.date": [
                        "[new Date(1637884800000), new Date(1643241599999)]"
                      ],
                      "index": [
                        "[ObjectId('5ecec41e1c3b9000e12c1225'), ObjectId('5ecec41e1c3b9000e12c1225')]"
                      ],
                      "is50PercentageOffAverage": [
                        "[null, null]"
                      ]
                    }
                  }
                }
              }
            }
          ]
        }
      }
    }
  ],
  "ok": 1
}

Why it’s not using the compound index? I’ve also tried to create the compound index with the 4 fields, 3 which are used in the filter and createDate which is used in sort, but it is not used as well.

2

You have equality match on the field index but you do not have any compound index that has that field as a prefix.

See Performance Best Practices: Indexing | MongoDB Blog and pay attention to the ESR rule.

could be written as

"index" : "5ecec41e1c3b9000e12c1225"

can be simplified to

{
  "auction.date": {
                "$lte": "2022-01-26T23:59:59.999Z" ,
                "$gte": "2021-11-26T00:00:00.000Z"
              }
},

and when these simplifications are done you can forgot the explicit $and and write the whole thing as:

{
  $match :
  {
    "index" : "5ecec41e1c3b9000e12c1225" ,
    "auction.date" :
    {
      "$lte" : "2022-01-26T23:59:59.999Z" ,
      "$gte": "2021-11-26T00:00:00.000Z"
    } ,
    "is50PercentageOffAverage" :
    {
      "$exists" : false
    }
  }
}

Finally, if is50PercentageOffAverage:{$exists:false} is part of you main use-cases you might consider Partial Indexes.

1 Like