Mongo is not using compound index in aggreation query

Working with Data
1

I have multiple indexes present but mongo is not using compound index that it should use ,
eg : id_1_expiresAt_1

Below is the query

db..aggregate(
[{
“$match”: {
“id”: {
“$in”: [
“hw_12er”,
“hw_offer”
]
}
}
},
{
“$match”: {
“expiresAt”: {
“$gt”: {
“$date”: “2024-04-09T13:36:39.565Z”
}
}
}
},
{
“$group”: {
“_id”: “id”,
“count”: {
“$sum”: “$quantity”
}
}
}]
).explain();

Using Explain I can see it is not using compound index rather than its going for existing single index what to do ?

I can see my compound index is coming in rejected Plan.

2

Please read Formatting code and log snippets in posts and then update you post.

If you know about markdown, you may use that to format.

1 Like
3

For the sake of others:

db.getCollection("Test").aggregate(
[
{
    "$match": {
        "ID": {
            "$in": [
                "hw_12er",
                "hw_offer"
            ]
        }
    }
},
{
    "$match": {
        "expiresAt": {
            "$gt": {
                "$date": "2024-04-09T13:36:39.565Z"
            }
        }
    }
},
{
    "$group": {
        "_id": "id",
        "count": {
            "$sum": "$quantity"
        }
    }
}
]).explain();

Given no example documents, I created some using this:

//db.getCollection("Test").createIndex({'ID':1, 'expiresAt':1})
//db.getCollection("Test").deleteMany({})

var batch = 5000;
var bucket = [];

//https://stackoverflow.com/questions/9035627/elegant-method-to-generate-array-of-random-dates-within-two-dates
function randomDate(start, end) {
  return new Date(start.getTime() + Math.random() * (end.getTime() - start.getTime()));
}

for (var i = 0; i < 1000000; i++){
    bucket.push({
        ID:Math.floor(Math.random() * 10),
        expiresAt:randomDate(new Date(2012, 0, 1), new Date()),
        quantity:Math.floor(Math.random() * 1000)
    })
    if(bucket.length > batch){
        print(`Commit: ${i}`)
        db.getCollection("Test").insertMany(bucket);
        bucket = [];
    }
}

if(bucket.length > 0){
    db.getCollection("Test").insertMany(bucket);
}

print(`Done`)

Given the above, I get the following (winning) execution plan:

        "winningPlan" : {
            "queryPlan" : {
                "stage" : "GROUP",
                "planNodeId" : 4.0,
                "inputStage" : {
                    "stage" : "FETCH",
                    "planNodeId" : 2.0,
                    "inputStage" : {
                        "stage" : "IXSCAN",
                        "planNodeId" : 1.0,
                        "keyPattern" : {
                            "ID" : 1.0,
                            "expiresAt" : 1.0
                        },
                        "indexName" : "ID_1_expiresAt_1",
                        "isMultiKey" : false,
                        "multiKeyPaths" : {
                            "ID" : [

                            ],
                            "expiresAt" : [

                            ]
                        },
                        "isUnique" : false,
                        "isSparse" : false,
                        "isPartial" : false,
                        "indexVersion" : 2.0,
                        "direction" : "forward",
                        "indexBounds" : {
                            "ID" : [
                                "[\"hw_12er\", \"hw_12er\"]",
                                "[\"hw_offer\", \"hw_offer\"]"
                            ],
                            "expiresAt" : [
                                "({ $date: \"2024-04-09T13:36:39.565Z\" }, [])"
                            ]
                        }
                    }

So it looks like the plan is as expected, does this behave the same on your system or give some sample documents and query in mongoplayground or something so it can be reproduced.

Above was on Mongo Community 7.0.6

1 Like
4

You could use .explain(“allPlansExecution”) to see the metrics for all of the candidate indexes and see why MongoDB is choosing a specifc one. You can also use .hint() with the index name to direct MongoDB to use a specific index.