Index not picked with nested field hierarchy but gets picked in the flatten mode

Working with Data
1

so i am struggling for 2 weeks on why does not my indexes get picked when i “explain” my queries.

i have this query:

{
“$and”: [

{
  "extraProperties.class": "Residential"
},
{
  "extraProperties.type": "Sale"
},
{
  "extraProperties.propertyType": "Condo Apartment"
},
{
  "extraProperties.propertyTypeStyle": "Apartment"
}

]
}

the above query wont pick this index :

{ “extraProperties.class”:1 , “extraProperties.type” : 1, “extraProperties.propertyType”:1,“extraProperties.propertyTypeStyle”:1}

i have been testing everything these days and finally i decided to flatten the hierarchy and now my query looks like this:

{
“$and”: [

{
“class”: “Residential”
},
{
“type”: “Sale”
},
{
“propertyType”: “Condo Apartment”
},
{
“propertyTypeStyle”: “Apartment”
}

]
}

now the above query will pick this index :

{ “class”:1 , “type” : 1, “propertyType”:1,“propertyTypeStyle”:1}

could someone explain what the hell is going on there?!?!

2

Hi @Masoud_Naghizade,

I suspect 2 things that can throw the optimzer off.

  1. A cached plan that will make it choose another index as for nested shape it is considered better.

  2. A more likely reason is that when your data is setting in a nested document array it is a multikey path index. Now this pattern search for documents with corresponding fields but they not neseciraly all need to be in the same subdocument and thus index becomes less efficient.

If you need all of those to be in a single doc use $elemMatch in your query

Thanks
Pavel

3

May be it is the $and operator that fools the query planner. Since there is an explicit and anyway try with the implicit version as follow:

{
  "extraProperties.class": "Residential" ,
  "extraProperties.type": "Sale" ,
  "extraProperties.propertyType": "Condo Apartment" ,
  "extraProperties.propertyTypeStyle": "Apartment"
}