Re: explain and PARAM_EXEC - Mailing list pgsql-hackers

Tom Lane wrote:
> Anyway, I hadn't looked at your patch before, but now that I have, it's
> not even approximately what I was suggesting.  What I thought you should
> do was change ruleutils.c to print the parameter expressions at the call
> site, ie in the T_SubPlan and T_AlternativeSubPlan cases in get_rule_expr.
>
See patch below against HEAD.

Example of query against catalog:

postgres=# explain verbose 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_catalog.pg_class b  (cost=0.00..2459.64 rows=296 width=12)
   Output: ((b.oid)::integer + 1), SubPlan 1 ($0 := b.relfilenode, $1 :=
b.relnamespace)
   SubPlan 1
     ->  Index Scan using pg_class_oid_index on pg_catalog.pg_class a
(cost=0.00..8.27 rows=1 width=4)
           Output: a.oid
           Index Cond: (a.oid = $0)
           Filter: (a.relnamespace = $1)
(7 rows)

Explain of alternative subplan (query comes from aggregates.sql in
regression).

regression=# explain verbose select ten, sum(distinct four) from onek a
group by ten
having exists (select 1 from onek b where sum(distinct a.four) = b.four);
                                      QUERY
PLAN
--------------------------------------------------------------------------------------
 GroupAggregate  (cost=197.83..216.52 rows=10 width=8)
   Output: a.ten, sum(DISTINCT a.four)
   Filter: (alternatives: SubPlan 1 ($0 := sum(DISTINCT a.four)) or
hashed SubPlan 2)
   ->  Sort  (cost=197.83..200.33 rows=1000 width=8)
         Output: a.ten, a.four
         Sort Key: a.ten
         ->  Seq Scan on public.onek a  (cost=0.00..148.00 rows=1000
width=8)
               Output: a.ten, a.four
   SubPlan 1
     ->  Seq Scan on public.onek b  (cost=0.00..150.50 rows=250 width=0)
           Filter: ($0 = b.four)
   SubPlan 2
     ->  Seq Scan on public.onek b  (cost=0.00..148.00 rows=1000 width=4)
           Output: b.four
(14 rows)

Would the explain above be better if the filter with subplans arguments
showed the EXISTS keyword? Much code from get_sublink_expr could be
reused to show additional info depending on the sublink type and testexpr.

Other info: there are now only appends to the context->buf. The problems
with OUTER var I mentioned earlier are gone now arguments are deparsed
using get_rule_expr instead of deparse_expression.

regards,
Yeb Havinga

diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index d16f1c4..68302e4 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -206,6 +206,7 @@ static void get_const_expr(Const *constval, deparse_context *context,
                int showtype);
 static void simple_quote_literal(StringInfo buf, const char *val);
 static void get_sublink_expr(SubLink *sublink, deparse_context *context);
+static void get_subplan_reference(SubPlan *subplan, deparse_context *context);
 static void get_from_clause(Query *query, const char *prefix,
                 deparse_context *context);
 static void get_from_clause_item(Node *jtnode, Query *query,
@@ -4647,20 +4648,13 @@ get_rule_expr(Node *node, deparse_context *context,
             break;

         case T_SubPlan:
-            {
-                SubPlan    *subplan = (SubPlan *) node;
-
-                /*
-                 * We cannot see an already-planned subplan in rule deparsing,
-                 * only while EXPLAINing a query plan.    We don't try to
-                 * reconstruct the original SQL, just reference the subplan
-                 * that appears elsewhere in EXPLAIN's result.
-                 */
-                if (subplan->useHashTable)
-                    appendStringInfo(buf, "(hashed %s)", subplan->plan_name);
-                else
-                    appendStringInfo(buf, "(%s)", subplan->plan_name);
-            }
+            /*
+             * We cannot see an already-planned subplan in rule deparsing, only
+             * while EXPLAINing a query plan. We don't try to reconstruct the
+             * original SQL, just reference the subplan that appears elsewhere
+             * in EXPLAIN's result.
+             */
+            get_subplan_reference((SubPlan *) node, context);
             break;

         case T_AlternativeSubPlan:
@@ -4673,12 +4667,9 @@ get_rule_expr(Node *node, deparse_context *context,
                 foreach(lc, asplan->subplans)
                 {
                     SubPlan    *splan = (SubPlan *) lfirst(lc);
-
                     Assert(IsA(splan, SubPlan));
-                    if (splan->useHashTable)
-                        appendStringInfo(buf, "hashed %s", splan->plan_name);
-                    else
-                        appendStringInfo(buf, "%s", splan->plan_name);
+                    get_subplan_reference(splan, context);
+
                     if (lnext(lc))
                         appendStringInfo(buf, " or ");
                 }
@@ -5934,6 +5925,43 @@ get_sublink_expr(SubLink *sublink, deparse_context *context)


 /* ----------
+ * get_subplan_reference            - Parse back a subplan reference
+ *
+ * ----------
+ */
+static void
+get_subplan_reference(SubPlan *subplan, deparse_context *context)
+{
+    StringInfo buf = context->buf;
+
+    if (subplan->useHashTable)
+        appendStringInfo(buf, "hashed ");
+
+    appendStringInfo(buf, "%s", subplan->plan_name);
+
+    /* Add the subplan arguments */
+    if (list_length(subplan->args) > 0)
+    {
+        ListCell *l;
+        int i = 0;
+
+        foreach(l, subplan->args)
+        {
+            Node *n = lfirst(l);
+            appendStringInfo(buf, "%s$%d := ",
+                             (i == 0) ? " (" : ", ",
+                             i);
+
+            get_rule_expr(n, context, true);
+            i++;
+        }
+
+        appendStringInfoChar(buf, ')');
+    }
+}
+
+
+/* ----------
  * get_from_clause            - Parse back a FROM clause
  *
  * "prefix" is the keyword that denotes the start of the list of FROM