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

Queries that scan an entire index to provide a sort should be covered when possible

XMLWordPrintableJSON

    • Type: Icon: Improvement Improvement
    • Resolution: Unresolved
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: 2.5.5
    • Component/s: Query Planning
    • Query Optimization
    • Fully Compatible
    • None
    • None
    • None
    • None
    • None
    • None
    • None

      This ticket is the same as the TODO here: https://github.com/mongodb/mongo/blob/e5e4a2427b869e40f1a1e6f65dc5e3fd11f451ac/src/mongo/db/query/planner_access.cpp#L1126.

      Currently, when we try to perform an indexed sort by scanning the whole index from MinKey to MaxKey, we always add a FETCH as the parent of the index scan. The FETCH is usually necessary, but may not be if the query is covered by the index.

      Example:

      > t = db.t
> t.drop()
true
> t.ensureIndex({a: 1, b: 1})
> t.find({b: {$gte: 0}}, {_id: 0, b: 1}).sort({a: 1})

      The indexed solution for this example is a scan over the entire index {a: 1, b: 1}, followed by a FETCH, followed by a PROJ:

      PROJ
---proj = { _id: 0.0, b: 1.0 }
---fetched = 1
---sortedByDiskLoc = 0
---getSort = []
Child:
------FETCH
---------filter:
                b $gte 0.0 First: notFirst: full path:
---------fetched = 1
---------sortedByDiskLoc = 0
---------getSort = [{ a: 1 }, { a: 1, b: 1 }, ]
---------Child:
------------IXSCAN
---------------keyPattern = { a: 1.0, b: 1.0 }
---------------direction = 1
---------------bounds = field #0['a']: [MinKey, MaxKey], field #1['b']: [MinKey, MaxKey]
---------------fetched = 0
---------------fetched = 0
---------------sortedByDiskLoc = 0
---------------getSort = [{ a: 1 }, { a: 1, b: 1 }, ]

      Since the value of "b" is provided by the index, the FETCH is unnecessary---in other words, this should execute as a covered query.

        1.
        		 Optimize OR trees for full IXSCAN plans SERVER-103592 Sub-task Backlog Unassigned  
        2.
        		 Optimize NOR predicates for full IXSCAN queries SERVER-103593 Sub-task Backlog Unassigned  
        3.
        		 Pushdown indexed predicates to IXSCAN stage for full index plan over timeseries collections SERVER-103594 Sub-task Backlog Unassigned  
        4.
        		 Pushdown indexed predicates to IXSCAN stage for full index plan over hashed indexes SERVER-103595 Sub-task Backlog Unassigned  
        5.
        		 Pushdown indexed predicates to IXSCAN stage for full index plan over multikey indexes SERVER-103596 Sub-task Backlog Unassigned  
        6.
        		 Optimize AND trees for full IXSCAN plans SERVER-103601 Sub-task In Code Review Carlos Alonso Pérez  

            Assignee:
            c.alonso@mongodb.com Carlos Alonso Pérez
            Reporter:
            david.storch@mongodb.com David Storch
            Votes:
            1 Vote for this issue
            Watchers:
            19 Start watching this issue

              Created:
              Updated: