Question about semantics of $ variables in json explain plans in 13
Is there any documentation on the semantics of $ variables in json explain
plans for both InitPlans and SubPlans in 13?
I'm trying to understand the attached json file.
- It looks like $0 represents the value from the outer query block when
the correlated subquery is evaluated
- It looks like $1 represents the result of the subquery evaluation
Here are the relevant lines from the plan. (I've attached the full plan as
a file.):
"Node Type": "Subquery Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Alias": "ANY_subquery",
"Filter": "(qroot.sendorder = \"ANY_subquery\".col0)",
"Plans": [
{
"Node Type": "Result",
"Parent Relationship": "Subquery",
"Parallel Aware": false,
"Plans": [
{
"Node Type": "Limit",
"Parent Relationship": "InitPlan",
"Subplan Name": "InitPlan 1 (returns $1)",
"Plans": [
{
"Node Type": "Index Only Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Scan Direction": "Forward",
"Index Name":
"message_u_destinatio_1kk5be278gggc",
"Relation Name": "pc_message",
"Alias": "qroot0",
"Index Cond": "((destinationid = 67) AND
(contactid = $0) AND (sendorder IS NOT NULL))",
Here's a formatted version of the query from the json file:
SELECT /* ISNULL:pc_message.FrozenSetID:, KeyTable:pc_message; */ qRoot.ID
col0, qRoot.CreationTime col1
FROM pc_message qRoot
WHERE qRoot.DestinationID = $1 AND qRoot.Status = $2 AND qRoot.contactID IS
NOT NULL AND qRoot.FrozenSetID IS NULL AND qRoot.SendOrder IN
(
SELECT MIN (qRoot0.SendOrder) col0
FROM pc_message qRoot0
WHERE qRoot0.DestinationID = $3 AND qRoot0.contactID =
qRoot.contactID)
ORDER BY col1 ASC, col0 ASC LIMIT 10
Thanks,
Jerry
pc_message415bc88c610f40c448a9c7a3eb19b704_fullqueryplan.json
Description: application/json
2 json explain plans for the same query/plan - why does one have constants while the other has parameter markers?
We are currently on 13. We are capturing the explain plans for query executions taking 1 second or longer and storing the json files. We are most of the way through implementing a home grown solution to generate a consistent hash value for a query plan, so we can find queries with multiple plans. I've attached 2 query plans that we've captured that differ in a seemingly strange way. (All executions are from the same exact code path.) One of the plans has parameter markers in the predicates in the values for "Recheck Cond" and "Index Cond", while the other does not. Any insight into why we are seeing parameter markers in the body of the query plan? Examples of the parameter markers: "Recheck Cond": "*((destinationid = $1)* AND (contactid IS NOT NULL) AND (status = $2))", "Index Cond": "*((destinationid = $1)* AND (contactid IS NOT NULL) AND (status = $2))", What we normally see: "Recheck Cond": "(*(destinationid = 67) *AND (contactid IS NOT NULL) AND (status = 1))", "Index Cond": "*((destinationid = 67) *AND (contactid IS NOT NULL) AND (status = 1))", The full query text: SELECT /* ISNULL:pc_message.FrozenSetID:, KeyTable:pc_message; */ qRoot.ID col0, qRoot.CreationTime col1 FROM pc_message qRoot WHERE qRoot.DestinationID = $1 AND qRoot.Status = $2 AND qRoot.contactID IS NOT NULL AND qRoot.FrozenSetID IS NULL AND qRoot.SendOrder IN ( SELECT MIN (qRoot0.SendOrder) col0 FROM pc_message qRoot0 WHERE qRoot0.DestinationID = $3 AND qRoot0.contactID = qRoot.contactID) ORDER BY col1 ASC, col0 ASC LIMIT 10 Thanks, Jerry pc_message674c4226db3858b916bd6d363b52f9f1_fullqueryplanwithparametermarkers.json Description: application/json pc_message415bc88c610f40c448a9c7a3eb19b704_fullqueryplanwithconstants.json Description: application/json
Re: 2 json explain plans for the same query/plan - why does one have constants while the other has parameter markers?
Jerry Brenner writes: > We are currently on 13. We are capturing the explain plans for query > executions taking 1 second or longer and storing the json files. We are > most of the way through implementing a home grown solution to generate a > consistent hash value for a query plan, so we can find queries with > multiple plans. I've attached 2 query plans that we've captured that > differ in a seemingly strange way. (All executions are from the same exact > code path.) One of the plans has parameter markers in the predicates in > the values for "Recheck Cond" and "Index Cond", while the other does not. > Any insight into why we are seeing parameter markers in the body of the > query plan? The one with parameter markers is a "generic" plan for a parameterized query. When you get a plan without parameter markers for the same input query, that's a "custom" plan in which concrete values of the parameters have been substituted, possibly allowing const-simplification and more accurate rowcount estimates. The backend will generally try custom plans a few times and then try a generic plan to see if that's meaningfully slower -- if not, replanning each time is deemed to be wasteful. regards, tom lane
Re: Question about semantics of $ variables in json explain plans in 13
Jerry Brenner writes: > Is there any documentation on the semantics of $ variables in json explain > plans for both InitPlans and SubPlans in 13? I don't think there's anything much in the user-facing docs, which is somewhat unfortunate because it's confusing: the notation is overloaded. $N could be a parameter supplied from outside the query (as in your $1, $2 and $3 in the source text), but it could also be a parameter supplied from an outer query level to a subplan, or it could be the result value of an InitPlan. The numbering of outside-the-query parameters is disjoint from that of the other kind. >- It looks like $0 represents the value from the outer query block when >the correlated subquery is evaluated >- It looks like $1 represents the result of the subquery evaluation Yeah, I think you're right here. $0 evidently corresponds to qRoot.contactID from the outer plan, and the plan label itself shows that $1 carries the sub-select's value back out. This $1 is unrelated to the $1 you wrote in the query text. (It looks like this is a custom plan in which "67" was explicitly substituted for your $3. Presumably $1 and $2 were replaced as well; we don't do half-custom plans.) regards, tom lane
Re: 2 json explain plans for the same query/plan - why does one have constants while the other has parameter markers?
Thanks for the quick response! That was very helpful! My impression is that almost all of the plans being captured are "custom", but now I know that I need to look closer. We also store the execution times, so we can look at the execution order for queries that are executed often enough to seem like they should stay in the cache. The addition of the new timestamp columns in pg_stat_statements in 17 will also help us get a better sense of how long the query had been in the cache. On Fri, Dec 8, 2023 at 4:44 PM Tom Lane wrote: > Jerry Brenner writes: > > We are currently on 13. We are capturing the explain plans for query > > executions taking 1 second or longer and storing the json files. We are > > most of the way through implementing a home grown solution to generate a > > consistent hash value for a query plan, so we can find queries with > > multiple plans. I've attached 2 query plans that we've captured that > > differ in a seemingly strange way. (All executions are from the same > exact > > code path.) One of the plans has parameter markers in the predicates in > > the values for "Recheck Cond" and "Index Cond", while the other does not. > > Any insight into why we are seeing parameter markers in the body of the > > query plan? > > The one with parameter markers is a "generic" plan for a parameterized > query. When you get a plan without parameter markers for the same > input query, that's a "custom" plan in which concrete values of the > parameters have been substituted, possibly allowing const-simplification > and more accurate rowcount estimates. The backend will generally try > custom plans a few times and then try a generic plan to see if that's > meaningfully slower -- if not, replanning each time is deemed to be > wasteful. > > regards, tom lane > >
