Question about semantics of $ variables in json explain plans in 13

From: Jerry Brenner <jbrenner(at)guidewire(dot)com>
To: pgsql-performance(at)lists(dot)postgresql(dot)org
Cc: Jim Sinkovic <jsinkovic(at)guidewire(dot)com>, Danny Lam <dlam(at)guidewire(dot)com>
Subject: Question about semantics of $ variables in json explain plans in 13
Date: 2023-12-08 23:09:52
Message-ID: CACoKFYQkvuc7399AyD2p7Q1pVENpT1C-sXsXtxHjhamMnuAGoQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Attachment Content-Type Size
pc_message415bc88c610f40c448a9c7a3eb19b704_fullqueryplan.json application/json 11.1 KB

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Jerry Brenner 2023-12-09 00:23:41 2 json explain plans for the same query/plan - why does one have constants while the other has parameter markers?
Previous Message Julien Rouhaud 2023-12-06 06:45:07 Re: Include a timestamp in future versions of pg_stat_statements when when a query entered the cache?