Thanks to your response.
How can i do it better with this criteria (looking documents in category_ids: 4, price: >200)?
Do i need to create another index, query or documents to be rewritten?
Bogdan
Op vrijdag 19 september 2014 19:48:49 UTC+2 schreef Will Berkeley:
Op vrijdag 19 september 2014 19:48:49 UTC+2 schreef Will Berkeley:
The count was 783618 documents, 880001 of which were found in the index bucket corresponding to the first condition in the $all and had to be scanned to see if they satisfied the second $all condition. Your index is not very selective on any of the $all criteria and using .count() is forcing MongoDB to touch almost every document to see if it satisfies the query.
I think you've run into a bug - could you try this with 2.6.5 release candidate? (Latest 2.6 nightly)
Can you also try reversing the two conditions in $all? The second one is much more selective and I'm wondering why it's not being picked (you can see it in explain).
Asya, I did what said but with no luck.
Here is my explain output:
MongoDB shell version: 2.6.5-rc4
connecting to: test
> db.collection.find({"attr" : {"$all": [{"$elemMatch": {"k": "price", "s": 0, "v": {"$gt": 110}}}, {"$elemMatch": {"k": "category_ids", "s": 0, "v": {"$in": [4]}}}]}}).explain(true);
{
"cursor" : "BtreeCursor attr.k_1_attr.s_1_attr.v_1",
"isMultiKey" : true,
"n" : 783618,
"nscannedObjects" : 880001,
"nscanned" : 880001,
"nscannedObjectsAllPlans" : 880120,
"nscannedAllPlans" : 880121,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 6876,
"nChunkSkips" : 0,
"millis" : 8964,
"indexBounds" : {
"attr.k" : [
[
"category_ids",
"category_ids"
]
],
"attr.s" : [
[
0,
0
]
],
"attr.v" : [
[
4,
4
]
]
},
"allPlans" : [
{
"cursor" : "BtreeCursor attr.k_1_attr.s_1_attr.v_1",
"isMultiKey" : true,
"n" : 783618,
"nscannedObjects" : 880001,
"nscanned" : 880001,
"scanAndOrder" : false,
"indexOnly" : false,
"nChunkSkips" : 0,
"indexBounds" : {
"attr.k" : [
[
"category_ids",
"category_ids"
]
],
"attr.s" : [
[
0,
0
]
],
"attr.v" : [
[
4,
4
]
]
}
},
{
"cursor" : "BtreeCursor attr.k_1_attr.s_1_attr.v_1",
"isMultiKey" : true,
"n" : 92,
"nscannedObjects" : 119,
"nscanned" : 120,
"scanAndOrder" : false,
"indexOnly" : false,
"nChunkSkips" : 0,
"indexBounds" : {
"attr.k" : [
[
"price",
"price"
]
],
"attr.s" : [
[
0,
0
]
],
"attr.v" : [
[
110,
Infinity
]
]
}
}
],
"server" : "elastica:27017",
"filterSet" : false,
"stats" : {
"type" : "KEEP_MUTATIONS",
"works" : 880003,
"yields" : 6876,
"unyields" : 6876,
"invalidates" : 0,
"advanced" : 783618,
"needTime" : 96384,
"needFetch" : 0,
"isEOF" : 1,
"children" : [
{
"type" : "FETCH",
"works" : 880003,
"yields" : 6876,
"unyields" : 6876,
"invalidates" : 0,
"advanced" : 783618,
"needTime" : 96384,
"needFetch" : 0,
"isEOF" : 1,
"alreadyHasObj" : 0,
"forcedFetches" : 0,
"matchTested" : 783618,
"children" : [
{
"type" : "IXSCAN",
"works" : 880002,
"yields" : 6876,
"unyields" : 6876,
"invalidates" : 0,
"advanced" : 880001,
"needTime" : 1,
"needFetch" : 0,
"isEOF" : 1,
"keyPattern" : "{ attr.k: 1.0, attr.s: 1.0, attr.v: 1.0 }",
"isMultiKey" : 1,
"boundsVerbose" : "field #0['attr.k']: [\"category_ids\", \"category_ids\"], field #1['attr.s']: [0.0, 0.0], field #2['attr.v']: [4.0, 4.0]",
"yieldMovedCursor" : 0,
"dupsTested" : 880001,
"dupsDropped" : 0,
"seenInvalidated" : 0,
"matchTested" : 0,
"keysExamined" : 880001,
"children" : [ ]
}
]
}
]
}
}
Thanks for reporting this, that seems really strange to me, let me see
if I can reproduce in a smaller environment and file a server ticket
so we can get to the bottom of it.
if I can reproduce in a smaller environment and file a server ticket
so we can get to the bottom of it.
have you the same problem (could you reproduce it)?
I think for MongoDB is a bit hard to work with different types in one index field. like now i have an array and a number.
I apologize for the delay, it has been crazy busy and this case slipped from my radar.
While it's good to keep same types in fields (for one thing to enable sorting) I don't think that's the cause of what you see.
I will try to reproduce it though as soon as I get a chance.
It looks like you filed SERVER-16011 and received an explanation that the query optimizer is using index bounds correctly.
Does that clarify what is happening with the query?
댓글 없음:
댓글 쓰기