Deduplicate index scan + fetch plans guaranteed to have similar performance

XMLWordPrintableJSON

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

      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
            Reporter:
            Matt Boros
            Votes:
            0 Vote for this issue
            Watchers:
            21 Start watching this issue

              Created:
              Updated:
              Resolved: