[SERVER-13732] Predicates in top-level implicit AND query not considered when generating index access plan for contained OR Created: 25/Apr/14 Updated: 27/Sep/19 Resolved: 28/Feb/17 |
|
| Status: | Closed |
| Project: | Core Server |
| Component/s: | Querying |
| Affects Version/s: | 2.6.0 |
| Fix Version/s: | 3.5.4 |
| Type: | Bug | Priority: | Critical - P2 |
| Reporter: | J Rassi | Assignee: | Tess Avitabile (Inactive) |
| Resolution: | Done | Votes: | 18 |
| Labels: | 2426, todo_in_code | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Issue Links: |
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Backwards Compatibility: | Fully Compatible | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Operating System: | ALL | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Sprint: | Quint Iteration 3, Quint Iteration 4, Quint Iteration 5, Quint Iteration 6, Query 2017-01-23, Query 2017-02-13, Query 2017-03-06 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Participants: | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Case: | (copied to CRM) | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Description |
| Comments |
| Comment by Githook User [ 17/Feb/17 ] | ||
|
Author: {u'username': u'tessavitabile', u'name': u'Tess Avitabile', u'email': u'tess.avitabile@mongodb.com'}Message: | ||
| Comment by Githook User [ 01/Sep/15 ] | ||
|
Author: {u'username': u'dstorch', u'name': u'David Storch', u'email': u'david.storch@10gen.com'}Message: | ||
| Comment by David Storch [ 01/Sep/15 ] | ||
|
Hi all, After reviewing the fix for this problem included in development release 3.1.6, we have decided that its risks outweigh its benefits. Unfortunately the fix results in a substantial performance regression in the query planner and introduces some fragile logic to the $or query execution machinery. Furthermore, the problem has a relatively straightforward application-level workaround. For these reasons, we are hesitant to include the fix in a stable release. We are disabling the codepath implementing the fix, re-opening this ticket, and refraining from pushing a backport to the 3.0.x stable release series. (Note that although the fix is disabled, we are leaving all the plumbing in place so that enabling in a future release is trivial, should the fix prove to be safe and the performance regression prove to have a minimal impact.) Our apologies for not fixing this. Please feel free to reach out to me on this ticket with questions or concerns. Best, | ||
| Comment by Githook User [ 10/Jul/15 ] | ||
|
Author: {u'username': u'dstorch', u'name': u'David Storch', u'email': u'david.storch@10gen.com'}Message: This allows queries with an $or contained within an explicit or implicit $and to be answered with | ||
| Comment by Urs Enke [ 18/Dec/14 ] | ||
|
You're right – I had mistyped. Of course, $in is only an alternative when the $or concerns a single field (or when all combinations of the affected fields' values are allowed, so each field could get its own $in without changing the meaning). But whoever is used to MySQL's comparable performance of "b=1 OR b=2" and "b IN (1,2)" could easily assume a suitably simply structured $or to be optimized to use an index, despite the posibility of using the operator in a way that would be harder to optimize. | ||
| Comment by Asya Kamsky [ 17/Dec/14 ] | ||
|
I believe you mean {a:1,$or:[{b:1},{b:2}]} correct? The issue is when the two queries within $or do not test the same field - then they cannot be rewritten using $in. | ||
| Comment by Urs Enke [ 17/Dec/14 ] | ||
|
A small note: Queries like
| ||
| Comment by David Storch [ 23/Sep/14 ] | ||
|
Hi all, A quick status update on this ticket follows. We have explored two options for solving this issue on the 2.6 branch:
After technical review, both of these solutions were deemed to be a step in the wrong direction. The system does not currently have a rewrite engine. Doing this particular logical rewrite breaks some internal assumptions (for instance, the update system's logic for computing the document to insert on {upsert: true} updates depends on the absence of any logical rewrites). Furthermore, this change would improve the plans for one class of queries at the expense of another class. Regarding the plan space exploration solution: moving forward we hope to alter our exploration strategy in order to be more flexible about which plans we consider and which we don't. Using the current strategy for exploring the plan space, this change is technically difficult if not infeasible. For the reasons listed above, backport to the 2.6 branch is currently not planned. Best, | ||
| Comment by J Rassi [ 25/Apr/14 ] | ||
|
Further detail follows. In version 2.4 and earlier: for specific classes of queries containing $or clauses, the query planner would consider a plan that unions index scans on separate indexes. Queries that qualified include:
For all other queries containing an $or clause, the 2.4 query planner would not consider assigning indexes to the $or child predicates. In version 2.6.0: the query planner always considers an indexed plan for $or, but the indexes chosen for the union are only based on the predicates contained in the $or children. That is, when planning the query {a:1, $or: [{b:1}, {c:1}]}, the 2.6 query planner will not consider the {a:1} predicate when creating a plan that takes a union of separate indexes. Employing the workaround (that is, rewriting the query as {$or: [{a:1, b:1}, {a:1, c:1}]}) allows both predicates to be used. |