You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by JD Zheng <jd...@gmail.com> on 2017/08/08 22:50:48 UTC
Help with planner optimize performance issue
Hi,
We use RelBuilder to build logical relational algebra trees for our domain specific language. It is possible that the user might construct a relational algebra tree with several “where” clause. In this case, the RelBuilder will build a relnode tree with multiple filters one followed by another back to back. We noticed that the optimize time for such tree grows exponentially. For example, with 1 where clause, normally it took a few ms. However, with 7 where clauses, the optimize time will take almost 1 minute.
Before I dig into the planner, does anyone encounter similar performance issue or have any suggestion as what to look into? Thanks,
-JD
Re: Help with planner optimize performance issue
Posted by JD Zheng <jd...@gmail.com>.
Yes, we are using the Volcano planner.
Thanks TED and Julian. That’s very helpful suggestions!
-JD
> On Aug 8, 2017, at 9:24 PM, Ted Xu <fr...@gmail.com> wrote:
>
> Hi JD,
>
> If you're using Volcano planner, it is common sense that applying rules
> will expand search space. However, if you put your rules carefully, the
> space expansion is linear to rule apply and rels. According to your test
> result, it's better if you can check your rules to see if there is
> exponential expansion (e.g., unlimited join ordering).
>
> There are still 2 ways to narrow down your search space,
>
> 1. Set RelNode importantce = 0 during optimzation, which tells the Volcano
> planner to cease further exploration (rule apply) on the very RelNode. You
> should use it carefully because it may cause CannotPlanException.
> 2. Use HepPlanner instead of VolcanoPlanner. Instead of retaining all
> optional plan, HepPlanner will heuristically choose one route at each rule
> apply. The search space is very limited but you may get sub-optimal plan.
>
> Regards,
> Ted Xu
>
> On Wed, Aug 9, 2017 at 9:53 AM JD Zheng <jd...@gmail.com> wrote:
>
>> Hi,
>>
>> We use RelBuilder to build logical relational algebra trees for our domain
>> specific language. It is possible that the user might construct a
>> relational algebra tree with several “where” clause. In this case, the
>> RelBuilder will build a relnode tree with multiple filters one followed by
>> another back to back. We noticed that the optimize time for such tree grows
>> exponentially. For example, with 1 where clause, normally it took a few ms.
>> However, with 7 where clauses, the optimize time will take almost 1 minute.
>>
>> Before I dig into the planner, does anyone encounter similar performance
>> issue or have any suggestion as what to look into? Thanks,
>>
>> -JD
Re: Help with planner optimize performance issue
Posted by Julian Hyde <jh...@gmail.com>.
I agree with what Ted said. In this case, you could use HepPlanner to tidy up the tree - combining filters - before you go into the VolcanoPlanner phase.
Real-world planners use tricks such as multi-phase optimization, because otherwise there are combinatorial traps.
Julian
> On Aug 8, 2017, at 9:24 PM, Ted Xu <fr...@gmail.com> wrote:
>
> Hi JD,
>
> If you're using Volcano planner, it is common sense that applying rules
> will expand search space. However, if you put your rules carefully, the
> space expansion is linear to rule apply and rels. According to your test
> result, it's better if you can check your rules to see if there is
> exponential expansion (e.g., unlimited join ordering).
>
> There are still 2 ways to narrow down your search space,
>
> 1. Set RelNode importantce = 0 during optimzation, which tells the Volcano
> planner to cease further exploration (rule apply) on the very RelNode. You
> should use it carefully because it may cause CannotPlanException.
> 2. Use HepPlanner instead of VolcanoPlanner. Instead of retaining all
> optional plan, HepPlanner will heuristically choose one route at each rule
> apply. The search space is very limited but you may get sub-optimal plan.
>
> Regards,
> Ted Xu
>
>> On Wed, Aug 9, 2017 at 9:53 AM JD Zheng <jd...@gmail.com> wrote:
>>
>> Hi,
>>
>> We use RelBuilder to build logical relational algebra trees for our domain
>> specific language. It is possible that the user might construct a
>> relational algebra tree with several “where” clause. In this case, the
>> RelBuilder will build a relnode tree with multiple filters one followed by
>> another back to back. We noticed that the optimize time for such tree grows
>> exponentially. For example, with 1 where clause, normally it took a few ms.
>> However, with 7 where clauses, the optimize time will take almost 1 minute.
>>
>> Before I dig into the planner, does anyone encounter similar performance
>> issue or have any suggestion as what to look into? Thanks,
>>
>> -JD
Re: Help with planner optimize performance issue
Posted by Ted Xu <fr...@gmail.com>.
Hi JD,
If you're using Volcano planner, it is common sense that applying rules
will expand search space. However, if you put your rules carefully, the
space expansion is linear to rule apply and rels. According to your test
result, it's better if you can check your rules to see if there is
exponential expansion (e.g., unlimited join ordering).
There are still 2 ways to narrow down your search space,
1. Set RelNode importantce = 0 during optimzation, which tells the Volcano
planner to cease further exploration (rule apply) on the very RelNode. You
should use it carefully because it may cause CannotPlanException.
2. Use HepPlanner instead of VolcanoPlanner. Instead of retaining all
optional plan, HepPlanner will heuristically choose one route at each rule
apply. The search space is very limited but you may get sub-optimal plan.
Regards,
Ted Xu
On Wed, Aug 9, 2017 at 9:53 AM JD Zheng <jd...@gmail.com> wrote:
> Hi,
>
> We use RelBuilder to build logical relational algebra trees for our domain
> specific language. It is possible that the user might construct a
> relational algebra tree with several “where” clause. In this case, the
> RelBuilder will build a relnode tree with multiple filters one followed by
> another back to back. We noticed that the optimize time for such tree grows
> exponentially. For example, with 1 where clause, normally it took a few ms.
> However, with 7 where clauses, the optimize time will take almost 1 minute.
>
> Before I dig into the planner, does anyone encounter similar performance
> issue or have any suggestion as what to look into? Thanks,
>
> -JD