On Aug 31, 2011, at 10:47 AM, Tom Lane wrote:
> Peter Eisentraut <peter_e@gmx.net> writes:
>> I don't have an answer for you, but this report looks suspiciously
>> similar to the one I posted the other day at
>> <http://archives.postgresql.org/pgsql-hackers/2011-08/msg01224.php>,
>> which, now that I think about it, also manifested itself after the
>> upgrade to 8.4.8.
>
> I think there is more than one thing going on here. I've identified a
> logic error in this 8.4 change:
> http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=7f3eba30
> which is that it is relying on vardata[12]->rel->rows to provide the
> number of rows coming in to the semijoin, but that's only accurate for a
> single level of join. With two nested semijoins you get a pretty wacko
> answer --- unless you prevent them from being folded by inserting OFFSET
> 0. So that's definitely a bug, but it doesn't explain Ben's complaint
> because he's griping about a case where he did have OFFSET 0. (I wonder
> though if this is the "8.4 planner regression" that he put in the OFFSET
> for originally. Sure would be nice if people reported such things
> instead of hacking around them and imagining that they'll get fixed
> magically.) It also doesn't explain Mark Kirkwood's complaint, since
> he's showing test cases that involve only one join.
>
> The only relevant-looking change I can find between 8.4.7 and 8.4.8 is
> http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=0ae8b300388c2a3eaf90e6e6f13d6be1f4d4ac2d
> which again should not have caused the amount of excitement we're seeing
> on this thread, since it should represent no worse than a reversion to
> 8.3 behavior. Possibly what we're after is some earlier 8.4.x patch.
Tom, if there's anything else we can provide that might you out, let me know. We're currently about to install an
earlier8.4 version to see if the problem goes away. Is there a particular version you'd be interested to know about?