Optimizer Bug?

From: "Mitterwald, Holger" <mittehlg(at)coi(dot)de>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Optimizer Bug?
Date: 2000-05-30 11:30:16
Message-ID: 3933A648.5EDF9585@coi.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Hello, I do have a quite suspicious behavior of the Qery-Optimizer (I
think):

If I execute the following statement:
query: explain select k.name, t.lang from kneipe k, typ t where
k.typ=t.typ;
ProcessUtility: explain select k.name, t.lang from kneipe k, typ t where
k.typ=t.typ;
NOTICE: QUERY PLAN:

Hash Join (cost=1.20..31.05 rows=413 width=32)
-> Seq Scan on kneipe k (cost=0.00..13.13 rows=413 width=16)
-> Hash (cost=1.16..1.16 rows=16 width=16)
-> Seq Scan on typ t (cost=0.00..1.16 rows=16 width=16)

----------------

Seems ok to me, but if I insert a (huge) table which is nowhere
referenced (here:"preis"), the
following happens:

query: explain select k.name, t.lang from kneipe k, typ t, preis p where
k.typ=t.typ;
ProcessUtility: explain select k.name, t.lang from kneipe k, typ t,
preis p where k.typ=t.typ;
NOTICE: QUERY PLAN:

Merge Join (cost=2948.99..3280.15 rows=673190 width=36)
-> Sort (cost=2917.91..2917.91 rows=26080 width=20)
-> Nested Loop (cost=0.00..746.76 rows=26080 width=20)
-> Seq Scan on typ t (cost=0.00..1.16 rows=16 width=16)
-> Seq Scan on preis p (cost=0.00..30.30 rows=1630
width=4)
-> Sort (cost=31.07..31.07 rows=413 width=16)
-> Seq Scan on kneipe k (cost=0.00..13.13 rows=413 width=16)

The query takes up to 100 times longer although the table "preis" is not
used at all.
I guess the Optimizer gets here something REALLY wrong....

Any tips (besides removing "preis" from the select statement...)?

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Peter Eisentraut 2000-05-30 11:53:26 Re: Optimizer Bug?
Previous Message Tom Lane 2000-05-29 21:17:25 Re: pg_dumpall's output not totally usable