enabling nestedloop and disabling hashjon
Hi,
I want to disable the hashjoin algorithm used by postgres by default, and
enable the nested loop join algorithm, can some one tell me how to do that.
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.
can some one tell me what I am doing wrong, or is there any other file
where I need to modify to enable nested loop join algorithm.
Thanks
ᐧ
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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 <tgl@sss.pgh.pa.us> wrote:
Show quoted text
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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 algorithmDoes "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
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.Nasby@bluetreble.com> wrote:
Show quoted text
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 algorithmDoes "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 algorithmDoes "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
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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.johnston@gmail.com> wrote:
Show quoted text
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 algorithmDoes "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 codeTo 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
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 codeTo 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
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
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers