Re: Reference to parent query from ANY sublink

Kevin Grittner <kgrittn@ymail.com> writes:
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> FWIW, that plan isn't obviously wrong; if it is broken, most
>> likely the reason is that the HashAggregate is incorrectly
>> unique-ifying the lower table.� (Unfortunately, EXPLAIN doesn't
>> show enough about the HashAgg to know what it's doing exactly.)

> Yeah, I found myself wishing for an EXPLAIN option that would show
> that.

It's not hard to do ... how about the attached?

I chose to print grouping keys for both Agg and Group nodes, and to
show them unconditionally.  There's some case maybe for only including
them in verbose mode, but since sort keys are shown unconditionally,
it seemed more consistent this way.

            regards, tom lane

diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index bd5428d..9969a25 100644
*** a/src/backend/commands/explain.c
--- b/src/backend/commands/explain.c
*************** static void show_sort_keys(SortState *so
*** 76,84 ****
                 ExplainState *es);
  static void show_merge_append_keys(MergeAppendState *mstate, List *ancestors,
                         ExplainState *es);
! static void show_sort_keys_common(PlanState *planstate,
!                       int nkeys, AttrNumber *keycols,
!                       List *ancestors, ExplainState *es);
  static void show_sort_info(SortState *sortstate, ExplainState *es);
  static void show_hash_info(HashState *hashstate, ExplainState *es);
  static void show_instrumentation_count(const char *qlabel, int which,
--- 76,88 ----
                 ExplainState *es);
  static void show_merge_append_keys(MergeAppendState *mstate, List *ancestors,
                         ExplainState *es);
! static void show_agg_keys(AggState *astate, List *ancestors,
!               ExplainState *es);
! static void show_group_keys(GroupState *gstate, List *ancestors,
!                 ExplainState *es);
! static void show_sort_group_keys(PlanState *planstate, const char *qlabel,
!                      int nkeys, AttrNumber *keycols,
!                      List *ancestors, ExplainState *es);
  static void show_sort_info(SortState *sortstate, ExplainState *es);
  static void show_hash_info(HashState *hashstate, ExplainState *es);
  static void show_instrumentation_count(const char *qlabel, int which,
*************** ExplainNode(PlanState *planstate, List *
*** 1341,1347 ****
--- 1345,1358 ----
                                             planstate, es);
              break;
          case T_Agg:
+             show_agg_keys((AggState *) planstate, ancestors, es);
+             show_upper_qual(plan->qual, "Filter", planstate, ancestors, es);
+             if (plan->qual)
+                 show_instrumentation_count("Rows Removed by Filter", 1,
+                                            planstate, es);
+             break;
          case T_Group:
+             show_group_keys((GroupState *) planstate, ancestors, es);
              show_upper_qual(plan->qual, "Filter", planstate, ancestors, es);
              if (plan->qual)
                  show_instrumentation_count("Rows Removed by Filter", 1,
*************** show_sort_keys(SortState *sortstate, Lis
*** 1693,1701 ****
  {
      Sort       *plan = (Sort *) sortstate->ss.ps.plan;

!     show_sort_keys_common((PlanState *) sortstate,
!                           plan->numCols, plan->sortColIdx,
!                           ancestors, es);
  }

  /*
--- 1704,1712 ----
  {
      Sort       *plan = (Sort *) sortstate->ss.ps.plan;

!     show_sort_group_keys((PlanState *) sortstate, "Sort Key",
!                          plan->numCols, plan->sortColIdx,
!                          ancestors, es);
  }

  /*
*************** show_merge_append_keys(MergeAppendState
*** 1707,1720 ****
  {
      MergeAppend *plan = (MergeAppend *) mstate->ps.plan;

!     show_sort_keys_common((PlanState *) mstate,
!                           plan->numCols, plan->sortColIdx,
!                           ancestors, es);
  }

  static void
! show_sort_keys_common(PlanState *planstate, int nkeys, AttrNumber *keycols,
!                       List *ancestors, ExplainState *es)
  {
      Plan       *plan = planstate->plan;
      List       *context;
--- 1718,1773 ----
  {
      MergeAppend *plan = (MergeAppend *) mstate->ps.plan;

!     show_sort_group_keys((PlanState *) mstate, "Sort Key",
!                          plan->numCols, plan->sortColIdx,
!                          ancestors, es);
  }

+ /*
+  * Show the grouping keys for an Agg node.
+  */
  static void
! show_agg_keys(AggState *astate, List *ancestors,
!               ExplainState *es)
! {
!     Agg           *plan = (Agg *) astate->ss.ps.plan;
!
!     if (plan->numCols > 0)
!     {
!         /* The key columns refer to the tlist of the child plan */
!         ancestors = lcons(astate, ancestors);
!         show_sort_group_keys(outerPlanState(astate), "Group Key",
!                              plan->numCols, plan->grpColIdx,
!                              ancestors, es);
!         ancestors = list_delete_first(ancestors);
!     }
! }
!
! /*
!  * Show the grouping keys for a Group node.
!  */
! static void
! show_group_keys(GroupState *gstate, List *ancestors,
!                 ExplainState *es)
! {
!     Group       *plan = (Group *) gstate->ss.ps.plan;
!
!     /* The key columns refer to the tlist of the child plan */
!     ancestors = lcons(gstate, ancestors);
!     show_sort_group_keys(outerPlanState(gstate), "Group Key",
!                          plan->numCols, plan->grpColIdx,
!                          ancestors, es);
!     ancestors = list_delete_first(ancestors);
! }
!
! /*
!  * Common code to show sort/group keys, which are represented in plan nodes
!  * as arrays of targetlist indexes
!  */
! static void
! show_sort_group_keys(PlanState *planstate, const char *qlabel,
!                      int nkeys, AttrNumber *keycols,
!                      List *ancestors, ExplainState *es)
  {
      Plan       *plan = planstate->plan;
      List       *context;
*************** show_sort_keys_common(PlanState *plansta
*** 1748,1754 ****
          result = lappend(result, exprstr);
      }

!     ExplainPropertyList("Sort Key", result, es);
  }

  /*
--- 1801,1807 ----
          result = lappend(result, exprstr);
      }

!     ExplainPropertyList(qlabel, result, es);
  }

  /*
diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out
index c05b39c..1a0ca5c 100644
*** a/src/test/regress/expected/aggregates.out
--- b/src/test/regress/expected/aggregates.out
*************** explain (costs off)
*** 659,670 ****
                               QUERY PLAN
  ---------------------------------------------------------------------
   HashAggregate
     InitPlan 1 (returns $0)
       ->  Limit
             ->  Index Only Scan Backward using tenk1_unique2 on tenk1
                   Index Cond: (unique2 IS NOT NULL)
     ->  Result
! (6 rows)

  select distinct max(unique2) from tenk1;
   max
--- 659,671 ----
                               QUERY PLAN
  ---------------------------------------------------------------------
   HashAggregate
+    Group Key: $0
     InitPlan 1 (returns $0)
       ->  Limit
             ->  Index Only Scan Backward using tenk1_unique2 on tenk1
                   Index Cond: (unique2 IS NOT NULL)
     ->  Result
! (7 rows)

  select distinct max(unique2) from tenk1;
   max
*************** explain (costs off)
*** 806,811 ****
--- 807,813 ----
                                            QUERY PLAN
  ----------------------------------------------------------------------------------------------
   HashAggregate
+    Group Key: $0, $1
     InitPlan 1 (returns $0)
       ->  Limit
             ->  Merge Append
*************** explain (costs off)
*** 831,837 ****
                   ->  Index Only Scan Backward using minmaxtest3i on minmaxtest3 minmaxtest3_1
                         Index Cond: (f1 IS NOT NULL)
     ->  Result
! (26 rows)

  select distinct min(f1), max(f1) from minmaxtest;
   min | max
--- 833,839 ----
                   ->  Index Only Scan Backward using minmaxtest3i on minmaxtest3 minmaxtest3_1
                         Index Cond: (f1 IS NOT NULL)
     ->  Result
! (27 rows)

  select distinct min(f1), max(f1) from minmaxtest;
   min | max
diff --git a/src/test/regress/expected/matview.out b/src/test/regress/expected/matview.out
index 9ba2b9a..31751eb 100644
*** a/src/test/regress/expected/matview.out
--- b/src/test/regress/expected/matview.out
*************** EXPLAIN (costs off)
*** 22,29 ****
       QUERY PLAN
  ---------------------
   HashAggregate
     ->  Seq Scan on t
! (2 rows)

  CREATE MATERIALIZED VIEW tm AS SELECT type, sum(amt) AS totamt FROM t GROUP BY type WITH NO DATA;
  SELECT relispopulated FROM pg_class WHERE oid = 'tm'::regclass;
--- 22,30 ----
       QUERY PLAN
  ---------------------
   HashAggregate
+    Group Key: type
     ->  Seq Scan on t
! (3 rows)

  CREATE MATERIALIZED VIEW tm AS SELECT type, sum(amt) AS totamt FROM t GROUP BY type WITH NO DATA;
  SELECT relispopulated FROM pg_class WHERE oid = 'tm'::regclass;
*************** EXPLAIN (costs off)
*** 59,66 ****
   Sort
     Sort Key: t.type
     ->  HashAggregate
           ->  Seq Scan on t
! (4 rows)

  CREATE MATERIALIZED VIEW tvm AS SELECT * FROM tv ORDER BY type;
  SELECT * FROM tvm;
--- 60,68 ----
   Sort
     Sort Key: t.type
     ->  HashAggregate
+          Group Key: t.type
           ->  Seq Scan on t
! (5 rows)

  CREATE MATERIALIZED VIEW tvm AS SELECT * FROM tv ORDER BY type;
  SELECT * FROM tvm;
*************** EXPLAIN (costs off)
*** 82,89 ****
  ---------------------------
   Aggregate
     ->  HashAggregate
           ->  Seq Scan on t
! (3 rows)

  CREATE MATERIALIZED VIEW tvvm AS SELECT * FROM tvv;
  CREATE VIEW tvvmv AS SELECT * FROM tvvm;
--- 84,92 ----
  ---------------------------
   Aggregate
     ->  HashAggregate
+          Group Key: t.type
           ->  Seq Scan on t
! (4 rows)

  CREATE MATERIALIZED VIEW tvvm AS SELECT * FROM tvv;
  CREATE VIEW tvvmv AS SELECT * FROM tvvm;
diff --git a/src/test/regress/expected/union.out b/src/test/regress/expected/union.out
index ae690cf..6f9ee5e 100644
*** a/src/test/regress/expected/union.out
--- b/src/test/regress/expected/union.out
*************** explain (costs off)
*** 494,505 ****
                      QUERY PLAN
  ---------------------------------------------------
   HashAggregate
     ->  Append
           ->  Index Scan using t1_ab_idx on t1
                 Index Cond: ((a || b) = 'ab'::text)
           ->  Index Only Scan using t2_pkey on t2
                 Index Cond: (ab = 'ab'::text)
! (6 rows)

  reset enable_seqscan;
  reset enable_indexscan;
--- 494,506 ----
                      QUERY PLAN
  ---------------------------------------------------
   HashAggregate
+    Group Key: ((t1.a || t1.b))
     ->  Append
           ->  Index Scan using t1_ab_idx on t1
                 Index Cond: ((a || b) = 'ab'::text)
           ->  Index Only Scan using t2_pkey on t2
                 Index Cond: (ab = 'ab'::text)
! (7 rows)

  reset enable_seqscan;
  reset enable_indexscan;
*************** SELECT * FROM
*** 552,568 ****
     SELECT 2 AS t, 4 AS x) ss
  WHERE x < 4
  ORDER BY x;
!            QUERY PLAN
! --------------------------------
   Sort
     Sort Key: ss.x
     ->  Subquery Scan on ss
           Filter: (ss.x < 4)
           ->  HashAggregate
                 ->  Append
                       ->  Result
                       ->  Result
! (8 rows)

  SELECT * FROM
    (SELECT 1 AS t, generate_series(1,10) AS x
--- 553,570 ----
     SELECT 2 AS t, 4 AS x) ss
  WHERE x < 4
  ORDER BY x;
!                        QUERY PLAN
! --------------------------------------------------------
   Sort
     Sort Key: ss.x
     ->  Subquery Scan on ss
           Filter: (ss.x < 4)
           ->  HashAggregate
+                Group Key: (1), (generate_series(1, 10))
                 ->  Append
                       ->  Result
                       ->  Result
! (9 rows)

  SELECT * FROM
    (SELECT 1 AS t, generate_series(1,10) AS x
diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out
index 1e6365b..0f21fcb 100644
*** a/src/test/regress/expected/window.out
--- b/src/test/regress/expected/window.out
*************** explain (costs off)
*** 619,630 ****
  select first_value(max(x)) over (), y
    from (select unique1 as x, ten+four as y from tenk1) ss
    group by y;
!           QUERY PLAN
! -------------------------------
   WindowAgg
     ->  HashAggregate
           ->  Seq Scan on tenk1
! (3 rows)

  -- test non-default frame specifications
  SELECT four, ten,
--- 619,631 ----
  select first_value(max(x)) over (), y
    from (select unique1 as x, ten+four as y from tenk1) ss
    group by y;
!                  QUERY PLAN
! ---------------------------------------------
   WindowAgg
     ->  HashAggregate
+          Group Key: (tenk1.ten + tenk1.four)
           ->  Seq Scan on tenk1
! (4 rows)

  -- test non-default frame specifications
  SELECT four, ten,