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