Re: optimizing constant quals within outer joins

Tom Lane wrote:

> 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)

I note that the rowcount is not altered by the one-time filter. Is this
an issue? I imagine the problem is not being able to estimate the
number of rows that pass the filter.

> -> 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)

I also wonder whether it wouldn't be better in this case to apply each
filter to each arm of the merge join.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.