Hi,
I’ve a collection with 760K documents.
The query takes up to 5 seconds to aggregate data. It’s very slow.
So I’ve created some indexes to increase performance but it’s always slow :
Indexes :
Result with explain execution stats :
{
"explainVersion": "2",
"stages": [
{
"$cursor": {
"queryPlanner": {
"namespace": "Tracking.eventsraw",
"indexFilterSet": false,
"parsedQuery": {
"$and": [
{
"CarrierEventType": {
"$exists": true
}
},
{ "IdCompany": { "$exists": true } }
]
},
"queryHash": "3960270F",
"planCacheKey": "28BEA210",
"maxIndexedOrSolutionsReached": false,
"maxIndexedAndSolutionsReached": false,
"maxScansToExplodeReached": false,
"winningPlan": {
"queryPlan": {
"stage": "GROUP",
"planNodeId": 3,
"inputStage": {
"stage": "FETCH",
"planNodeId": 2,
"filter": {
"$and": [
{
"IdCompany": {
"$exists": true
}
},
{
"CarrierEventType": {
"$exists": true
}
}
]
},
"inputStage": {
"stage": "IXSCAN",
"planNodeId": 1,
"keyPattern": {
"IdCompany": 1,
"CarrierEventType": 1
},
"indexName": "IdCompany_1_CarrierEventType_1",
"isMultiKey": false,
"multiKeyPaths": {
"IdCompany": [],
"CarrierEventType": []
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "forward",
"indexBounds": {
"IdCompany": [
"[MinKey, MaxKey]"
],
"CarrierEventType": [
"[MinKey, MaxKey]"
]
}
}
}
},
"slotBasedPlan": {
"slots": "$$RESULT=s40 env: { s1 = TimeZoneDatabase(Antarctica/Davis...Indian/Mauritius) (timeZoneDB), s2 = Nothing (SEARCH_META), s3 = Timestamp(1703087130, 1) (CLUSTER_TIME), s4 = 1703087131279 (NOW) }",
"stages": "[3] mkobj s40 [_id = s35, published = s36, publishedFailed = s37, completed = s38, created = s39] true false \n[3] project [s35 = newObj (\"carriername\", s15, \"companyname\", s16), s36 = doubleDoubleSumFinalize (s21), s37 = doubleDoubleSumFinalize (s25), s38 = doubleDoubleSumFinalize (s29), s39 = doubleDoubleSumFinalize (s34)] \n[3] group [s15, s16] [s21 = aggDoubleDoubleSum (s20), s25 = aggDoubleDoubleSum (s24), s29 = aggDoubleDoubleSum (s28), s34 = aggDoubleDoubleSum (s33)] \n[3] nlj [s13, s15, s16, s17, s20, s24, s28, s30] [s13, s15, s16, s17, s20, s24, s28, s30] \n left \n [3] project [s30 = getField (s13, \"_id\")] \n [3] nlj [s13, s15, s16, s17, s20, s24] [s13, s15, s16, s17, s20, s24] \n left \n [3] nlj [s13, s15, s16, s17, s20] [s13, s15, s16, s17, s20] \n left \n [3] nlj [s13, s15, s16, s17] [s13, s15, s16, s17] \n left \n [3] project [s17 = getField (s13, \"State\")] \n [3] project [s16 = getField (s13, \"IdCompany\")] \n [3] project [s15 = getField (s13, \"CarrierEventType\")] \n [2] filter {applyClassicMatcher (ClassicMatcher({ $and: [ { IdCompany: { $exists: true } }, { CarrierEventType: { $exists: true } } ] }), s13)} \n [2] nlj [] [s9, s5, s6, s7, s8] \n left \n [1] nlj [s6, s8] [s10, s11] \n left \n [1] project [s6 = \"IdCompany_1_CarrierEventType_1\", s8 = {\"IdCompany\" : 1, \"CarrierEventType\" : 1}, s10 = KS(0A0A0104), s11 = KS(F0F0FE04)] \n [1] limit 1 \n [1] coscan \n right \n [1] project [s5 = s12] \n [1] ixseek s10 s11 s7 s9 s12 [] @\"6616dab0-d2b1-443b-a3c7-62b4494fb99f\" @\"IdCompany_1_CarrierEventType_1\" true \n \n \n right \n [2] limit 1 \n [2] seek s9 s13 s14 s5 s6 s7 s8 [] @\"6616dab0-d2b1-443b-a3c7-62b4494fb99f\" true false \n \n \n right \n [3] limit 1 \n [3] union [s20] [\n [s18] [3] nlj [] [] \n left \n [3] filter {let [l2.0 = let [l1.0 = s17, l1.1 = \"Published\"] fillEmpty (l1.0 <=> l1.1 == 0, exists (l1.0) && typeMatch (l1.0, -65) == exists (l1.1) && typeMatch (l1.1, -65))] exists (l2.0) && ! typeMatch (l2.0, 1088) && l2.0 <=> false != 0 && l2.0 <=> 0 != 0} \n [3] limit 1 \n [3] coscan \n right \n [3] project [s18 = 1] \n [3] limit 1 \n [3] coscan \n \n , \n [s19] [3] project [s19 = 0] \n [3] limit 1 \n [3] coscan \n ] \n \n \n right \n [3] limit 1 \n [3] union [s24] [\n [s22] [3] nlj [] [] \n left \n [3] filter {let [l4.0 = let [l3.0 = s17, l3.1 = \"PublishedFailed\"] fillEmpty (l3.0 <=> l3.1 == 0, exists (l3.0) && typeMatch (l3.0, -65) == exists (l3.1) && typeMatch (l3.1, -65))] exists (l4.0) && ! typeMatch (l4.0, 1088) && l4.0 <=> false != 0 && l4.0 <=> 0 != 0} \n [3] limit 1 \n [3] coscan \n right \n [3] project [s22 = 1] \n [3] limit 1 \n [3] coscan \n \n , \n [s23] [3] project [s23 = 0] \n [3] limit 1 \n [3] coscan \n ] \n \n \n right \n [3] limit 1 \n [3] union [s28] [\n [s26] [3] nlj [] [] \n left \n [3] filter {let [l6.0 = let [l5.0 = s17, l5.1 = \"Completed\"] fillEmpty (l5.0 <=> l5.1 == 0, exists (l5.0) && typeMatch (l5.0, -65) == exists (l5.1) && typeMatch (l5.1, -65))] exists (l6.0) && ! typeMatch (l6.0, 1088) && l6.0 <=> false != 0 && l6.0 <=> 0 != 0} \n [3] limit 1 \n [3] coscan \n right \n [3] project [s26 = 1] \n [3] limit 1 \n [3] coscan \n \n , \n [s27] [3] project [s27 = 0] \n [3] limit 1 \n [3] coscan \n ] \n \n \n right \n [3] limit 1 \n [3] union [s33] [\n [s31] [3] nlj [] [] \n left \n [3] filter {let [l8.0 = let [l7.0 = s30, l7.1 = ObjectId(\"65822e800000000000000000\")] fillEmpty (l7.0 <=> l7.1 >= 0, exists (l7.0) && typeMatch (l7.0, -65) >= exists (l7.1) && typeMatch (l7.1, -65))] exists (l8.0) && ! typeMatch (l8.0, 1088) && l8.0 <=> false != 0 && l8.0 <=> 0 != 0} \n [3] limit 1 \n [3] coscan \n right \n [3] project [s31 = 1] \n [3] limit 1 \n [3] coscan \n \n , \n [s32] [3] project [s32 = 0] \n [3] limit 1 \n [3] coscan \n ] \n \n"
}
},
"rejectedPlans": [
{
"queryPlan": {
"stage": "GROUP",
"planNodeId": 3,
"inputStage": {
"stage": "FETCH",
"planNodeId": 2,
"filter": {
"$and": [
{
"IdCompany": {
"$exists": true
}
},
{
"CarrierEventType": {
"$exists": true
}
}
]
},
"inputStage": {
"stage": "IXSCAN",
"planNodeId": 1,
"keyPattern": {
"IdCompany": 1,
"CarrierEventType": 1,
"State": 1
},
"indexName": "IdCompany_1_CarrierEventType_1_State_1",
"isMultiKey": false,
"multiKeyPaths": {
"IdCompany": [],
"CarrierEventType": [],
"State": []
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "forward",
"indexBounds": {
"IdCompany": [
"[MinKey, MaxKey]"
],
"CarrierEventType": [
"[MinKey, MaxKey]"
],
"State": [
"[MinKey, MaxKey]"
]
}
}
}
},
"slotBasedPlan": {
"slots": "$$RESULT=s40 env: { s1 = TimeZoneDatabase(Antarctica/Davis...Indian/Mauritius) (timeZoneDB), s2 = Nothing (SEARCH_META), s3 = Timestamp(1703087130, 1) (CLUSTER_TIME), s4 = 1703087131279 (NOW) }",
"stages": "[3] mkobj s40 [_id = s35, published = s36, publishedFailed = s37, completed = s38, created = s39] true false \n[3] project [s35 = newObj (\"carriername\", s15, \"companyname\", s16), s36 = doubleDoubleSumFinalize (s21), s37 = doubleDoubleSumFinalize (s25), s38 = doubleDoubleSumFinalize (s29), s39 = doubleDoubleSumFinalize (s34)] \n[3] group [s15, s16] [s21 = aggDoubleDoubleSum (s20), s25 = aggDoubleDoubleSum (s24), s29 = aggDoubleDoubleSum (s28), s34 = aggDoubleDoubleSum (s33)] \n[3] nlj [s13, s15, s16, s17, s20, s24, s28, s30] [s13, s15, s16, s17, s20, s24, s28, s30] \n left \n [3] project [s30 = getField (s13, \"_id\")] \n [3] nlj [s13, s15, s16, s17, s20, s24] [s13, s15, s16, s17, s20, s24] \n left \n [3] nlj [s13, s15, s16, s17, s20] [s13, s15, s16, s17, s20] \n left \n [3] nlj [s13, s15, s16, s17] [s13, s15, s16, s17] \n left \n [3] project [s17 = getField (s13, \"State\")] \n [3] project [s16 = getField (s13, \"IdCompany\")] \n [3] project [s15 = getField (s13, \"CarrierEventType\")] \n [2] filter {applyClassicMatcher (ClassicMatcher({ $and: [ { IdCompany: { $exists: true } }, { CarrierEventType: { $exists: true } } ] }), s13)} \n [2] nlj [] [s9, s5, s6, s7, s8] \n left \n [1] nlj [s6, s8] [s10, s11] \n left \n [1] project [s6 = \"IdCompany_1_CarrierEventType_1_State_1\", s8 = {\"IdCompany\" : 1, \"CarrierEventType\" : 1, \"State\" : 1}, s10 = KS(0A0A0A0104), s11 = KS(F0F0F0FE04)] \n [1] limit 1 \n [1] coscan \n right \n [1] project [s5 = s12] \n [1] ixseek s10 s11 s7 s9 s12 [] @\"6616dab0-d2b1-443b-a3c7-62b4494fb99f\" @\"IdCompany_1_CarrierEventType_1_State_1\" true \n \n \n right \n [2] limit 1 \n [2] seek s9 s13 s14 s5 s6 s7 s8 [] @\"6616dab0-d2b1-443b-a3c7-62b4494fb99f\" true false \n \n \n right \n [3] limit 1 \n [3] union [s20] [\n [s18] [3] nlj [] [] \n left \n [3] filter {let [l2.0 = let [l1.0 = s17, l1.1 = \"Published\"] fillEmpty (l1.0 <=> l1.1 == 0, exists (l1.0) && typeMatch (l1.0, -65) == exists (l1.1) && typeMatch (l1.1, -65))] exists (l2.0) && ! typeMatch (l2.0, 1088) && l2.0 <=> false != 0 && l2.0 <=> 0 != 0} \n [3] limit 1 \n [3] coscan \n right \n [3] project [s18 = 1] \n [3] limit 1 \n [3] coscan \n \n , \n [s19] [3] project [s19 = 0] \n [3] limit 1 \n [3] coscan \n ] \n \n \n right \n [3] limit 1 \n [3] union [s24] [\n [s22] [3] nlj [] [] \n left \n [3] filter {let [l4.0 = let [l3.0 = s17, l3.1 = \"PublishedFailed\"] fillEmpty (l3.0 <=> l3.1 == 0, exists (l3.0) && typeMatch (l3.0, -65) == exists (l3.1) && typeMatch (l3.1, -65))] exists (l4.0) && ! typeMatch (l4.0, 1088) && l4.0 <=> false != 0 && l4.0 <=> 0 != 0} \n [3] limit 1 \n [3] coscan \n right \n [3] project [s22 = 1] \n [3] limit 1 \n [3] coscan \n \n , \n [s23] [3] project [s23 = 0] \n [3] limit 1 \n [3] coscan \n ] \n \n \n right \n [3] limit 1 \n [3] union [s28] [\n [s26] [3] nlj [] [] \n left \n [3] filter {let [l6.0 = let [l5.0 = s17, l5.1 = \"Completed\"] fillEmpty (l5.0 <=> l5.1 == 0, exists (l5.0) && typeMatch (l5.0, -65) == exists (l5.1) && typeMatch (l5.1, -65))] exists (l6.0) && ! typeMatch (l6.0, 1088) && l6.0 <=> false != 0 && l6.0 <=> 0 != 0} \n [3] limit 1 \n [3] coscan \n right \n [3] project [s26 = 1] \n [3] limit 1 \n [3] coscan \n \n , \n [s27] [3] project [s27 = 0] \n [3] limit 1 \n [3] coscan \n ] \n \n \n right \n [3] limit 1 \n [3] union [s33] [\n [s31] [3] nlj [] [] \n left \n [3] filter {let [l8.0 = let [l7.0 = s30, l7.1 = ObjectId(\"65822e800000000000000000\")] fillEmpty (l7.0 <=> l7.1 >= 0, exists (l7.0) && typeMatch (l7.0, -65) >= exists (l7.1) && typeMatch (l7.1, -65))] exists (l8.0) && ! typeMatch (l8.0, 1088) && l8.0 <=> false != 0 && l8.0 <=> 0 != 0} \n [3] limit 1 \n [3] coscan \n right \n [3] project [s31 = 1] \n [3] limit 1 \n [3] coscan \n \n , \n [s32] [3] project [s32 = 0] \n [3] limit 1 \n [3] coscan \n ] \n \n"
}
}
]
},
"executionStats": {
"executionSuccess": true,
"nReturned": 5,
"executionTimeMillis": 5354,
"totalKeysExamined": 766747,
"totalDocsExamined": 766747,
"executionStages": {
"stage": "mkobj",
"planNodeId": 3,
"nReturned": 5,
"executionTimeMillisEstimate": 5320,
"opens": 1,
"closes": 1,
"saveState": 767,
"restoreState": 767,
"isEOF": 1,
"objSlot": 40,
"fields": [],
"projectFields": [
"_id",
"published",
"publishedFailed",
"completed",
"created"
],
db.getCollection('eventsraw').aggregate(
[
{
$match: {
CarrierEventType: { $exists: true },
IdCompany: { $exists: true }
}
},
{
$group: {
_id: {
carriername: '$CarrierEventType',
companyname: '$IdCompany'
},
published: {
$sum: {
$cond: [
{ $eq: ['$State', 'Published'] },
1,
0
]
}
},
publishedFailed: {
$sum: {
$cond: [
{
$eq: ['$State', 'PublishedFailed']
},
1,
0
]
}
},
completed: {
$sum: {
$cond: [
{ $eq: ['$State', 'Completed'] },
1,
0
]
}
},
created: {
$sum: {
$cond: [
{
$gte: [
'$_id',
ObjectId(Math.floor(new Date().setUTCHours(0, 0, 0, 0) / 1000).toString(16) + "0000000000000000")
)
]
},
1,
0
]
}
}
}
},
{
$project: {
_id: 0,
carriername: '$_id.carriername',
companyname: '$_id.companyname',
published: 1,
publishedFailed: 1,
completed: 1,
created: 1,
ischange: 'false'
}
}
]
);