Re: explain and PARAM_EXEC

Tom Lane wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>
>> So I guess there are two issues here: (1) somehow I feel like we
>> should be telling the user what expression is being used to initialize
>> $0, $1, etc. when they are PARAM_EXEC parameters;
>>
>
> Maybe, but the only reasonable place to put it would be within the
> (SubPlan N) reference, which is not a place where verboseness would be
> appreciated, I think. In principle it could look something like
>
> (SubPlan N ($0 := b.oid))
>
> but with a few parameters and a bunch of other stuff on the same line
> that would get out of hand.
>
I'm currently digesting subplan/initplan handling and I really like
Robert's idea of showing actual parameters.

postgres=# explain select oid::int + 1,(select oid from pg_class a where
a.oid = b.relfilenode and a.relnamespace = b.relnamespace) from pg_class b;
QUERY
PLAN
---------------------------------------------------------------------------------------------
Seq Scan on pg_class b (cost=0.00..2459.64 rows=296 width=12)
SubPlan 1 ($0 := b.relfilenode, $1 := b.relnamespace)
-> Index Scan using pg_class_oid_index on pg_class a
(cost=0.00..8.27 rows=1 width=4)
Index Cond: (oid = $0)
Filter: (relnamespace = $1)
(5 rows)

Only changes in ExplainSubPlans, all regression tests passed (which
surprized me a bit, no explains with subplanes in expected results?).
NB: this is not a patch to HEAD but a local version, so line numbers are
off, patch supplied for discussion purposes only.

kind regards,
Yeb Havinga

diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 56d9c5b..454d59b 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -1686,20 +1686,47 @@ static void
ExplainSubPlans(List *plans, const char *relationship, ExplainState *es)
{
ListCell *lst;

foreach(lst, plans)
{
SubPlanState *sps = (SubPlanState *) lfirst(lst);
- SubPlan *sp = (SubPlan *) sps->xprstate.expr;
+ SubPlan *sp = (SubPlan *) sps->xprstate.expr;
+ StringInfo signature = makeStringInfo();
+ int i = 0;
+ List *context;
+ bool useprefix;
+ ListCell *c;
+
+ context = deparse_context_for_plan((Node
*)exec_subplan_get_plan(es->pstmt, sp),
+
NULL,
+
es->rtable,
+
es->pstmt->subplans);
+ useprefix = list_length(es->rtable) > 1;
+
+ appendStringInfoString(signature, sp->plan_name);
+
+ foreach(c, sp->args)
+ {
+ Node *n = lfirst(c);
+ appendStringInfo(signature, "%s$%d := %s",
+ (i == 0) ? " ("
: ", ",
+ i,
+
deparse_expression(n, context, useprefix, true));
+ i++;
+ }
+
+ if (i > 0)
+ appendStringInfoString(signature, ")");

ExplainNode(exec_subplan_get_plan(es->pstmt, sp),
sps->planstate,
NULL,
- relationship, sp->plan_name,
+ relationship,
+ signature->data,
es);
}
}

/*
* Explain a property, such as sort keys or targets, that takes the form of
* a list of unlabeled items. "data" is a list of C strings.