Hello!
I have a very complex query that runs on top of our largest collection in production.
Here is the following query:
"pipeline": [
{
"$match": {
"IsDeleted": false
}
},
{
"$match": {
"$and": [
{
"IsIgnored": false
},
{
"CessionInformation": null
},
{
"WaitingCessionId": null
},
{
"TransferInformation": null
},
{
"$or": [
{
"AccountAdjustmentInformation": {
"$ne": null
},
"PaymentInformation": {
"$ne": null
},
"ItemState": "PaidNotCeded",
"PaymentInformation.EducbankLiquidationDate": {
"$ne": null,
"$lte": {
"$date": "2025-02-04T00:00:00Z"
}
}
},
{
"ItemState": "Paid",
"PaymentInformation.EducbankLiquidationDate": {
"$ne": null,
"$lte": {
"$date": "2025-02-04T00:00:00Z"
}
},
"SecuritizerSnapShot": {
"$ne": null
},
"SecuritizerSnapShot.Code": "TRVSEC02"
},
{
"AccountAdjustmentInformation": {
"$ne": null
},
"PaymentInformation": {
"$ne": null
},
"ItemState": "PaidReBought",
"PaymentInformation.EducbankLiquidationDate": {
"$ne": null,
"$lte": {
"$date": "2025-02-04T00:00:00Z"
}
}
}
]
}
]
}
}
]
"planSummary": "IXSCAN { IsDeleted: 1, CreationTime: -1, LastModificationTime: -1, DeletionTime: -1 }",
"cursorid": 4062731986869923600,
"keysExamined": 5450990,
"docsExamined": 5450990,
"nBatches": 1,
"cursorExhausted": true,
"numYields": 26784,
"nreturned": 7085,
"queryHash": "74D30FD0",
"planCacheKey": "A1BFC908",
And here are the indexes that I created to cover the query, at least partially, because i Know how hard it is to cover it all.
db.XXXX.createIndex(
{ ItemState: 1, "PaymentInformation.EducbankLiquidationDate": 1 },
{
partialFilterExpression: {
"IsIgnored": false,
"CessionInformation": null,
"WaitingCessionId": null,
"TransferInformation": null
},
name: "ItemState_LiquidationDate_Index"
}
)
db.XXXX.createIndex(
{ ItemState: 1, "PaymentInformation.PaymentMethod": 1 },
{
partialFilterExpression: {
"IsIgnored": false,
"CessionInformation": null,
"WaitingCessionId": null,
"TransferInformation": null
},
name: "ItemState_PaymentMethod_Index"
}
)
db.XXXX.createIndex(
{ ItemState: 1, "AccountAdjustmentInformation.LiquidationDate": 1 },
{
partialFilterExpression: {
"IsIgnored": false,
"CessionInformation": null,
"WaitingCessionId": null,
"TransferInformation": null
},
name: "ItemState_AccountAdjustment_LiquidationDate_Index"
}
)
I thought that for each branch of the “or” condition in the query tree, the planner would be able to use a separate index. However, I believe I’m missing something.
When I ran this code locally, the indexes worked as expected. The main difference between the production and local environments is the number of documents in the collection—I did not export all documents to my local machine.
I was wondering… should I add IsDeleted to the partial index? Would that be enough?
Am I overlooking something?
Thank you for your time and attention.
Gilcemir Filho