"AND", "OR" and Materialize :((((

Hi all,

We've got the following 3 tables and 2 simple queries. The only difference
lies in the join condition: the first uses OR, the second uses AND.

I expected some difference in the performace according to the
difference in the evaluation of the logical form, but not
3 magnitudes !!!

So the question is:

WHY SHALL IT MATERIALIZE A CONSTANT RESULT IN A LOOP OF 120000 TIMES ??
[it would be enough to materialize only once, or even never, because
the size of the materialized table is not larger than 1Mb... ]

ps.: there are indeces on all referenced fields.


atti=# explain select count(*) from _108 left join (_111 cross join _110) on (_108.objectid=_111._108objectid OR
_108.objectid=_110._108objectid);
 
NOTICE:  QUERY PLAN:

Aggregate  (cost=5017202.06..5017202.06 rows=1 width=24) ->  Nested Loop  (cost=0.00..5016900.05 rows=120806 width=24)
    ->  Seq Scan on _108  (cost=0.00..44.70 rows=1670 width=8)       ->  Materialize  (cost=2097.79..2097.79 rows=60421
width=16)            ->  Nested Loop  (cost=0.00..2097.79 rows=60421 width=16)                   ->  Seq Scan on _110
(cost=0.00..1.37rows=37 width=8)                   ->  Seq Scan on _111  (cost=0.00..40.33 rows=1633 width=8)
 

EXPLAIN
atti=# explain select count(*) from _108 left join (_111 cross join _110) on _108.objectid=_111._108objectid AND
_108.objectid=_110._108objectid;
 
NOTICE:  QUERY PLAN:

Aggregate  (cost=7965.68..7965.68 rows=1 width=24) ->  Merge Join  (cost=7030.14..7961.51 rows=1670 width=24)       ->
Sort (cost=134.09..134.09 rows=1670 width=8)             ->  Seq Scan on _108  (cost=0.00..44.70 rows=1670 width=8)
 ->  Sort  (cost=6896.05..6896.05 rows=60421 width=16)             ->  Nested Loop  (cost=0.00..2097.79 rows=60421
width=16)                  ->  Seq Scan on _110  (cost=0.00..1.37 rows=37 width=8)                   ->  Seq Scan on
_111 (cost=0.00..40.33 rows=1633 width=8)
 

Attila