Re: why doesn't optimizer can pull up where a > ( ... )

Hi Hackers:

 First I found the following queries running bad on pg. 

 select count(*) from part2 p1 where p_size > 40 and p_retailprice > (select avg(p_retailprice) from part2 p2 where p2.p_brand=p1.p_brand);

the plan is

                                     QUERY PLAN
------------------------------------------------------------------------------------
 Aggregate  (cost=1899310537.28..1899310537.29 rows=1 width=8)
   ->  Seq Scan on part2 p1  (cost=0.00..1899310456.00 rows=32513 width=0)
         Filter: ((p_size > 40) AND (p_retailprice > (SubPlan 1)))
         SubPlan 1
           ->  Aggregate  (cost=6331.00..6331.01 rows=1 width=32)
                 ->  Seq Scan on part2 p2  (cost=0.00..5956.00 rows=150000 width=4)
                       Filter: (p_brand = p1.p_brand)

however if we change it to the following format, it runs pretty quick. 

select count(*) from part2,
(select p_brand, avg(p_retailprice) as avg_price from part2 where p_size > 40 group by p_brand) p2
where p_retailprice > p2.avg_price
and p_size > 40
and part2.p_brand = p2.p_brand;

The above example comes from https://community.pivotal.io/s/article/Pivotal-Query-Optimizer-Explained with a litter modification. 

1.  why pg can't translate the query 1 to query 2.  after some checking on pull_up_sublinks_qual_recurse,  I still doesn't get the idea. 

2.  why pg can't do it,  while greenplum can? 

Thanks