pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedQuestion about semantics of $ variables in json explain plans in 13
2+ messages / 2 participants
[nested] [flat]
* Question about semantics of $ variables in json explain plans in 13
@ 2023-12-08 23:09 Jerry Brenner <jbrenner@guidewire.com>
0 siblings, 1 reply; 2+ messages in thread
From: Jerry Brenner @ 2023-12-08 23:09 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org; +Cc: Jim Sinkovic <jsinkovic@guidewire.com>; Danny Lam <dlam@guidewire.com>
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
Attachments:
[application/json] pc_message415bc88c610f40c448a9c7a3eb19b704_fullqueryplan.json (11.1K, ../../CACoKFYQkvuc7399AyD2p7Q1pVENpT1C-sXsXtxHjhamMnuAGoQ@mail.gmail.com/3-pc_message415bc88c610f40c448a9c7a3eb19b704_fullqueryplan.json)
download
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Question about semantics of $ variables in json explain plans in 13
@ 2023-12-09 01:03 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jerry Brenner <jbrenner@guidewire.com>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2023-12-09 01:03 UTC (permalink / raw)
To: Jerry Brenner <jbrenner@guidewire.com>; +Cc: pgsql-performance@lists.postgresql.org, Jim Sinkovic <jsinkovic@guidewire.com>; Danny Lam <dlam@guidewire.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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2023-12-09 01:03 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-12-08 23:09 Question about semantics of $ variables in json explain plans in 13 Jerry Brenner <jbrenner@guidewire.com>
2023-12-09 01:03 ` Tom Lane <tgl@sss.pgh.pa.us>
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