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

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(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: Monday, May 27, 2019 7:56 PM
> To: Kato, Sho/加藤 翔 <kato-sho(at)jp(dot)fujitsu(dot)com>
> Cc: 'David Rowley' <david(dot)rowley(at)2ndquadrant(dot)com>;
> pgsql-hackers(at)postgresql(dot)org
> Subject: Re: Why does not subquery pruning conditions inherit to parent
> query?
>
> "Kato, Sho" <kato-sho(at)jp(dot)fujitsu(dot)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
>
>