create table t1(a int, b int);
create table t2 (like t1);
set gp_cte_sharing = on;

explain(locus, costs off) with x as (select a, count(*) as b from t1 group by a union all select a, count(*) as b from t2 group by a) select count(*) from x a join x b on a.a = b.b;
                               QUERY PLAN
------------------------------------------------------------------------
 Finalize Aggregate
   Locus: Entry
   ->  Gather Motion 3:1  (slice1; segments: 3)
         Locus: Entry
         ->  Partial Aggregate
               Locus: Hashed
               ->  Hash Join
                     Locus: Hashed
                     Hash Cond: (b.b = a.a)
                     ->  Redistribute Motion 3:3  (slice2; segments: 3)
                           Locus: Hashed
                           Hash Key: b.b
                           ->  Subquery Scan on b
                                 Locus: Strewn
                                 ->  Shared Scan (share slice:id 2:0)
                                       Locus: NULL
                     ->  Hash
                           Locus: Hashed
                           ->  Subquery Scan on a
                                 Locus: Hashed
                                 ->  Shared Scan (share slice:id 1:0)
                                       Locus: NULL
                                       ->  Append
                                             Locus: Hashed
                                             ->  HashAggregate
                                                   Locus: Hashed
                                                   Group Key: t1.a
                                                   ->  Seq Scan on t1
                                                         Locus: Hashed
                                             ->  HashAggregate
                                                   Locus: Hashed
                                                   Group Key: t2.a
                                                   ->  Seq Scan on t2
                                                         Locus: Hashed
 Optimizer: Postgres query optimizer
(35 rows)