[SERVER-13946] Consider putting skip stages below fetch stages Created: 14/May/14  Updated: 13/May/20  Resolved: 21/Jul/18

Status: Closed
Project: Core Server
Component/s: Querying
Affects Version/s: 2.6.1
Fix Version/s: 3.4.19, 3.6.9, 4.0.3, 4.1.2

Type: Improvement Priority: Major - P3
Reporter: David Storch Assignee: Justin Seyster
Resolution: Done Votes: 18
Labels: QFB, asya
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Backports
Depends
Duplicate
is duplicated by SERVER-15169 performance regression in .skip() usi... Closed
is duplicated by SERVER-2845 when using skip, objects still report... Closed
is duplicated by SERVER-24025 Don't load .skip()ed documents when p... Closed
Related
related to SERVER-14663 Put skip stage below projection Backlog
Backwards Compatibility: Fully Compatible
Backport Requested:
v4.0, v3.6, v3.4
Sprint: Query 2018-07-02, Query 2018-07-16, Query 2018-07-30
Participants:
Case:

 Description   

Suppose we have a query for which the following conditions hold:

  1. The query predicates can all be answered using an index.
  2. The query is not covered (i.e. it requires a fetch stage) because there is no projection.
  3. There is a large skip value.

Currently, we will fetch documents for all the skipped results. This means unnecessary IO / unnecessarily large nscannedObjects. As an optimization, we could avoid the extra fetching by putting the skip stage below the fetch.

As an example, say we have documents of the form {a: <int>, b: <int>, c: <int>, x: <int>} and an index {a: 1, b: 1, c: 1}. The following query

db.coll.find({a: 3, b: 4}).sort({c: 1}).skip(10000);

can use the index to answer both query predicates and to do the sort. However, the query is not covered: we need to fetch documents in order to get the '_id' and 'x' fields. In this case it is possible to put the skip stage below the fetch in order to avoid fetching the 10,000 skipped documents.



 Comments   
Comment by Githook User [ 21/Dec/18 ]

Author:

{'username': 'jseyster', 'email': 'justin.seyster@mongodb.com', 'name': 'Justin Seyster'}

Message: SERVER-13946 Put skip stages before fetch stages.

(cherry picked from commit 69f3e89f6921fc4ff2b5413952eeb517af69bb83)
(cherry picked from commit 8e540c0b6db93ce994cc548f000900bdc740f80a)
Branch: v3.4
https://github.com/mongodb/mongo/commit/2030f398d7f3eb23f164770fc2325dfcb28b4bf4

Comment by Githook User [ 15/Oct/18 ]

Author:

{'name': 'Justin Seyster', 'email': 'justin.seyster@mongodb.com', 'username': 'jseyster'}

Message: SERVER-13946 Put skip stages before fetch stages.

(cherry picked from commit 69f3e89f6921fc4ff2b5413952eeb517af69bb83)
Branch: v3.6
https://github.com/mongodb/mongo/commit/8e540c0b6db93ce994cc548f000900bdc740f80a

Comment by Githook User [ 20/Sep/18 ]

Author:

{'name': 'Justin Seyster', 'email': 'justin.seyster@mongodb.com', 'username': 'jseyster'}

Message: SERVER-13946 Put skip stages before fetch stages.

(cherry picked from commit 69f3e89f6921fc4ff2b5413952eeb517af69bb83)
Branch: v4.0
https://github.com/mongodb/mongo/commit/9e6641ed5785c7898ecfba5f0742827b988f5f9a

Comment by Githook User [ 21/Jul/18 ]

Author:

{'username': 'jseyster', 'name': 'Justin Seyster', 'email': 'justin.seyster@mongodb.com'}

Message: SERVER-13946 Put skip stages before fetch stages.
Branch: master
https://github.com/mongodb/mongo/commit/69f3e89f6921fc4ff2b5413952eeb517af69bb83

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