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
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
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.000000The 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, Pennsylvania 19026Bruce 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, Pennsylvania 19026Bruce 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, Pennsylvania 19026