Why is indexFilterSet: false in the query planner when it appears to plan based on the index filter?

Working with Data
, , ,
1

I have configured the following index filter on my database according to the docs

db.command({
  'planCacheSetFilter': 'myobject',
  'query': {'slug': 'A', 'account_id': 'A'},
  'indexes': [slug_account'],
})

running the following query:

query_plan = collection.find({'slug': 'test', 'account_id': 'bob' }).explain()

will give the following query plan:

{
  "queryPlanner": {
    "mongosPlannerVersion": 1,
    "winningPlan": {
      "stage": "SHARD_MERGE",
      "shards": [
        {
          "shardName": "shard1",
          "connectionString": "shard1/127.0.0.1:27030",
          "serverInfo": {
            "host": "redacted",
            "port": 27030,
            "version": "4.4.13",
            "gitVersion": "df25c71b8674a78e17468f48bcda5285decb9246"
          },
          "plannerVersion": 1,
          "namespace": "redacted.myobject",
          "indexFilterSet": false,
          "parsedQuery": {
            "$and": [
              {
                "account_id": {
                  "$eq": 1
                }
              },
              {
                "slug": {
                  "$eq": "test"
                }
              }
            ]
          },
          "winningPlan": {
            "stage": "SHARDING_FILTER",
            "inputStage": {
              "stage": "FETCH",
              "inputStage": {
                "stage": "IXSCAN",
                "keyPattern": {
                  "slug": 1,
                  "account_id": 1,
                },
                "indexName": "slug_account",
                "isMultiKey": true,
                "multiKeyPaths": {
                  "slug": [],
                  "account_id": []
                },
                "isUnique": false,
                "isSparse": false,
                "isPartial": false,
                "indexVersion": 2,
                "direction": "forward",
                "indexBounds": {
                  "crash_type_slug": [
                    "[\"test\", \"test\"]"
                  ],
                  "account_id": [
                    "[\"bob\", \"bob\"]"
                  ]
                }
              }
            }
          },
          "rejectedPlans": []
        },
        {
          "shardName": "shard2",
          "connectionString": "shard2/127.0.0.1:27031",
          "serverInfo": {
            "host": "redacted",
            "port": 27031,
            "version": "4.4.13",
            "gitVersion": "df25c71b8674a78e17468f48bcda5285decb9246"
          },
          "plannerVersion": 1,
          "namespace": "redacted.myobject",
          "indexFilterSet": false,
          "parsedQuery": {
            "$and": [
              {
                "account_id": {
                  "$eq": 1
                }
              },
              {
                "slug": {
                  "$eq": "pc-crashes"
                }
              }
            ]
          },
          "winningPlan": {
            "stage": "SHARDING_FILTER",
            "inputStage": {
              "stage": "FETCH",
              "inputStage": {
                "stage": "IXSCAN",
                "keyPattern": {
                  "slug": 1,
                  "account_id": 1
                },
                "indexName": "slug_account",
                "isMultiKey": true,
                "multiKeyPaths": {
                  "crash_type_slug": [],
                  "account_id": []
                },
                "isUnique": false,
                "isSparse": false,
                "isPartial": false,
                "indexVersion": 2,
                "direction": "forward",
                "indexBounds": {
                  "slug": [
                    "[\"test\", \"test\"]"
                  ],
                  "account_id": [
                    "[\"bob\", \"bob\"]"
                  ]
                }
              }
            }
          },
          "rejectedPlans": []
        }
      ]
    }
  },
  "executionStats": {
    "nReturned": 0,
    "executionTimeMillis": 0,
    "totalKeysExamined": 0,
    "totalDocsExamined": 0,
    "executionStages": {
      "stage": "SHARD_MERGE",
      "nReturned": 0,
      "executionTimeMillis": 0,
      "totalKeysExamined": 0,
      "totalDocsExamined": 0,
      "totalChildMillis": 0,
      "shards": [
        {
          "shardName": "shard1",
          "executionSuccess": true,
          "nReturned": 0,
          "executionTimeMillis": 0,
          "totalKeysExamined": 0,
          "totalDocsExamined": 0,
          "executionStages": {
            "stage": "SHARDING_FILTER",
            "nReturned": 0,
            "executionTimeMillisEstimate": 0,
            "works": 1,
            "advanced": 0,
            "needTime": 0,
            "needYield": 0,
            "saveState": 0,
            "restoreState": 0,
            "isEOF": 1,
            "chunkSkips": 0,
            "inputStage": {
              "stage": "FETCH",
              "nReturned": 0,
              "executionTimeMillisEstimate": 0,
              "works": 1,
              "advanced": 0,
              "needTime": 0,
              "needYield": 0,
              "saveState": 0,
              "restoreState": 0,
              "isEOF": 1,
              "docsExamined": 0,
              "alreadyHasObj": 0,
              "inputStage": {
                "stage": "IXSCAN",
                "nReturned": 0,
                "executionTimeMillisEstimate": 0,
                "works": 1,
                "advanced": 0,
                "needTime": 0,
                "needYield": 0,
                "saveState": 0,
                "restoreState": 0,
                "isEOF": 1,
                "keyPattern": {
                  "slug": 1,
                  "account_id": 1
                },
                "indexName": "slug_account",
                "isMultiKey": true,
                "multiKeyPaths": {
                  "slug": [],
                  "account_id": []
                },
                "isUnique": false,
                "isSparse": false,
                "isPartial": false,
                "indexVersion": 2,
                "direction": "forward",
                "indexBounds": {
                  "slug": [
                    "[\"test\", \"test\"]"
                  ],
                  "account_id": [
                    "[\"bob\", \"bob\"]"
                  ]
                },
                "keysExamined": 0,
                "seeks": 1,
                "dupsTested": 0,
                "dupsDropped": 0
              }
            }
          },
          "allPlansExecution": []
        },
        {
          "shardName": "shard2",
          "executionSuccess": true,
          "nReturned": 0,
          "executionTimeMillis": 0,
          "totalKeysExamined": 0,
          "totalDocsExamined": 0,
          "executionStages": {
            "stage": "SHARDING_FILTER",
            "nReturned": 0,
            "executionTimeMillisEstimate": 0,
            "works": 1,
            "advanced": 0,
            "needTime": 0,
            "needYield": 0,
            "saveState": 0,
            "restoreState": 0,
            "isEOF": 1,
            "chunkSkips": 0,
            "inputStage": {
              "stage": "FETCH",
              "nReturned": 0,
              "executionTimeMillisEstimate": 0,
              "works": 1,
              "advanced": 0,
              "needTime": 0,
              "needYield": 0,
              "saveState": 0,
              "restoreState": 0,
              "isEOF": 1,
              "docsExamined": 0,
              "alreadyHasObj": 0,
              "inputStage": {
                "stage": "IXSCAN",
                "nReturned": 0,
                "executionTimeMillisEstimate": 0,
                "works": 1,
                "advanced": 0,
                "needTime": 0,
                "needYield": 0,
                "saveState": 0,
                "restoreState": 0,
                "isEOF": 1,
                "keyPattern": {
                  "slug": 1,
                  "account_id": 1
                },
                "indexName": "slug_account",
                "isMultiKey": true,
                "multiKeyPaths": {
                  "slug": [],
                  "account_id": []
                },
                "isUnique": false,
                "isSparse": false,
                "isPartial": false,
                "indexVersion": 2,
                "direction": "forward",
                "indexBounds": {
                  "slug": [
                    "[\"test\", \"test\"]"
                  ],
                  "account_id": [
                    "[\"bob\", \"bob\"]"
                  ]
                },
                "keysExamined": 0,
                "seeks": 1,
                "dupsTested": 0,
                "dupsDropped": 0
              }
            }
          },
          "allPlansExecution": []
        }
      ]
    },
    "allPlansExecution": [
      {
        "shardName": "shard1",
        "allPlans": []
      },
      {
        "shardName": "shard2",
        "allPlans": []
      }
    ]
  },
  "serverInfo": {
    "host": "redacted",
    "port": 27017,
    "version": "4.4.13",
    "gitVersion": "df25c71b8674a78e17468f48bcda5285decb9246"
  },
  "ok": 1.0,
  "operationTime": "Timestamp(1667249852, 13)",
  "$clusterTime": {
    "clusterTime": "Timestamp(1667249852, 13)",
    "signature": {
      "hash": "b'\\x00\\x00\\x00\\x00\\x00\\x00\\x00\\x00\\x00\\x00\\x00\\x00\\x00\\x00\\x00\\x00\\x00\\x00\\x00\\x00'",
      "keyId": 0
    }
  }
}

which has the correct index picked (out of several) but still shows indexFilterSet to be false. The docs simply state

A boolean that specifies whether MongoDB applied an index filter for the query shape.

and I believe the query shape is correct. One way to verify this is that an aggregate pipeline does show indexFilterSet true for a similar query.

aggregate_pipeline = [
  {'$match': {'slug': 'test', 'account_id': 'bob'}},
  {"$group":{"_id":{"$const":1},"n":{"$sum":{"$const":1}}}},
]
query_plan_on_shards = db.command('aggregate', 'myobject', pipeline=aggregate_pipeline, explain=True)

{
  "serverInfo": {
    "host": "redacted",
    "port": 27017,
    "version": "4.4.13",
    "gitVersion": "df25c71b8674a78e17468f48bcda5285decb9246"
  },
  "mergeType": "mongos",
  "splitPipeline": {
    "shardsPart": [
      {
        "$match": {
          "$and": [
            {
              "crash_type_slug": {
                "$eq": "test"
              }
            },
            {
              "account_id": {
                "$eq": 1
              }
            }
          ]
        }
      },
      {
        "$group": {
          "_id": {
            "$const": 1
          },
          "n": {
            "$sum": {
              "$const": 1
            }
          }
        }
      }
    ],
    "mergerPart": [
      {
        "$group": {
          "_id": "$$ROOT._id",
          "n": {
            "$sum": "$$ROOT.n"
          },
          "$doingMerge": true
        }
      }
    ]
  },
  "shards": {
    "shard1": {
      "host": "127.0.0.1:27030",
      "stages": [
        {
          "$cursor": {
            "queryPlanner": {
              "plannerVersion": 1,
              "namespace": "redacted.myobject",
              "indexFilterSet": true,
              "parsedQuery": {
                "$and": [
                  {
                    "account_id": {
                      "$eq": 1
                    }
                  },
                  {
                    "slug": {
                      "$eq": "test"
                    }
                  }
                ]
              },
              "queryHash": "91AC2316",
              "planCacheKey": "ACBF7BFB",
              "winningPlan": {
                "stage": "SHARDING_FILTER",
                "inputStage": {
                  "stage": "FETCH",
                  "inputStage": {
                    "stage": "IXSCAN",
                    "keyPattern": {
                      "slug": 1,
                      "account_id": 1
                    },
                    "indexName": "slug_account",
                    "isMultiKey": true,
                    "multiKeyPaths": {
                      "slug": [],
                      "account_id": []
                    },
                    "isUnique": false,
                    "isSparse": false,
                    "isPartial": false,
                    "indexVersion": 2,
                    "direction": "forward",
                    "indexBounds": {
                      "crash_type_slug": [
                        "[\"test\", \"test\"]"
                      ],
                      "account_id": [
                        "[\"bob\", \"bob\"]"
                      ],
                      "state": [
                        "[MinKey, MaxKey]"
                      ],
                      "searchable_tags": [
                        "[MinKey, MaxKey]"
                      ],
                      "priority": [
                        "[MaxKey, MinKey]"
                      ],
                      "created_at": [
                        "[MinKey, MaxKey]"
                      ]
                    }
                  }
                }
              },
              "rejectedPlans": []
            }
          }
        },
        {
          "$group": {
            "_id": {
              "$const": 1
            },
            "n": {
              "$sum": {
                "$const": 1
              }
            }
          }
        }
      ]
    },
    "shard2": {
      "host": "127.0.0.1:27031",
      "stages": [
        {
          "$cursor": {
            "queryPlanner": {
              "plannerVersion": 1,
              "namespace": "redacted.myobject",
              "indexFilterSet": true,
              "parsedQuery": {
                "$and": [
                  {
                    "account_id": {
                      "$eq": 1
                    }
                  },
                  {
                    "slug": {
                      "$eq": "test"
                    }
                  }
                ]
              },
              "queryHash": "91AC2316",
              "planCacheKey": "ACBF7BFB",
              "winningPlan": {
                "stage": "SHARDING_FILTER",
                "inputStage": {
                  "stage": "FETCH",
                  "inputStage": {
                    "stage": "IXSCAN",
                    "keyPattern": {
                      "slug": 1,
                      "account_id": 1,
                    },
                    "indexName": "slug_account",
                    "isMultiKey": true,
                    "multiKeyPaths": {
                      "slug": [],
                      "account_id": []
                    },
                    "isUnique": false,
                    "isSparse": false,
                    "isPartial": false,
                    "indexVersion": 2,
                    "direction": "forward",
                    "indexBounds": {
                      "crash_type_slug": [
                        "[\"test\", \"test\"]"
                      ],
                      "account_id": [
                        "[\"bob\", \"bob\"]"
                      ]
                    }
                  }
                }
              },
              "rejectedPlans": []
            }
          }
        },
        {
          "$group": {
            "_id": {
              "$const": 1
            },
            "n": {
              "$sum": {
                "$const": 1
              }
            }
          }
        }
      ]
    }
  },
  "ok": 1.0,
  "operationTime": "Timestamp(1667250309, 13)",
  "$clusterTime": {
    "clusterTime": "Timestamp(1667250309, 13)",
    "signature": {
      "hash": "b'\\x00\\x00\\x00\\x00\\x00\\x00\\x00\\x00\\x00\\x00\\x00\\x00\\x00\\x00\\x00\\x00\\x00\\x00\\x00\\x00'",
      "keyId": 0
    }
  }
}

interestingly, one more piece of the puzzle, is that setting an index filter like so:

db.command({
  'planCacheSetFilter': 'myobject',
  'query': {'slug': 'A', 'account_id': 'A'},
  'indexes': [{'slug_account': 1],
})

(e.g. with {'slug_account': 1} rather than ‘slug_account’) results in a COLLSCAN, despite the aggregate pipeline still returning indexFilterSet: true and using the correct index.

Can someone explain what is going on here? Is the index filter an experimental feature with some unknown edge cases, or am I just setting these up wrong/reading the query planner incorrectly?

3

Hi,
I am facing the very same problem with Mongo 4.4.8 : we have some huge wildcard indexes that I need to keep visible, but want to avoid for analyses of some queries plan calculation.
I take the very same query found in entries given by cache plan

db.getCollection("documents").getPlanCache().list()

map the query shape to the best index

db.runCommand(
   {
      planCacheSetFilter: "documents",
      query: { "UserNr" : "5500"},
      sort: { },
      projection: { },
      indexes: [ {"UserIndexName"} ]
   }
);

and then did some tried with and/or without collation in query shape, with and/or without collation in query… I never ever receive a “indexFilterSet” : true for a query

db.getCollection("documents").find({"UserNr" : "5500"}).explain();

If anyone has managed to obtain, in explain() function, w feedback with “indexFilterSet” : true, I would be very happy to have a hint about what is he doing to achieve this.

Thank’s