Re: [HACKERS] enabling nestedloop and disabling hashjon
Ravi Kiran ravi.kolanp...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] enabling nestedloop and disabling hashjon
Jim Nasby-5 wrote On 2/10/15 9:29 AM, Tom Lane wrote: Ravi Kiran lt; ravi.kolanpaka@ gt; 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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] enabling nestedloop and disabling hashjon
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] enabling nestedloop and disabling hashjon
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. ᐧ On Fri, Feb 13, 2015 at 2:34 AM, Jim Nasby jim.na...@bluetreble.com wrote: On 2/10/15 9:29 AM, Tom Lane wrote: Ravi Kiran ravi.kolanp...@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
Re: [HACKERS] enabling nestedloop and disabling hashjon
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] enabling nestedloop and disabling hashjon
On 12/2/15 18:29, Tom Lane wrote: Ravi Kiran ravi.kolanp...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] enabling nestedloop and disabling hashjon
On 2/10/15 9:29 AM, Tom Lane wrote: Ravi Kiran ravi.kolanp...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] enabling nestedloop and disabling hashjon
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. @Tom lane Sir, I forgot to remove the # comment marker in the file's entry, Thank you. ᐧ On Fri, Feb 13, 2015 at 2:50 AM, David G Johnston david.g.johns...@gmail.com wrote: Jim Nasby-5 wrote On 2/10/15 9:29 AM, Tom Lane wrote: Ravi Kiran lt; ravi.kolanpaka@ gt; 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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] enabling nestedloop and disabling hashjon
Ravi Kiran ravi.kolanp...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] enabling nestedloop and disabling hashjon
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 Thanks ᐧ On Tue, Feb 10, 2015 at 5:28 AM, Tom Lane t...@sss.pgh.pa.us wrote: Ravi Kiran ravi.kolanp...@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
Re: [HACKERS] enabling nestedloop and disabling hashjon
Ravi Kiran ravi.kolanp...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
