[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: |
|
||||||||||||||||
| Operating System: | Linux | ||||||||||||||||
| Participants: | |||||||||||||||||
| Description |
|
mongos> db.current_views.find({ ts: { $gte: 1352300700, $lt: 1352301000 }, site: "506f0fea7cbdc70464aea9e9" } ).explain() , , , , site: "506f0fea7cbdc70464aea9e9" } ).hint( {ts:1, site:1}).explain() , , |
| 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 ) |