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?
