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

push down $skip stage to query when possible

    XMLWordPrintable

Details

    • Improvement
    • Status: Closed
    • Major - P3
    • Resolution: Fixed
    • None
    • 4.9.0
    • Aggregation Framework
    • None
    • Fully Compatible
    • Query 2020-09-07, Query 2020-09-21, Query 2020-10-05, Query 2020-10-19
    • 0

    Description

      Not sure why we don't do this:

      db.skip.explain().aggregate([{$match:{a:{$gte:7}}},{$sort:{a:1}},{$skip:2}])
      {
      	"stages" : [
      		{
      			"$cursor" : {
      				"query" : {
      					"a" : {
      						"$gte" : 7
      					}
      				},
      				"sort" : {
      					"a" : 1
      				},
      				"queryPlanner" : {
      					"plannerVersion" : 1,
      					"namespace" : "agg.skip",
      					"indexFilterSet" : false,
      					"parsedQuery" : {
      						"a" : {
      							"$gte" : 7
      						}
      					},
      					"winningPlan" : {
      						"stage" : "FETCH",
      						"inputStage" : {
      							"stage" : "IXSCAN",
      							"keyPattern" : {
      								"a" : 1
      							},
      							"indexName" : "a_1",
      							"isMultiKey" : false,
      							"multiKeyPaths" : {
      								"a" : [ ]
      							},
      							"isUnique" : false,
      							"isSparse" : false,
      							"isPartial" : false,
      							"indexVersion" : 2,
      							"direction" : "forward",
      							"indexBounds" : {
      								"a" : [
      									"[7.0, inf.0]"
      								]
      							}
      						}
      					},
      					"rejectedPlans" : [ ]
      				}
      			}
      		},
      		{
      			"$skip" : NumberLong(2)
      		}
      	],
      	"ok" : 1
      }
      

      > db.skip.explain().aggregate([{$match:{a:{$gte:7}}},{$sort:{a:1}},{$skip:2},{$limit:1}])
      {
      	"stages" : [
      		{
      			"$cursor" : {
      				"query" : {
      					"a" : {
      						"$gte" : 7
      					}
      				},
      				"sort" : {
      					"a" : 1
      				},
      				"limit" : NumberLong(3),
      				"queryPlanner" : {
      					"plannerVersion" : 1,
      					"namespace" : "agg.skip",
      					"indexFilterSet" : false,
      					"parsedQuery" : {
      						"a" : {
      							"$gte" : 7
      						}
      					},
      					"winningPlan" : {
      						"stage" : "FETCH",
      						"inputStage" : {
      							"stage" : "IXSCAN",
      							"keyPattern" : {
      								"a" : 1
      							},
      							"indexName" : "a_1",
      							"isMultiKey" : false,
      							"multiKeyPaths" : {
      								"a" : [ ]
      							},
      							"isUnique" : false,
      							"isSparse" : false,
      							"isPartial" : false,
      							"indexVersion" : 2,
      							"direction" : "forward",
      							"indexBounds" : {
      								"a" : [
      									"[7.0, inf.0]"
      								]
      							}
      						}
      					},
      					"rejectedPlans" : [ ]
      				}
      			}
      		},
      		{
      			"$skip" : NumberLong(2)
      		}
      	],
      	"ok" : 1
      }
      

      In the first case we don't push anything down, in the second case we push down limit(3) - sum of skip and limit values - but in both cases we then do the skip in agg in the next stage.

      Attachments

        1. huge_projection_pipeline.js
          4.06 MB
        2. sample_document.json
          173 kB
        3. skip_push_down.patch
          34 kB

        Issue Links

          Activity

            People

              nikita.lapkov@mongodb.com Nikita Lapkov
              asya.kamsky@mongodb.com Asya Kamsky
              Votes:
              1 Vote for this issue
              Watchers:
              13 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: