I have fixed bushy plans, enabled with the postgres -b option.  Here is
proof from OPTIMIZER_DEBUG:

	levels left: 7
	(9 8 7 6 ): size=1 width=16
	        path list:
	        Nestloop size=1 cost=0.000000
	                MergeJoin size=1 cost=0.000000
	                           clauses=(x5.y = x6.y)
	                           sortouter=1 sortinner=1
	                        SeqScan(6) size=0 cost=0.000000
	                        SeqScan(7) size=0 cost=0.000000
	                MergeJoin size=1 cost=0.000000
	                           clauses=(x7.y = x8.y)
	                           sortouter=1 sortinner=1
	                        SeqScan(8) size=0 cost=0.000000
	                        SeqScan(9) size=0 cost=0.000000
	 

The regression tests pass with bushy plans enabled.  I am not sure if
the executor is actually using a bushy plan, though.

The old bushy code was poor.  It tried to do bushy plans by modifying
the joininfo nodes.  I removed all that code, and just do the join
search in make_rels_by_clause_joins().  This is much more logical, and
does not require the joininfo setup/cleanup that the old code attempted.

Frankly, I would like to enable bushy plans and right-handed plans by
default.  The optimizer is now fast enough that a 9-table join is almost
instantaneous, even with bush plans.  People are not sophisticated
enough to know when to enables these options themselves. I am not sure I
am either.  I would like to enables both, fix whatever breaks, and
programmatically enable the options when they make sense.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Wed, 17 Feb 1999 19:47:34 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Bushy Plans fixed
On Wed, 17 Feb 1999, Bruce Momjian wrote:

> Frankly, I would like to enable bushy plans and right-handed plans by
> default.  The optimizer is now fast enough that a 9-table join is almost
> instantaneous, even with bush plans.  People are not sophisticated
> enough to know when to enables these options themselves. I am not sure I
> am either.  I would like to enables both, fix whatever breaks, and
> programmatically enable the options when they make sense.

Sounds reasonable to me...I know, in my case, it isn't something I'd think
to enable, and tend to be the type that uses btree's for indices all the
time cause I don't really understand why/where I'd use others...

Marc G. Fournier                                
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org 


Wed, 17 Feb 1999 21:58:42 -0400 (AST)
The Hermit Hacker <scrappy@hub.org>
Re: [HACKERS] Bushy Plans fixed
Bruce Momjian wrote:
> 
> I have fixed bushy plans, enabled with the postgres -b option.  Here is
> proof from OPTIMIZER_DEBUG:

Nice!!!

>         levels left: 7
>         (9 8 7 6 ): size=1 width=16
>                 path list:
>                 Nestloop size=1 cost=0.000000
>                         MergeJoin size=1 cost=0.000000
>                                    clauses=(x5.y = x6.y)
>                                    sortouter=1 sortinner=1
>                                 SeqScan(6) size=0 cost=0.000000
>                                 SeqScan(7) size=0 cost=0.000000
>                         MergeJoin size=1 cost=0.000000
>                                    clauses=(x7.y = x8.y)
>                                    sortouter=1 sortinner=1
>                                 SeqScan(8) size=0 cost=0.000000
>                                 SeqScan(9) size=0 cost=0.000000
> 
> 
> The regression tests pass with bushy plans enabled.  I am not sure if
> the executor is actually using a bushy plan, though.

Sure, it does.

> 
> The old bushy code was poor.  It tried to do bushy plans by modifying
> the joininfo nodes.  I removed all that code, and just do the join
> search in make_rels_by_clause_joins().  This is much more logical, and
> does not require the joininfo setup/cleanup that the old code attempted.
> 
> Frankly, I would like to enable bushy plans and right-handed plans by
> default.  The optimizer is now fast enough that a 9-table join is almost
> instantaneous, even with bush plans.  People are not sophisticated
> enough to know when to enables these options themselves. I am not sure I
> am either.  I would like to enables both, fix whatever breaks, and
> programmatically enable the options when they make sense.

We need not in right-sided plans code any more. 
I agreed that we should enable bushes by default.

Vadim

