Postgres Pro
Downloads
Home   >   mailing lists

How can the Aggregation move to the outer query - Mailing list pgsql-hackers

From Andy Fan
Subject How can the Aggregation move to the outer query
Date
Msg-id CAKU4AWoGHOJ2+DxN4yHHMWH6fHXDG6099+ZW16A1WtcQWd=UsA@mail.gmail.com
Whole thread Raw
Responses Re: How can the Aggregation move to the outer query
List pgsql-hackers
Tree view
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)
   InitPlan 1 (returns $0)
     ->  Aggregate
           ->  Seq Scan on m1 t1
(5 rows)

The above is good to me. The aggregate is run in the subPlan/InitPlan.

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? 

--
Best Regards

pgsql-hackers by date:

Previous
From: Bharath Rupireddy
Date:
Subject: Re: Skipping logical replication transactions on subscriber side
Next
From: Aleksander Alekseev
Date:
Subject: Add ZSON extension to /contrib/