[SERVER-7579] QueryOptimizer not choosing the right key when querying. Created: 07/Nov/12  Updated: 07/Mar/14  Resolved: 14/Dec/12

Status: Closed
Project: Core Server
Component/s: Querying
Affects Version/s: 2.2.0
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Esteban Feldman Assignee: Aaron Staple
Resolution: Done Votes: 0
Labels: query
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

Linux vizvid-mongo-1 3.2.0-31-virtual #50-Ubuntu SMP Fri Sep 7 16:36:36 UTC 2012 x86_64 x86_64 x86_64 GNU/Linux 8GB ram


Issue Links:
Duplicate
is duplicated by SERVER-7578 QueryOptimizer not choosing the right... Closed
Related
is related to SERVER-7944 add index hint support for operations... Closed
Operating System: Linux
Participants:

 Description   

mongos> db.current_views.find({ ts:

{ $gte: 1352300700, $lt: 1352301000 }

, site: "506f0fea7cbdc70464aea9e9" } ).explain()
{
"clusteredType" : "ParallelSort",
"shards" : {
"vizvid-mongo-1:27019" : [
{
"cursor" : "BtreeCursor site_1",
"isMultiKey" : false,
"n" : 3413,
"nscannedObjects" : 4470768,
"nscanned" : 4470768,
"nscannedObjectsAllPlans" : 4470768,
"nscannedAllPlans" : 4470768,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 29754,
"nChunkSkips" : 0,
"millis" : 16086,
"indexBounds" :

{ "site" : [ [ "506f0fea7cbdc70464aea9e9", "506f0fea7cbdc70464aea9e9" ] ] }

,
"server" : "vizvid-mongo-1:27019"
}
]
},
"cursor" : "BtreeCursor site_1",
"n" : 3413,
"nChunkSkips" : 0,
"nYields" : 29754,
"nscanned" : 4470768,
"nscannedAllPlans" : 4470768,
"nscannedObjects" : 4470768,
"nscannedObjectsAllPlans" : 4470768,
"millisShardTotal" : 16086,
"millisShardAvg" : 16086,
"millis" : 16086,
"indexBounds" :

{ "site" : [ [ "506f0fea7cbdc70464aea9e9", "506f0fea7cbdc70464aea9e9" ] ] }

,
"server" : "vizvid-mongo-1:27019"
}
]
},
"cursor" : "BtreeCursor site_1",
"n" : 3413,
"nChunkSkips" : 0,
"nYields" : 29754,
"nscanned" : 4470768,
"nscannedAllPlans" : 4470768,
"nscannedObjects" : 4470768,
"nscannedObjectsAllPlans" : 4470768,
"millisShardTotal" : 16086,
"millisShardAvg" : 16086,
"numQueries" : 1,
"numShards" : 1,
"indexBounds" :

{ "site" : [ [ "506f0fea7cbdc70464aea9e9", "506f0fea7cbdc70464aea9e9" ] ] }

,
"millis" : 16087
}
###########################
.... Now with the hint ....
###########################
mongos> db.current_views.find({ ts:

{ $gte: 1352300700, $lt: 1352301000 }

, site: "506f0fea7cbdc70464aea9e9" } ).hint(

{ts:1, site:1}

).explain()
{
"clusteredType" : "ParallelSort",
"shards" : {
"vizvid-mongo-1:27019" : [
{
"cursor" : "BtreeCursor ts_1_site_1",
"isMultiKey" : false,
"n" : 3413,
"nscannedObjects" : 3413,
"nscanned" : 3472,
"nscannedObjectsAllPlans" : 3413,
"nscannedAllPlans" : 3472,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 1,
"nChunkSkips" : 0,
"millis" : 27,
"indexBounds" :

{ "ts" : [ [ 1352300700, 1352301000 ] ], "site" : [ [ "506f0fea7cbdc70464aea9e9", "506f0fea7cbdc70464aea9e9" ] ] }

,
"server" : "vizvid-mongo-1:27019"
}
]
},
"cursor" : "BtreeCursor ts_1_site_1",
"n" : 3413,
"nChunkSkips" : 0,
"nYields" : 1,
"nscanned" : 3472,
"nscannedAllPlans" : 3472,
"nscannedObjects" : 3413,
"nscannedObjectsAllPlans" : 3413,
"millisShardTotal" : 27,
"millisShardAvg" : 27,
"numQueries" : 1,
"numShards" : 1,
"indexBounds" :

{ "ts" : [ [ 1352300700, 1352301000 ] ], "site" : [ [ "506f0fea7cbdc70464aea9e9", "506f0fea7cbdc70464aea9e9" ] ] }

,
"millis" : 29
}



 Comments   
Comment by Aaron Staple [ 14/Dec/12 ]

Hi Esteban - Haven't heard from you recently so I'm going to close the ticket. Feel free to reopen for further assistance.

Comment by Esteban Feldman [ 12/Nov/12 ]

Hi Aaron, thanks for taking time for this.

What about hinting an aggregation? Because all this came up with slow aggregation, were I had to explain() the $match parts in a find cause aggregation doesn't have explain though.

Comment by Aaron Staple [ 12/Nov/12 ]

Hi Esteban - explain() does not used cached ("learned") query plans, it always tries all query plans. Additionally hinted plans are not learned. So the issue should be reproducible if your data is unchanged. (If your data has changed, however, it could easily change the query optimizer's query plan selection behavior.)

It's worth noting that it would be possible to get the behavior you described if the

{ site:1 }

index was more selective than the

{ ts:1, site:1 }

index over the first 101 matches. The query optimizer uses the selectivity of the first 101 matches to estimate selectivity overall, but this is not guaranteed to be an accurate estimate over the entire query. If you are able to run explain( true ) we can verify if this is the cause of the behavior you saw.

If you want to ensure the

{ ts:1, site:1 }

index is always chosen, you can hint that index.

Comment by Esteban Feldman [ 08/Nov/12 ]

@Aaron, Can't reproduce now since after the hint it learned that and its using it. Will post when I get more data.

Comment by Aaron Staple [ 07/Nov/12 ]

Hi Esteban - Can you send explain output for the same queries, but provide a true argument to explain: find( ... ).explain( true )

Generated at Thu Feb 08 03:14:57 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.