Mongo index selection differs between aggregate and find and bet

ghz 9months ago ⋅ 102 views

Mongo index selection differs between aggregate and find and between databases

This question is two part, but both relate to index selection heuristics.

Context: We have around 4000 databases of identical collection, document and index structure (this last one in particular has been confirmed repeatedly), spread across ~40 clusters - this isn't particularly important beyond the fact that it identifies the unusual behaviour we're seeing.

In each of these databases, we have a tasks collection - with a number of indexes defined. In particular:

{ ignored: 1 }
{ assignment_strategy: 1, due_date: 1 }

We have an aggregation:

[
{ $match: { 
   ignored: false,
   due_date: { $exists: true, $ne: null },
   assignment_strategy: "whatever",
   complete: false,
   assignment_attempted: false
} },
{ $group: {
  _id: "$label",
  count: { $sum: 1 }
} }
]

Two problems:

  1. In most of the databases, this correctly chooses the index { assignment_strategy: 1, due_date: 1 }. Unfortunately, in some of them it uses { ignored: 1 }
  2. In the databases that choose the wrong index for aggregation - if I switch to a find (using the $match as the selector) it chooses the correct index

The { ignored: 1 } index should be unequivocally wrong in this case - ignored: false is usually the majority of tasks (ignored ~= deleted). In one case, of the ~857k documents the { ignored: 1 } index returns ~544k for doc scan - the query takes 11 seconds. Compared with the correct index which filters to ~90k (still not ideal) but only takes ~600ms.

The databases in question are all hosted by Atlas - there seems to be no correlation between which clusters have databases which select the wrong index - my assumption is it's entirely based on the distribution of documents within the collection. We're on mongo 6.

My questions:

  1. where can I read more about the mongo index selection heuristic? I'm familiar with most of the output of explain, but can't fathom why mongo would choose an index that even explain recognises is terrible (both on examined:returned ratio and time)
  2. why would the index selection change between find and aggregate?

Regarding question (2) I'm wondering if this is because the group stage makes it look like the returned docs is smaller than it is (the $group returns only a handful of docs, but the $match matches to ~2500) but I'd hope mongo is smarter than this. I tried changing the $group stage to make the _id: "all" - interestingly this did two things:

  1. it changed the structure of the explain result - it no longer had a .queryPlanner entry (it had stages instead).
  2. it used the correct index.

We're going to make the "correct" index even more selective (to get that 90k down closer to the 2k) but I'm not convinced that a more selective index will help, given that mongo is insisting on choosing the wrong index currently.

Answers

MongoDB's index selection heuristic is a complex process influenced by various factors such as the query predicates, index statistics, index size, and query planner logic. While MongoDB's query planner aims to select the most efficient index for a given query, there are cases where it may not always choose the optimal index.

To address your questions:

  1. Learning about MongoDB Index Selection Heuristic:

    • MongoDB's official documentation provides insights into index selection and query optimization. The section on "Index Intersection" and "Index Selection" in the documentation can be particularly useful.
    • The MongoDB blog and MongoDB University courses may also provide additional insights and practical examples related to index selection and query optimization.

  2. Reasons for Index Selection Behavior:

    • MongoDB's index selection may change based on various factors such as the distribution of data, index statistics, and query predicates. It's possible that the distribution of data in some databases leads to suboptimal index selection.
    • The behavior where index selection changes between find and aggregate queries can be influenced by the query planner's evaluation of the query plan for each operation. The query planner may evaluate the query differently for find and aggregate operations, leading to different index selection decisions.

  3. Group Stage Impact on Index Selection:

    • The group stage in the aggregation pipeline may indeed affect the query planner's evaluation of the query plan. When using the $group stage, MongoDB's query planner may consider the group's key and aggregation expressions when selecting an index.
    • Changing the group stage to have a single group key ("all") may simplify the query planner's evaluation, potentially resulting in a different index selection.

  4. Improving Index Selectivity:

    • Making the "correct" index more selective can indeed help improve query performance by reducing the number of documents scanned. However, it's essential to balance index selectivity with query coverage to ensure that the index can effectively filter out irrelevant documents without excluding too many relevant documents.

In summary, MongoDB's index selection is influenced by various factors, and while the query planner aims to select the most efficient index, there can be cases where it may not choose the optimal index. Understanding the query planner's behavior and optimizing index selectivity can help improve query performance in such cases. Additionally, monitoring and profiling query performance can provide insights into index selection decisions and help identify opportunities for optimization.

#mongodb