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

Deduplicate index scan + fetch plans guaranteed to have similar performance

    • Type: Icon: Improvement Improvement
    • Resolution: Fixed
    • Priority: Icon: Major - P3 Major - P3
    • 8.0.0-rc0
    • Affects Version/s: None
    • Component/s: None
    • Query Optimization
    • Fully Compatible
    • 200

      In SERVER-82549 we have a query where we consider 14 plans with the same structure (index scan + fetch + filter). The index scans are technically different, but are guaranteed to have the same performance, and return the same RIDs:

      Plan 1

      filter
      |
      seek
      |
      index scan
      X: [100, 100],
      Y: [Minkey, Maxkey]
      

      Plan 2

      filter
      |
      seek
      |
      index scan
      X: [100, 100],
      Z: [Minkey, Maxkey]
      

      Plan 3

      filter
      |
      seek
      |
      index scan
      X: [100, 100],
      Y: [Minkey, Maxkey],
      Z: [Minkey, Maxkey]
      

      The non-open bounds in these index scans are identical. They will produce the same RIDs. For queries without a sort we should consider these duplicates and only trial one of them, preferably the one with the least number of indexed fields.

      We've had a number of issues recently where multiplanning is slow. Removing identical plans is one way to help fix this, and generally improve multiplanner performance.

            Assignee:
            matt.boros@mongodb.com Matt Boros
            Reporter:
            matt.boros@mongodb.com Matt Boros
            Votes:
            0 Vote for this issue
            Watchers:
            21 Start watching this issue

              Created:
              Updated:
              Resolved: