Re: explain and PARAM_EXEC

Tom Lane wrote:
> Robert Haas <robertmhaas@gmail.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.64rows=296 width=12)  SubPlan 1 ($0 := b.relfilenode, $1 := b.relnamespace)    ->  Index Scan using
pg_class_oid_indexon 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 voidExplainSubPlans(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);       }}/* *
Explaina property, such as sort keys or targets, that takes the form of * a list of unlabeled items.  "data" is a list
ofC strings.