Last active
May 15, 2018 06:55
-
-
Save N-Coder/b9e89a925e895c605d84bfeed648d82c to your computer and use it in GitHub Desktop.
MongoDB indexed array { $gt: -Infinity } vs { $exists: true, $ne: [] }}
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
> db.users.find({'seen_events': { $gt: -Infinity }}).explain('executionStats') | |
{ | |
"queryPlanner" : { | |
"plannerVersion" : 1, | |
"namespace" : "dsl_tweet_database.users", | |
"indexFilterSet" : false, | |
"parsedQuery" : { | |
"seen_events" : { | |
"$gt" : -Infinity | |
} | |
}, | |
"winningPlan" : { | |
"stage" : "FETCH", | |
"inputStage" : { | |
"stage" : "IXSCAN", | |
"keyPattern" : { | |
"seen_events" : 1 | |
}, | |
"indexName" : "seen_events_1", | |
"isMultiKey" : true, | |
"multiKeyPaths" : { | |
"seen_events" : [ | |
"seen_events" | |
] | |
}, | |
"isUnique" : false, | |
"isSparse" : false, | |
"isPartial" : false, | |
"indexVersion" : 2, | |
"direction" : "forward", | |
"indexBounds" : { | |
"seen_events" : [ | |
"(-inf.0, inf.0]" | |
] | |
} | |
} | |
}, | |
"rejectedPlans" : [ ] | |
}, | |
"executionStats" : { | |
"executionSuccess" : true, | |
"nReturned" : 0, | |
"executionTimeMillis" : 1, | |
"totalKeysExamined" : 0, | |
"totalDocsExamined" : 0, | |
"executionStages" : { | |
"stage" : "FETCH", | |
"nReturned" : 0, | |
"executionTimeMillisEstimate" : 0, | |
"works" : 1, | |
"advanced" : 0, | |
"needTime" : 0, | |
"needYield" : 0, | |
"saveState" : 0, | |
"restoreState" : 0, | |
"isEOF" : 1, | |
"invalidates" : 0, | |
"docsExamined" : 0, | |
"alreadyHasObj" : 0, | |
"inputStage" : { | |
"stage" : "IXSCAN", | |
"nReturned" : 0, | |
"executionTimeMillisEstimate" : 0, | |
"works" : 1, | |
"advanced" : 0, | |
"needTime" : 0, | |
"needYield" : 0, | |
"saveState" : 0, | |
"restoreState" : 0, | |
"isEOF" : 1, | |
"invalidates" : 0, | |
"keyPattern" : { | |
"seen_events" : 1 | |
}, | |
"indexName" : "seen_events_1", | |
"isMultiKey" : true, | |
"multiKeyPaths" : { | |
"seen_events" : [ | |
"seen_events" | |
] | |
}, | |
"isUnique" : false, | |
"isSparse" : false, | |
"isPartial" : false, | |
"indexVersion" : 2, | |
"direction" : "forward", | |
"indexBounds" : { | |
"seen_events" : [ | |
"(-inf.0, inf.0]" | |
] | |
}, | |
"keysExamined" : 0, | |
"seeks" : 1, | |
"dupsTested" : 0, | |
"dupsDropped" : 0, | |
"seenInvalidated" : 0 | |
} | |
} | |
}, | |
"serverInfo" : { | |
"host" : "dsl-mongodb", | |
"port" : 27017, | |
"version" : "3.6.4", | |
"gitVersion" : "d0181a711f7e7f39e60b5aeb1dc7097bf6ae5856" | |
}, | |
"ok" : 1 | |
} | |
> db.users.find({'seen_events': { $exists: true, $ne: [] }}).explain('executionStats') | |
{ | |
"queryPlanner" : { | |
"plannerVersion" : 1, | |
"namespace" : "dsl_tweet_database.users", | |
"indexFilterSet" : false, | |
"parsedQuery" : { | |
"$and" : [ | |
{ | |
"seen_events" : { | |
"$exists" : true | |
} | |
}, | |
{ | |
"$nor" : [ | |
{ | |
"seen_events" : { | |
"$eq" : [ ] | |
} | |
} | |
] | |
} | |
] | |
}, | |
"winningPlan" : { | |
"stage" : "FETCH", | |
"filter" : { | |
"$and" : [ | |
{ | |
"seen_events" : { | |
"$exists" : true | |
} | |
}, | |
{ | |
"$nor" : [ | |
{ | |
"seen_events" : { | |
"$eq" : [ ] | |
} | |
} | |
] | |
} | |
] | |
}, | |
"inputStage" : { | |
"stage" : "IXSCAN", | |
"keyPattern" : { | |
"seen_events" : 1 | |
}, | |
"indexName" : "seen_events_1", | |
"isMultiKey" : true, | |
"multiKeyPaths" : { | |
"seen_events" : [ | |
"seen_events" | |
] | |
}, | |
"isUnique" : false, | |
"isSparse" : false, | |
"isPartial" : false, | |
"indexVersion" : 2, | |
"direction" : "forward", | |
"indexBounds" : { | |
"seen_events" : [ | |
"[MinKey, MaxKey]" | |
] | |
} | |
} | |
}, | |
"rejectedPlans" : [ | |
{ | |
"stage" : "FETCH", | |
"filter" : { | |
"$and" : [ | |
{ | |
"$nor" : [ | |
{ | |
"seen_events" : { | |
"$eq" : [ ] | |
} | |
} | |
] | |
}, | |
{ | |
"seen_events" : { | |
"$exists" : true | |
} | |
} | |
] | |
}, | |
"inputStage" : { | |
"stage" : "IXSCAN", | |
"keyPattern" : { | |
"seen_events" : 1 | |
}, | |
"indexName" : "seen_events_1", | |
"isMultiKey" : true, | |
"multiKeyPaths" : { | |
"seen_events" : [ | |
"seen_events" | |
] | |
}, | |
"isUnique" : false, | |
"isSparse" : false, | |
"isPartial" : false, | |
"indexVersion" : 2, | |
"direction" : "forward", | |
"indexBounds" : { | |
"seen_events" : [ | |
"[MinKey, undefined)", | |
"(undefined, [])", | |
"([], MaxKey]" | |
] | |
} | |
} | |
} | |
] | |
}, | |
"executionStats" : { | |
"executionSuccess" : true, | |
"nReturned" : 1239016, | |
"executionTimeMillis" : 107540, | |
"totalKeysExamined" : 2929608, | |
"totalDocsExamined" : 2785777, | |
"executionStages" : { | |
"stage" : "FETCH", | |
"filter" : { | |
"$and" : [ | |
{ | |
"seen_events" : { | |
"$exists" : true | |
} | |
}, | |
{ | |
"$nor" : [ | |
{ | |
"seen_events" : { | |
"$eq" : [ ] | |
} | |
} | |
] | |
} | |
] | |
}, | |
"nReturned" : 1239016, | |
"executionTimeMillisEstimate" : 100264, | |
"works" : 2929609, | |
"advanced" : 1239016, | |
"needTime" : 1690592, | |
"needYield" : 0, | |
"saveState" : 29956, | |
"restoreState" : 29956, | |
"isEOF" : 1, | |
"invalidates" : 0, | |
"docsExamined" : 2785777, | |
"alreadyHasObj" : 0, | |
"inputStage" : { | |
"stage" : "IXSCAN", | |
"nReturned" : 2785777, | |
"executionTimeMillisEstimate" : 4707, | |
"works" : 2929609, | |
"advanced" : 2785777, | |
"needTime" : 143831, | |
"needYield" : 0, | |
"saveState" : 29956, | |
"restoreState" : 29956, | |
"isEOF" : 1, | |
"invalidates" : 0, | |
"keyPattern" : { | |
"seen_events" : 1 | |
}, | |
"indexName" : "seen_events_1", | |
"isMultiKey" : true, | |
"multiKeyPaths" : { | |
"seen_events" : [ | |
"seen_events" | |
] | |
}, | |
"isUnique" : false, | |
"isSparse" : false, | |
"isPartial" : false, | |
"indexVersion" : 2, | |
"direction" : "forward", | |
"indexBounds" : { | |
"seen_events" : [ | |
"[MinKey, MaxKey]" | |
] | |
}, | |
"keysExamined" : 2929608, | |
"seeks" : 1, | |
"dupsTested" : 2929608, | |
"dupsDropped" : 143831, | |
"seenInvalidated" : 0 | |
} | |
} | |
}, | |
"serverInfo" : { | |
"host" : "dsl-mongodb", | |
"port" : 27017, | |
"version" : "3.6.4", | |
"gitVersion" : "d0181a711f7e7f39e60b5aeb1dc7097bf6ae5856" | |
}, | |
"ok" : 1 | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment