[SERVER-34455] Consider distinguishing parsed query optimizations in the plan cache shapes Created: 13/Apr/18  Updated: 06/Dec/22  Resolved: 08/Jun/18

Status: Closed
Project: Core Server
Component/s: Querying
Affects Version/s: None
Fix Version/s: None

Type: Improvement Priority: Major - P3
Reporter: Chris Harris Assignee: Backlog - Query Team (Inactive)
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
duplicates SERVER-23332 Expose query plan cache key in system... Closed
Related
related to SERVER-23332 Expose query plan cache key in system... Closed
Assigned Teams:
Query
Participants:
Case:

 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" : {
			
		}
	}
]



 Comments   
Comment by Ian Whalen (Inactive) [ 08/Jun/18 ]

christopher.harris we're closing this as a dupe of SERVER-23332. Please talk to Asya if you have any questions.

Comment by Ian Whalen (Inactive) [ 18/May/18 ]

christopher.harris ping on the above question?

Comment by Ian Whalen (Inactive) [ 27/Apr/18 ]

christopher.harris does this just boil down to adding cache key information in the plan cache introspection commands similar to the linked ticket for log lines (SERVER-23332)?

Generated at Thu Feb 08 04:36:44 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.