Thread: How to change query planner configuration paramerters
Hi all,
I came across http://www.postgresql.org/docs/8.4/static/runtime-config-query.html which describes
how to change query planner configuration paramerters.
I need to know how I can change these parameters.
For example if I need to turn off enable_hashjoin, how can I do that?
Best Regards,
Melaka
postgres=# set enable_hashjoin to off;
SET
postgres=# show enable_hashjoin;
enable_hashjoin
-----------------
off
(1 row)
Above, changes applies for the current session (its Session-Level). If you want to do at Database-level use ALTER DATABASE and for entire Cluster-level edit postgresql.conf and do changes as per your requirement.
On Sun, Sep 18, 2011 at 2:55 PM, Melaka Gunasekara <donmelaka@gmail.com> wrote:
Hi all,I came across http://www.postgresql.org/docs/8.4/static/runtime-config-query.html which describeshow to change query planner configuration paramerters.I need to know how I can change these parameters.For example if I need to turn off enable_hashjoin, how can I do that?
Best Regards,Melaka
Hi Raghavendra,
--
Best Regards,
Thanks for your quick reply,
I did as you suggessted and following is my output.
melaka=# set enable_mergejoin to off;
SET
melaka=# show enable_mergejoin;
enable_mergejoin
------------------
off
(1 row)
SET
melaka=# show enable_mergejoin;
enable_mergejoin
------------------
off
(1 row)
Then I executed the following query
melaka=# EXPLAIN select * from distributors full outer join films on distributors.did=films.did;
Then the output was
Merge Full Join (cost=10000000074.40..10000000093.69 rows=1159 width=286)
Merge Cond: (films.did = distributors.did)
-> Sort (cost=30.08..31.03 rows=380 width=184)
Sort Key: films.did
-> Seq Scan on films (cost=0.00..13.80 rows=380 width=184)
-> Sort (cost=44.32..45.85 rows=610 width=102)
Sort Key: distributors.did
-> Seq Scan on distributors (cost=0.00..16.10 rows=610 width=102)
(8 rows)
Merge Cond: (films.did = distributors.did)
-> Sort (cost=30.08..31.03 rows=380 width=184)
Sort Key: films.did
-> Seq Scan on films (cost=0.00..13.80 rows=380 width=184)
-> Sort (cost=44.32..45.85 rows=610 width=102)
Sort Key: distributors.did
-> Seq Scan on distributors (cost=0.00..16.10 rows=610 width=102)
(8 rows)
Can you suggest why the merge join is being suggested when I have turned it off ?
On Sun, Sep 18, 2011 at 3:05 PM, Raghavendra <raghavendra.rao@enterprisedb.com> wrote:
postgres=# set enable_hashjoin to off;SETpostgres=# show enable_hashjoin;enable_hashjoin-----------------off(1 row)Above, changes applies for the current session (its Session-Level). If you want to do at Database-level use ALTER DATABASE and for entire Cluster-level edit postgresql.conf and do changes as per your requirement.On Sun, Sep 18, 2011 at 2:55 PM, Melaka Gunasekara <donmelaka@gmail.com> wrote:Hi all,I came across http://www.postgresql.org/docs/8.4/static/runtime-config-query.html which describeshow to change query planner configuration paramerters.I need to know how I can change these parameters.For example if I need to turn off enable_hashjoin, how can I do that?
Best Regards,Melaka
--
Best Regards,
Melaka
On 18/09/2011 5:51 PM, Melaka Gunasekara wrote: > Merge Full Join (cost=10000000074.40..10000000093.69 rows=1159 width=286) > Can you suggest why the merge join is being suggested when I have > turned it off ? > AFAIK SETting a join type to "off" really just increases the cost estimate so high that the planner will avoid using it where it has any alternative. In this case, it doesn't seem to think it has any other way to execute the query, or it thinks that any other way will be so incredibly, insanely slow that the merge join is still better. -- Craig Ringer
Craig Ringer <ringerc@ringerc.id.au> writes:
> On 18/09/2011 5:51 PM, Melaka Gunasekara wrote:
>> Merge Full Join (cost=10000000074.40..10000000093.69 rows=1159 width=286)
>> Can you suggest why the merge join is being suggested when I have
>> turned it off ?
> AFAIK SETting a join type to "off" really just increases the cost
> estimate so high that the planner will avoid using it where it has any
> alternative. In this case, it doesn't seem to think it has any other way
> to execute the query, or it thinks that any other way will be so
> incredibly, insanely slow that the merge join is still better.
It's the first of those --- FULL joins are only implemented in the
mergejoin logic, not in hash or nestloop joins, so there is no other way
to do this query. (But as of 9.1, hash joins can do them too.)
regards, tom lane
Thanks for the information Tom.
It solved my confusion.
--
Best Regards,
On Sun, Sep 18, 2011 at 10:03 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
It's the first of those --- FULL joins are only implemented in theCraig Ringer <ringerc@ringerc.id.au> writes:
> On 18/09/2011 5:51 PM, Melaka Gunasekara wrote:
>> Merge Full Join (cost=10000000074.40..10000000093.69 rows=1159 width=286)
>> Can you suggest why the merge join is being suggested when I have
>> turned it off ?
> AFAIK SETting a join type to "off" really just increases the cost
> estimate so high that the planner will avoid using it where it has any
> alternative. In this case, it doesn't seem to think it has any other way
> to execute the query, or it thinks that any other way will be so
> incredibly, insanely slow that the merge join is still better.
mergejoin logic, not in hash or nestloop joins, so there is no other way
to do this query. (But as of 9.1, hash joins can do them too.)
regards, tom lane
Best Regards,
Melaka