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