From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jerry Brenner <jbrenner@guidewire.com>
Cc: pgsql-performance@lists.postgresql.org, Jim Sinkovic <jsinkovic@guidewire.com>
Cc: Danny Lam <dlam@guidewire.com>
Subject: Re: Question about semantics of $ variables in json explain plans in 13
Date: Fri, 08 Dec 2023 20:03:51 -0500
Message-ID: <1568643.1702083831@sss.pgh.pa.us> (raw)
In-Reply-To: <CACoKFYQkvuc7399AyD2p7Q1pVENpT1C-sXsXtxHjhamMnuAGoQ@mail.gmail.com>
References: <CACoKFYQkvuc7399AyD2p7Q1pVENpT1C-sXsXtxHjhamMnuAGoQ@mail.gmail.com>
Jerry Brenner <jbrenner@guidewire.com> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, jbrenner@guidewire.com, jsinkovic@guidewire.com, dlam@guidewire.com
Subject: Re: Question about semantics of $ variables in json explain plans in 13
In-Reply-To: <1568643.1702083831@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox