I'm really happy to have index planSummary logging in 2.6! I'm seeing some unexpected results on certain queries though. How should I interpret this?
Sat Jan 3 02:02:19.339 [conn250732] query <collection> query: { $query: { a: "zZXuz5tAW", b: { $lt: new Date(1408499759517) }, c: { $in: [ 1.0, 2.0, 3.0, 4.0, 5.0, 6.0, 7.0, 8.0, 9.0 ] }, d: { $in: [ null, "*", "EVcfZZtgw8" ] } }, $orderBy: { b: -1 }} planSummary: IXSCAN { a: 1.0, c: 1.0, : 1.0 }, IXSCAN { a: 1.0, c: 1.0, b: 1.0 }, IXSCAN { a: 1.0, c: 1.0, b: 1.0 }, IXSCAN { a: 1.0, c: 1.0, b: 1.0 }, IXSCAN { a: 1.0, c: 1.0, b: 1.0 }, IXSCAN { a: 1.0, c: 1.0, b: 1.0 }, IXSCAN { a: 1.0, c: 1.0, b: 1.0 }, IXSCAN { a: 1.0, c: 1.0, b: 1.0 }, IXSCAN { a: 1.0, c: 1.0, b: 1.0 } ntoreturn:10 ntoskip:0 nscanned:5 nscannedOjects:5 keyUpdates:0 numYields:0 locks(micros) r:964 nreturned:5 reslen:2555 0ms
Does this mean that there are 9 parallel scans of the index on {a,c,b}? It looks like there is an IXSCAN for each value in the "c: { $in: [] }" predicate.
IXSCAN { a: 1.0, c: 1.0, b: 1.0 }, IXSCAN { a: 1.0, c: 1.0, b: 1.0 }, IXSCAN { a: 1.0, c: 1.0, b: 1.0 }, IXSCAN { a: 1.0, c: 1.0, b: 1.0 }, IXSCAN { a: 1.0, c: 1.0, b: 1.0 }, IXSCAN { a: 1.0, c: 1.0, b: 1.0 }, IXSCAN { a: 1.0, c: 1.0, b: 1.0 }, IXSCAN { a: 1.0, c: 1.0, b: 1.0 }, IXSCAN { a: 1.0, c: 1.0, b: 1.0 }
Yes, what's happening is that for each value of "c" the query is traversing the a,c,b index (in reverse order to get the results sorted in reverse order by "b"). The it will merge the (up to) nine result sets for a single sorted result set.
Contrast that with the same query without the "order by" which can traverse the a,c,b index once, but only using a,c part of the index to select (first) and then using the b part of the index to keep or reject the matched record. These will *not* be in correct order to satisfy the sort by b, but it will only have to traverse the index once.
You can see this level of detail by using explain() with your sample query (on a data set that has some matching and non-matching records) both with and without the "sort" clause and observe the difference.
For $in with sort:
"clauses" : [
{
"cursor" : "BtreeCursor a_1_c_1_b_1 reverse",
"isMultiKey" : false,
"n" : 0,
"nscannedObjects" : 0,
"nscanned" : 0,
"scanAndOrder" : false,
"indexOnly" : false,
"nChunkSkips" : 0,
"indexBounds" : {
"a" : [
[
"zZXuz5tAW",
"zZXuz5tAW"
]
],
"c" : [
[
9,
9
]
],
"b" : [
[
ISODate("2014-08-20T01:55:59.
true
]
]
}
},
{
"cursor" : "BtreeCursor a_1_c_1_b_1 reverse",
"isMultiKey" : false,
"n" : 1,
"nscannedObjects" : 1,
"nscanned" : 1,
"scanAndOrder" : false,
"indexOnly" : false,
"nChunkSkips" : 0,
"indexBounds" : {
"a" : [
[
"zZXuz5tAW",
"zZXuz5tAW"
]
],
"c" : [
[
8,
8
]
],
"b" : [
[
ISODate("2014-08-20T01:55:59.
true
]
]
}
},
(etc.)
For $in without sort:
{
"cursor" : "BtreeCursor a_1_c_1_b_1",
"isMultiKey" : false,
"n" : 1,
"nscannedObjects" : 1,
"nscanned" : 2,
"nscannedObjectsAllPlans" : 1,
"nscannedAllPlans" : 2,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 0,
"nChunkSkips" : 0,
"millis" : 0,
"indexBounds" : {
"a" : [
[
"zZXuz5tAW",
"zZXuz5tAW"
]
],
"c" : [
[
1,
1
],
[
2,
2
],
[
3,
3
],
[
4,
4
],
[
5,
5
],
[
6,
6
],
[
7,
7
],
[
8,
8
],
[
9,
9
]
],
"b" : [
[
true,
ISODate("2014-08-20T01:55:59.
]
]
},
HTH,
댓글 없음:
댓글 쓰기