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

Consider distinguishing parsed query optimizations in the plan cache shapes

    XMLWordPrintable

Details

    • Improvement
    • Status: Closed
    • Major - P3
    • Resolution: Duplicate
    • None
    • None
    • Querying
    • None

    Description

      Some query shape optimizations are not reflected in an obvious way by the cached plans. For example, the parser appears to transform a single value $in list into an equality when parsing the query:

      > c.find({x:{$in:[3]}}).explain().queryPlanner.parsedQuery
      { "x" : { "$eq" : 3 } }
      > c.find({x:{$in:[6,7]}}).explain().queryPlanner.parsedQuery
      { "x" : { "$in" : [ 6, 7 ] } }
      

      However, the shape reported by the plan cache does not visually reflect those optimizations. This means that the following situations can occur:

      • Two entries will appear to have the same shape - one with a single value for the $in list and the other with a larger number of values.
      • A single entry will be present reflecting both a direct equality query and an optimized single value $in list query. The representation will depend on variant of the shape present at the time it is cached.

      > db.version()
      3.6.2
      > c.getPlanCache().listQueryShapes()
      [ ]
      > c.find({x:{$in:[3]}})
      > c.getPlanCache().listQueryShapes()
      [
      	{
      		"query" : {
      			"x" : {
      				"$in" : [
      					3
      				]
      			}
      		},
      		"sort" : {
      			
      		},
      		"projection" : {
      			
      		}
      	}
      ]
      > c.find({x:5})
      > c.getPlanCache().listQueryShapes()
      [
      	{
      		"query" : {
      			"x" : {
      				"$in" : [
      					3
      				]
      			}
      		},
      		"sort" : {
      			
      		},
      		"projection" : {
      			
      		}
      	}
      ]
      > c.find({x:{$in:[6,7]}})
      > c.getPlanCache().listQueryShapes()
      [
      	{
      		"query" : {
      			"x" : {
      				"$in" : [
      					6,
      					7
      				]
      			}
      		},
      		"sort" : {
      			
      		},
      		"projection" : {
      			
      		}
      	},
      	{
      		"query" : {
      			"x" : {
      				"$in" : [
      					3
      				]
      			}
      		},
      		"sort" : {
      			
      		},
      		"projection" : {
      			
      		}
      	}
      ]
      > c.getPlanCache().clear()
      > c.getPlanCache().listQueryShapes()
      [ ]
      > c.find({x:5})
      > c.getPlanCache().listQueryShapes()
      [
      	{
      		"query" : {
      			"x" : 5
      		},
      		"sort" : {
      			
      		},
      		"projection" : {
      			
      		}
      	}
      ]
      > c.find({x:{$in:[3]}})
      > c.getPlanCache().listQueryShapes()
      [
      	{
      		"query" : {
      			"x" : 5
      		},
      		"sort" : {
      			
      		},
      		"projection" : {
      			
      		}
      	}
      ]
      > c.find({x:{$in:[6,7]}})
      > c.getPlanCache().listQueryShapes()
      [
      	{
      		"query" : {
      			"x" : {
      				"$in" : [
      					6,
      					7
      				]
      			}
      		},
      		"sort" : {
      			
      		},
      		"projection" : {
      			
      		}
      	},
      	{
      		"query" : {
      			"x" : 5
      		},
      		"sort" : {
      			
      		},
      		"projection" : {
      			
      		}
      	}
      ]
      

      For completeness for this specific example, other changes in the length of the $in list appear to have the same query shape as intuitively expected:

      >c.getPlanCache().clear()
      > c.find({x:{$in:[6,7]}})
      > c.getPlanCache().listQueryShapes()
      [
      	{
      		"query" : {
      			"x" : {
      				"$in" : [
      					6,
      					7
      				]
      			}
      		},
      		"sort" : {
      			
      		},
      		"projection" : {
      			
      		}
      	}
      ]
      > c.find({x:{$in:[8,9,10]}})
      > c.getPlanCache().listQueryShapes()
      [
      	{
      		"query" : {
      			"x" : {
      				"$in" : [
      					6,
      					7
      				]
      			}
      		},
      		"sort" : {
      			
      		},
      		"projection" : {
      			
      		}
      	}
      ]
      

      Attachments

        Issue Links

          Activity

            People

              backlog-server-query Backlog - Query Team (Inactive)
              christopher.harris@mongodb.com Christopher Harris
              Votes:
              0 Vote for this issue
              Watchers:
              8 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: