Attribute Pattern query condition order leading to vastly different run times

Hello,

I have a MongoDB collection implementing the attribute pattern, which works well most of the time.
However it seems that the order of conditions in the query can lead to slow queries, depending on how the data is distributed.

Given this (simplified) data structure

{
   filterAttributes: [
   {
      k: "booleanAttribute",
      v: true,
   },
   {
      k: "idAttribute",
      v: 123,
   },
   ...
 ]
 ...
}

and index

{
   "filterAttributes.k": 1,
   "filterAttributes.v": 1,
}

query1

{
   $and: [ 
        { filterAttributes: { $elemMatch: { k: "booleanAttribute", v: true } }},
        { filterAttributes: { $elemMatch: { k: "idAttribute", v: 123 } }}
    ]
}    

query2

{
   $and: [
        { filterAttributes: { $elemMatch: { k: "idAttribute", v: 123 } }},
        { filterAttributes: { $elemMatch: { k: "booleanAttribute", v: true } }}
    ]
}    

If the collection has a large amount of true booleanAttribute values but low amount of 123 idAttribute values, then query1 will be magnitudes slower than query2.

It seems that MongoDB can not use an index intersection to use the more selective attribute.

Is there a better way to query the data structure here?

2 Likes