Re: Why does not subquery pruning conditions inherit to parent query?

On Fri, 24 May 2019 at 19:44, Kato, Sho <kato-sho(at)jp(dot)fujitsu(dot)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