Unexpected index selection behavior - order of indexes matters?

[Daniel_Pleshkov]

Hello,
In the process of diagnosing query slowness I detected that the wrong index is being used.
Hinting towards the right index yielded the wanted performance, Trying building different variations of the wanted index had no impact on the index selection.
As a temporary attempt - I have deleted the ‘wrong’ index and then the index was selected correctly. What happened next had left me confused, as when I restored the ‘wrong’ index, the ‘right’ index is still selected.

I am familiar with the fundamental concept in MongoDB that fields order within an index affects index selection, query planning and execution, but I cannot find any mention to that order of indexes affects index selection - which is my conclusion from my observation in this case.

Is this a documented behavior of MongoDB? Is it a bug? I will be grateful for any help.

Some additional info

• MongoDB Version 4.4.18
• Both indexes, ‘wrong’ and ‘right’ are compound
• The query is not very basic and contains some branching, something like this:

{$and: [ 
    {
        $and: [...], $or: [...]
    },
    {
        $or: [
            {
                a:{$in: [...]}, 
                b:{$in: [...]}
            }
            //.... etc
       ]}
]}

[kevinadi (Kevin Adistambha)]

Hi @Daniel_Pleshkov welcome to the community!

As you’ve discovered, it is entirely possible for the query planner to chosse a suboptimal index. This could be the case when two or more indexes can satisfy the query, and the planner can’t tell for sure which one will be more performant than others since superficially they look similar. See my answer on StackOverflow for details on this.

Once an index is selected, it will be cached. What you’re seeing is the effect of this cache. Removing and readding the index basically invalidates the cache. Note that you can manually flush the plan cache as well. See Query Plan Cache Methods.

To recap, either remove the unecessary index, or use hint() if you know something that the query planner doesn’t

Best regards
Kevin