Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-bugs by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group