MongoDB not using index when sorting

Question

I'm using mongo 4.0.12 and I'm trying to tune my most executed query:

db.getCollection('ServiceInvoice').find(
{
    "Provider.ParentId": "60f9d7631b1f243eb82903ee",
    "Provider._id": "60f9d803fa27e34fdc4ec159",
    "Environment": 1,
    "Status": 2,
    "IssuedOn":
    {
        "$gte": { DateTime: new Date("2022-02-01T00:00:00Z") },
        "$lte": { DateTime: new Date("2022-02-01T23:59:59Z") }
    }
}).limit(50).skip(1050).sort({ "IssueOn.DateTime": -1 })

using an index like:

{
    "Environment" : 1.0,
    "Provider.ParentId" : 1.0,
    "Provider._id" : 1.0,
    "Status" : 1.0,
    "IssuedOn" : 1.0,
    "IssuedOn.DateTime" : -1.0
}

and gives me this explain:

{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "test.ServiceInvoice",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "$and" : [ 
                {
                    "Environment" : {
                        "$eq" : 1.0
                    }
                }, 
                {
                    "Provider.ParentId" : {
                        "$eq" : "60f9d7631b1f243eb82903ee"
                    }
                }, 
                {
                    "Provider._id" : {
                        "$eq" : "60f9d803fa27e34fdc4ec159"
                    }
                }, 
                {
                    "Status" : {
                        "$eq" : 2.0
                    }
                }, 
                {
                    "IssuedOn" : {
                        "$lte" : {
                            "DateTime" : ISODate("2022-02-01T23:59:59.000Z")
                        }
                    }
                }, 
                {
                    "IssuedOn" : {
                        "$gte" : {
                            "DateTime" : ISODate("2022-02-01T00:00:00.000Z")
                        }
                    }
                }
            ]
        },
        "winningPlan" : {
            "stage" : "SKIP",
            "skipAmount" : 0,
            "inputStage" : {
                "stage" : "SORT",
                "sortPattern" : {
                    "IssueOn.DateTime" : -1.0
                },
                "limitAmount" : 1100,
                "inputStage" : {
                    "stage" : "SORT_KEY_GENERATOR",
                    "inputStage" : {
                        "stage" : "FETCH",
                        "inputStage" : {
                            "stage" : "IXSCAN",
                            "keyPattern" : {
                                "Environment" : 1.0,
                                "Provider.ParentId" : 1.0,
                                "Provider._id" : 1.0,
                                "Status" : 1.0,
                                "IssuedOn" : 1.0,
                                "IssuedOn.DateTime" : -1.0
                            },
                            "indexName" : "Environment_1_Provider.ParentId_1_Provider._id_1_Status_1_IssueOn_1_IssueOn.DateTime_-1",
                            "isMultiKey" : false,
                            "multiKeyPaths" : {
                                "Environment" : [],
                                "Provider.ParentId" : [],
                                "Provider._id" : [],
                                "Status" : [],
                                "IssuedOn" : [],
                                "IssuedOn.DateTime" : []
                            },
                            "isUnique" : false,
                            "isSparse" : false,
                            "isPartial" : false,
                            "indexVersion" : 2,
                            "direction" : "forward",
                            "indexBounds" : {
                                "Environment" : [ 
                                    "[1.0, 1.0]"
                                ],
                                "Provider.ParentId" : [ 
                                    "[\"60f9d7631b1f243eb82903ee\", \"60f9d7631b1f243eb82903ee\"]"
                                ],
                                "Provider._id" : [ 
                                    "[\"60f9d803fa27e34fdc4ec159\", \"60f9d803fa27e34fdc4ec159\"]"
                                ],
                                "Status" : [ 
                                    "[2.0, 2.0]"
                                ],
                                "IssuedOn" : [ 
                                    "[{ DateTime: new Date(1643673600000) }, { DateTime: new Date(1643759999000) }]"
                                ],
                                "IssuedOn.DateTime" : [ 
                                    "[MaxKey, MinKey]"
                                ]
                            }
                        }
                    }
                }
            }
        },
        "rejectedPlans" : []
    },
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 50,
        "executionTimeMillis" : 99,
        "totalKeysExamined" : 31622,
        "totalDocsExamined" : 31622,
        "executionStages" : {
            "stage" : "SKIP",
            "nReturned" : 50,
            "executionTimeMillisEstimate" : 6,
            "works" : 32725,
            "advanced" : 50,
            "needTime" : 32674,
            "needYield" : 0,
            "saveState" : 255,
            "restoreState" : 255,
            "isEOF" : 1,
            "invalidates" : 0,
            "skipAmount" : 0,
            "inputStage" : {
                "stage" : "SORT",
                "nReturned" : 1100,
                "executionTimeMillisEstimate" : 6,
                "works" : 32725,
                "advanced" : 1100,
                "needTime" : 31624,
                "needYield" : 0,
                "saveState" : 255,
                "restoreState" : 255,
                "isEOF" : 1,
                "invalidates" : 0,
                "sortPattern" : {
                    "IssueOn.DateTime" : -1.0
                },
                "memUsage" : 3057213,
                "memLimit" : 33554432,
                "limitAmount" : 1100,
                "inputStage" : {
                    "stage" : "SORT_KEY_GENERATOR",
                    "nReturned" : 31622,
                    "executionTimeMillisEstimate" : 4,
                    "works" : 31624,
                    "advanced" : 31622,
                    "needTime" : 1,
                    "needYield" : 0,
                    "saveState" : 255,
                    "restoreState" : 255,
                    "isEOF" : 1,
                    "invalidates" : 0,
                    "inputStage" : {
                        "stage" : "FETCH",
                        "nReturned" : 31622,
                        "executionTimeMillisEstimate" : 3,
                        "works" : 31623,
                        "advanced" : 31622,
                        "needTime" : 0,
                        "needYield" : 0,
                        "saveState" : 255,
                        "restoreState" : 255,
                        "isEOF" : 1,
                        "invalidates" : 0,
                        "docsExamined" : 31622,
                        "alreadyHasObj" : 0,
                        "inputStage" : {
                            "stage" : "IXSCAN",
                            "nReturned" : 31622,
                            "executionTimeMillisEstimate" : 1,
                            "works" : 31623,
                            "advanced" : 31622,
                            "needTime" : 0,
                            "needYield" : 0,
                            "saveState" : 255,
                            "restoreState" : 255,
                            "isEOF" : 1,
                            "invalidates" : 0,
                            "keyPattern" : {
                                "Environment" : 1.0,
                                "Provider.ParentId" : 1.0,
                                "Provider._id" : 1.0,
                                "Status" : 1.0,
                                "IssuedOn" : 1.0,
                                "IssuedOn.DateTime" : -1.0
                            },
                            "indexName" : "Environment_1_Provider.ParentId_1_Provider._id_1_Status_1_IssueOn_1_IssueOn.DateTime_-1",
                            "isMultiKey" : false,
                            "multiKeyPaths" : {
                                "Environment" : [],
                                "Provider.ParentId" : [],
                                "Provider._id" : [],
                                "Status" : [],
                                "IssuedOn" : [],
                                "IssuedOn.DateTime" : []
                            },
                            "isUnique" : false,
                            "isSparse" : false,
                            "isPartial" : false,
                            "indexVersion" : 2,
                            "direction" : "forward",
                            "indexBounds" : {
                                "Environment" : [ 
                                    "[1.0, 1.0]"
                                ],
                                "Provider.ParentId" : [ 
                                    "[\"60f9d7631b1f243eb82903ee\", \"60f9d7631b1f243eb82903ee\"]"
                                ],
                                "Provider._id" : [ 
                                    "[\"60f9d803fa27e34fdc4ec159\", \"60f9d803fa27e34fdc4ec159\"]"
                                ],
                                "Status" : [ 
                                    "[2.0, 2.0]"
                                ],
                                "IssuedOn" : [ 
                                    "[{ DateTime: new Date(1643673600000) }, { DateTime: new Date(1643759999000) }]"
                                ],
                                "IssuedOn.DateTime" : [ 
                                    "[MaxKey, MinKey]"
                                ]
                            },
                            "keysExamined" : 31622,
                            "seeks" : 1,
                            "dupsTested" : 0,
                            "dupsDropped" : 0,
                            "seenInvalidated" : 0
                        }
                    }
                }
            }
        },
        "allPlansExecution" : []
    },
    "serverInfo" : {
        "host" : "d4ef6b3e9c6c",
        "port" : 27017,
        "version" : "4.0.12",
        "gitVersion" : "5776e3cbf9e7afe86e6b29e22520ffb6766e95d4"
    },
    "ok" : 1.0
}

However, dbKoda keeps me saying that I must create an index for sorting. I've already tried to create a separated index for IssuedOn.DateTime, but it keeps me recommending the creation and I don't see any effects.

How can I solve this problem? (Changes to the document fields are not an option).

Answer 1

According to these threads - MongoDB - Index not being used when sorting and limiting on ranged query and https://emptysqua.re/blog/optimizing-mongodb-compound-indexes/

A compund Index should be created following this order:

1. Equality Tests: Add all equality-tested fields to the compound index, in any order;
2. Sort Fields (ascending / descending only matters if there are multiple sort fields): Add sort fields to the index in the same order and direction as your query's sort;
3. Range Filters: First, add the range filter for the field with the lowest cardinality (fewest distinct values in the collection). Then the next lowest-cardinality range filter, and so on to the highest-cardinality.

So, the solution was creating an index like this:

{
    "Environment" : 1.0,
    "Provider.ParentId" : 1.0,
    "Provider._id" : 1.0,
    "Status" : 1.0,
    "IssuedOn.DateTime" : -1.0,
    "IssuedOn" : 1.0
}

And now, the query uses the index for sorting and fetch only the records in range.