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.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.