Re: another 8.1->8.4 regression

[Ben Chobot <bench@xxxxxxxxxxxxxxx>]

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?