Hi,
We are facing a query performance issue while fetching the aggregation results. It is taking 55 secs for one month of data and I am worried too much if queried for 3months of data. I have created the cover all index for companyId, typeId, date and sentiment.score .
Here is my query
db.collection.aggregate(
[
{ $match : { $and : [
{ "company" : { $elemMatch : {companyId : 500, typeId : {$in : [17]}}}},
{ date : { $gte: ISODate("2021-08-01T00:00:00.00Z"), $lte: ISODate("2021-08-30T23:59:59.00Z") }},
]}},
{ $group : {_id : "null", "count" : {$sum: 1},
"positiveCount" : { $sum : {$cond : [{$gte : ["$sentiment.score", "0.2"]}, 1, 0]}},
"negativeCount" : { $sum : {$cond : [{$lte : ["$sentiment.score", "-0.2"]}, 1, 0]}},
"neutralCount" : { $sum : {$cond : [{$and:[{ $lt : ["$sentiment.score", "0.2"]}, { $gt : [ "$sentiment.score", "-0.2"]}]}, 1, 0]}}
}},
{ $project : {
"totalVolume" : "$count",
"positiveCount" : "$positiveCount",
"negativeCount" : "$negativeCount",
"neutralCount" : "$neutralCount",
"positiveAvg" : { "$multiply" : [100, { "$divide" : ["$positiveCount", "$count"]}]},
"negativeAvg" : { "$multiply" : [100, { "$divide" : ["$negativeCount", "$count"]}]},
"neutralAvg" : { "$multiply" : [100, { "$divide" : ["$neutralCount", "$count"]}]},
"_id" : 0
} }
],
{ hint : "company.companyId_1_company.typeId_1_date_1_sentiment.score_1"}
);
My execution stats is
{
"stages" : [
{
"$cursor" : {
"queryPlanner" : {
"plannerVersion" : 1,
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"company" : {
"$elemMatch" : {
"$and" : [
{
"companyId" : {
"$eq" : 500
}
},
{
"typeId" : {
"$eq" : 10
}
}
]
}
}
},
{
"date" : {
"$lte" : ISODate("2021-08-30T23:59:59Z")
}
},
{
"date" : {
"$gte" : ISODate("2021-08-01T00:00:00Z")
}
}
]
},
"queryHash" : "7A03D6B2",
"planCacheKey" : "72BC03F5",
"winningPlan" : {
"stage" : "PROJECTION_DEFAULT",
"transformBy" : {
"sentiment.score" : 1,
"_id" : 0
},
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"companyDetails" : {
"$elemMatch" : {
"$and" : [
{
"companyId" : {
"$eq" : 500
}
},
{
"typeId" : {
"$eq" : 10
}
}
]
}
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"company.companyId" : 1,
"company.typeId" : 1,
"date" : 1,
"sentiment.score" : 1
},
"indexName" : "company.companyId_1_company.typeId_1_Date_1_sentiment.score_1",
"isMultiKey" : true,
"multiKeyPaths" : {
"company.companyId" : [
"company"
],
"company.typeId" : [
"company"
],
"date" : [ ],
"sentiment.score" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"company.companyId" : [
"[500.0, 500.0]"
],
"company.typeId" : [
"[10.0, 10.0]"
],
"date" : [
"[new Date(1627776000000), new Date(1630367999000)]"
],
"sentiment.score" : [
"[MinKey, MaxKey]"
]
}
}
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 208239,
"executionTimeMillis" : 54535,
"totalKeysExamined" : 208239,
"totalDocsExamined" : 208239,
"executionStages" : {
"stage" : "PROJECTION_DEFAULT",
"nReturned" : 208239,
"executionTimeMillisEstimate" : 54017,
"works" : 208240,
"advanced" : 208239,
"needTime" : 0,
"needYield" : 0,
"saveState" : 2869,
"restoreState" : 2869,
"isEOF" : 1,
"transformBy" : {
"sentiment.score" : 1,
"_id" : 0
},
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"companyDetails" : {
"$elemMatch" : {
"$and" : [
{
"companyId" : {
"$eq" : 500
}
},
{
"typeId" : {
"$eq" : 10
}
}
]
}
}
},
"nReturned" : 208239,
"executionTimeMillisEstimate" : 53711,
"works" : 208240,
"advanced" : 208239,
"needTime" : 0,
"needYield" : 0,
"saveState" : 2869,
"restoreState" : 2869,
"isEOF" : 1,
"docsExamined" : 208239,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 208239,
"executionTimeMillisEstimate" : 306,
"works" : 208240,
"advanced" : 208239,
"needTime" : 0,
"needYield" : 0,
"saveState" : 2869,
"restoreState" : 2869,
"isEOF" : 1,
"keyPattern" : {
"company.companyId" : 1,
"company.typeId" : 1,
"date" : 1,
"sentiment.score" : 1
},
"indexName" : "company.companyId_1_company.typeId_1_date_1_sentiment.score_1",
"isMultiKey" : true,
"multiKeyPaths" : {
"company.companyId" : [
"company"
],
"companyDetails.typeId" : [
"company"
],
"date" : [ ],
"sentiment.score" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"company.companyId" : [
"[500.0, 500.0]"
],
"company.typeId" : [
"[10.0, 10.0]"
],
"date" : [
"[new Date(1627776000000), new Date(1630367999000)]"
],
"sentiment.score" : [
"[MinKey, MaxKey]"
]
},
"keysExamined" : 208239,
"seeks" : 1,
"dupsTested" : 208239,
"dupsDropped" : 0
}
}
},
"allPlansExecution" : [ ]
}
},
"nReturned" : NumberLong(208239),
"executionTimeMillisEstimate" : NumberLong(54380)
},
{
"$group" : {
"_id" : {
"$const" : "null"
},
"count" : {
"$sum" : {
"$const" : 1
}
},
"positiveCount" : {
"$sum" : {
"$cond" : [
{
"$gte" : [
"$sentiment.score",
{
"$const" : "0.2"
}
]
},
{
"$const" : 1
},
{
"$const" : 0
}
]
}
},
"negativeCount" : {
"$sum" : {
"$cond" : [
{
"$lte" : [
"$sentiment.score",
{
"$const" : "-0.2"
}
]
},
{
"$const" : 1
},
{
"$const" : 0
}
]
}
},
"neutralCount" : {
"$sum" : {
"$cond" : [
{
"$and" : [
{
"$lt" : [
"$sentiment.score",
{
"$const" : "0.2"
}
]
},
{
"$gt" : [
"$sentiment.score",
{
"$const" : "-0.2"
}
]
}
]
},
{
"$const" : 1
},
{
"$const" : 0
}
]
}
}
},
"nReturned" : NumberLong(1),
"executionTimeMillisEstimate" : NumberLong(54522)
},
{
"$project" : {
"totalVolume" : "$count",
"positiveCount" : "$positiveCount",
"negativeCount" : "$negativeCount",
"neutralCount" : "$neutralCount",
"positiveAvg" : {
"$multiply" : [
{
"$divide" : [
"$positiveCount",
"$count"
]
},
{
"$const" : 100
}
]
},
"negativeAvg" : {
"$multiply" : [
{
"$divide" : [
"$negativeCount",
"$count"
]
},
{
"$const" : 100
}
]
},
"neutralAvg" : {
"$multiply" : [
{
"$divide" : [
"$neutralCount",
"$count"
]
},
{
"$const" : 100
}
]
},
"_id" : false
},
"nReturned" : NumberLong(1),
"executionTimeMillisEstimate" : NumberLong(54532)
}
],
"serverInfo" : {
"host" : "ip-192-168-83-49.ec2.internal",
"port" : 27017,
"version" : "4.4.9",
"gitVersion" : "b4048e19814bfebac717cf5a880076aa69aba481"
},
"ok" : 1
}
I want to avoid the FETCH stage and make this query execute in milliseconds. Please provide any suggestions to optimize the query