[SERVER-21471] Bad index selection on aggregate with $match + $sort Created: 16/Nov/15 Updated: 18/Oct/16 Resolved: 20/Nov/15 |
|
| Status: | Closed |
| Project: | Core Server |
| Component/s: | Aggregation Framework |
| Affects Version/s: | None |
| Fix Version/s: | None |
| Type: | Bug | Priority: | Major - P3 |
| Reporter: | Mark Zealey | Assignee: | Charlie Swanson |
| Resolution: | Duplicate | Votes: | 0 |
| Labels: | None | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Issue Links: |
|
||||||||||||||||
| Operating System: | ALL | ||||||||||||||||
| Participants: | |||||||||||||||||
| Description |
|
I have a collection with 2m records. unique index on _id and foo columns
takes about 5 seconds to return;
returns instantly. An explain shows that the first one chooses to use the _id index for some reason therefore causing a whole table scan. I seem to recall this also being an issue back in mongo 2.4 but is still there in mongo 3 |
| Comments |
| Comment by Charlie Swanson [ 20/Nov/15 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Actually, I've found an issue already filed, which this duplicates, so I will close this as a duplicate. Please watch the other ticket for updates. Thanks! As for hinting support, see | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Mark Zealey [ 20/Nov/15 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Perhaps if this is tricky to fix you could add a way of specifying an index hint to a pipeline, or a pipeline operator that would do as you suggest with $project but basically stop the optimizer from looking at future stages after it? | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Mark Zealey [ 20/Nov/15 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Yup the equivalent find() pipeline works just fine; it's just on the aggregation pipeline that we have this issue. Thanks, Mark | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Charlie Swanson [ 20/Nov/15 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Thanks for the report mark.zealey. This is a known issue, and is an artifact of the way the aggregation system integrates with the query system. Currently the aggregation system will always prefer plans that do not have a blocking sort stage, if there are any available. I'm fairly confident this is what's happening, but can you also provide the following additional information, just to be sure:
If this is indeed the problem, there are a couple options I can think of to work around it:
That's about all I can think of for now, but there may be other workarounds. If you confirm this is what's happening, I can convert this ticket into a feature request to better optimize in situations like this. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Mark Zealey [ 20/Nov/15 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
This is still an issue in your 3.2 apt repository build at least: db version v3.2.0-rc3-75-g5b3257d
with the sort being on id or the sort being removed, this changes to a direct btree index search:
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Charlie Swanson [ 16/Nov/15 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Hi Mark, If you wouldn't mind, could you try reproducing this on a 3.2 release candidate? I'm not sure, but I believe |