Re: optimizing constant quals within outer joins

Greg Stark <gsstark(at)mit(dot)edu> writes:
>> I'm not sure why it's not pulling up from the left side of the left join
>> though. That might be a bug. What PG version is this exactly?

> In fact it doesn't even pull it up out of a regular join. I looked into this
> when it was first brought up on IRC and as near as I can tell it is trying to
> do so and somehow just failing.

Hm, some of this actually did work as recently as 8.1:

regression=# explain select 1 from (select * from int8_tbl where foo('foo')) as x;
QUERY PLAN
--------------------------------------------------------------
Result (cost=0.00..1.05 rows=5 width=0)
One-Time Filter: foo('foo'::text)
-> Seq Scan on int8_tbl (cost=0.00..1.05 rows=5 width=0)
(3 rows)

while HEAD produces

regression=# explain select 1 from (select * from int8_tbl where foo('foo')) as x;
QUERY PLAN
--------------------------------------------------------
Seq Scan on int8_tbl (cost=0.00..1.06 rows=2 width=0)
Filter: foo('foo'::text)
(2 rows)

I think I broke that case by removing simplify_jointree from the "prep"
phase and moving its processing into deconstruct_jointree, which happens
after query_planner tries to pull out non-variable WHERE clauses.
The raw result of pull_up_subqueries will be a jointree that looks like

{FromExpr
({FromExpr
({RangeTblRef})
(foo function call)})
(empty top-level qual)}

and unfortunately query_planner is only looking in the topmost
FromExpr's qual list. Before, simplify_jointree would fold the two
FromExprs together before query_planner saw them, but now that doesn't
happen. Obviously Veil users are gonna be real unhappy about this :-(

The most direct fix for this would be to get rid of the
always-rather-ad-hoc pull_constant_clauses step in query_planner,
and make deconstruct_jointree responsible for recognizing potential
gating clauses and stashing them somewhere appropriate (probably in
a new field of PlannerInfo). However this only gets us back to stuff
that works in existing releases, it doesn't fix Phil's complaint.

Phil's problem is that the whole notion of moving constant quals into
a gating Result node is only applied at the top level of query_planner.
Seems like this would need to be pushed into the guts of the planner
to handle the cases he wants. Not quite sure where a clean place for
it would be ... [ thinks ... ] Maybe we should just let such quals be
processed normally all through the planner, and have createplan.c pull
them out at the last moment and stick a Result atop the plan node they
would otherwise have gone into. See also the note about variable-free
clauses in distribute_qual_to_rels. I think that code would still work
but we'd want to change the comment.

regards, tom lane