I have been scratching my head over this problem long now. My Count query takes aroud ~500ms for 5000 documents and I hope someone can help me optimise it.
My collection contains following schema:
{
"tour": {
"stops": [ // stop with locationId
"locationId": "bla.."
],[
// stop without locationId
]
}"scheduleInfo": {
"stopSchedules": [
"lastETAResultUnix": NumberLong("date...")
]
}
}
The above query takes me around 500ms for ~5000 documents without using any index on "lastETAResultUnix" field. Expain(true) with the query gives me this...
{
"queryPlanner": {
"plannerVersion": 1,
"namespace": "TestUnixDate.tourCloudTest2",
"indexFilterSet": false,
"parsedQuery": {
"$and": [
{
"scheduleInfo.stopSchedules.lastETAResultUnix": {
"$lte": 1461967200
}
},
{
"scheduleInfo.stopSchedules.lastETAResultUnix": {
"$gte": -62135596800
}
},
{
"tour.stops.locationId": {
"$exists": true
}
}
]
},
"winningPlan": {
"stage": "COLLSCAN",
"filter": {
"$and": [
{
"scheduleInfo.stopSchedules.lastETAResultUnix": {
"$lte": 1461967200
}
},
{
"scheduleInfo.stopSchedules.lastETAResultUnix": {
"$gte": -62135596800
}
},
{
"tour.stops.locationId": {
"$exists": true
}
}
]
},
"direction": "forward"
},
"rejectedPlans": [
]
},
"executionStats": {
"executionSuccess": true,
"nReturned": 5661,
"executionTimeMillis": 827,
"totalKeysExamined": 0,
"totalDocsExamined": 45302,
"executionStages": {
"stage": "COLLSCAN",
"filter": {
"$and": [
{
"scheduleInfo.stopSchedules.lastETAResultUnix": {
"$lte": 1461967200
}
},
{
"scheduleInfo.stopSchedules.lastETAResultUnix": {
"$gte": -62135596800
}
},
{
"tour.stops.locationId": {
"$exists": true
}
}
]
},
"nReturned": 5661,
"executionTimeMillisEstimate": 780,
"works": 45304,
"advanced": 5661,
"needTime": 39642,
"needFetch": 0,
"saveState": 353,
"restoreState": 353,
"isEOF": 1,
"invalidates": 0,
"direction": "forward",
"docsExamined": 45302
},
"allPlansExecution": [
]
},
"serverInfo": {
"host": "DAve-build-1",
"port": 27017,
"version": "3.0.6",
"gitVersion": "nogitversion"
},
"ok": 1
}
Performance using an index on the lastETAResult field is horrible. Again with explain ...
{
"queryPlanner": {
"plannerVersion": 1,
"namespace": "TestUnixDate.tourCloudTest2",
"indexFilterSet": false,
"parsedQuery": {
"$and": [
{
"scheduleInfo.stopSchedules.lastETAResultUnix": {
"$lte": 1461967200
}
},
{
"scheduleInfo.stopSchedules.lastETAResultUnix": {
"$gte": -62135596800
}
},
{
"tour.stops.locationId": {
"$exists": true
}
}
]
},
"winningPlan": {
"stage": "KEEP_MUTATIONS",
"inputStage": {
"stage": "FETCH",
"filter": {
"$and": [
{
"scheduleInfo.stopSchedules.lastETAResultUnix": {
"$gte": -62135596800
}
},
{
"tour.stops.locationId": {
"$exists": true
}
}
]
},
"inputStage": {
"stage": "IXSCAN",
"keyPattern": {
"scheduleInfo.stopSchedules.lastETAResultUnix": 1
},
"indexName": "scheduleInfo.stopSchedules.lastETAResultUnix_1",
"isMultiKey": true,
"direction": "forward",
"indexBounds": {
"scheduleInfo.stopSchedules.lastETAResultUnix": [
"[-1.#INF, 1461967200.0]"
]
}
}
}
},
"rejectedPlans": [
]
},
"executionStats": {
"executionSuccess": true,
"nReturned": 5661,
"executionTimeMillis": 4631,
"totalKeysExamined": 76868,
"totalDocsExamined": 45065,
"executionStages": {
"stage": "KEEP_MUTATIONS",
"nReturned": 5661,
"executionTimeMillisEstimate": 4380,
"works": 76869,
"advanced": 5661,
"needTime": 71207,
"needFetch": 0,
"saveState": 609,
"restoreState": 609,
"isEOF": 1,
"invalidates": 0,
"inputStage": {
"stage": "FETCH",
"filter": {
"$and": [
{
"scheduleInfo.stopSchedules.lastETAResultUnix": {
"$gte": -62135596800
}
},
{
"tour.stops.locationId": {
"$exists": true
}
}
]
},
"nReturned": 5661,
"executionTimeMillisEstimate": 4360,
"works": 76869,
"advanced": 5661,
"needTime": 71207,
"needFetch": 0,
"saveState": 609,
"restoreState": 609,
"isEOF": 1,
"invalidates": 0,
"docsExamined": 45065,
"alreadyHasObj": 0,
"inputStage": {
"stage": "IXSCAN",
"nReturned": 45065,
"executionTimeMillisEstimate": 3750,
"works": 76869,
"advanced": 45065,
"needTime": 31803,
"needFetch": 0,
"saveState": 609,
"restoreState": 609,
"isEOF": 1,
"invalidates": 0,
"keyPattern": {
"scheduleInfo.stopSchedules.lastETAResultUnix": 1
},
"indexName": "scheduleInfo.stopSchedules.lastETAResultUnix_1",
"isMultiKey": true,
"direction": "forward",
"indexBounds": {
"scheduleInfo.stopSchedules.lastETAResultUnix": [
"[-1.#INF, 1461967200.0]"
]
},
"keysExamined": 76868,
"dupsTested": 76868,
"dupsDropped": 31803,
"seenInvalidated": 0,
"matchTested": 0
}
}
},
"allPlansExecution": [
]
},
"serverInfo": {
"host": "DAve-build-1",
"port": 27017,
"version": "3.0.6",
"gitVersion": "nogitversion"
},
"ok": 1
}
Thank you for any help.