Yes you are right.Can i ask you one last thing?My table contains 1.157.000 rows but i have used bucket pattern so i have 1 document that contains 12 subdocuments inside.I use the explain stat and i see something weird.It says that the planner used indexscan but it scanned the whole table.isnt this sequence scan?I post the query and the execution plan below.
agg_pipeline=[
{"$match": {
"samples.timestamp1": {"$gte": ISODate("2010-01-01 00:00:00",
"%Y-%m-%d %H:%M:%S"),
"$lte" :ISODate("2020-12-31 01:55:00", "%Y-%m-%d %H:%M:%S")},
"id13": {"$gt": 5}}},
{"$unwind": "$samples"},
{"$match": {
"samples.id13": {"$gt": 5}}},
{"$group": {
"_id": {"$dateToString": {"format": "%Y-%m-%d %H",
"date": "$samples.timestamp1"}},}},
{"$sort": {"_id": -1}},
{ "$limit": 5},
{"$project": {
"_id": 0,
"hour":"$_id"}}
];
and the explain is here
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-31T01:55:00Z")
}
},
{
"id13" : {
"$gt" : 5
}
},
{
"samples.timestamp1" : {
"$gte" : ISODate("2010-01-01T00:00:00Z")
}
}
]
},
"queryHash" : "D8B2A1E8",
"planCacheKey" : "322C4E92",
"winningPlan" : {
"stage" : "PROJECTION_SIMPLE",
"transformBy" : {
"samples" : 1,
"_id" : 0
},
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"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(1609379700000), new Date(-9223372036854775808)]"
],
"samples.id13" : [
"[MinKey, MaxKey]"
]
}
}
}
},
"rejectedPlans" : [
{
"stage" : "PROJECTION_SIMPLE",
"transformBy" : {
"samples" : 1,
"_id" : 0
},
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"id13" : {
"$gt" : 5
}
},
{
"samples.timestamp1" : {
"$lte" : ISODate("2020-12-31T01: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" : 0,
"executionTimeMillis" : 582,
"totalKeysExamined" : 1156920,
"totalDocsExamined" : 96410,
"executionStages" : {
"stage" : "PROJECTION_SIMPLE",
"nReturned" : 0,
"executionTimeMillisEstimate" : 54,
"works" : 1156921,
"advanced" : 0,
"needTime" : 1156920,
"needYield" : 0,
"saveState" : 1186,
"restoreState" : 1186,
"isEOF" : 1,
"transformBy" : {
"samples" : 1,
"_id" : 0
},
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"id13" : {
"$gt" : 5
}
},
{
"samples.timestamp1" : {
"$gte" : ISODate("2010-01-01T00:00:00Z")
}
}
]
},
"nReturned" : 0,
"executionTimeMillisEstimate" : 52,
"works" : 1156921,
"advanced" : 0,
"needTime" : 1156920,
"needYield" : 0,
"saveState" : 1186,
"restoreState" : 1186,
"isEOF" : 1,
"docsExamined" : 96410,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 96410,
"executionTimeMillisEstimate" : 47,
"works" : 1156921,
"advanced" : 96410,
"needTime" : 1060510,
"needYield" : 0,
"saveState" : 1186,
"restoreState" : 1186,
"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(1609379700000), new Date(-9223372036854775808)]"
],
"samples.id13" : [
"[MinKey, MaxKey]"
]
},
"keysExamined" : 1156920,
"seeks" : 1,
"dupsTested" : 1156920,
"dupsDropped" : 1060510
}
}
},
"allPlansExecution" : [
{
"nReturned" : 0,
"executionTimeMillisEstimate" : 0,
"totalKeysExamined" : 28929,
"totalDocsExamined" : 2411,
"executionStages" : {
"stage" : "PROJECTION_SIMPLE",
"nReturned" : 0,
"executionTimeMillisEstimate" : 0,
"works" : 28929,
"advanced" : 0,
"needTime" : 28929,
"needYield" : 0,
"saveState" : 58,
"restoreState" : 57,
"isEOF" : 0,
"transformBy" : {
"samples" : 1,
"_id" : 0
},
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"id13" : {
"$gt" : 5
}
},
{
"samples.timestamp1" : {
"$gte" : ISODate("2010-01-01T00:00:00Z")
}
}
]
},
"nReturned" : 0,
"executionTimeMillisEstimate" : 0,
"works" : 28929,
"advanced" : 0,
"needTime" : 28929,
"needYield" : 0,
"saveState" : 58,
"restoreState" : 57,
"isEOF" : 0,
"docsExamined" : 2411,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 2411,
"executionTimeMillisEstimate" : 0,
"works" : 28929,
"advanced" : 2411,
"needTime" : 26518,
"needYield" : 0,
"saveState" : 58,
"restoreState" : 57,
"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(1609379700000), new Date(-9223372036854775808)]"
],
"samples.id13" : [
"[MinKey, MaxKey]"
]
},
"keysExamined" : 28929,
"seeks" : 1,
"dupsTested" : 28929,
"dupsDropped" : 26518
}
}
}
},
{
"nReturned" : 0,
"executionTimeMillisEstimate" : 3,
"totalKeysExamined" : 28929,
"totalDocsExamined" : 2412,
"executionStages" : {
"stage" : "PROJECTION_SIMPLE",
"nReturned" : 0,
"executionTimeMillisEstimate" : 3,
"works" : 28929,
"advanced" : 0,
"needTime" : 28929,
"needYield" : 0,
"saveState" : 1186,
"restoreState" : 1186,
"isEOF" : 0,
"transformBy" : {
"samples" : 1,
"_id" : 0
},
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"id13" : {
"$gt" : 5
}
},
{
"samples.timestamp1" : {
"$lte" : ISODate("2020-12-31T01:55:00Z")
}
}
]
},
"nReturned" : 0,
"executionTimeMillisEstimate" : 3,
"works" : 28929,
"advanced" : 0,
"needTime" : 28929,
"needYield" : 0,
"saveState" : 1186,
"restoreState" : 1186,
"isEOF" : 0,
"docsExamined" : 2412,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 2412,
"executionTimeMillisEstimate" : 3,
"works" : 28929,
"advanced" : 2412,
"needTime" : 26517,
"needYield" : 0,
"saveState" : 1186,
"restoreState" : 1186,
"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" : 28929,
"seeks" : 1,
"dupsTested" : 28929,
"dupsDropped" : 26517
}
}
}
}
]
}
},
"nReturned" : NumberLong(0),
"executionTimeMillisEstimate" : NumberLong(552)
},
{
"$unwind" : {
"path" : "$samples"
},
"nReturned" : NumberLong(0),
"executionTimeMillisEstimate" : NumberLong(552)
},
{
"$match" : {
"samples.id13" : {
"$gt" : 5
}
},
"nReturned" : NumberLong(0),
"executionTimeMillisEstimate" : NumberLong(552)
},
{
"$group" : {
"_id" : {
"$dateToString" : {
"date" : "$samples.timestamp1",
"format" : {
"$const" : "%Y-%m-%d %H"
}
}
}
},
"nReturned" : NumberLong(0),
"executionTimeMillisEstimate" : NumberLong(552)
},
{
"$sort" : {
"sortKey" : {
"_id" : -1
},
"limit" : NumberLong(5)
},
"nReturned" : NumberLong(0),
"executionTimeMillisEstimate" : NumberLong(552)
},
{
"$project" : {
"hour" : "$_id",
"_id" : false
},
"nReturned" : NumberLong(0),
"executionTimeMillisEstimate" : NumberLong(552)
}
],
"serverInfo" : {
"host" : "xaris-MS-7817",
"port" : 27017,
"version" : "4.4.6",
"gitVersion" : "72e66213c2c3eab37d9358d5e78ad7f5c1d0d0d7"
},
"ok" : 1
}