Thu, 18 Feb 1999 09:54:20 +0700
Vadim Mikheev <vadim@krs.ru>
Re: [HACKERS] Bushy Plans fixed
> > Frankly, I would like to enable bushy plans and right-handed plans by
> > default.  The optimizer is now fast enough that a 9-table join is almost
> > instantaneous, even with bush plans.  People are not sophisticated
> > enough to know when to enables these options themselves. I am not sure I
> > am either.  I would like to enables both, fix whatever breaks, and
> > programmatically enable the options when they make sense.
> 
> We need not in right-sided plans code any more. 
> I agreed that we should enable bushes by default.

Are you saying right-hand plans are not useful if we have bushy plans?
If so, I will remove the right-hand code.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Wed, 17 Feb 1999 22:36:25 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Bushy Plans fixed
Bruce Momjian wrote:
> 
> > > Frankly, I would like to enable bushy plans and right-handed plans by
> > > default.  The optimizer is now fast enough that a 9-table join is almost
> > > instantaneous, even with bush plans.  People are not sophisticated
> > > enough to know when to enables these options themselves. I am not sure I
> > > am either.  I would like to enables both, fix whatever breaks, and
> > > programmatically enable the options when they make sense.
> >
> > We need not in right-sided plans code any more.
> > I agreed that we should enable bushes by default.
> 
> Are you saying right-hand plans are not useful if we have bushy plans?
> If so, I will remove the right-hand code.

I mean that bushes should be able to produce right-sided plans.
So - remove right-sided coded.

Vadim

Thu, 18 Feb 1999 11:35:39 +0700
Vadim Mikheev <vadim@krs.ru>
Re: [HACKERS] Bushy Plans fixed
> Bruce Momjian wrote:
> > 
> > > > Frankly, I would like to enable bushy plans and right-handed plans by
> > > > default.  The optimizer is now fast enough that a 9-table join is almost
> > > > instantaneous, even with bush plans.  People are not sophisticated
> > > > enough to know when to enables these options themselves. I am not sure I
> > > > am either.  I would like to enables both, fix whatever breaks, and
> > > > programmatically enable the options when they make sense.
> > >
> > > We need not in right-sided plans code any more.
> > > I agreed that we should enable bushes by default.
> > 
> > Are you saying right-hand plans are not useful if we have bushy plans?
> > If so, I will remove the right-hand code.
> 
> I mean that bushes should be able to produce right-sided plans.
> So - remove right-sided coded.

The bushy code joined joined relations, not base relations.  Currently,
base relations are always inner without right-hand plans.  See
make_rels_by_clause_joins() and let me know what needs to be changed, or
feel free to modify it yourself.  I will check your modifications.


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Wed, 17 Feb 1999 23:43:45 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Bushy Plans fixed
Bruce Momjian wrote:
> 
> >
> > I mean that bushes should be able to produce right-sided plans.
> > So - remove right-sided coded.
> 
> The bushy code joined joined relations, not base relations.  Currently,
> base relations are always inner without right-hand plans.  See
> make_rels_by_clause_joins() and let me know what needs to be changed, or
> feel free to modify it yourself.  I will check your modifications.

No time -:(
So, leave right-sided plans as is and enable them and bashes
by default.

Vadim

Thu, 18 Feb 1999 11:57:32 +0700
Vadim Mikheev <vadim@krs.ru>
Re: [HACKERS] Bushy Plans fixed
> Bruce Momjian wrote:
> > 
> > >
> > > I mean that bushes should be able to produce right-sided plans.
> > > So - remove right-sided coded.
> > 
> > The bushy code joined joined relations, not base relations.  Currently,
> > base relations are always inner without right-hand plans.  See
> > make_rels_by_clause_joins() and let me know what needs to be changed, or
> > feel free to modify it yourself.  I will check your modifications.
> 
> No time -:(
> So, leave right-sided plans as is and enable them and bashes
> by default.

I have removed right-sided plans in the bushy case, because the joins
will happen on their own when it processes the other joined rel.

In the joined rel/base rel join case, the code is not going to
auto-handle this, is it, without right-handed plans?

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Thu, 18 Feb 1999 00:10:16 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Bushy Plans fixed