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