Tom Lane <tgl@sss.pgh.pa.us> writes:
> Phil Frost <indigo@bitglue.com> 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
sqlstable 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
Scanon 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.74rows=1940 width=0) -> Seq Scan on a (cost=0.00..29.40 rows=1940 width=0)
(5 rows)
--
greg
