From: | Doug Fields <dfields-pg-general(at)pexicom(dot)com> |
---|---|
To: | "Ian Harding" <ianh(at)tpchd(dot)org> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Force a merge join? |
Date: | 2002-05-17 18:51:06 |
Message-ID: | 5.1.0.14.2.20020517144729.029a8f78@mail.pexicom.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Titus J. Anderson | 2002-05-17 18:56:23 | Query not working as expected... |
Previous Message | Stephan Szabo | 2002-05-17 18:44:30 | Re: if !NULL ? |