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