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

From: Meszaros Attila <tilla(at)draconis(dot)elte(dot)hu>
To: pgsql-sql(at)postgresql(dot)org
Subject: "AND", "OR" and Materialize :((((
Date: 2001-08-26 12:38:10
Message-ID: Pine.LNX.4.21.0108261437150.22782-100000@draconis.csoma.elte.hu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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.37 rows=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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Tom Lane 2001-08-26 15:24:34 Re: [SQL] undocumented setval()
Previous Message Cedar Cox 2001-08-26 08:40:33 undocumented setval()