Обсуждение: Bushy Plans fixed
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
MergeJoinsize=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,
Pennsylvania19026
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
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
> > 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, Pennsylvania19026
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
> 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, Pennsylvania19026
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
> 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, Pennsylvania19026