Re: another 8.1->8.4 regression

On Feb 16, 2010, at 1:29 PM, Ben Chobot wrote:

> I'm having problems with another one of my queries after moving from 8.1.19 to 8.4.2. On 8.1.19, the plan looked like this:
>
> http://wood.silentmedia.com/bench/8119
>
> That runs pretty well. On 8.4.2, the same query looks like this:
>
> http://wood.silentmedia.com/bench/842_bad
>
> If I turn off mergejoin and hashjoin, I can get 8.4.2 to spit out this:
>
> http://wood.silentmedia.com/bench/842_better
>
> ...which it thinks is going to suck but which does not.
>
> The query and relevant table definitions are here:
>
> http://wood.silentmedia.com/bench/query_and_definitions
>
>
> Any suggestions? I'm guessing the problem is with the absurd over-estimation on the nested loop under the sort node, but I'm not sure why it's so bad.

After looking at this some more, I'm pretty confused at both of 8.4.2's plans. They both have a Nested Loop node in them where the expected row count is a bit over 2 million, and yet the inner nodes have expected row counts of 1 and 152. I was under the impression that a nested loop between R and S would return no more than R*S?