Tom,
Thanks for the patch. We've tried it here, and it improved query plan
slightly (indeed, it looks exactly like the plan from 8.2.6 now).
But, as you've said, empty sub-joins are still not discarded, so query
execution time did not improve. And this is the same in both 8.2 and 8.3.
Note that only 'UPDATE FROM' does not discard sub-joins. 'SELECT' on the
same tables is just fine:
=> EXPLAIN SELECT * FROM foo, bar WHERE part = 2 AND foo.bar_id =
bar.bar_id;
QUERY PLAN
-----------------------------------------------------------------------------
Hash Join (cost=64.47..104.08 rows=18 width=16)
Hash Cond: (bar.bar_id = public.foo.bar_id)
-> Seq Scan on bar (cost=0.00..31.40 rows=2140 width=4)
-> Hash (cost=64.25..64.25 rows=18 width=12)
-> Append (cost=0.00..64.25 rows=18 width=12)
-> Seq Scan on foo (cost=0.00..32.12 rows=9 width=12)
Filter: (part = 2)
-> Seq Scan on foo_2 foo (cost=0.00..32.12 rows=9
width=12)
Filter: (part = 2)
(9 rows)
-- Alex
Tom Lane wrote:
> Hmm, the immediate problem is that cost_mergejoin is coming out with
> a silly cost (NaN) because of division by zero. The attached patch
> should get it back to 8.2-equivalent behavior. But really we're missing
> a bet because the sub-joins ought to get discarded entirely when we know
> they must be empty. There are various places testing for this but it
> looks like make_join_rel() needs to do it too.
>
> regards, tom lane