Thread: Problem with complexer join still persists sometimes

Hi,
I still have a problem with one query not returning all matching queries.
But it get's better than a few weeks before :-)
The symptom now:
As long as I do NOT create statisics wit 'VACUUM ANALYZE' I get all matching
queries. After the first 'VACCUM ANALYZE' the query malfunctions.

As a attachment: a.txt a EXPLAIN SELECT before the VACUUM, b.txt a EXPLAIN SELECT after the VACUUM.

For the used queries please search my last mail to hackers..

Bye!
----
Michael Reifenberger
Plaut Software GmbH, R/3 Basis

Michael Reifenberger <root@nihil.plaut.de> writes:
> I still have a problem with one query not returning all matching queries.

You didn't say what version you're using, but if it's a 6.5 prerelease
then I think this is the same problem I reported on Sunday: the
optimizer is generating mergejoin plans that don't sort the input.

> NOTICE:  QUERY PLAN:
> 
> Merge Join  (cost=10.96 size=149 width=184)
>   ->  Seq Scan on kunden k  (cost=2.39 size=42 width=28)
>   ->  Merge Join  (cost=7.06 size=4 width=156)
>         ->  Seq Scan on emp e  (cost=1.07 size=2 width=28)
>         ->  Nested Loop  (cost=5.86 size=2 width=128)
>               ->  Seq Scan on pausch p  (cost=1.07 size=2 width=52)
>               ->  Index Scan using reise_2 on reise r  (cost=2.40 size=8 width=76)

The sequential scans wouldn't necessarily produce sorted output,
but MergeJoin depends on having sorted input, so this plan looks
pretty bogus to me.

BTW, Charles Hornberger also sees this problem in the 29-Mar snapshot,
but reports that the 23-Mar snapshot doesn't have the bug.  (Does that
agree with your results, Michael?)  Apparently it was broken by some
recent change, not the large optimizer changes Bruce and I made a few
weeks ago.
        regards, tom lane