From: | Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com> |
---|---|
To: | PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | why doesn't optimizer can pull up where a > ( ... ) |
Date: | 2019-11-20 12:15:19 |
Message-ID: | CAKU4AWodctmbU+Zj6U83y_RniQk0UeXBvKH1ZaJ=LR_iC90GOw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Andy Fan | 2019-11-20 12:16:33 | Re: why doesn't optimizer can pull up where a > ( ... ) |
Previous Message | Juan José Santamaría Flecha | 2019-11-20 12:11:26 | Re: logical decoding : exceeded maxAllocatedDescs for .spill files |