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 100000 Thanks, Jerry
pc_message415bc88c610f40c448a9c7a3eb19b704_fullqueryplan.json
Description: application/json