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

Presence of extraneous $project causes a less efficient plan to be used

    • Query Optimization
    • Fully Compatible
    • Query 2017-03-27, Query 2017-04-17, QO 2022-10-03

      Even though the user explicitly tells us to include specific fields, we can see that they are not actually being used, so doesn't it make sense to optimize $project away the same as if it wasn't there?

      db.t1.aggregate([ {$match:{username:/^user8/}}, {$project:{username:1}},{$group:{_id:1,count:{$sum:1}}}],{explain:true})
      {
      	"stages" : [
      		{
      			"$cursor" : {
      				"query" : {
      					"username" : /^user8/
      				},
      				"fields" : {
      					"username" : 1,
      					"_id" : 1
      				},
      				"plan" : {
      					"cursor" : "BtreeCursor username_1",
      					"isMultiKey" : false,
      					"scanAndOrder" : false,
      					"indexBounds" : {
      						"username" : [
      							[
      								"user8",
      								"user9"
      							],
      							[
      								/^user8/,
      								/^user8/
      							]
      						]
      					},
      					"allPlans" : [
      						{
      							"cursor" : "BtreeCursor username_1",
      							"isMultiKey" : false,
      							"scanAndOrder" : false,
      							"indexBounds" : {
      								"username" : [
      									[
      										"user8",
      										"user9"
      									],
      									[
      										/^user8/,
      										/^user8/
      									]
      								]
      							}
      						}
      					]
      				}
      			}
      		},
      		{
      			"$project" : {
      				"username" : true
      			}
      		},
      		{
      			"$group" : {
      				"_id" : {
      					"$const" : 1
      				},
      				"count" : {
      					"$sum" : {
      						"$const" : 1
      					}
      				}
      			}
      		}
      	]
      

      Without the (needless) $project

      db.t1.aggregate([ {$match:{username:/^user8/}}, {$group:{_id:1,count:{$sum:1}}}],{explain:true})
      {
      	"stages" : [
      		{
      			"$cursor" : {
      				"query" : {
      					"username" : /^user8/
      				},
      				"fields" : {
      					"_id" : 0,
      					"$noFieldsNeeded" : 1
      				},
      				"plan" : {
      					"cursor" : "BtreeCursor username_1",
      					"isMultiKey" : false,
      					"scanAndOrder" : false,
      					"indexBounds" : {
      						"username" : [
      							[
      								"user8",
      								"user9"
      							],
      							[
      								/^user8/,
      								/^user8/
      							]
      						]
      					},
      					"allPlans" : [
      						{
      							"cursor" : "BtreeCursor username_1",
      							"isMultiKey" : false,
      							"scanAndOrder" : false,
      							"indexBounds" : {
      								"username" : [
      									[
      										"user8",
      										"user9"
      									],
      									[
      										/^user8/,
      										/^user8/
      									]
      								]
      							}
      						}
      					]
      				}
      			}
      		},
      		{
      			"$group" : {
      				"_id" : {
      					"$const" : 1
      				},
      				"count" : {
      					"$sum" : {
      						"$const" : 1
      					}
      				}
      			}
      		}
      	],
      	"ok" : 1
      }
      

            Assignee:
            backlog-query-optimization [DO NOT USE] Backlog - Query Optimization
            Reporter:
            asya.kamsky@mongodb.com Asya Kamsky
            Votes:
            2 Vote for this issue
            Watchers:
            25 Start watching this issue

              Created:
              Updated: