Re: [HACKERS] Problem with complexer join still persists sometimes

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