On Fri, 24 May 2019 at 19:44, Kato, Sho <kato-...@jp.fujitsu.com> wrote: > I execute following query to the partitioned table, but the plan is different > from my assumption, so please tell me the reason. > > postgres=# explain select * from jta, (select a, max(b) from jtb where a = 1 > group by a ) c1 where jta.a = c1.a; > QUERY PLAN > ------------------------------------------------------------------------ > Hash Join (cost=38.66..589.52 rows=1402 width=12) > Hash Cond: (jta0.a = jtb0.a) > -> Append (cost=0.00..482.50 rows=25500 width=4) > -> Seq Scan on jta0 (cost=0.00..35.50 rows=2550 width=4) > -> Seq Scan on jta1 (cost=0.00..35.50 rows=2550 width=4) > -> Seq Scan on jta2 (cost=0.00..35.50 rows=2550 width=4) > -> Seq Scan on jta3 (cost=0.00..35.50 rows=2550 width=4) > -> Seq Scan on jta4 (cost=0.00..35.50 rows=2550 width=4) > -> Seq Scan on jta5 (cost=0.00..35.50 rows=2550 width=4) > -> Seq Scan on jta6 (cost=0.00..35.50 rows=2550 width=4) > -> Seq Scan on jta7 (cost=0.00..35.50 rows=2550 width=4) > -> Seq Scan on jta8 (cost=0.00..35.50 rows=2550 width=4) > -> Seq Scan on jta9 (cost=0.00..35.50 rows=2550 width=4) > -> Hash (cost=38.53..38.53 rows=11 width=8) > -> GroupAggregate (cost=0.00..38.42 rows=11 width=8) > Group Key: jtb0.a > -> Seq Scan on jtb0 (cost=0.00..38.25 rows=11 width=8) > Filter: (a = 1) > (18 rows) > > I assume that subquery aggregate only pruned table and parent query joins > pruned table and subquery results. > However, parent query scan all partitions and join. > In my investigation, because is_simple_query() returns false if subquery > contains GROUP BY, parent query does not prune. > Is it possible to improve this?
The planner can only push quals down into a subquery, it cannot pull quals from a subquery into the outer query. If you write the query like: explain select * from jta, (select a, max(b) from jtb group by a ) c1 where jta.a = c1.a and c1.a = 1; you should get the plan that you want. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
