Re: Optimizer Bug?

"Mitterwald, Holger" <mittehlg@coi.de> writes:
> 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;
> 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....

The optimizer is doing exactly what it is supposed to do: you told
it to join over three tables, and three tables is what you will get.

6.5 in fact failed to join preis if no reference to it was visible in
the query, but *that* behavior was buggy, not this one.  It's perfectly
clear from the SQL standard that the result of the query must be the
three-way cross-product minus rows that don't meet the WHERE condition.
Without joining preis, we don't produce the expected number of rows.

            regards, tom lane