I am sorry for the late reply, when I disabled the hash join command "enable_hashjoin=off" in the postgresql.conf file, it was not working. But I when I used the command "set enable_hashjoin=off" command in the back end. It worked.
I am not able to understand why it did not get disabled when I changed it in the postgresql file.
yes sir, I did try the pg_ctl reload command, but its still using the hash join algorithm and not the nested loop algorithm. I even restarted the server, even then its still using the hash join algorithm
Does "show enable_hashjoin" say it's off? If not, I think you must've fat-fingered the postgresql.conf change somehow.
For future reference, posts like this belong on pgsql-performance.
The other possibility is that the query estimates are so high that the setting doesn't matter. When you set any of the enable_* settings to off, all that really happens is the planner adds a cost of 10M to those nodes when it's planning. Normally that's enough to toss those plans out, but in extreme cases the cost estimates will still come up with the un-desired plan.
Can you post EXPLAIN ANALYZE output with the setting on and off? Or at least plain EXLPAIN output. -- Jim Nasby, Data Architect, Blue Treble Consulting Data in Trouble? Get it in Treble! http://BlueTreble.com