Re: optimizing constant quals within outer joins

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Phil Frost <indigo(at)bitglue(dot)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 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