Thread: enforcing a join type
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
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.
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.
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 >
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 >
> 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
> 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