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:
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.