Manual Index Intersection

[Igor]

It seems that a lot of people complaining about that mongo is not using index intersections effectively, if at all. As I understand this, the planner is not always selecting the right/most effective plan when intersections can be involved.

Is it possible, and how hard it is, to create a method where we tell the planner exactly what to to regarding intersections just like we do with hints?

For example:

.find({...}).hint("2d_index").intersect("int_field_index").intersect("date_field_index").....

[Stephen Steneker]

On Thursday, 14 December 2017 08:14:41 UTC+11, Igor wrote:

It seems that a lot of people complaining about that mongo is not using index intersections effectively, if at all. As I understand this, the planner is not always selecting the right/most effective plan when intersections can be involved.

Is it possible, and how hard it is, to create a method where we tell the planner exactly what to to regarding intersections just like we do with hints?

Hi Igor,

Index intersection can indeed be difficult for the query optimizer to consistently select as a winning plan, and for common queries I would suggest creating compound indexes where possible. While index intersection sounds conceptually appealing, merging the results from multiple indexes typically involves more work than using a single index. The MongoDB query planner does not currently track detailed metrics on index key distribution which could enable more effective index intersection.

As at MongoDB 3.6 it is not possible to hint index intersection. There is a relevant feature request you can watch/upvote in the MongoDB issue tracker: https://jira.mongodb.org/browse/SERVER-22741.

Regards, Stennie

[Monika Shah]

Hi stephen,

            Can you elaborate " while index intersection sounds conceptually appealing, merging the results from the multiple indexes typically involves more work than using a single index?

            becuase , my understanding says Queries over single point intervals return documents sorted by disk location, then index intersection of two individual index (m keys by index1 and n keys by index2 ) should take maximum(m,n) operations.

           I request you to tell me How can I ensure index intersection hint on my local mongodb server to test nworks?