Aggregation pipeline optimization - coalescence

Hi Team,

$limit 20 + $limit 10 => $limit 10 (minimum of two)
$skip 20 + $skip 10 => $skip 30 (sum of both)

Why does limit pick the least value between stages while skip sums up the value between stages ?

Because it is the only way it make sense.

  1. Assume you have 40 documents, with _id in [ 1 … 49 ]
  2. The $limit:20 will keep documents with _id in [ 1 … 20 ]
  3. The $limit:10 will keep 10 documents from the above 2. So it keeps documents with _id in [ 1 … 10 ]
  4. So doing $limit:20 and then $limit:10 on the 20 documents is the same as doing a $limit:10 on the original documents so it can be done in a single stage.
  5. The $skip:20 will bring the cursor at the 21st document so reminding documents are _id in [ 21 … 40 ]
  6. The following $skip:10 will advance the cursor another 10 so reminder documents are _id in [ 31 … ]
  7. Which means a $skip:20 followed by a $skip:10 is equivalent to a $skip:30 and can be done in a single stage.
1 Like

Thanks @steevej. This is helpful.