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.