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

Put skip stage below projection

    • Type: Icon: Improvement Improvement
    • Resolution: Unresolved
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: 2.6.3
    • Component/s: Querying
    • None
    • Query Optimization

      Currently, the query engine adds a SKIP stage on the top of an execution tree after projection analysis. The consequence is that the server does the work of computing projections even for documents which are thrown away due to the skip.

      This can be reproduced by the following script:

      t.drop();
      for (var i = 0; i < 100000; i++) { t.insert({a: i, b: i, c: i}); }
      
      // Collection scan which skips all documents except for the last,
      // with a projection.
      t.find({}, {a: 1, b: 1, c: 1}).skip(99999).explain()
      
      // Collection scan which skips all documents except for the last;
      // no projection.
      t.find({}).skip(99999).explain()
      

      I have run this script against both 2.4.10 and 2.6.3. For the first query (the one with the projection), 2.6.3 is two or three times slower than 2.4.10. When the projection is removed, the performance of 2.4.10 and 2.6.3 is about equal (actually, 2.6.3 is maybe a little faster).

      This slowdown is likely due to computing projections for the skipped documents. As a performance optimization for skip + projection queries, we can apply the projection after the skip.

            Assignee:
            backlog-query-optimization [DO NOT USE] Backlog - Query Optimization
            Reporter:
            david.storch@mongodb.com David Storch
            Votes:
            5 Vote for this issue
            Watchers:
            11 Start watching this issue

              Created:
              Updated: