Why compound wildcard index still do fetch?

Working with Data
, , ,
7

Hi @steevej ,

After changed to "attributes.country" : { "$gte" : "AA" , "$lte" : "ZZ" }, no fetch anymore.
Awesome!

But the $group will contain more fields, for example:

[
  {
    $match: {
      year: 2024,
      month: 8,
      day: 1,
      "attributes.country": {$gte: 'AA', $lte: 'ZZ'},
      "attributes.lang": {$gte: 'aa', $lte: 'zz'}
    }
  },
  {
    $group: {
      _id: {
        appId: "$appId",
        os: "$os",
        country: "$attributes.country",
        lang: "$attributes.lang"
      },
      value: {
        $sum: 1
      }
    }
  }
]

This still uses fetch:

{
 "stage": "FETCH",
 "planNodeId": 2,
 "nReturned": 178774,
 "executionTimeMillisEstimate": 1783,
 "opens": 1,
 "closes": 1,
 "saveState": 696,
 "restoreState": 696,
 "isEOF": 1,
 "numTested": 180895,
 "filter": {
  "$and": [
   {
    "attributes.country": {
     "$lte": "ZZ"
    }
   },
   {
    "attributes.country": {
     "$gte": "AA"
    }
   },
   {
    "attributes.lang": {
     "$lte": "zz"
    }
   },
   {
    "attributes.lang": {
     "$gte": "aa"
    }
   }
  ]
 },
 "docsExamined": 180895,
 "keysExamined": 0
}

I tried "attributes.lang": 'en', but it’s not working.
Is this caused by

The query predicate specifies exactly one field covered by the wildcard index.

from https://www.mongodb.com/docs/manual/core/indexes/index-types/index-wildcard/#covered-queries, or other reasons ?

I also tried below, but still no luck:

[
  {
    $match: {
      year: 2024,
      month: 8,
      day: 1,
      "attributes.country": {$gte: 'AA', $lte: 'ZZ'},
    }
  },
  {
    $group: {
      _id: {
        appId: "$appId",
        os: "$os",
        country: "$attributes.country",
        lang: "$attributes.lang"
      },
      value: {
        $sum: 1
      }
    }
  }
]

{
 "stage": "FETCH",
 "planNodeId": 2,
 "nReturned": 180858,
 "executionTimeMillisEstimate": 716,
 "opens": 1,
 "closes": 1,
 "saveState": 181,
 "restoreState": 181,
 "isEOF": 1,
 "totalDocsExamined": 180858,
 "totalKeysExamined": 182916,
 "collectionScans": 0,
 "collectionSeeks": 180858,
 "indexScans": 0,
 "indexSeeks": 1,
 "indexesUsed": [
  "compound_wildcard_test",
  "compound_wildcard_test"
 ],
 "innerOpens": 180858,
 "innerCloses": 1,
 "outerProjects": [],
 "outerCorrelated": [
  20,
  21,
  22,
  23,
  24
 ],
 "docsExamined": 180858,
 "keysExamined": 0
}

Please let me know if any hints.
Thank you very much!

1 Like