Hello guys.i cant understand something in explain plan.
My data consist of 1.157.000 rows.I have nested documents.Every documents consist of 12 subdocuments inside so we have around 96000 documents.I use combound index on samples.timestamp1,samples.id13
My query look like this:
mydb1.mongodbbucketnocpu2index.aggregate([
{
"$match": {
"samples.timestamp1": {"$gte": datetime.strptime("2010-01-01 00:00:00", "%Y-%m-%d %H:%M:%S"),
"$lte" :datetime.strptime("2020-12-31 00:55:00", "%Y-%m-%d %H:%M:%S")},
"samples.id13": {
"$gt": 5
}
}
},
{ "$unwind": "$samples" },
{
"$match": {
"samples.id13": {
"$gt": 5
}
}
},
{
"$group": {
"_id": {"$dateToString": { "format": "%Y-%m-%d ", "date": "$samples.timestamp1" }},
"avg_id13": {
"$avg": "$samples.id13"
}
}
},
{"$sort": {"_id": -1}}
{
"$project": {
"_id": 0,
"day":"$_id",
"avg_id13": 1
}
}
])
and the explain plan is this:
db.mongodbbucketnocpu2index.explain(true).aggregate(agg_pipeline);
{
"stages" : [
{
"$cursor" : {
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "mongodbtime.mongodbbucketnocpu2index",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"samples.timestamp1" : {
"$lte" : ISODate("2020-12-31T00:55:00Z")
}
},
{
"samples.id13" : {
"$gt" : 5
}
},
{
"samples.timestamp1" : {
"$gte" : ISODate("2010-01-01T00:00:00Z")
}
}
]
},
"queryHash" : "E9B4DE5C",
"planCacheKey" : "C7A0292D",
"winningPlan" : {
"stage" : "PROJECTION_SIMPLE",
"transformBy" : {
"samples" : 1,
"_id" : 0
},
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"samples.id13" : {
"$gt" : 5
}
},
{
"samples.timestamp1" : {
"$gte" : ISODate("2010-01-01T00:00:00Z")
}
}
]
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"samples.timestamp1" : -1,
"samples.id13" : 1
},
"indexName" : "samples.timestamp1_-1_samples.id13_1",
"isMultiKey" : true,
"multiKeyPaths" : {
"samples.timestamp1" : [
"samples"
],
"samples.id13" : [
"samples"
]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"samples.timestamp1" : [
"[new Date(1609376100000), new Date(-9223372036854775808)]"
],
"samples.id13" : [
"[MinKey, MaxKey]"
]
}
}
}
},
"rejectedPlans" : [
{
"stage" : "PROJECTION_SIMPLE",
"transformBy" : {
"samples" : 1,
"_id" : 0
},
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"samples.id13" : {
"$gt" : 5
}
},
{
"samples.timestamp1" : {
"$lte" : ISODate("2020-12-31T00:55:00Z")
}
}
]
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"samples.timestamp1" : -1,
"samples.id13" : 1
},
"indexName" : "samples.timestamp1_-1_samples.id13_1",
"isMultiKey" : true,
"multiKeyPaths" : {
"samples.timestamp1" : [
"samples"
],
"samples.id13" : [
"samples"
]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"samples.timestamp1" : [
"[new Date(9223372036854775807), new Date(1262304000000)]"
],
"samples.id13" : [
"[MinKey, MaxKey]"
]
}
}
}
}
]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 63960,
"executionTimeMillis" : 5261,
"totalKeysExamined" : 1156908,
"totalDocsExamined" : 96409,
"executionStages" : {
"stage" : "PROJECTION_SIMPLE",
"nReturned" : 63960,
"executionTimeMillisEstimate" : 1985,
"works" : 1156909,
"advanced" : 63960,
"needTime" : 1092948,
"needYield" : 0,
"saveState" : 1271,
"restoreState" : 1271,
"isEOF" : 1,
"transformBy" : {
"samples" : 1,
"_id" : 0
},
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"samples.id13" : {
"$gt" : 5
}
},
{
"samples.timestamp1" : {
"$gte" : ISODate("2010-01-01T00:00:00Z")
}
}
]
},
"nReturned" : 63960,
"executionTimeMillisEstimate" : 1979,
"works" : 1156909,
"advanced" : 63960,
"needTime" : 1092948,
"needYield" : 0,
"saveState" : 1271,
"restoreState" : 1271,
"isEOF" : 1,
"docsExamined" : 96409,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 96409,
"executionTimeMillisEstimate" : 109,
"works" : 1156909,
"advanced" : 96409,
"needTime" : 1060499,
"needYield" : 0,
"saveState" : 1271,
"restoreState" : 1271,
"isEOF" : 1,
"keyPattern" : {
"samples.timestamp1" : -1,
"samples.id13" : 1
},
"indexName" : "samples.timestamp1_-1_samples.id13_1",
"isMultiKey" : true,
"multiKeyPaths" : {
"samples.timestamp1" : [
"samples"
],
"samples.id13" : [
"samples"
]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"samples.timestamp1" : [
"[new Date(1609376100000), new Date(-9223372036854775808)]"
],
"samples.id13" : [
"[MinKey, MaxKey]"
]
},
"keysExamined" : 1156908,
"seeks" : 1,
"dupsTested" : 1156908,
"dupsDropped" : 1060499
}
}
},
"allPlansExecution" : [
{
"nReturned" : 101,
"executionTimeMillisEstimate" : 20,
"totalKeysExamined" : 2725,
"totalDocsExamined" : 228,
"executionStages" : {
"stage" : "PROJECTION_SIMPLE",
"nReturned" : 101,
"executionTimeMillisEstimate" : 20,
"works" : 2725,
"advanced" : 101,
"needTime" : 2624,
"needYield" : 0,
"saveState" : 7,
"restoreState" : 6,
"isEOF" : 0,
"transformBy" : {
"samples" : 1,
"_id" : 0
},
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"samples.id13" : {
"$gt" : 5
}
},
{
"samples.timestamp1" : {
"$gte" : ISODate("2010-01-01T00:00:00Z")
}
}
]
},
"nReturned" : 101,
"executionTimeMillisEstimate" : 20,
"works" : 2725,
"advanced" : 101,
"needTime" : 2624,
"needYield" : 0,
"saveState" : 7,
"restoreState" : 6,
"isEOF" : 0,
"docsExamined" : 228,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 228,
"executionTimeMillisEstimate" : 0,
"works" : 2725,
"advanced" : 228,
"needTime" : 2497,
"needYield" : 0,
"saveState" : 7,
"restoreState" : 6,
"isEOF" : 0,
"keyPattern" : {
"samples.timestamp1" : -1,
"samples.id13" : 1
},
"indexName" : "samples.timestamp1_-1_samples.id13_1",
"isMultiKey" : true,
"multiKeyPaths" : {
"samples.timestamp1" : [
"samples"
],
"samples.id13" : [
"samples"
]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"samples.timestamp1" : [
"[new Date(1609376100000), new Date(-9223372036854775808)]"
],
"samples.id13" : [
"[MinKey, MaxKey]"
]
},
"keysExamined" : 2725,
"seeks" : 1,
"dupsTested" : 2725,
"dupsDropped" : 2497
}
}
}
},
{
"nReturned" : 90,
"executionTimeMillisEstimate" : 0,
"totalKeysExamined" : 2725,
"totalDocsExamined" : 228,
"executionStages" : {
"stage" : "PROJECTION_SIMPLE",
"nReturned" : 90,
"executionTimeMillisEstimate" : 0,
"works" : 2725,
"advanced" : 90,
"needTime" : 2635,
"needYield" : 0,
"saveState" : 1271,
"restoreState" : 1271,
"isEOF" : 0,
"transformBy" : {
"samples" : 1,
"_id" : 0
},
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"samples.id13" : {
"$gt" : 5
}
},
{
"samples.timestamp1" : {
"$lte" : ISODate("2020-12-31T00:55:00Z")
}
}
]
},
"nReturned" : 90,
"executionTimeMillisEstimate" : 0,
"works" : 2725,
"advanced" : 90,
"needTime" : 2635,
"needYield" : 0,
"saveState" : 1271,
"restoreState" : 1271,
"isEOF" : 0,
"docsExamined" : 228,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 228,
"executionTimeMillisEstimate" : 0,
"works" : 2725,
"advanced" : 228,
"needTime" : 2497,
"needYield" : 0,
"saveState" : 1271,
"restoreState" : 1271,
"isEOF" : 0,
"keyPattern" : {
"samples.timestamp1" : -1,
"samples.id13" : 1
},
"indexName" : "samples.timestamp1_-1_samples.id13_1",
"isMultiKey" : true,
"multiKeyPaths" : {
"samples.timestamp1" : [
"samples"
],
"samples.id13" : [
"samples"
]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"samples.timestamp1" : [
"[new Date(9223372036854775807), new Date(1262304000000)]"
],
"samples.id13" : [
"[MinKey, MaxKey]"
]
},
"keysExamined" : 2725,
"seeks" : 1,
"dupsTested" : 2725,
"dupsDropped" : 2497
}
}
}
}
]
}
},
"nReturned" : NumberLong(63960),
"executionTimeMillisEstimate" : NumberLong(3826)
},
{
"$unwind" : {
"path" : "$samples"
},
"nReturned" : NumberLong(767520),
"executionTimeMillisEstimate" : NumberLong(3887)
},
{
"$match" : {
"samples.id13" : {
"$gt" : 5
}
},
"nReturned" : NumberLong(749342),
"executionTimeMillisEstimate" : NumberLong(4563)
},
{
"$group" : {
"_id" : {
"$dateToString" : {
"date" : "$samples.timestamp1",
"format" : {
"$const" : "%Y-%m-%d "
}
}
},
"avg_id13" : {
"$avg" : "$samples.id13"
}
},
"nReturned" : NumberLong(3178),
"executionTimeMillisEstimate" : NumberLong(5193)
},
{
"$sort" : {
"sortKey" : {
"_id" : -1
}
},
"nReturned" : NumberLong(3178),
"executionTimeMillisEstimate" : NumberLong(5193)
},
{
"$project" : {
"avg_id13" : true,
"day" : "$_id",
"_id" : false
},
"nReturned" : NumberLong(3178),
"executionTimeMillisEstimate" : NumberLong(5193)
}
],
"serverInfo" : {
"host" : "xaris-MS-7817",
"port" : 27017,
"version" : "4.4.6",
"gitVersion" : "72e66213c2c3eab37d9358d5e78ad7f5c1d0d0d7"
},
"ok" : 1
}
I see here that the query use index scan although it scan the whole collection
"executionSuccess" : true,
"nReturned" : 63960,
"executionTimeMillis" : 5261,
"totalKeysExamined" : 1156908,
"totalDocsExamined" : 96409,
"executionStages" : {
.
.
.
"executionTimeMillisEstimate" : 1979,
"works" : 1156909,
"advanced" : 63960,
"needTime" : 1092948,
"needYield" : 0,
"saveState" : 1271,
"restoreState" : 1271,
"isEOF" : 1,
"docsExamined" : 96409,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
Can someone explain to me why does the planner does an index scan although it scan the whole collection?wouldnt be better if it did collection scan?