pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to change query planner configuration paramerters
6+ messages / 4 participants
[nested] [flat]
* How to change query planner configuration paramerters
@ 2011-09-18 09:25 Melaka Gunasekara <donmelaka@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Melaka Gunasekara @ 2011-09-18 09:25 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to change query planner configuration paramerters
@ 2011-09-18 09:35 Raghavendra <raghavendra.rao@enterprisedb.com>
parent: Melaka Gunasekara <donmelaka@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Raghavendra @ 2011-09-18 09:35 UTC (permalink / raw)
To: Melaka Gunasekara <donmelaka@gmail.com>; +Cc: pgsql-admin
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/
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
> 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
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to change query planner configuration paramerters
@ 2011-09-18 09:51 Melaka Gunasekara <donmelaka@gmail.com>
parent: Raghavendra <raghavendra.rao@enterprisedb.com>
0 siblings, 1 reply; 6+ messages in thread
From: Melaka Gunasekara @ 2011-09-18 09:51 UTC (permalink / raw)
To: Raghavendra <raghavendra.rao@enterprisedb.com>; +Cc: pgsql-admin
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 <
raghavendra.rao@enterprisedb.com> wrote:
> 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/
>
>
>
> 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
>> 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
>>
>>
>
--
Best Regards,
Melaka
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to change query planner configuration paramerters
@ 2011-09-18 10:43 Craig Ringer <ringerc@ringerc.id.au>
parent: Melaka Gunasekara <donmelaka@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Craig Ringer @ 2011-09-18 10:43 UTC (permalink / raw)
To: Melaka Gunasekara <donmelaka@gmail.com>; +Cc: Raghavendra <raghavendra.rao@enterprisedb.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to change query planner configuration paramerters
@ 2011-09-18 16:33 Tom Lane <tgl@sss.pgh.pa.us>
parent: Craig Ringer <ringerc@ringerc.id.au>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2011-09-18 16:33 UTC (permalink / raw)
To: Craig Ringer <ringerc@ringerc.id.au>; +Cc: Melaka Gunasekara <donmelaka@gmail.com>; Raghavendra <raghavendra.rao@enterprisedb.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to change query planner configuration paramerters
@ 2011-09-18 16:42 Melaka Gunasekara <donmelaka@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Melaka Gunasekara @ 2011-09-18 16:42 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Craig Ringer <ringerc@ringerc.id.au>; Raghavendra <raghavendra.rao@enterprisedb.com>; pgsql-admin
Thanks for the information Tom.
It solved my confusion.
On Sun, Sep 18, 2011 at 10:03 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 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
>
--
Best Regards,
Melaka
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2011-09-18 16:42 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-09-18 09:25 How to change query planner configuration paramerters Melaka Gunasekara <donmelaka@gmail.com>
2011-09-18 09:35 ` Raghavendra <raghavendra.rao@enterprisedb.com>
2011-09-18 09:51 ` Melaka Gunasekara <donmelaka@gmail.com>
2011-09-18 10:43 ` Craig Ringer <ringerc@ringerc.id.au>
2011-09-18 16:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-09-18 16:42 ` Melaka Gunasekara <donmelaka@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox