On Mon, Aug 10, 2009 at 12:22 AM, Ip Wing Kin John<wkipjohn@xxxxxxxxx> 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. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
