Re: 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(at)qwest(dot)net]
Sent: Wednesday, August 04, 2004 5:41 PM
To: Hicham G. Elmongui
Cc: pgsql-hackers(at)postgresql(dot)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(at)qwest(dot)net]
> Sent: Wednesday, August 04, 2004 5:41 PM
> To: Hicham G. Elmongui
> Cc: pgsql-hackers(at)postgresql(dot)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(at)qwest(dot)net]
Sent: Wednesday, August 04, 2004 6:42 PM
To: Hicham G. Elmongui
Cc: pgsql-hackers(at)postgresql(dot)org; pgsql-general(at)postgresql(dot)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(at)qwest(dot)net]
> Sent: Wednesday, August 04, 2004 5:41 PM
> To: Hicham G. Elmongui
> Cc: pgsql-hackers(at)postgresql(dot)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