RE: Why does not subquery pruning conditions inherit to parentquery?

Friday, May 24, 2019 5:10 PM, David Rowley wrote:
> 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.

Thank you for your replay.

You are right. I should do that.
However, following query looks like the subquery qual is pushed down into the outer query.

postgres=# explain select * from jta, (select a from jtb where a = 1) c1 where jta.a = c1.a;
                            QUERY PLAN                            
------------------------------------------------------------------
 Nested Loop  (cost=0.00..81.94 rows=143 width=8)
   ->  Seq Scan on jta0  (cost=0.00..41.88 rows=13 width=4)
         Filter: (a = 1)
   ->  Materialize  (cost=0.00..38.30 rows=11 width=4)
         ->  Seq Scan on jtb0  (cost=0.00..38.25 rows=11 width=4)
               Filter: (a = 1)
(6 rows)

So, I think I could improve this behavior.
Why such a difference occur?

regards,

Sho Kato
> -----Original Message-----
> From: David Rowley [mailto:david.rowley@2ndquadrant.com]
> Sent: Friday, May 24, 2019 5:10 PM
> To: Kato, Sho/加藤 翔 <kato-sho@jp.fujitsu.com>
> Cc: pgsql-hackers@postgresql.org
> Subject: Re: Why does not subquery pruning conditions inherit to parent
> query?
> 
> On Fri, 24 May 2019 at 19:44, Kato, Sho <kato-sho@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
> 
>