[SERVER-7944] add index hint support for operations that read indexes Created: 14/Dec/12 Updated: 06/Dec/22 Resolved: 17/Jan/18 |
|
| Status: | Closed |
| Project: | Core Server |
| Component/s: | None |
| Affects Version/s: | None |
| Fix Version/s: | None |
| Type: | Improvement | Priority: | Major - P3 |
| Reporter: | Aaron Staple | Assignee: | Backlog - Query Team (Inactive) |
| Resolution: | Done | Votes: | 63 |
| Labels: | usability | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Issue Links: |
|
||||||||||||||||||||||||||||||||||||
| Assigned Teams: |
Query
|
||||||||||||||||||||||||||||||||||||
| Participants: | |||||||||||||||||||||||||||||||||||||
| Comments |
| Comment by Jason R. Coombs [ 17/Jan/18 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Asya Kamsky [ 17/Jan/18 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Since hint use has been added for aggregation and other tickets are tracking hint use with update, distinct and map-reduce, I'm closing this ticket as done. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Asya Kamsky [ 13/Apr/17 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
This ticket is now tracking other commands which use indexes and don't have hint support. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Hoyt Ren [ 07/Dec/15 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
I believe here is another issue relative Could we rise the priority of this issue. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Nate Smith [ 27/May/15 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Hints on aggregations would be extremely helpful for optimizing our reporting feature. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Elad Moshe [ 13/Jan/15 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Thank you asya, indeed quick benchmarking indicates that the real performance is identical with and without the hint. I wasn't aware of explain not using cached indexes, although it definitely makes sense. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Asya Kamsky [ 13/Jan/15 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
elad.moshe@capriza.com Aggregation doesn't do anything different than regular query - and regular query should not be scanning available plans every time - note that explain() is a special case and it does not use cached indexes, so the output you pasted does not indicate that without the hint the query would run longer than with the hint - if this is the cached index, then aggregation $match will use it and it will be as efficient as a regular query that doesn't need to scan all plans. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Elad Moshe [ 13/Jan/15 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
This has to get higher priority IMHO. ------
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Asya Kamsky [ 16/Jul/14 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
I'm not aware of pymongp lagging in any features. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Abhishek Vaid [ 14/Jul/14 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Can someone tell me what's the correct way (syntactically) to provide hint in pymongo (for an aggregation query)? Pymongo seems to be lagging behind most of 2.6+ updates done. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Luiz Felipe Mendes [ 29/May/14 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
asya , we are using match at the beginning but we are not using sort. Mongodb seems to be trying a lot of plans (indexes) and choosing a less optimal one. I'm studying about "Query Plans" and planCacheFilter. My idea is to use http://docs.mongodb.org/manual/reference/command/planCacheSetFilter/#dbcmd.planCacheSetFilter to "force" mongodb to only look to the optimal index. Is it okay to do this? | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Asya Kamsky [ 29/May/14 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
luiz.mendes@zahpee.com does your aggregation start with $match and/or $sort that have a choice of more than one index and it's picking the less optimal one? It would be helpful to know what the scenario is (and what version you are on, as there may be possible work-arounds in different versions). | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Luiz Felipe Mendes [ 29/May/14 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
I really need this. =( | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Asya Kamsky [ 08/Apr/14 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
As it turns out, the issue wasn't the sort, it was returning more than 16MB from shards to mongos. Not directly index or $hint related, and at least that issue is resolved by 2.6.0. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Jon Hyman [ 08/Apr/14 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Asya, I'll follow up with you on email separately. It was all over indexed fields, I definitely would not try to sort on an unindexed field over this many documents. The pipeline had a match on A, a sort on B, and then a group. The index is {A: 1, B: 1}. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Asya Kamsky [ 08/Apr/14 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Note that in 2.6.0 you can use explain feature to see which index would be used - it does not actually run the aggregation, so you can quickly iterate over possible pipelines till you get the explain you like. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Asya Kamsky [ 08/Apr/14 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Actually, jonhyman that error indicates that you didn't have an index to support the $sort - I was specifically suggesting adding $sort that has an available index, in which case it would not be using in memory sorting and would not exceed RAM limitation. If you provide some more details (maybe on google groups rather than here) we can see why the correct (or intended) index was not used. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Jon Hyman [ 07/Apr/14 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
You should be careful when using $sort trick this if you're aggregating over a lot of data. We aggregate over hundreds of thousands of rows, and we just tried implementing it and spectacularly failed due to "exception: terminating request: request heap use exceeded 10% of physical RAM" As such, we'd still really love to be able to $hint the index in the aggregation framework. We've had to do a lot of coding around this but still get bit by this and it cause cause queries to take 10x as long. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Mirodil Kh [X] [ 08/Jan/14 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
very useful feature if you have many indexes | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Asya Kamsky [ 20/Nov/13 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
See http://docs.mongodb.org/manual/core/aggregation-pipeline/#early-filtering second paragraph. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Nic Cottrell (Personal) [ 20/Nov/13 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Interesting! The experts always say do a $match as the first step in any aggregation call, but I haven't seen in the docs that a subsequent sort is combined and sent as a single step to the shards. Will have to try this, but should be a great solution that we can implement immediately! | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Asya Kamsky [ 20/Nov/13 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
One possible way to "hint" an index would be to add a sort that would be supported by the index you want to use. Example: you are matching on a and b and optimizer is choosing index on a, c instead of one on b, d. If you add $sort: { d:1 }immediately before or after $match (doesn't matter which as they are combined) you will force the optimizer to use b,d index to filter and sort. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Nic Cottrell (Personal) [ 20/Nov/13 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Any chance this can be added into 2.5? Or perhaps you clever chaps at Mongodb have an ever better idea how to achieve the same result? | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Jon Hyman [ 19/Nov/13 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
We just got bitten by this. The match query takes 110 seconds in an explain(). If I hint() it, it drops down to 2 seconds which in my app is acceptable. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Yuri Finkelstein [ 28/Oct/13 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
How is this a P4 feature? It's a P2 at least. Without it it's scary to use a secondary index in $match queries. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Nic Cottrell (Personal) [ 27/Aug/13 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
I'd love this too, but I guess I can do a workaround where I do two separate queries. The first is only on the fields in index I work to 'virtual' force that index, then match again with the other fields. Right now, the query optimizer doesn't seem to work and it's using a very bad index in the matcher | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Piyush Verma [ 27/Aug/13 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
+1 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Andrew T [ 06/Jul/13 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Please add support for this. I can think of so many cases for critical queries that you would want to force mongo to use a certain index. I am currently dealing with this headache and a simple index hint would alleviate the entire problem. This doesn't even seem like a difficult thing to do. Add a new operator called "$hint" and usage would go as such aggregate([{$match:{"x":1,$hint:"x_1"}}]) | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Robert Dickinson [ 20/Jun/13 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
You can add me to the list of people who need this. I have an aggregation operation that is critical to my application. It currently chooses a horribly inefficient index to use because that index happens to be more selective over the first 101 entries than the index custom-designed for exactly this query (as verified using find/explain). The "correct" index would result in approximately 12,000 documents scanned. The index that aggregate chooses does essentially the equivalent of a full table scan of 350,000 documents. The performance is actually significantly worse than even that would indicate, because there are a number of clauses in a top level $or operation. In the "correct" index case, it scans a total of 12,000 document across all clauses in the $or. It the degenerate case that aggregate uses, it scans 350,000 documents for EACH clause in the $or, which can add up to tens of millions of document scans. So anyway, this is an operation that should complete in around 50ms, but instead takes 30-60 seconds or more, and is currently making my app unusable. I'm investigating a workaround using map-reduce or a bunch of application logic to combine the results of a bunch of separate (hinted) count operations. It's not clear whether either of those options is workable. Based on my performance tuning, I've come to the conclusion that you have to hint every significant query in order to ensure decent performance with Mongo at scale, and not being able to do this for aggregate essentially means you can't use aggregation with Mongo if you care about performance at scale in a complex app (I mean, it might work if you get lucky with the index picking genie, but that's no way to plan your development). |