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

Monday, May 27, 2019 7:56 PM Tom Lane wrote:
> No, what is happening there is that the subquery gets inlined into the
> outer query.  That can't happen in your previous example because of the
> aggregation/GROUP BY --- but subqueries that are just scan/join queries
> generally get merged into the parent.

Thank you for your replay and sorry for late response.

Ok, I understand.
Is it possible to improve a subquery quals to pull up into outer query?
Oracle looks like do that.

Regards, Kato Sho
> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Monday, May 27, 2019 7:56 PM
> To: Kato, Sho/加藤 翔 <kato-sho@jp.fujitsu.com>
> Cc: 'David Rowley' <david.rowley@2ndquadrant.com>;
> pgsql-hackers@postgresql.org
> Subject: Re: Why does not subquery pruning conditions inherit to parent
> query?
> 
> "Kato, Sho" <kato-sho@jp.fujitsu.com> writes:
> > 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.
> 
> > 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)
> 
> No, what is happening there is that the subquery gets inlined into the
> outer query.  That can't happen in your previous example because of the
> aggregation/GROUP BY --- but subqueries that are just scan/join queries
> generally get merged into the parent.
> 
>             regards, tom lane
> 
>