Hi,
If I want the planner/optimizer to always choose merge join when it needs to
join relations. How can I do it ?
Thanks,
--h


Wed, 4 Aug 2004 15:53:58 -0500
"Hicham G. Elmongui" <elmongui@cs.purdue.edu>
enforcing a join type
On Wed, 2004-08-04 at 14:53, Hicham G. Elmongui wrote:
> Hi,
> If I want the planner/optimizer to always choose merge join when it needs to
> join relations. How can I do it ?

>From my past experience, I'd guess what you're really trying to do is
STOP the planner from choosing a nested_loop join, in which case it's
quite easy:

set enable_nestloop = off;
select * from ...

Of course, you could apply the same basic trick to all other join
methods, and postgresql would then favor using the merge join.


Wed, 04 Aug 2004 16:41:10 -0600
"Scott Marlowe" <smarlowe@qwest.net>
Re: enforcing a join type
I didn't mean about doing this from a front end. I want to disable
nested_loop and hash_join from the backend.
I tried to set the variables (enable_nestloop and enable_hashjoin) in
costsize.c, but this didn't do it.
Thanks,
--h




-----Original Message-----
From: Scott Marlowe [mailto:smarlowe@qwest.net] 
Sent: Wednesday, August 04, 2004 5:41 PM
To: Hicham G. Elmongui
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] enforcing a join type

On Wed, 2004-08-04 at 14:53, Hicham G. Elmongui wrote:
> Hi,
> If I want the planner/optimizer to always choose merge join when it needs
to
> join relations. How can I do it ?

>From my past experience, I'd guess what you're really trying to do is
STOP the planner from choosing a nested_loop join, in which case it's
quite easy:

set enable_nestloop = off;
select * from ...

Of course, you could apply the same basic trick to all other join
methods, and postgresql would then favor using the merge join.


Wed, 4 Aug 2004 18:26:05 -0500
"Hicham G. Elmongui" <elmongui@cs.purdue.edu>
Re: enforcing a join type
As this is not really a hacking issue, I'm moving it out of hackers and
into general.  Please post all replies there not in hackers.

Anyway, I'm afraid I'd have to ask WHY you're trying to just disable
it?  Is the query planner making the wrong decision with good
statistics, or are you getting bad statistics?

Can you post an explain analyze of the query(s) that are making you want
to make this change?  Just turning off a join method isn't the way to
fix PostgreSQL, getting it to pick the right one is.

On Wed, 2004-08-04 at 17:26, Hicham G. Elmongui wrote:
> I didn't mean about doing this from a front end. I want to disable
> nested_loop and hash_join from the backend.
> I tried to set the variables (enable_nestloop and enable_hashjoin) in
> costsize.c, but this didn't do it.
> Thanks,
> --h
> 
> 
> 
> 
> -----Original Message-----
> From: Scott Marlowe [mailto:smarlowe@qwest.net] 
> Sent: Wednesday, August 04, 2004 5:41 PM
> To: Hicham G. Elmongui
> Cc: pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS] enforcing a join type
> 
> On Wed, 2004-08-04 at 14:53, Hicham G. Elmongui wrote:
> > Hi,
> > If I want the planner/optimizer to always choose merge join when it needs
> to
> > join relations. How can I do it ?
> 
> >From my past experience, I'd guess what you're really trying to do is
> STOP the planner from choosing a nested_loop join, in which case it's
> quite easy:
> 
> set enable_nestloop = off;
> select * from ...
> 
> Of course, you could apply the same basic trick to all other join
> methods, and postgresql would then favor using the merge join.
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
> 


Wed, 04 Aug 2004 17:41:52 -0600
"Scott Marlowe" <smarlowe@qwest.net>
Re: [HACKERS] enforcing a join type
Maybe I didn't make myself clear enough. I didn't have a problem with
postgresql. I am just playing around with the code, tracing some parts in
order to understand the code well.
This is just an experiment with the code. That's why I posted it to hackers.
Please let me know if this is still the wrong place for this question.
--h




-----Original Message-----
From: Scott Marlowe [mailto:smarlowe@qwest.net] 
Sent: Wednesday, August 04, 2004 6:42 PM
To: Hicham G. Elmongui
Cc: pgsql-hackers@postgresql.org; pgsql-general@postgresql.org
Subject: Re: [HACKERS] enforcing a join type

As this is not really a hacking issue, I'm moving it out of hackers and
into general.  Please post all replies there not in hackers.

Anyway, I'm afraid I'd have to ask WHY you're trying to just disable
it?  Is the query planner making the wrong decision with good
statistics, or are you getting bad statistics?

Can you post an explain analyze of the query(s) that are making you want
to make this change?  Just turning off a join method isn't the way to
fix PostgreSQL, getting it to pick the right one is.

On Wed, 2004-08-04 at 17:26, Hicham G. Elmongui wrote:
> I didn't mean about doing this from a front end. I want to disable
> nested_loop and hash_join from the backend.
> I tried to set the variables (enable_nestloop and enable_hashjoin) in
> costsize.c, but this didn't do it.
> Thanks,
> --h
> 
> 
> 
> 
> -----Original Message-----
> From: Scott Marlowe [mailto:smarlowe@qwest.net] 
> Sent: Wednesday, August 04, 2004 5:41 PM
> To: Hicham G. Elmongui
> Cc: pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS] enforcing a join type
> 
> On Wed, 2004-08-04 at 14:53, Hicham G. Elmongui wrote:
> > Hi,
> > If I want the planner/optimizer to always choose merge join when it
needs
> to
> > join relations. How can I do it ?
> 
> >From my past experience, I'd guess what you're really trying to do is
> STOP the planner from choosing a nested_loop join, in which case it's
> quite easy:
> 
> set enable_nestloop = off;
> select * from ...
> 
> Of course, you could apply the same basic trick to all other join
> methods, and postgresql would then favor using the merge join.
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
> 


Wed, 4 Aug 2004 18:48:22 -0500
"Hicham G. Elmongui" <elmongui@cs.purdue.edu>
Re: enforcing a join type
> If I want the planner/optimizer to always choose merge join when it needs to
> join relations. How can I do it ?

You can't, unless in your transaction you set enable_nestloop, 
enable_seqscan, etc. all to off except for the join type you want.

Chris


Thu, 05 Aug 2004 09:28:23 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: enforcing a join type
> I didn't mean about doing this from a front end. I want to disable
> nested_loop and hash_join from the backend.
> I tried to set the variables (enable_nestloop and enable_hashjoin) in
> costsize.c, but this didn't do it.

Turn them off in your postgresql.conf then.

Chris


Thu, 05 Aug 2004 09:32:42 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: enforcing a join type