|
Also let me add the explain results in both occasions.
This is the default one:
{
|
"queryPlanner" : {
|
"plannerVersion" : 1,
|
"namespace" : "db.Measurement",
|
"indexFilterSet" : false,
|
"parsedQuery" : {
|
"$and" : [
|
{
|
"fieldA" : {
|
"$eq" : 0.0
|
}
|
},
|
{
|
"fieldB" : {
|
"$eq" : "0013A20041673C35"
|
}
|
},
|
{
|
"timestamp" : {
|
"$lte" : ISODate("2017-12-06T10:49:36.833Z")
|
}
|
},
|
{
|
"timestamp" : {
|
"$gte" : ISODate("2017-11-29T14:55:15.682Z")
|
}
|
}
|
]
|
},
|
"winningPlan" : {
|
"stage" : "FETCH",
|
"filter" : {
|
"$and" : [
|
{
|
"fieldA" : {
|
"$eq" : 0.0
|
}
|
},
|
{
|
"fieldB" : {
|
"$eq" : "0013A20041673C35"
|
}
|
}
|
]
|
},
|
"inputStage" : {
|
"stage" : "IXSCAN",
|
"keyPattern" : {
|
"timestamp" : 1
|
},
|
"indexName" : "timestamp_index",
|
"isMultiKey" : false,
|
"multiKeyPaths" : {
|
"timestamp" : []
|
},
|
"isUnique" : false,
|
"isSparse" : false,
|
"isPartial" : false,
|
"indexVersion" : 2,
|
"direction" : "backward",
|
"indexBounds" : {
|
"timestamp" : [
|
"[new Date(1512557376833), new Date(1511967315682)]"
|
]
|
}
|
}
|
},
|
"rejectedPlans" : [
|
{
|
"stage" : "SORT",
|
"sortPattern" : {
|
"timestamp" : -1.0
|
},
|
"inputStage" : {
|
"stage" : "SORT_KEY_GENERATOR",
|
"inputStage" : {
|
"stage" : "FETCH",
|
"filter" : {
|
"$and" : [
|
{
|
"timestamp" : {
|
"$lte" : ISODate("2017-12-06T10:49:36.833Z")
|
}
|
},
|
{
|
"timestamp" : {
|
"$gte" : ISODate("2017-11-29T14:55:15.682Z")
|
}
|
}
|
]
|
},
|
"inputStage" : {
|
"stage" : "IXSCAN",
|
"keyPattern" : {
|
"fieldA" : 1,
|
"fieldB" : 1
|
},
|
"indexName" : "fieldA_fieldB_index",
|
"isMultiKey" : false,
|
"multiKeyPaths" : {
|
"fieldA" : [],
|
"fieldB" : []
|
},
|
"isUnique" : false,
|
"isSparse" : false,
|
"isPartial" : false,
|
"indexVersion" : 2,
|
"direction" : "forward",
|
"indexBounds" : {
|
"fieldA" : [
|
"[0.0, 0.0]"
|
],
|
"fieldB" : [
|
"[\"0013A20041673C35\", \"0013A20041673C35\"]"
|
]
|
}
|
}
|
}
|
}
|
}
|
]
|
},
|
"executionStats" : {
|
"executionSuccess" : true,
|
"nReturned" : 3235,
|
"executionTimeMillis" : 2934,
|
"totalKeysExamined" : 1234298,
|
"totalDocsExamined" : 1234298,
|
"executionStages" : {
|
"stage" : "FETCH",
|
"filter" : {
|
"$and" : [
|
{
|
"fieldA" : {
|
"$eq" : 0.0
|
}
|
},
|
{
|
"fieldB" : {
|
"$eq" : "0013A20041673C35"
|
}
|
}
|
]
|
},
|
"nReturned" : 3235,
|
"executionTimeMillisEstimate" : 2790,
|
"works" : 1234299,
|
"advanced" : 3235,
|
"needTime" : 1231063,
|
"needYield" : 0,
|
"saveState" : 9674,
|
"restoreState" : 9674,
|
"isEOF" : 1,
|
"invalidates" : 0,
|
"docsExamined" : 1234298,
|
"alreadyHasObj" : 0,
|
"inputStage" : {
|
"stage" : "IXSCAN",
|
"nReturned" : 1234298,
|
"executionTimeMillisEstimate" : 476,
|
"works" : 1234299,
|
"advanced" : 1234298,
|
"needTime" : 0,
|
"needYield" : 0,
|
"saveState" : 9674,
|
"restoreState" : 9674,
|
"isEOF" : 1,
|
"invalidates" : 0,
|
"keyPattern" : {
|
"timestamp" : 1
|
},
|
"indexName" : "timestamp_index",
|
"isMultiKey" : false,
|
"multiKeyPaths" : {
|
"timestamp" : []
|
},
|
"isUnique" : false,
|
"isSparse" : false,
|
"isPartial" : false,
|
"indexVersion" : 2,
|
"direction" : "backward",
|
"indexBounds" : {
|
"timestamp" : [
|
"[new Date(1512557376833), new Date(1511967315682)]"
|
]
|
},
|
"keysExamined" : 1234298,
|
"seeks" : 1,
|
"dupsTested" : 0,
|
"dupsDropped" : 0,
|
"seenInvalidated" : 0
|
}
|
}
|
},
|
"serverInfo" : {
|
"host" : "mitsos-XPS-13-9360",
|
"port" : 27017,
|
"version" : "3.4.10",
|
"gitVersion" : "078f28920cb24de0dd479b5ea6c66c644f6326e9"
|
},
|
"ok" : 1.0
|
}
|
This one is with the hint:
{
|
"queryPlanner" : {
|
"plannerVersion" : 1,
|
"namespace" : "db.Measurement",
|
"indexFilterSet" : false,
|
"parsedQuery" : {
|
"$and" : [
|
{
|
"fieldA" : {
|
"$eq" : 0.0
|
}
|
},
|
{
|
"fieldB" : {
|
"$eq" : "0013A20041673C35"
|
}
|
},
|
{
|
"timestamp" : {
|
"$lte" : ISODate("2017-12-06T10:49:36.833Z")
|
}
|
},
|
{
|
"timestamp" : {
|
"$gte" : ISODate("2017-11-29T14:55:15.682Z")
|
}
|
}
|
]
|
},
|
"winningPlan" : {
|
"stage" : "SORT",
|
"sortPattern" : {
|
"timestamp" : -1.0
|
},
|
"inputStage" : {
|
"stage" : "SORT_KEY_GENERATOR",
|
"inputStage" : {
|
"stage" : "FETCH",
|
"filter" : {
|
"$and" : [
|
{
|
"timestamp" : {
|
"$lte" : ISODate("2017-12-06T10:49:36.833Z")
|
}
|
},
|
{
|
"timestamp" : {
|
"$gte" : ISODate("2017-11-29T14:55:15.682Z")
|
}
|
}
|
]
|
},
|
"inputStage" : {
|
"stage" : "IXSCAN",
|
"keyPattern" : {
|
"fieldA" : 1,
|
"fieldB" : 1
|
},
|
"indexName" : "fieldA_fieldB_index",
|
"isMultiKey" : false,
|
"multiKeyPaths" : {
|
"fieldA" : [],
|
"fieldB" : []
|
},
|
"isUnique" : false,
|
"isSparse" : false,
|
"isPartial" : false,
|
"indexVersion" : 2,
|
"direction" : "forward",
|
"indexBounds" : {
|
"fieldA" : [
|
"[0.0, 0.0]"
|
],
|
"fieldB" : [
|
"[\"0013A20041673C35\", \"0013A20041673C35\"]"
|
]
|
}
|
}
|
}
|
}
|
},
|
"rejectedPlans" : []
|
},
|
"executionStats" : {
|
"executionSuccess" : true,
|
"nReturned" : 3235,
|
"executionTimeMillis" : 24,
|
"totalKeysExamined" : 8445,
|
"totalDocsExamined" : 8445,
|
"executionStages" : {
|
"stage" : "SORT",
|
"nReturned" : 3235,
|
"executionTimeMillisEstimate" : 30,
|
"works" : 11683,
|
"advanced" : 3235,
|
"needTime" : 8447,
|
"needYield" : 0,
|
"saveState" : 91,
|
"restoreState" : 91,
|
"isEOF" : 1,
|
"invalidates" : 0,
|
"sortPattern" : {
|
"timestamp" : -1.0
|
},
|
"memUsage" : 905800,
|
"memLimit" : 33554432,
|
"inputStage" : {
|
"stage" : "SORT_KEY_GENERATOR",
|
"nReturned" : 3235,
|
"executionTimeMillisEstimate" : 20,
|
"works" : 8447,
|
"advanced" : 3235,
|
"needTime" : 5211,
|
"needYield" : 0,
|
"saveState" : 91,
|
"restoreState" : 91,
|
"isEOF" : 1,
|
"invalidates" : 0,
|
"inputStage" : {
|
"stage" : "FETCH",
|
"filter" : {
|
"$and" : [
|
{
|
"timestamp" : {
|
"$lte" : ISODate("2017-12-06T10:49:36.833Z")
|
}
|
},
|
{
|
"timestamp" : {
|
"$gte" : ISODate("2017-11-29T14:55:15.682Z")
|
}
|
}
|
]
|
},
|
"nReturned" : 3235,
|
"executionTimeMillisEstimate" : 20,
|
"works" : 8446,
|
"advanced" : 3235,
|
"needTime" : 5210,
|
"needYield" : 0,
|
"saveState" : 91,
|
"restoreState" : 91,
|
"isEOF" : 1,
|
"invalidates" : 0,
|
"docsExamined" : 8445,
|
"alreadyHasObj" : 0,
|
"inputStage" : {
|
"stage" : "IXSCAN",
|
"nReturned" : 8445,
|
"executionTimeMillisEstimate" : 10,
|
"works" : 8446,
|
"advanced" : 8445,
|
"needTime" : 0,
|
"needYield" : 0,
|
"saveState" : 91,
|
"restoreState" : 91,
|
"isEOF" : 1,
|
"invalidates" : 0,
|
"keyPattern" : {
|
"fieldA" : 1,
|
"fieldB" : 1
|
},
|
"indexName" : "fieldA_fieldB_index",
|
"isMultiKey" : false,
|
"multiKeyPaths" : {
|
"fieldA" : [],
|
"fieldB" : []
|
},
|
"isUnique" : false,
|
"isSparse" : false,
|
"isPartial" : false,
|
"indexVersion" : 2,
|
"direction" : "forward",
|
"indexBounds" : {
|
"fieldA" : [
|
"[0.0, 0.0]"
|
],
|
"fieldB" : [
|
"[\"0013A20041673C35\", \"0013A20041673C35\"]"
|
]
|
},
|
"keysExamined" : 8445,
|
"seeks" : 1,
|
"dupsTested" : 0,
|
"dupsDropped" : 0,
|
"seenInvalidated" : 0
|
}
|
}
|
}
|
}
|
},
|
"serverInfo" : {
|
"host" : "mitsos-XPS-13-9360",
|
"port" : 27017,
|
"version" : "3.4.10",
|
"gitVersion" : "078f28920cb24de0dd479b5ea6c66c644f6326e9"
|
},
|
"ok" : 1.0
|
}
|
|