[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:
Related
related to SERVER-83234 Allow users to hint plans using the Q... Needs Scheduling
is related to SERVER-1649 Allows hints when multiple indexes ar... Backlog
is related to SERVER-28587 MongoDB aggregation query slowness Closed
Assigned Teams:
Query Optimization
Participants:
Case:

 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.

collection.find().hint({$intersect: {a: 1, b: -1}})

 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?

Generated at Thu Feb 08 04:01:18 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.