$lt much slower than $gt?
I have a very strange behavior:
I have a collection with the following fields:
a - an email address, e.g. "email@address.com"
b - an array, containing an integer and string, [ 0, "test" ] or [ 1, "foo"] or [ 2, "bar" ] (these are the only 3 values possible)
c - an timestamp (integer)
I created the following compound index:
{ a : 1, b : 1, c : -1 }
when I run:
db.col.find({a:"some@email.com", b : { $lt : 2 }}).sort({c:-1})
The query doesn't return (it's a 2.5 mil doc table) [I waited for a couple of minutes], I also tried hinting the query and creating and index with b as DESCENDING.
however, if I run:
db.col.find({a:"some@email.com", b : { $gt : 0 }}).sort({c:-1})
The query uses the index and runs immediately, with or without a hint.
BTW, a $in query also run quickly.
Can you help me understand what's the different between $gt and $lt and why do they act so differently?
Forgot to mention, I ran all the queries with a limit of 20, so it's not about the number of matching docs.
Could you provide some additional information:
What version is this?
What does explain(true) return when appended to the query that isn't
running fast?
Definitely!
I ran it on a smaller data set, but the results are similar (a lot of documents are being scanned).
This is the explain:
TESTMONGO:PRIMARY> db.files.find({a:"some@email.com", b : { $lt : 2 }}).sort({c:-1}).limit(20).explain(true)
{
"clauses" : [
{
"cursor" : "BtreeCursor a_1_b_1_c_-1",
"isMultiKey" : true,
"n" : 20,
"nscannedObjects" : 9979,
"nscanned" : 9979,
"scanAndOrder" : true,
"indexOnly" : false,
"nChunkSkips" : 0,
"indexBounds" : {
"a" : [
[
"some@email.com",
"some@email.com"
]
],
"b" : [
[
-Infinity,
2
]
],
"c" : [
[
{
"$maxElement" : 1
},
{
"$minElement" : 1
}
]
]
}
},
{
"cursor" : "BtreeCursor a_1_b_1_c_-1",
"isMultiKey" : true,
"n" : 0,
"nscannedObjects" : 0,
"nscanned" : 0,
"scanAndOrder" : true,
"indexOnly" : false,
"nChunkSkips" : 0,
"indexBounds" : {
"a" : [
[
"some@email.com",
"some@email.com"
]
],
"b" : [
[
-Infinity,
2
]
],
"c" : [
[
{
"$maxElement" : 1
},
{
"$minElement" : 1
}
]
]
}
}
],
"cursor" : "QueryOptimizerCursor",
"n" : 20,
"nscannedObjects" : 9979,
"nscanned" : 9979,
"nscannedObjectsAllPlans" : 9979,
"nscannedAllPlans" : 9979,
"scanAndOrder" : false,
"nYields" : 78,
"nChunkSkips" : 0,
"millis" : 128,
"allPlans" : [
{
"clauses" : [
{
"cursor" : "BtreeCursor a_1_b_1_c_-1",
"isMultiKey" : true,
"n" : 20,
"nscannedObjects" : 9979,
"nscanned" : 9979,
"scanAndOrder" : true,
"indexOnly" : false,
"nChunkSkips" : 0,
"indexBounds" : {
"a" : [
[
"some@email.com",
"some@email.com"
]
],
"b" : [
[
-Infinity,
2
]
],
"c" : [
[
{
"$maxElement" : 1
},
{
"$minElement" : 1
}
]
]
}
},
{
"cursor" : "BtreeCursor a_1_b_1_c_-1",
"isMultiKey" : true,
"n" : 0,
"nscannedObjects" : 0,
"nscanned" : 0,
"scanAndOrder" : true,
"indexOnly" : false,
"nChunkSkips" : 0,
"indexBounds" : {
"a" : [
[
"some@email.com",
"some@email.com"
]
],
"b" : [
[
-Infinity,
2
]
],
"c" : [
[
{
"$maxElement" : 1
},
{
"$minElement" : 1
}
]
]
}
}
],
"cursor" : "QueryOptimizerCursor",
"n" : 20,
"nscannedObjects" : 9979,
"nscanned" : 9979,
"scanAndOrder" : false,
"nChunkSkips" : 0
}
],
"server" : "TESTMONGO:27017",
"filterSet" : false,
"stats" : {
"type" : "KEEP_MUTATIONS",
"works" : 10001,
"yields" : 78,
"unyields" : 78,
"invalidates" : 0,
"advanced" : 20,
"needTime" : 9981,
"needFetch" : 0,
"isEOF" : 0,
"children" : [
{
"type" : "OR",
"works" : 10001,
"yields" : 78,
"unyields" : 78,
"invalidates" : 0,
"advanced" : 20,
"needTime" : 9981,
"needFetch" : 0,
"isEOF" : 0,
"dupsTested" : 20,
"dupsDropped" : 0,
"locsForgotten" : 0,
"matchTested_0" : 0,
"matchTested_1" : 0,
"children" : [
{
"type" : "SORT",
"works" : 10001,
"yields" : 78,
"unyields" : 78,
"invalidates" : 0,
"advanced" : 20,
"needTime" : 9980,
"needFetch" : 0,
"isEOF" : 1,
"forcedFetches" : 0,
"memUsage" : 100347,
"memLimit" : 33554432,
"children" : [
{
"type" : "FETCH",
"works" : 9980,
"yields" : 78,
"unyields" : 78,
"invalidates" : 0,
"advanced" : 9979,
"needTime" : 0,
"needFetch" : 0,
"isEOF" : 1,
"alreadyHasObj" : 0,
"forcedFetches" : 0,
"matchTested" : 0,
"children" : [
{
"type" : "IXSCAN",
"works" : 9980,
"yields" : 78,
"unyields" : 78,
"invalidates" : 0,
"advanced" : 9979,
"needTime" : 0,
"needFetch" : 0,
"isEOF" : 1,
"keyPattern" : "{ a: 1.0, b: 1.0, c: -1.0 }",
"isMultiKey" : 1,
"boundsVerbose" : "field #0['a']: [\"some@email.com\", \"some@email.com\"], field #1['b']: [-inf.0, 2.0), field #2['c']: [MaxKey, MinKey]",
"yieldMovedCursor" : 0,
"dupsTested" : 9979,
"dupsDropped" : 0,
"seenInvalidated" : 0,
"matchTested" : 0,
"keysExamined" : 9979,
"children" : [ ]
}
]
}
]
},
{
"type" : "SORT",
"works" : 0,
"yields" : 78,
"unyields" : 78,
"invalidates" : 0,
"advanced" : 0,
"needTime" : 0,
"needFetch" : 0,
"isEOF" : 0,
"forcedFetches" : 0,
"memUsage" : 0,
"memLimit" : 33554432,
"children" : [
{
"type" : "FETCH",
"works" : 0,
"yields" : 78,
"unyields" : 78,
"invalidates" : 0,
"advanced" : 0,
"needTime" : 0,
"needFetch" : 0,
"isEOF" : 0,
"alreadyHasObj" : 0,
"forcedFetches" : 0,
"matchTested" : 0,
"children" : [
{
"type" : "IXSCAN",
"works" : 0,
"yields" : 78,
"unyields" : 78,
"invalidates" : 0,
"advanced" : 0,
"needTime" : 0,
"needFetch" : 0,
"isEOF" : 0,
"keyPattern" : "{ a: 1.0, b: 1.0, c: -1.0 }",
"isMultiKey" : 1,
"boundsVerbose" : "field #0['a']: [\"some@email.com\", \"some@email.com\"], field #1['b']: [-inf.0, 2.0), field #2['c']: [MaxKey, MinKey]",
"yieldMovedCursor" : 0,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0,
"matchTested" : 0,
"keysExamined" : 0,
"children" : [ ]
}
]
}
]
}
]
}
]
}
}
The version is 2.6.6.
Ping?
Something fishy is going on here...
This is an explain for an OR query - but you don't have any $or
clauses in your query... Which of the fields that you're querying
is an array, btw?
Unless it's using an index intersection for filtering and sorting -
which would be really strange - can you provide a few sample
documents, we can try to reproduce here.
댓글 없음:
댓글 쓰기