On Mon, Aug 10, 2009 at 12:22 AM, Ip Wing Kin John<wkipjohn@gmail.com> wrote:
> Hi Scott,
>
> Thanks for you suggestion. I have follow your suggestion by disable
> nestloop and have a substantial improvement. Takes 51s now. I have
> attached the new query plan in another file.
>
> What I want to ask is, is there any other way to hint the planner to
> choose to use merge join rather than nested loop by modifying my SQL?
> I did try to sort my second inner join by the join condition, but the
> planner still prefer to use nested loop.
>
> As I am afraid changing the system wide configuration will have some
> side effect on my other queries.
Yeah, that's more of a troubleshooting procedure than something you'd
want to institute system wide. If you must set it for this query, you
can do so just before you run it in your connection, then turn it back
on for the rest of your queries. I.e.:
set enable_nestloop=off;
select ....;
set enable_nestloop=on;
I've had one or two big queries in the past that no amount of tuning
and setting stats target higher and analyzing could force to choose
the right plan.
If you haven't already, try setting the default statistic target
higher and re-analyzing to see if that helps. After that you can play
around a bit with the cost parameters to see what helps. Note that
just like setting enable_nestloop on or off, you can do so for the
current connection only and not globally, especially while just
testing.