[SERVER-78351] Limit pushdown doesn't work on sharded clusters if $skip stage is present Created: 22/Jun/23  Updated: 29/Oct/23  Resolved: 30/Jun/23

Status: Closed
Project: Core Server
Component/s: None
Affects Version/s: None
Fix Version/s: 7.1.0-rc0, 7.0.0-rc7, 6.0.9

Type: Bug Priority: Major - P3
Reporter: Evan Darke Assignee: Ted Tuckman
Resolution: Fixed Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Backports
Assigned Teams:
Query Integration
Backwards Compatibility: Fully Compatible
Operating System: ALL
Backport Requested:
v7.0, v6.0
Participants:

 Description   

Summarized from https://mongodb.slack.com/archives/C039SULHYGH/p1684257620795129
 
$search has custom logic for limit push down which is missing an optimization when occuring after $skip stages. a skip of 50 and limit of 50 should be pushed down as a limit of 50 to each shard and then limited again on mongos. Instead, each shard has to return a full batch.
 
Example 1: Sharded cluster comparing skip=0 to no skip

db.listings.aggregate([\{ $search: { queryString: { defaultPath: "data.host_type", query: "h*" }} }, \{$limit: 50}]): 249ms
 
db.listings.aggregate([\{ $search: { queryString: { defaultPath: "data.host_type", query: "h*" }} }, \{$skip: 0}, \{$limit: 50}]): 752ms

 

Example 2: Sharded cluster, problem seems exacerbated by including a $project stage

db.listings.aggregate([{ $search: { queryString: { defaultPath: "data.host_type", query: "h*" }} }, { $project: { highlight:
{ "$meta": "searchScore" }
}}, {$limit: 50}]) : 495ms
db.listings.aggregate([{ $search: { queryString: { defaultPath: "data.host_type", query: "h*" }} }, { $project: { highlight:
{ "$meta": "searchScore" }
}}, {$skip:0}, {$limit: 50}]): 12,528ms

 

 

 

Explain gist for query with {$skip:0} : https://gist.github.com/Edarke/c663b6f1acafa05af713781e4ce096eb

 

Explain gist for query without skip: https://gist.github.com/Edarke/5a423dc836a532d4b38228c421eac78c

 

 

Relevant code pointer

 

 

 


Generated at Thu Feb 08 06:38:07 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.