Re: How can the Aggregation move to the outer query

2021-05-25 Thread Andy Fan
On Tue, May 25, 2021 at 10:23 PM Tom Lane wrote: > David Rowley writes: > > On Tue, 25 May 2021 at 22:28, Andy Fan wrote: > >> explain (costs off) select (select count(*) filter (where t2.b = 1) > from m1 t1) > >> from m1 t2 where t2.b % 2 = 1; > >> > >> This one is too confusing to me since t

Re: How can the Aggregation move to the outer query

2021-05-25 Thread Tom Lane
David Rowley writes: > On Tue, 25 May 2021 at 22:28, Andy Fan wrote: >> explain (costs off) select (select count(*) filter (where t2.b = 1) from m1 >> t1) >> from m1 t2 where t2.b % 2 = 1; >> >> This one is too confusing to me since the Aggregate happens >> on t2 rather than t1. What happens

Re: How can the Aggregation move to the outer query

2021-05-25 Thread Andy Fan
On Tue, May 25, 2021 at 7:42 PM David Rowley wrote: > On Tue, 25 May 2021 at 22:28, Andy Fan 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 > > --- > >

Re: How can the Aggregation move to the outer query

2021-05-25 Thread David Rowley
On Tue, 25 May 2021 at 22:28, Andy Fan 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)

How can the Aggregation move to the outer query

2021-05-25 Thread Andy Fan
My question can be demonstrated with the below example: create table m1(a int, b int); explain (costs off) select (select count(*) filter (*where true*) from m1 t1) from m1 t2 where t2.b % 2 = 1; QUERY PLAN - Seq Scan on m1 t2 Filter: ((b % 2) = 1)