[SERVER-22741] Ability to hint index intersection Created: 18/Feb/16 Updated: 14/Nov/23 |
|
| Status: | Backlog |
| Project: | Core Server |
| Component/s: | Querying |
| Affects Version/s: | None |
| Fix Version/s: | None |
| Type: | Improvement | Priority: | Major - P3 |
| Reporter: | Alexander Komyagin | Assignee: | Backlog - Query Optimization |
| Resolution: | Unresolved | Votes: | 17 |
| Labels: | None | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Issue Links: |
|
||||||||||||||||
| Assigned Teams: |
Query Optimization
|
||||||||||||||||
| Participants: | |||||||||||||||||
| Case: | (copied to CRM) | ||||||||||||||||
| Description |
|
Currently it's very hard to trigger index intersection plans, however, if the user knows that it should be beneficial, they should be able to "hint" that to the query optimizer. |
| Comments |
| Comment by Nuri Halperin [ 03/Oct/23 ] | |
|
I would vote for this feature, since intersection has the potential to be a better plan for some workloads. | |
| Comment by Timour Katchaounov [ 19/Apr/21 ] | |
|
This feature should be part of a wider effort to implement optimizer hints that allow the enforcement of any plan that is expressible in terms of the execution engine (both classic and SBE). This ability is important both for testing and solving customer performance problems. My point being that we shouldn't address each case without having a general plan. | |
| Comment by Sven Maiwald [ 28/Apr/20 ] | |
|
I would like to vote for this feature, because in our use case the intersection of indices would result in a highly selective behavior when combining two single-field indices with low cardinality. How about this syntax, to combine index on field a forward and index on field b backward combined.
I don't know if it is possible to self-intersect a wildcard index. But this would bring the overall performance and admistrative gain when running ad-hoc queries on a stable data model. | |
| Comment by Vyacheslav Stroy [ 06/Apr/20 ] | |
|
I experimented quite a lot with different setups and MongoDB versions, and I never ever witnessed a situation when query planner chooses an index intersection plan. Small collections with a handful of entries, huge collections with a few billions of documents, different Mongo versions, low end servers or servers with 512GB RAM – a plan containing the AND_SORTED stage is always rejected, no matter what. The best what I can hope for is the IXSCAN on one of the fields + filtered FETCH plan. Which of course may completely kill a database when the fetch stage has to reject millions of entries. Are there any plans to implement this feature? Or maybe fix the query planner? |