Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-41279

Eliminate failed plans from consideration during query planning

    • Fully Compatible
    • ALL
    • Query 2019-06-17, Query 2019-07-01, Query 2019-07-15, Query 2019-07-29
    • 0

      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
      

            Assignee:
            samuel.mercier@mongodb.com Sam Mercier
            Reporter:
            christopher.harris@mongodb.com Chris Harris
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

              Created:
              Updated:
              Resolved: