Greg Stark <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings