Thread: enabling nestedloop and disabling hashjon

Ravi Kiran <ravi.kolanpaka@gmail.com> writes:
> I tried modifying the postgresql.conf file where I set the value
> enable_hashjoin=off and also enable_mergejoin=off, so that I could force
> postgres to use nested loop.
> but postgres is still using the hash join algorithm even after modifying
> the postgresql code.

Did you remember "pg_ctl reload"?  enable_hashjoin=off will most certainly
work if it's active.
        regards, tom lane

Ravi Kiran <ravi.kolanpaka@gmail.com> writes:
> 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.
        regards, tom lane

On 2/10/15 9:29 AM, Tom Lane wrote:
> Ravi Kiran <ravi.kolanpaka@gmail.com> writes:
>> 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

Jim Nasby-5 wrote
> On 2/10/15 9:29 AM, Tom Lane wrote:
>> Ravi Kiran <

> ravi.kolanpaka@

> > writes:
>>> 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.


> but postgres is still using the hash join algorithm even after modifying
> the postgresql code

To be fair given the original post, and some other recent posts by the same
author, the question is not "why is my query performing slowly" but rather
"I'm trying to change how PostgreSQL works and cannot figure out how to
properly enable and disable algorithms".  -hackers seems like the proper
forum though the OP could give more context as to his overarching goals to
make that more clear to readers.

David J.




--
View this message in context:
http://postgresql.nabble.com/enabling-nestedloop-and-disabling-hashjon-tp5837275p5837728.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

Ravi Kiran <ravi.kolanpaka@gmail.com> writes:
> 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.

The two plausible explanations for that are (1) you didn't do a reload
or (2) you forgot to remove the '#' comment marker in the file's entry.
        regards, tom lane

On 2/12/15 3:20 PM, David G Johnston wrote:
>>> >>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.
>
>> >but postgres is still using the hash join algorithm even after modifying
>> >the postgresql code
> To be fair given the original post, and some other recent posts by the same
> author, the question is not "why is my query performing slowly" but rather
> "I'm trying to change how PostgreSQL works and cannot figure out how to
> properly enable and disable algorithms".  -hackers seems like the proper
> forum though the OP could give more context as to his overarching goals to
> make that more clear to readers.

-hackers is for discussion directly related to developing Postgres 
itself. This email was request for help, and nothing to do with actual 
development.

I'm not trying to dismiss the importance of the request; it is 
important. But the proper forum for it was -general or -performance.
-- 
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com

On 2/12/15 3:34 PM, Ravi Kiran wrote:
> sorry for the inconvenience if caused to anyone, but as David G johnston
> said, I was trying to change how the postgresql works and was not able
> to figure out how it should be done. I will make sure it will be clear
> from the next time. Thank you very much.

And we're glad for the input. But we get a tremendous amount of email, 
so it's best if posts go to the right place. Just for future reference.

> @Tom lane   Sir, I forgot to remove the # comment marker in the file's
> entry, Thank you.

Glad you were able to fix the problem. :)
-- 
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com

On 12/2/15 18:29, Tom Lane wrote:
> Ravi Kiran <ravi.kolanpaka@gmail.com> writes:
>> 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.
> 
> The two plausible explanations for that are (1) you didn't do a reload
> or (2) you forgot to remove the '#' comment marker in the file's entry.

Or you changed the wrong postgresql.conf file

Regards

Rodrigo Gonzalez