[SERVER-14663] Put skip stage below projection Created: 23/Jul/14  Updated: 06/Dec/22

Status: Backlog
Project: Core Server
Component/s: Querying
Affects Version/s: 2.6.3
Fix Version/s: None

Type: Improvement Priority: Major - P3
Reporter: David Storch Assignee: Backlog - Query Optimization
Resolution: Unresolved Votes: 5
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Related
related to SERVER-43297 Inefficient query on view due to $lim... Closed
is related to SERVER-13946 Consider putting skip stages below fe... Closed
Assigned Teams:
Query Optimization
Participants:

 Description   

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.


Generated at Thu Feb 08 03:35:34 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.