Hi,
I try to optimize slow queries on my Serverless cluster.
I added few indexes recently, but since then, I always get the following error message running same queries as before: ‘connection XX to XX.XX.XX.XX:27017 closed’.
I tried increasing the timeouts (maxTimeMS and socketTimeoutMS), but it looks like it’s not helping (the error message is displayed before those timeouts).
I also wanted to use MongoDB ‘explain’ function to optimize my queries, but I get the same error message.
Here is an example of document:
{
"_id": {
"$oid": "601c0b651736cb0655300f95"
},
"invoiceId": "71996cf996ac40cdb8dbab8361302f88",
"partnerId": 1,
"accountId": "71996cf996ac40cdb8dbab8361302f88",
"amount": 10,
"balance": 10,
"billingNonRecoverable": true,
"createdAt": {
"$date": "2020-12-01T00:00:05.000Z"
},
"invoiceNumber": "INV00123456",
"isTest": false,
"meanOfPayment": "CC",
"status": "Posted",
"updatedAt": {
"$date": "2020-12-11T06:06:30.000Z"
},
"hasBeenProcessed": true,
"lastRecaptureEmailDate": {
"$date": "2020-12-01T00:00:00.000Z"
},
"meanOfPaymentLastUpdatedDate": {
"$date": "2021-10-22T00:00:00.000Z"
}
}
Here is an example of complex query:
{
partnerId: 1,
isTest: false,
billingNonRecoverable: false,
status: 'Posted',
meanOfPayment: { $ne: 'FREE' },
createdAt: { $lte: ISODate('2021-11-02T23:00:00.000Z') },
deletedAt: undefined,
balance: { $ne: 0 },
$and: [
{
$or: [
{ lastRecaptureEmailDate: undefined },
{ lastRecaptureEmailDate: { $lte: '2021-11-03T23:00:00.000Z' } }
]
},
{
$or: [
{ nextSepaAttemptDate: undefined },
{ nextSepaAttemptDate: { $lt: '2021-11-14T23:00:00.000Z' } }
]
},
{
$or: [
{ meanOfPaymentLastUpdatedDate: undefined },
{ $expr: { $lte: ['$meanOfPaymentLastUpdatedDate', '$createdAt'] } }
]
}
]
}
And here are my indexes:
Do you have any idea how to optimize this query, and if the issue is related to the Serverless or not?
Best regards,
Maxime