Compound MongoDB index not used for exact same query

Question

I have index like this: businessId_1_accountId_1_dataHash_1_deleted_1

And query like this, does not use it, but uses another one. I can't understand why.

db.getCollection("transactions").find({
  "businessId": ObjectId("62c56bbdba7f1d001368f217"),
  "accountId": ObjectId("62c56bbef2c6530d4a9b5976"),
  "dataHash": "00002478a5c9594923833b3534b2d0b17bb7298a",
  deleted: { $ne: true }
}, { _id: true }).explain();

Here is the explain result

{
    "explainVersion" : "1",
    "queryPlanner" : {
        "namespace" : "stage.transactions",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "$and" : [
                {
                    "accountId" : {
                        "$eq" : ObjectId("62c56bbef2c6530d4a9b5976")
                    }
                },
                {
                    "businessId" : {
                        "$eq" : ObjectId("62c56bbdba7f1d001368f217")
                    }
                },
                {
                    "dataHash" : {
                        "$eq" : "00002478a5c9594923833b3534b2d0b17bb7298a"
                    }
                },
                {
                    "deleted" : {
                        "$not" : {
                            "$eq" : true
                        }
                    }
                }
            ]
        },
        "queryHash" : "29075D21",
        "planCacheKey" : "7C80754C",
        "maxIndexedOrSolutionsReached" : false,
        "maxIndexedAndSolutionsReached" : false,
        "maxScansToExplodeReached" : false,
        "winningPlan" : {
            "stage" : "PROJECTION_SIMPLE",
            "transformBy" : {
                "_id" : true
            },
            "inputStage" : {
                "stage" : "FETCH",
                "filter" : {
                    "$and" : [
                        {
                            "accountId" : {
                                "$eq" : ObjectId("62c56bbef2c6530d4a9b5976")
                            }
                        },
                        {
                            "dataHash" : {
                                "$eq" : "00002478a5c9594923833b3534b2d0b17bb7298a"
                            }
                        }
                    ]
                },
                "inputStage" : {
                    "stage" : "IXSCAN",
                    "keyPattern" : {
                        "businessId" : 1.0,
                        "reason" : 1.0,
                        "deleted" : 1.0
                    },
                    "indexName" : "businessId_1_reason_1_deleted_1",
                    "isMultiKey" : false,
                    "multiKeyPaths" : {
                        "businessId" : [

                        ],
                        "reason" : [

                        ],
                        "deleted" : [

                        ]
                    },
                    "isUnique" : false,
                    "isSparse" : false,
                    "isPartial" : false,
                    "indexVersion" : 2.0,
                    "direction" : "forward",
                    "indexBounds" : {
                        "businessId" : [
                            "[ObjectId('62c56bbdba7f1d001368f217'), ObjectId('62c56bbdba7f1d001368f217')]"
                        ],
                        "reason" : [
                            "[MinKey, MaxKey]"
                        ],
                        "deleted" : [
                            "[MinKey, true)",
                            "(true, MaxKey]"
                        ]
                    }
                }
            }
        },
        "rejectedPlans" : [
            {
                "stage" : "PROJECTION_SIMPLE",
                "transformBy" : {
                    "_id" : true
                },
                "inputStage" : {
                    "stage" : "FETCH",
                    "filter" : {
                        "$and" : [
                            {
                                "accountId" : {
                                    "$eq" : ObjectId("62c56bbef2c6530d4a9b5976")
                                }
                            },
                            {
                                "dataHash" : {
                                    "$eq" : "00002478a5c9594923833b3534b2d0b17bb7298a"
                                }
                            }
                        ]
                    },
                    "inputStage" : {
                        "stage" : "IXSCAN",
                        "keyPattern" : {
                            "businessId" : 1.0,
                            "tags" : 1.0,
                            "deleted" : 1.0
                        },
                        "indexName" : "businessId_1_tags_1_deleted_1",
                        "isMultiKey" : true,
                        "multiKeyPaths" : {
                            "businessId" : [

                            ],
                            "tags" : [
                                "tags"
                            ],
                            "deleted" : [

                            ]
                        },
                        "isUnique" : false,
                        "isSparse" : false,
                        "isPartial" : false,
                        "indexVersion" : 2.0,
                        "direction" : "forward",
                        "indexBounds" : {
                            "businessId" : [
                                "[ObjectId('62c56bbdba7f1d001368f217'), ObjectId('62c56bbdba7f1d001368f217')]"
                            ],
                            "tags" : [
                                "[MinKey, MaxKey]"
                            ],
                            "deleted" : [
                                "[MinKey, true)",
                                "(true, MaxKey]"
                            ]
                        }
                    }
                }
            },
            {
                "stage" : "PROJECTION_SIMPLE",
                "transformBy" : {
                    "_id" : true
                },
                "inputStage" : {
                    "stage" : "FETCH",
                    "filter" : {
                        "$and" : [
                            {
                                "accountId" : {
                                    "$eq" : ObjectId("62c56bbef2c6530d4a9b5976")
                                }
                            },
                            {
                                "dataHash" : {
                                    "$eq" : "00002478a5c9594923833b3534b2d0b17bb7298a"
                                }
                            }
                        ]
                    },
                    "inputStage" : {
                        "stage" : "IXSCAN",
                        "keyPattern" : {
                            "businessId" : 1.0,
                            "documentId" : 1.0,
                            "status" : 1.0,
                            "deleted" : 1.0
                        },
                        "indexName" : "businessId_1_documentId_1_status_1_deleted_1",
                        "isMultiKey" : false,
                        "multiKeyPaths" : {
                            "businessId" : [

                            ],
                            "documentId" : [

                            ],
                            "status" : [

                            ],
                            "deleted" : [

                            ]
                        },
                        "isUnique" : false,
                        "isSparse" : false,
                        "isPartial" : false,
                        "indexVersion" : 2.0,
                        "direction" : "forward",
                        "indexBounds" : {
                            "businessId" : [
                                "[ObjectId('62c56bbdba7f1d001368f217'), ObjectId('62c56bbdba7f1d001368f217')]"
                            ],
                            "documentId" : [
                                "[MinKey, MaxKey]"
                            ],
                            "status" : [
                                "[MinKey, MaxKey]"
                            ],
                            "deleted" : [
                                "[MinKey, true)",
                                "(true, MaxKey]"
                            ]
                        }
                    }
                }
            },
            {
                "stage" : "PROJECTION_SIMPLE",
                "transformBy" : {
                    "_id" : true
                },
                "inputStage" : {
                    "stage" : "FETCH",
                    "filter" : {
                        "$and" : [
                            {
                                "businessId" : {
                                    "$eq" : ObjectId("62c56bbdba7f1d001368f217")
                                }
                            },
                            {
                                "dataHash" : {
                                    "$eq" : "00002478a5c9594923833b3534b2d0b17bb7298a"
                                }
                            }
                        ]
                    },
                    "inputStage" : {
                        "stage" : "IXSCAN",
                        "keyPattern" : {
                            "accountId" : 1.0,
                            "description" : 1.0,
                            "address.name" : 1.0,
                            "deleted" : 1.0
                        },
                        "indexName" : "accountId_1_description_1_address.name_1_deleted_1",
                        "isMultiKey" : false,
                        "multiKeyPaths" : {
                            "accountId" : [

                            ],
                            "description" : [

                            ],
                            "address.name" : [

                            ],
                            "deleted" : [

                            ]
                        },
                        "isUnique" : false,
                        "isSparse" : false,
                        "isPartial" : false,
                        "indexVersion" : 2.0,
                        "direction" : "forward",
                        "indexBounds" : {
                            "accountId" : [
                                "[ObjectId('62c56bbef2c6530d4a9b5976'), ObjectId('62c56bbef2c6530d4a9b5976')]"
                            ],
                            "description" : [
                                "[MinKey, MaxKey]"
                            ],
                            "address.name" : [
                                "[MinKey, MaxKey]"
                            ],
                            "deleted" : [
                                "[MinKey, true)",
                                "(true, MaxKey]"
                            ]
                        }
                    }
                }
            },
            {
                "stage" : "PROJECTION_SIMPLE",
                "transformBy" : {
                    "_id" : true
                },
                "inputStage" : {
                    "stage" : "FETCH",
                    "filter" : {
                        "$and" : [
                            {
                                "businessId" : {
                                    "$eq" : ObjectId("62c56bbdba7f1d001368f217")
                                }
                            },
                            {
                                "dataHash" : {
                                    "$eq" : "00002478a5c9594923833b3534b2d0b17bb7298a"
                                }
                            }
                        ]
                    },
                    "inputStage" : {
                        "stage" : "IXSCAN",
                        "keyPattern" : {
                            "accountId" : 1.0,
                            "timestamp" : 1.0,
                            "amount" : 1.0,
                            "type" : 1.0,
                            "deleted" : 1.0
                        },
                        "indexName" : "accountId_1_timestamp_1_amount_1_type_1_deleted_1",
                        "isMultiKey" : false,
                        "multiKeyPaths" : {
                            "accountId" : [

                            ],
                            "timestamp" : [

                            ],
                            "amount" : [

                            ],
                            "type" : [

                            ],
                            "deleted" : [

                            ]
                        },
                        "isUnique" : false,
                        "isSparse" : false,
                        "isPartial" : false,
                        "indexVersion" : 2.0,
                        "direction" : "forward",
                        "indexBounds" : {
                            "accountId" : [
                                "[ObjectId('62c56bbef2c6530d4a9b5976'), ObjectId('62c56bbef2c6530d4a9b5976')]"
                            ],
                            "timestamp" : [
                                "[MinKey, MaxKey]"
                            ],
                            "amount" : [
                                "[MinKey, MaxKey]"
                            ],
                            "type" : [
                                "[MinKey, MaxKey]"
                            ],
                            "deleted" : [
                                "[MinKey, true)",
                                "(true, MaxKey]"
                            ]
                        }
                    }
                }
            },
            {
                "stage" : "PROJECTION_SIMPLE",
                "transformBy" : {
                    "_id" : true
                },
                "inputStage" : {
                    "stage" : "FETCH",
                    "filter" : {
                        "dataHash" : {
                            "$eq" : "00002478a5c9594923833b3534b2d0b17bb7298a"
                        }
                    },
                    "inputStage" : {
                        "stage" : "IXSCAN",
                        "keyPattern" : {
                            "businessId" : 1.0,
                            "accountId" : 1.0,
                            "deleted" : 1.0
                        },
                        "indexName" : "businessId_1_accountId_1_deleted_1",
                        "isMultiKey" : false,
                        "multiKeyPaths" : {
                            "businessId" : [

                            ],
                            "accountId" : [

                            ],
                            "deleted" : [

                            ]
                        },
                        "isUnique" : false,
                        "isSparse" : false,
                        "isPartial" : false,
                        "indexVersion" : 2.0,
                        "direction" : "forward",
                        "indexBounds" : {
                            "businessId" : [
                                "[ObjectId('62c56bbdba7f1d001368f217'), ObjectId('62c56bbdba7f1d001368f217')]"
                            ],
                            "accountId" : [
                                "[ObjectId('62c56bbef2c6530d4a9b5976'), ObjectId('62c56bbef2c6530d4a9b5976')]"
                            ],
                            "deleted" : [
                                "[MinKey, true)",
                                "(true, MaxKey]"
                            ]
                        }
                    }
                }
            },
            {
                "stage" : "PROJECTION_SIMPLE",
                "transformBy" : {
                    "_id" : true
                },
                "inputStage" : {
                    "stage" : "FETCH",
                    "filter" : {
                        "dataHash" : {
                            "$eq" : "00002478a5c9594923833b3534b2d0b17bb7298a"
                        }
                    },
                    "inputStage" : {
                        "stage" : "IXSCAN",
                        "keyPattern" : {
                            "businessId" : 1.0,
                            "accountId" : 1.0,
                            "vendorId" : 1.0,
                            "category.code" : 1.0,
                            "deleted" : 1.0
                        },
                        "indexName" : "businessId_1_accountId_1_vendorId_1_category.code_1_deleted_1",
                        "isMultiKey" : false,
                        "multiKeyPaths" : {
                            "businessId" : [

                            ],
                            "accountId" : [

                            ],
                            "vendorId" : [

                            ],
                            "category.code" : [

                            ],
                            "deleted" : [

                            ]
                        },
                        "isUnique" : false,
                        "isSparse" : false,
                        "isPartial" : false,
                        "indexVersion" : 2.0,
                        "direction" : "forward",
                        "indexBounds" : {
                            "businessId" : [
                                "[ObjectId('62c56bbdba7f1d001368f217'), ObjectId('62c56bbdba7f1d001368f217')]"
                            ],
                            "accountId" : [
                                "[ObjectId('62c56bbef2c6530d4a9b5976'), ObjectId('62c56bbef2c6530d4a9b5976')]"
                            ],
                            "vendorId" : [
                                "[MinKey, MaxKey]"
                            ],
                            "category.code" : [
                                "[MinKey, MaxKey]"
                            ],
                            "deleted" : [
                                "[MinKey, true)",
                                "(true, MaxKey]"
                            ]
                        }
                    }
                }
            },
            {
                "stage" : "PROJECTION_SIMPLE",
                "transformBy" : {
                    "_id" : true
                },
                "inputStage" : {
                    "stage" : "FETCH",
                    "filter" : {
                        "$and" : [
                            {
                                "accountId" : {
                                    "$eq" : ObjectId("62c56bbef2c6530d4a9b5976")
                                }
                            },
                            {
                                "businessId" : {
                                    "$eq" : ObjectId("62c56bbdba7f1d001368f217")
                                }
                            }
                        ]
                    },
                    "inputStage" : {
                        "stage" : "IXSCAN",
                        "keyPattern" : {
                            "dataHash" : 1.0,
                            "deleted" : 1.0
                        },
                        "indexName" : "dataHash_1_deleted_1",
                        "isMultiKey" : false,
                        "multiKeyPaths" : {
                            "dataHash" : [

                            ],
                            "deleted" : [

                            ]
                        },
                        "isUnique" : false,
                        "isSparse" : false,
                        "isPartial" : false,
                        "indexVersion" : 2.0,
                        "direction" : "forward",
                        "indexBounds" : {
                            "dataHash" : [
                                "[\"00002478a5c9594923833b3534b2d0b17bb7298a\", \"00002478a5c9594923833b3534b2d0b17bb7298a\"]"
                            ],
                            "deleted" : [
                                "[MinKey, true)",
                                "(true, MaxKey]"
                            ]
                        }
                    }
                }
            },
            {
                "stage" : "PROJECTION_SIMPLE",
                "transformBy" : {
                    "_id" : true
                },
                "inputStage" : {
                    "stage" : "FETCH",
                    "filter" : {
                        "$and" : [
                            {
                                "accountId" : {
                                    "$eq" : ObjectId("62c56bbef2c6530d4a9b5976")
                                }
                            },
                            {
                                "dataHash" : {
                                    "$eq" : "00002478a5c9594923833b3534b2d0b17bb7298a"
                                }
                            }
                        ]
                    },
                    "inputStage" : {
                        "stage" : "IXSCAN",
                        "keyPattern" : {
                            "businessId" : 1.0,
                            "address.name" : 1.0,
                            "status" : 1.0,
                            "description" : 1.0,
                            "vendorId" : 1.0,
                            "deleted" : 1.0
                        },
                        "indexName" : "businessId_1_address.name_1_status_1_description_1_vendorId_1_deleted_1",
                        "isMultiKey" : false,
                        "multiKeyPaths" : {
                            "businessId" : [

                            ],
                            "address.name" : [

                            ],
                            "status" : [

                            ],
                            "description" : [

                            ],
                            "vendorId" : [

                            ],
                            "deleted" : [

                            ]
                        },
                        "isUnique" : false,
                        "isSparse" : false,
                        "isPartial" : false,
                        "indexVersion" : 2.0,
                        "direction" : "forward",
                        "indexBounds" : {
                            "businessId" : [
                                "[ObjectId('62c56bbdba7f1d001368f217'), ObjectId('62c56bbdba7f1d001368f217')]"
                            ],
                            "address.name" : [
                                "[MinKey, MaxKey]"
                            ],
                            "status" : [
                                "[MinKey, MaxKey]"
                            ],
                            "description" : [
                                "[MinKey, MaxKey]"
                            ],
                            "vendorId" : [
                                "[MinKey, MaxKey]"
                            ],
                            "deleted" : [
                                "[MinKey, true)",
                                "(true, MaxKey]"
                            ]
                        }
                    }
                }
            },
            {
                "stage" : "PROJECTION_SIMPLE",
                "transformBy" : {
                    "_id" : true
                },
                "inputStage" : {
                    "stage" : "FETCH",
                    "inputStage" : {
                        "stage" : "IXSCAN",
                        "keyPattern" : {
                            "businessId" : 1.0,
                            "accountId" : 1.0,
                            "dataHash" : 1.0,
                            "deleted" : 1.0
                        },
                        "indexName" : "businessId_1_accountId_1_dataHash_1_deleted_1",
                        "isMultiKey" : false,
                        "multiKeyPaths" : {
                            "businessId" : [

                            ],
                            "accountId" : [

                            ],
                            "dataHash" : [

                            ],
                            "deleted" : [

                            ]
                        },
                        "isUnique" : false,
                        "isSparse" : false,
                        "isPartial" : false,
                        "indexVersion" : 2.0,
                        "direction" : "forward",
                        "indexBounds" : {
                            "businessId" : [
                                "[ObjectId('62c56bbdba7f1d001368f217'), ObjectId('62c56bbdba7f1d001368f217')]"
                            ],
                            "accountId" : [
                                "[ObjectId('62c56bbef2c6530d4a9b5976'), ObjectId('62c56bbef2c6530d4a9b5976')]"
                            ],
                            "dataHash" : [
                                "[\"00002478a5c9594923833b3534b2d0b17bb7298a\", \"00002478a5c9594923833b3534b2d0b17bb7298a\"]"
                            ],
                            "deleted" : [
                                "[MinKey, true)",
                                "(true, MaxKey]"
                            ]
                        }
                    }
                }
            }
        ]
    },
    "command" : {
        "find" : "transactions",
        "filter" : {
            "businessId" : ObjectId("62c56bbdba7f1d001368f217"),
            "accountId" : ObjectId("62c56bbef2c6530d4a9b5976"),
            "dataHash" : "00002478a5c9594923833b3534b2d0b17bb7298a",
            "deleted" : {
                "$ne" : true
            }
        },
        "projection" : {
            "_id" : true
        },
        "$db" : "stage"
    },
    "serverInfo" : {
        "host" : "cluster0-shard-00-01.u0b4v.mongodb.net",
        "port" : 27017.0,
        "version" : "5.0.12",
        "gitVersion" : "79cfcdd83eb6f64e164a588d0daf9bb873328b45"
    },
    "serverParameters" : {
        "internalQueryFacetBufferSizeBytes" : 104857600.0,
        "internalQueryFacetMaxOutputDocSizeBytes" : 104857600.0,
        "internalLookupStageIntermediateDocumentMaxSizeBytes" : 16793600.0,
        "internalDocumentSourceGroupMaxMemoryBytes" : 104857600.0,
        "internalQueryMaxBlockingSortMemoryUsageBytes" : 33554432.0,
        "internalQueryProhibitBlockingMergeOnMongoS" : 0.0,
        "internalQueryMaxAddToSetBytes" : 104857600.0,
        "internalDocumentSourceSetWindowFieldsMaxMemoryBytes" : 104857600.0
    },
    "ok" : 1.0,
    "$clusterTime" : {
        "clusterTime" : Timestamp(1663107520, 28),
        "signature" : {
            "hash" : BinData(0, "E5Mndz8gTLw14zfsCVW0yZAa9hg="),
            "keyId" : NumberLong(7083587916596772929)
        }
    },
    "operationTime" : Timestamp(1663107520, 28)
}

Winning plan index is businessId_1_reason_1_deleted_1, so it uses only businessId for indexing. Of course I can use .hint(), but... Why it happens? I read the docs, but did not found answer. It should work

Answer 1

Accurately answering this question requires a higher verbosity explain output, namely "allPlansExecution". If you can provide that we can take a look and I'd be happy to amend my answer with any additional observations learned from it.

In the absence of such information, we can make the following observations:

1. As with any database system, the environmental inputs to the query planner are very important. Is the problem being experienced on an environment that has data representative of a production system? It is common for development environments that don't contain much data to select "unexpected" plans, but that is mostly a consequence of not having appropriate information to make a decision as opposed to a problem that will manifest in practice with real data.
2. This environment has quite a few indexes, many of which contain similar fields. In addition to the commonly recited considerations for indexes (such as incremental impact on write throughput and increased space usage), they also result in more complexity for the optimizer when planning queries. This may represent an opportunity to review and potentially consolidate some of your indexes.
3. If this query, specifically the projection to only retrieve the _id field, is what the application will be doing in practice, then consider appending the _id field to the end of the index. This should allow the database to skip retrieval of the full document via the FETCH just to obtain that single value. In other words, such an index would result in a Covered Query option for the optimizer to consider.