Tom Lane <[EMAIL PROTECTED]> writes:
> Phil Frost <[EMAIL PROTECTED]> writes:
> > The planner in fact can move the function around without changing the
> > output.
>
> Not when it's within the nullable side of an outer join --- moving a
> WHERE clause up out of that would make the difference between no row
> out, and a null-extended row out, which are certainly not the same.
>
> 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.
postgres=# create function foo(text) returns bool as 'select case when $1 =
''foo'' then true else false end' language sql stable strict ;
postgres=# explain select 1 from a,a as b where foo('foo') ;
QUERY PLAN
-------------------------------------------------------------------------
Result (cost=31.34..75332.74 rows=3763600 width=0)
One-Time Filter: foo('foo'::text)
-> Nested Loop (cost=31.34..75332.74 rows=3763600 width=0)
-> Seq Scan on a (cost=0.00..29.40 rows=1940 width=0)
-> Materialize (cost=31.34..50.74 rows=1940 width=0)
-> Seq Scan on a b (cost=0.00..29.40 rows=1940 width=0)
(6 rows)
postgres=# explain select 1 from (select * from a where foo('foo')) as x, a;
QUERY PLAN
-----------------------------------------------------------------
Nested Loop (cost=31.34..25169.19 rows=1255180 width=0)
-> Seq Scan on a (cost=0.00..34.25 rows=647 width=0)
Filter: foo('foo'::text)
-> Materialize (cost=31.34..50.74 rows=1940 width=0)
-> Seq Scan on a (cost=0.00..29.40 rows=1940 width=0)
(5 rows)
--
greg
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
