[SERVER-59337] Bad index selection when using $in Created: 13/Aug/21 Updated: 11/Oct/21 Resolved: 11/Oct/21 |
|
| Status: | Closed |
| Project: | Core Server |
| Component/s: | None |
| Affects Version/s: | None |
| Fix Version/s: | None |
| Type: | Bug | Priority: | Major - P3 |
| Reporter: | Johnny Shields | Assignee: | Eric Sedor |
| Resolution: | Duplicate | Votes: | 0 |
| Labels: | None | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Issue Links: |
|
||||||||
| Operating System: | ALL | ||||||||
| Participants: | |||||||||
| Description |
|
Would like to report an incident which happened on MongoDB 4.4.8 running on Atlas. We have a large table "reservations" and we did an $in query on the "customer_ids" field for roughly 240 values. The table has the following indexes available:
Looking at the log below, for some reason, the QueryPlanner selected an index which did not include customer_ids at all (deleted_at: 1, shop_id: 1, st: 1). This caused a full table scan and apparently locked our database from getting replication updates. To resolve the issue on our end, I will add an index for (customer_ids: 1, deleted_at: 1). However, given this situation (an "any" with 240 ID keys) I still feel the QueryPlanner should have given preference to the customer_ids field. Please let me know your thoughts.
Full query is below:
Edited by MongoDB for formatting purposes |
| Comments |
| Comment by Eric Sedor [ 11/Oct/21 ] |
|
Hi again Johnny, Thanks for your patience. The planner can be surprising at times, and we do want to improve behavior in cases where multiple indexes are viable and specific queried values are leading to poor plan choices in unpredictable ways. As you've probably already found, hints are often necessary in these scenarios, as is attention to keeping the shared prefixes among indexes to a minimum. When reporting such issues, the output of explain("allPlansExecution") is most helpful when it comes to distinguishing among some identified weaknesses. In this case a colleague has pointed me to |
| Comment by Johnny Shields [ 18/Aug/21 ] |
|
Hi Eric, I've been using MongoDB for 8 years. This QueryPlanner behavior is strange. Unfortunately I don't have the time to start a thread on MongoDB forums and I've already taken countermeasures on my side, but I really think someone involved in QueryPlanner design should look at this edge-case; I raised this issue for your benefit. If no one wants to look at it, then fine to close. |
| Comment by Eric Sedor [ 18/Aug/21 ] |
|
One thing I can quickly suggest is that you will want to dig into the explain plan for the query to reason about why a given selection occurred. The best place to start a discussion around this will be by posting on the MongoDB Developer Community Forums. The community there can help with initial considerations around query planning, reading explain plans, and ruling out other common reasons for certain plan selections. If the discussion there leads you to suspect a bug in the MongoDB server, then we'd want to investigate it as a possible bug here in the SERVER project. Sincerely, |