[ADMIN] How to change query planner configuration paramerters

[Melaka Gunasekara]

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

[Raghavendra]

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.

---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/

Post by Melaka Gunasekara
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

[Melaka Gunasekara]

Hi Raghavendra,

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)


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)

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 <

Post by Raghavendra
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.
---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/

Post by Melaka Gunasekara
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

[Craig Ringer]

Post by Melaka Gunasekara
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

[Tom Lane]

Post by Craig Ringer

Post by Melaka Gunasekara
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

[Melaka Gunasekara]

Thanks for the information Tom.
It solved my confusion.

Post by Melaka Gunasekara

Post by Craig Ringer

Post by Melaka Gunasekara
Merge Full Join (cost=10000000074.40..10000000093.69 rows=1159

width=286)

Post by Craig Ringer

Post by Melaka Gunasekara
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