Hi. I have a query that takes 1 minute while everything happens with IXSCAN. No documents are taken from the disk, yet the query is very slow. Could you help me to understand why and how I can improve this?
"executionStages" : {
"stage" : "FETCH",
"nReturned" : 0,
"executionTimeMillisEstimate" : 3344,
"works" : 604601,
"advanced" : 0,
"needTime" : 604599,
"needYield" : 0,
"saveState" : 23619,
"restoreState" : 23619,
"isEOF" : 1,
"docsExamined" : 0,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 0,
"executionTimeMillisEstimate" : 3294,
"works" : 604600,
"advanced" : 0,
"needTime" : 604599,
"needYield" : 0,
"saveState" : 23619,
"restoreState" : 23619,
"isEOF" : 1,
"keyPattern" : {
"ivi_purchase.state" : 1,
"ivi_purchase.expires_at" : -1,
"state" : 1
},
"indexName" : "ivi_purchase.state_1_ivi_purchase.expires_at_-1_state_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"ivi_purchase.state" : [ ],
"ivi_purchase.expires_at" : [ ],
"state" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"ivi_purchase.state" : [
"[2.0, 2.0]"
],
"ivi_purchase.expires_at" : [
"(new Date(1691150495789), true)"
],
"state" : [
"[0.0, 0.0]"
]
},
"keysExamined" : 604600,
"seeks" : 604600,
"dupsTested" : 0,
"dupsDropped" : 0
}
}
},
"serverInfo" : {
"host" : "...",
"port" : 27017,
"version" : "4.2.18",
"gitVersion" : "f65ce5e25c0b26a00d091a4d24eec1a8b3a4c016"
},
Just in case somebody is interested in this:
frontend:PRIMARY> db.getProfilingStatus()
{
"was" : 0,
"slowms" : 100,
"sampleRate" : 1,
"$clusterTime" : {
"clusterTime" : Timestamp(1691148595, 2055),
"signature" : {
"hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
"keyId" : NumberLong(0)
}
},
"operationTime" : Timestamp(1691148595, 2055)
}