Phil Frost <indigo@bitglue.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.61rows=1986 width=244) Sort Key: b.unique1 -> Seq Scan on onek b
(cost=0.00..162.86rows=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=19721width=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.62rows=1986 width=244) Sort Key: b.unique1 -> Result (cost=0.00..162.86
rows=1986width=244) One-Time Filter: expensive(1) -> Seq Scan on onek b
(cost=0.00..162.86rows=1986 width=244)
(12 rows)
regards, tom lane