Re: Force a merge join?

Hi Ian,

At 01:06 PM 5/17/2002, you wrote:
>I am having the exact same problem, although setting enable-nestloop = off 
>fixes my problem.  Does it not affect yours?

No, in my case, I'm using LOWER(x) = LOWER(y) which precludes merge joins - 
however, when I refactor the DB in a test situation to do x = y, as another 
person has mentioned, it can be forced into merge joins.

>Anyway, I occasionally recreate and reload my entire database.  When I do 
>this, the planner is flying blind and chooses merge join.  As soon as I 
>vaccum analyze, it chooses nested loop and certain queries take 1.5 days 
>to complete.  If I set enable_nestloop off, they take seconds.
>
>Does yours act like this?  That is, if you can reload the data in the 
>affected tables so the planner uses default values, does it choose merge 
>join?  Tom had indicated he would be interested in why this happens.  I 
>can forward my schema and another example to the group if anyone wants.

In fact, yes it does. How do I know? Very simple: I did a SELECT * INTO ... 
to copy my real table to a testing table so I could refactor it. Then I did 
the usual EXPLAIN ANALYZE queries, and it was using merge joins. Then, I 
did an "ANALYZE" (which is like VACUUM ANALYZE without the slow VACUUM) and 
voila - nested loops and half second queries turning into five minute 
nightmares. Then enable_nestloop would fix the problem again after that.

I played with some of the CPU TUPLE parameters but couldn't get it to force 
merge joins without giving really ridiculous values which would doubtlessly 
screw other things up.

Cheers,

Doug