Re: How can the Aggregation move to the outer query

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.

David