Re: How can the Aggregation move to the outer query

On Tue, May 25, 2021 at 7:42 PM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:

> On Tue, 25 May 2021 at 22:28, Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com> wrote:
> >
> > explain (costs off) select (select count(*) filter (where t2.b = 1)
> from m1 t1)
> > from m1 t2 where t2.b % 2 = 1;
> >
> > QUERY PLAN
> > -------------------------------
> > Aggregate
> > -> Seq Scan on m1 t2
> > Filter: ((b % 2) = 1)
> > SubPlan 1
> > -> Seq Scan on m1 t1
> > (5 rows)
> >
> > This one is too confusing to me since the Aggregate happens
> > on t2 rather than t1. What happens here? Would this query
> > generate 1 row all the time like SELECT aggfunc(a) FROM t?
>
> I think you're misreading the plan. There's a scan on t2 with a
> subplan then an aggregate on top of that. Because you made the
> subquery correlated by adding t2.b, it cannot be executed as an
> initplan.
>
> You might see what's going on better if you add VERBOSE to the EXPLAIN
> options.
>
>
Thanks, VERBOSE does provide more information.

Aggregate
Output: (SubPlan 1)
-> Seq Scan on public.m1 t2
Output: t2.a, t2.b
Filter: ((t2.b % 2) = 1)
SubPlan 1
-> Seq Scan on public.m1 t1
Output: count(*) FILTER (WHERE (t2.b = 1))
(8 rows)

I am still confused about the SubPlan1, how can it output a
count(*) without an Aggregate under it (If this is not easy to
explain, I can try more by myself later).

But after all, I find this case when working on the UniqueKey stuff,
I have rule that if (query->hasAgg && !query->groupClause), then
there are only 1 row for this query. In the above case, the outer query
(t2) hasAgg=true and subplan's hasAgg=false, which looks not right
to me. I think the hasAgg=true should be in the subquery and outer
query should have hasAgg=false. anything I missed?

--
Best Regards
Andy Fan (https://www.aliyun.com/)