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