Re: optimizing constant quals within outer joins

Phil Frost <indigo(at)bitglue(dot)com> writes:
> I have an optimization I'd like to see which I think should be pretty
> easy for someone familiar with the planner code to implement.

I've done something about this for 8.2. It could possibly be improved
on, in that it's not terribly smart about where to place the gating
Result nodes, but at least it uses them correctly ...

regression=# explain select * from (select * from onek a where expensive(0)) ss1 join (select * from onek b where expensive(1)) ss2 using(unique1);
QUERY PLAN
-------------------------------------------------------------------------------
Result (cost=543.30..849.05 rows=19721 width=484)
One-Time Filter: (expensive(0) AND expensive(1))
-> Merge Join (cost=543.30..849.05 rows=19721 width=484)
Merge Cond: (a.unique1 = b.unique1)
-> Sort (cost=271.65..276.61 rows=1986 width=244)
Sort Key: a.unique1
-> Seq Scan on onek a (cost=0.00..162.86 rows=1986 width=244)
-> Sort (cost=271.65..276.61 rows=1986 width=244)
Sort Key: b.unique1
-> Seq Scan on onek b (cost=0.00..162.86 rows=1986 width=244)
(10 rows)

regression=# explain select * from (select * from onek a where expensive(0)) ss1 left join (select * from onek b where expensive(1)) ss2 using(unique1);
QUERY PLAN
-------------------------------------------------------------------------------------
Result (cost=543.30..849.05 rows=19721 width=484)
One-Time Filter: expensive(0)
-> Merge Left Join (cost=543.30..849.05 rows=19721 width=484)
Merge Cond: (a.unique1 = b.unique1)
-> Sort (cost=271.65..276.61 rows=1986 width=244)
Sort Key: a.unique1
-> Seq Scan on onek a (cost=0.00..162.86 rows=1986 width=244)
-> Sort (cost=271.65..276.62 rows=1986 width=244)
Sort Key: b.unique1
-> Result (cost=0.00..162.86 rows=1986 width=244)
One-Time Filter: expensive(1)
-> Seq Scan on onek b (cost=0.00..162.86 rows=1986 width=244)
(12 rows)

regards, tom lane