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

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

XMLWordPrintableJSON

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

      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: