Compound Wildcard Index bounds reverts to [MinKey, MaxKey] when filter contains fields outside CWI

XMLWordPrintableJSON

    • Type: Improvement
    • Resolution: Unresolved
    • Priority: Major - P3
    • None
    • Affects Version/s: 7.0.1, 8.0.0-rc0
    • Component/s: None
    • Query Optimization
    • None
    • None
    • None
    • None
    • None
    • None
    • None

      I'm observing a weird behaviour with CWI's, where queries containing filters beyond those covered by the CWI, results in the `indexBounds` to revert to '[MinKey, MaxKey]`.

      Say I have CWI:

      db.cwi.createIndex({ TenantId: 1, 'CustomFields.$**': 1, Date: 1 })

      If I query only using fields contained in the CWI:

      db.cwi.explain().find({
    TenantId: 1,
    'CustomFields.Prop': 2,
    Date: { $gte: ISODate('2023-01-01'), $lte: ISODate('2024-01-01') },
})

      I get this winning query plan:

      {
  "stage": "FETCH",
  "planNodeId": new NumberInt("2"),
  "inputStage": {
    "stage": "IXSCAN",
    "planNodeId": new NumberInt("1"),
    "keyPattern": {
      "TenantId": new NumberInt("1"),
      "$_path": new NumberInt("1"),
      "CustomFields.Prop": new NumberInt("1"),
      "Date": new NumberInt("1")
    },
    "indexName": "TenantId_1_CustomFields.$**_1_Date_1",
    "isMultiKey": false,
    "multiKeyPaths": {
      "TenantId": [
      ],
      "$_path": [
      ],
      "CustomFields.Prop": [
      ],
      "Date": [
      ]
    },
    "isUnique": false,
    "isSparse": true,
    "isPartial": false,
    "indexVersion": new NumberInt("2"),
    "direction": "forward",
    "indexBounds": {
      "TenantId": [
        "[1, 1]"
      ],
      "$_path": [
        "[\"CustomFields.Prop\", \"CustomFields.Prop\"]"
      ],
      "CustomFields.Prop": [
        "[2, 2]"
      ],
      "Date": [
        "[new Date(1672531200000), new Date(1704067200000)]"
      ]
    }
  }
}

      But if I add an extra criteria, say `

      { Foo: 'Bar' }

      `:

      db.cwi.explain().find({
    TenantId: 1,
    'CustomFields.Prop': 2,
    Date: { $gte: ISODate('2023-01-01'), $lte: ISODate('2024-01-01') },
    Foo: 'Bar'
})

      I suddenly get a query plan where the index bounds on `CustomFields.Prop` and `Date` are both `[MinKey, MaxKey]`:

      {
  "stage": "FETCH",
  "planNodeId": new NumberInt("2"),
  "filter": {
    "$and": [
      {
        "CustomFields.Prop": {
          "$eq": new NumberInt("2")
        }
      },
      {
        "Date": {
          "$lte": new ISODate("2024-01-01T00:00:00.000Z")
        }
      },
      {
        "Date": {
          "$gte": new ISODate("2023-01-01T00:00:00.000Z")
        }
      },
      {
        "Foo": {
          "$eq": "Bar"
        }
      }
    ]
  },
  "inputStage": {
    "stage": "IXSCAN",
    "planNodeId": new NumberInt("1"),
    "keyPattern": {
      "TenantId": new NumberInt("1"),
      "$_path": new NumberInt("1"),
      "Date": new NumberInt("1")
    },
    "indexName": "TenantId_1_CustomFields.$**_1_Date_1",
    "isMultiKey": false,
    "multiKeyPaths": {
      "TenantId": [
      ],
      "$_path": [
      ],
      "Date": [
      ]
    },
    "isUnique": false,
    "isSparse": true,
    "isPartial": false,
    "indexVersion": new NumberInt("2"),
    "direction": "forward",
    "indexBounds": {
      "TenantId": [
        "[1, 1]"
      ],
      "$_path": [
        "[MinKey, MaxKey]"
      ],
      "Date": [
        "[MinKey, MaxKey]"
      ]
    }
  }
}

      Note the index bounds and the `filter` on the outer fetch stage which is suddenly doing the work of filtering on `CustomFields.Prop` and `Date`.

      I see no reason why this is the case, an optimal query plan would be as before with the CWI filtering by all fields it covers, and then only a filter on the fetch stag on `

      { Foo: 'Bar' }

      `.

        1. query_base.json
          101 kB
        2. query_with_extra_field.json
          52 kB
        3. query_with_hint.json
          5 kB
        4. repro.js
          0.6 kB

            Assignee:
            Unassigned
            Reporter:
            Michael Dahl
            Votes:
            1 Vote for this issue
            Watchers:
            23 Start watching this issue

              Created:
              Updated: