It is mentioned that sometimes hint() method has to be used to explicitly mention which index(s) we want to be used for the document filtering process. In which scenarios does the query optimizer fail to identify the most efficient indexes to be used while filtering the documents ? (Please do share any documentation link, if available)
hint() method will force the query optimizer to use a specific index to fulfill the query.
Sometimes, the query optimizer may select the collection scan as the winning plan for a given query shape.
Please refer to the following documentation links for more details:
Please let me know, if you have any questions.
Just my thoughts:
mongodis a new instance, i.e. brand new or has been restarted. In such a scenario there will be no plan cache.
- When data related to one or more indexes has changed. In such a scenario the plan cache may be out-of-date until you run a query to create a shape and plan cache.
- When the number of documents requested is pretty huge. Although query plans are empirical, there’s a very slight possibility that the chosen index may not be the most efficient because when the plan cache is built, it executes the query on a small subset of documents.