[SERVER-41279] Eliminate failed plans from consideration during query planning Created: 22/May/19  Updated: 29/Oct/23  Resolved: 19/Jul/19

Status: Closed
Project: Core Server
Component/s: Index Maintenance, Querying
Affects Version/s: None
Fix Version/s: 4.3.1

Type: Bug Priority: Major - P3
Reporter: Chris Harris Assignee: Sam Mercier
Resolution: Fixed Votes: 0
Labels: query-44-grooming, storch
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Problem/Incident
Backwards Compatibility: Fully Compatible
Operating System: ALL
Sprint: Query 2019-06-17, Query 2019-07-01, Query 2019-07-15, Query 2019-07-29
Participants:
Linked BF Score: 0

 Description   

Candidate plans can fail during the trial phase of the query planning process.  A typical example would be when plans with blocking sorts exceed their configured memory threshold.  Once failed, such plans should not be eligible for selection as the winning plan.

As part of addressing this ticket, we should consider whether to remove the concept of a backup plan entirely from the code base.  Backup plans were removed from the plan cache in 3.0 via SERVER-15225/SERVER-20139, so they are currently only used during plan selection and even then only until the plan advances and becomes unblocked.  If no plan advanced during the trial phase, then the winner should be determined by the tie breakers.  It's likely that the majority of the time the non-blocking plan will win due to the noSortBonus it earns.  This would seemingly result in an inability to ever trigger the need for a backup plan since a blocking sort plan which had not advanced would be prevented from winning plan selection.    

There is at least one edge case for that situation which should be accounted for.  If the blocking sort plan can provide a covered plan thus offsetting the noSortBonus tie breaker with the noFetchBonus:

2019-05-22T13:44:25.447-0500 D2 QUERY    [conn14] Scoring query plan: IXSCAN { x: 1, z: 1, y: 1, _id: 1 } planHitEOF=0
2019-05-22T13:44:25.447-0500 D2 QUERY    [conn14] score(1.00002) = baseScore(1) + productivity((0 advanced)/(10000 works) = 0) + tieBreakers(1e-05 noFetchBonus + 0 noSortBonus + 1e-05 noIxisectBonus = 2e-05)
2019-05-22T13:44:25.447-0500 D5 QUERY    [conn14] score = 1.00002
...
2019-05-22T13:44:25.447-0500 D2 QUERY    [conn14] Scoring query plan: IXSCAN { _id: 1 } planHitEOF=0
2019-05-22T13:44:25.447-0500 D2 QUERY    [conn14] score(1.00002) = baseScore(1) + productivity((0 advanced)/(10000 works) = 0) + tieBreakers(0 noFetchBonus + 1e-05 noSortBonus + 1e-05 noIxisectBonus = 2e-05)
2019-05-22T13:44:25.447-0500 D5 QUERY    [conn14] score = 1.00002
... 
2019-05-22T13:44:25.447-0500 D2 QUERY    [conn14] Winning plan: IXSCAN { x: 1, z: 1, y: 1, _id: 1 }
2019-05-22T13:44:25.447-0500 D5 QUERY    [conn14] Winner has blocking stage, looking for backup plan...
2019-05-22T13:44:25.447-0500 D5 QUERY    [conn14] Candidate 1 is backup child



 Comments   
Comment by Githook User [ 11/Jul/19 ]

Author:

{'name': 'samontea', 'email': 'merciers.merciers@gmail.com', 'username': 'samontea'}

Message: SERVER-41279 Eliminate failed plans from consideration during query planning
Branch: master
https://github.com/mongodb/mongo/commit/902b3f9135861b69b6581909299a8ada2db6588f

Comment by Githook User [ 10/Jul/19 ]

Author:

{'name': 'Xiangyu Yao', 'email': 'xiangyu.yao@mongodb.com', 'username': 'xy24'}

Message: Revert "SERVER-41279 Eliminate failed plans from consideration during query planning"

This reverts commit 6bba6446e632b557ccc03834d4d48e90336679fc.
Branch: master
https://github.com/mongodb/mongo/commit/135c88363bdb5e7b353825f3fec4aa0d7db80571

Comment by Githook User [ 10/Jul/19 ]

Author:

{'name': 'samontea', 'username': 'samontea', 'email': 'merciers.merciers@gmail.com'}

Message: SERVER-41279 Eliminate failed plans from consideration during query planning
Branch: master
https://github.com/mongodb/mongo/commit/6bba6446e632b557ccc03834d4d48e90336679fc

Comment by Sam Mercier [ 31/May/19 ]

christopher.harris If it's okay with you, I'm going to move removing backup plans entirely from the code base from this ticket. It feels like substantially different work from what's described in the rest of the ticket.

Generated at Thu Feb 08 04:57:18 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.