You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Jihoon Son <ji...@apache.org> on 2021/03/09 02:37:33 UTC
Question about parallel query planning
Hi all,
I posted the same question on the ASF slack channel, but am posting
here as well to get a quicker response.
I'm seeing an issue in query planning that it takes a long time (+5
sec) for a giant union query that has 120 subqueries in it. I captured
a flame graph (attached in this email) to see where the bottleneck is,
and based on the flame graph, I believe the query planner spent most
of time to explore the search space of candidate plans to find the
best plan. This seems because of those many subqueries in the same
union query. Is my understanding correct? If so, for this particular
case, it seems possible to parallelize exploring the search space. Do
you have any plan for parallelizing this part? I'm not sure whether
it's already done though in the master branch. I tried to search for a
jira ticket on https://issues.apache.org/jira/browse/CALCITE, but
couldn't find anything with my search skill.
Thanks,
Jihoon
Re: Question about parallel query planning
Posted by JiaTao Tao <ta...@gmail.com>.
Yes, it has the benefit.
Regards!
Aron Tao
Julian Hyde <jh...@gmail.com> 于2021年3月12日周五 上午5:11写道：
> Are these, by any chance, pair-wise unions that can be flattened to n-way
> unions? That kind of transformation is almost always beneficial.
>
> Julian
>
> > On Mar 11, 2021, at 12:34 AM, JiaTao Tao <ta...@gmail.com> wrote:
> >
> > Hi Jihoon Son
> > I met the same problem(hundreds of union), and my advice is to move some
> > rules to hep planner, like sub-query remove, union merge, etc. And this
> > works for me.
> >
> > Regards!
> >
> > Aron Tao
> >
> >
> > Julian Hyde <jh...@apache.org> 于2021年3月10日周三 上午2:59写道：
> >
> >> At a high level, the Volcano/Cascades planning algorithm is amenable
> >> to parallelization. It uses a "work queue" (of matched rules that have
> >> not been applied yet) and each task is additive (adds relational
> >> expressions to the graph of relational expressions and their
> >> equivalence sets, and things are immutable once added to the graph).
> >>
> >> The devil will be in the details: making sure that the shared data
> >> structures work correctly when other threads are modifying them. For
> >> example, what happens when I try to add a RelNode to a set that is
> >> currently being merged merged with another set?
> >>
> >> Other shared data structures include metadata (aka statistics) and
> >> type factories. I think that their APIs are in fairly good shape for
> >> making them parallel.
> >>
> >> Julian
> >>
> >>
> >>> On Tue, Mar 9, 2021 at 10:45 AM Jihoon Son <ji...@apache.org>
> wrote:
> >>>
> >>> Hi Vladimir, thank you for your reply.
> >>>
> >>> 5 sec might not be bad from a technical point of view, but our user
> >>> wants their queries to finish in 2 - 3 seconds including planning
> >>> time. The actual query execution time for this particular query was 2
> >>> seconds which can be improved to 20 ms in my testing. However, the
> >>> planning time is the bottleneck and thus improving execution time did
> >>> not help much in this case.
> >>>
> >>>> Did you have a chance to check which exact rules contributed to the
> >> planning time? You may inject a listener to VolcanoPlanner to check
> that.
> >>>
> >>> I didn't before, so I just looked at the code to learn how to inject a
> >>> listener to VolcanoPlanner. But I'm not sure how I can do it. We are
> >>> creating a org.apache.calcite.prepare.PlannerImpl using
> >>> org.apache.calcite.tools.Frameworks.getPlanner()
> >>> (
> >>
> https://github.com/apache/druid/blob/master/sql/src/main/java/org/apache/druid/sql/calcite/planner/DruidPlanner.java#L89
> >> ).
> >>> This PlannerImpl has VolcanoPlanner in it, but neither expose it to
> >>> outside nor provide an interface for adding a listener. I guess I can
> >>> add an interface in PlannerImpl (and Planner) and make a custom build
> >>> of Calcite. But I'm wondering if there is a way that I can inject a
> >>> listener without making a custom build.
> >>>
> >>> Jihoon
> >>>
> >>> On Tue, Mar 9, 2021 at 12:03 AM Vladimir Ozerov <pp...@gmail.com>
> >> wrote:
> >>>>
> >>>> *at such = at such scale
> >>>>
> >>>> Вт, 9 марта 2021 г. в 11:01, Vladimir Ozerov <pp...@gmail.com>:
> >>>>
> >>>>> Hi Jihoon,
> >>>>>
> >>>>> I would say that 5 sec could be actually a pretty good result at
> >> such. Did
> >>>>> you have a chance to check which exact rules contributed to the
> >> planning
> >>>>> time? You may inject a listener to VolcanoPlanner to check that.
> >>>>>
> >>>>> Regards,
> >>>>> Vladimir
> >>>>>
> >>>>> Вт, 9 марта 2021 г. в 05:37, Jihoon Son <ji...@apache.org>:
> >>>>>
> >>>>>> Hi all,
> >>>>>>
> >>>>>> I posted the same question on the ASF slack channel, but am posting
> >>>>>> here as well to get a quicker response.
> >>>>>>
> >>>>>> I'm seeing an issue in query planning that it takes a long time (+5
> >>>>>> sec) for a giant union query that has 120 subqueries in it. I
> >> captured
> >>>>>> a flame graph (attached in this email) to see where the bottleneck
> >> is,
> >>>>>> and based on the flame graph, I believe the query planner spent most
> >>>>>> of time to explore the search space of candidate plans to find the
> >>>>>> best plan. This seems because of those many subqueries in the same
> >>>>>> union query. Is my understanding correct? If so, for this particular
> >>>>>> case, it seems possible to parallelize exploring the search space.
> >> Do
> >>>>>> you have any plan for parallelizing this part? I'm not sure whether
> >>>>>> it's already done though in the master branch. I tried to search
> >> for a
> >>>>>> jira ticket on https://issues.apache.org/jira/browse/CALCITE, but
> >>>>>> couldn't find anything with my search skill.
> >>>>>>
> >>>>>> Thanks,
> >>>>>> Jihoon
> >>>>>>
> >>>>>
> >>
>
Re: Question about parallel query planning
Posted by Vladimir Sitnikov <si...@gmail.com>.
Jihoon, could you share a reproducer?
If the issue can be reproduced within Calcite codebase, then it would be
helpful to have such a test.
Vladimir
Re: Question about parallel query planning
Posted by JiaTao Tao <ta...@gmail.com>.
You can move the union merge rule to hep, this rule has the benefit.
Regards!
Aron Tao
Jihoon Son <ji...@apache.org> 于2021年3月12日周五 上午6:36写道：
> Julian, thank you for the pointers. I will look at more closely how we
> can make the shared data structures thread-safe.
>
> > Are these, by any chance, pair-wise unions that can be flattened to
> n-way unions? That kind of transformation is almost always beneficial.
>
> Can you elaborate more on this idea? What do you mean by n-way unions?
> This particular query I'm looking at is a flat union query that has
> 121 simple scan queries. All these subqueries have a pattern of
> "SELECT 'string_literal' FROM table WHERE filter LIMIT 1". I think
> this union query can be rewritten to a similar query to avoid using
> UNIONs at all, such as a scan query using CASE WHEN or an aggregate
> query using aggregate functions with FILTER. However, execution time
> of the rewritten query would be slower than the original union query
> as the LIMIT clause cannot be pushed down to scan.
>
> > my advice is to move some rules to hep planner, like sub-query remove,
> union merge, etc.
>
> Aron, thank you for the tip. The subquery remove rule is already
> processed by HepPlanner. For the union merge rule, we disabled it
> because of the performance issue with unions. Do you have any other
> suggestions for what rules to move?
>
> Thanks,
> Jihoon
>
> On Thu, Mar 11, 2021 at 1:11 PM Julian Hyde <jh...@gmail.com>
> wrote:
> >
> > Are these, by any chance, pair-wise unions that can be flattened to
> n-way unions? That kind of transformation is almost always beneficial.
> >
> > Julian
> >
> > > On Mar 11, 2021, at 12:34 AM, JiaTao Tao <ta...@gmail.com> wrote:
> > >
> > > Hi Jihoon Son
> > > I met the same problem(hundreds of union), and my advice is to move
> some
> > > rules to hep planner, like sub-query remove, union merge, etc. And this
> > > works for me.
> > >
> > > Regards!
> > >
> > > Aron Tao
> > >
> > >
> > > Julian Hyde <jh...@apache.org> 于2021年3月10日周三 上午2:59写道：
> > >
> > >> At a high level, the Volcano/Cascades planning algorithm is amenable
> > >> to parallelization. It uses a "work queue" (of matched rules that have
> > >> not been applied yet) and each task is additive (adds relational
> > >> expressions to the graph of relational expressions and their
> > >> equivalence sets, and things are immutable once added to the graph).
> > >>
> > >> The devil will be in the details: making sure that the shared data
> > >> structures work correctly when other threads are modifying them. For
> > >> example, what happens when I try to add a RelNode to a set that is
> > >> currently being merged merged with another set?
> > >>
> > >> Other shared data structures include metadata (aka statistics) and
> > >> type factories. I think that their APIs are in fairly good shape for
> > >> making them parallel.
> > >>
> > >> Julian
> > >>
> > >>
> > >>> On Tue, Mar 9, 2021 at 10:45 AM Jihoon Son <ji...@apache.org>
> wrote:
> > >>>
> > >>> Hi Vladimir, thank you for your reply.
> > >>>
> > >>> 5 sec might not be bad from a technical point of view, but our user
> > >>> wants their queries to finish in 2 - 3 seconds including planning
> > >>> time. The actual query execution time for this particular query was 2
> > >>> seconds which can be improved to 20 ms in my testing. However, the
> > >>> planning time is the bottleneck and thus improving execution time did
> > >>> not help much in this case.
> > >>>
> > >>>> Did you have a chance to check which exact rules contributed to the
> > >> planning time? You may inject a listener to VolcanoPlanner to check
> that.
> > >>>
> > >>> I didn't before, so I just looked at the code to learn how to inject
> a
> > >>> listener to VolcanoPlanner. But I'm not sure how I can do it. We are
> > >>> creating a org.apache.calcite.prepare.PlannerImpl using
> > >>> org.apache.calcite.tools.Frameworks.getPlanner()
> > >>> (
> > >>
> https://github.com/apache/druid/blob/master/sql/src/main/java/org/apache/druid/sql/calcite/planner/DruidPlanner.java#L89
> > >> ).
> > >>> This PlannerImpl has VolcanoPlanner in it, but neither expose it to
> > >>> outside nor provide an interface for adding a listener. I guess I can
> > >>> add an interface in PlannerImpl (and Planner) and make a custom build
> > >>> of Calcite. But I'm wondering if there is a way that I can inject a
> > >>> listener without making a custom build.
> > >>>
> > >>> Jihoon
> > >>>
> > >>> On Tue, Mar 9, 2021 at 12:03 AM Vladimir Ozerov <pp...@gmail.com>
> > >> wrote:
> > >>>>
> > >>>> *at such = at such scale
> > >>>>
> > >>>> Вт, 9 марта 2021 г. в 11:01, Vladimir Ozerov <pp...@gmail.com>:
> > >>>>
> > >>>>> Hi Jihoon,
> > >>>>>
> > >>>>> I would say that 5 sec could be actually a pretty good result at
> > >> such. Did
> > >>>>> you have a chance to check which exact rules contributed to the
> > >> planning
> > >>>>> time? You may inject a listener to VolcanoPlanner to check that.
> > >>>>>
> > >>>>> Regards,
> > >>>>> Vladimir
> > >>>>>
> > >>>>> Вт, 9 марта 2021 г. в 05:37, Jihoon Son <ji...@apache.org>:
> > >>>>>
> > >>>>>> Hi all,
> > >>>>>>
> > >>>>>> I posted the same question on the ASF slack channel, but am
> posting
> > >>>>>> here as well to get a quicker response.
> > >>>>>>
> > >>>>>> I'm seeing an issue in query planning that it takes a long time
> (+5
> > >>>>>> sec) for a giant union query that has 120 subqueries in it. I
> > >> captured
> > >>>>>> a flame graph (attached in this email) to see where the bottleneck
> > >> is,
> > >>>>>> and based on the flame graph, I believe the query planner spent
> most
> > >>>>>> of time to explore the search space of candidate plans to find the
> > >>>>>> best plan. This seems because of those many subqueries in the same
> > >>>>>> union query. Is my understanding correct? If so, for this
> particular
> > >>>>>> case, it seems possible to parallelize exploring the search space.
> > >> Do
> > >>>>>> you have any plan for parallelizing this part? I'm not sure
> whether
> > >>>>>> it's already done though in the master branch. I tried to search
> > >> for a
> > >>>>>> jira ticket on https://issues.apache.org/jira/browse/CALCITE, but
> > >>>>>> couldn't find anything with my search skill.
> > >>>>>>
> > >>>>>> Thanks,
> > >>>>>> Jihoon
> > >>>>>>
> > >>>>>
> > >>
>
Re: Question about parallel query planning
Posted by Jihoon Son <ji...@apache.org>.
Julian, thank you for the pointers. I will look at more closely how we
can make the shared data structures thread-safe.
> Are these, by any chance, pair-wise unions that can be flattened to n-way unions? That kind of transformation is almost always beneficial.
Can you elaborate more on this idea? What do you mean by n-way unions?
This particular query I'm looking at is a flat union query that has
121 simple scan queries. All these subqueries have a pattern of
"SELECT 'string_literal' FROM table WHERE filter LIMIT 1". I think
this union query can be rewritten to a similar query to avoid using
UNIONs at all, such as a scan query using CASE WHEN or an aggregate
query using aggregate functions with FILTER. However, execution time
of the rewritten query would be slower than the original union query
as the LIMIT clause cannot be pushed down to scan.
> my advice is to move some rules to hep planner, like sub-query remove, union merge, etc.
Aron, thank you for the tip. The subquery remove rule is already
processed by HepPlanner. For the union merge rule, we disabled it
because of the performance issue with unions. Do you have any other
suggestions for what rules to move?
Thanks,
Jihoon
On Thu, Mar 11, 2021 at 1:11 PM Julian Hyde <jh...@gmail.com> wrote:
>
> Are these, by any chance, pair-wise unions that can be flattened to n-way unions? That kind of transformation is almost always beneficial.
>
> Julian
>
> > On Mar 11, 2021, at 12:34 AM, JiaTao Tao <ta...@gmail.com> wrote:
> >
> > Hi Jihoon Son
> > I met the same problem(hundreds of union), and my advice is to move some
> > rules to hep planner, like sub-query remove, union merge, etc. And this
> > works for me.
> >
> > Regards!
> >
> > Aron Tao
> >
> >
> > Julian Hyde <jh...@apache.org> 于2021年3月10日周三 上午2:59写道：
> >
> >> At a high level, the Volcano/Cascades planning algorithm is amenable
> >> to parallelization. It uses a "work queue" (of matched rules that have
> >> not been applied yet) and each task is additive (adds relational
> >> expressions to the graph of relational expressions and their
> >> equivalence sets, and things are immutable once added to the graph).
> >>
> >> The devil will be in the details: making sure that the shared data
> >> structures work correctly when other threads are modifying them. For
> >> example, what happens when I try to add a RelNode to a set that is
> >> currently being merged merged with another set?
> >>
> >> Other shared data structures include metadata (aka statistics) and
> >> type factories. I think that their APIs are in fairly good shape for
> >> making them parallel.
> >>
> >> Julian
> >>
> >>
> >>> On Tue, Mar 9, 2021 at 10:45 AM Jihoon Son <ji...@apache.org> wrote:
> >>>
> >>> Hi Vladimir, thank you for your reply.
> >>>
> >>> 5 sec might not be bad from a technical point of view, but our user
> >>> wants their queries to finish in 2 - 3 seconds including planning
> >>> time. The actual query execution time for this particular query was 2
> >>> seconds which can be improved to 20 ms in my testing. However, the
> >>> planning time is the bottleneck and thus improving execution time did
> >>> not help much in this case.
> >>>
> >>>> Did you have a chance to check which exact rules contributed to the
> >> planning time? You may inject a listener to VolcanoPlanner to check that.
> >>>
> >>> I didn't before, so I just looked at the code to learn how to inject a
> >>> listener to VolcanoPlanner. But I'm not sure how I can do it. We are
> >>> creating a org.apache.calcite.prepare.PlannerImpl using
> >>> org.apache.calcite.tools.Frameworks.getPlanner()
> >>> (
> >> https://github.com/apache/druid/blob/master/sql/src/main/java/org/apache/druid/sql/calcite/planner/DruidPlanner.java#L89
> >> ).
> >>> This PlannerImpl has VolcanoPlanner in it, but neither expose it to
> >>> outside nor provide an interface for adding a listener. I guess I can
> >>> add an interface in PlannerImpl (and Planner) and make a custom build
> >>> of Calcite. But I'm wondering if there is a way that I can inject a
> >>> listener without making a custom build.
> >>>
> >>> Jihoon
> >>>
> >>> On Tue, Mar 9, 2021 at 12:03 AM Vladimir Ozerov <pp...@gmail.com>
> >> wrote:
> >>>>
> >>>> *at such = at such scale
> >>>>
> >>>> Вт, 9 марта 2021 г. в 11:01, Vladimir Ozerov <pp...@gmail.com>:
> >>>>
> >>>>> Hi Jihoon,
> >>>>>
> >>>>> I would say that 5 sec could be actually a pretty good result at
> >> such. Did
> >>>>> you have a chance to check which exact rules contributed to the
> >> planning
> >>>>> time? You may inject a listener to VolcanoPlanner to check that.
> >>>>>
> >>>>> Regards,
> >>>>> Vladimir
> >>>>>
> >>>>> Вт, 9 марта 2021 г. в 05:37, Jihoon Son <ji...@apache.org>:
> >>>>>
> >>>>>> Hi all,
> >>>>>>
> >>>>>> I posted the same question on the ASF slack channel, but am posting
> >>>>>> here as well to get a quicker response.
> >>>>>>
> >>>>>> I'm seeing an issue in query planning that it takes a long time (+5
> >>>>>> sec) for a giant union query that has 120 subqueries in it. I
> >> captured
> >>>>>> a flame graph (attached in this email) to see where the bottleneck
> >> is,
> >>>>>> and based on the flame graph, I believe the query planner spent most
> >>>>>> of time to explore the search space of candidate plans to find the
> >>>>>> best plan. This seems because of those many subqueries in the same
> >>>>>> union query. Is my understanding correct? If so, for this particular
> >>>>>> case, it seems possible to parallelize exploring the search space.
> >> Do
> >>>>>> you have any plan for parallelizing this part? I'm not sure whether
> >>>>>> it's already done though in the master branch. I tried to search
> >> for a
> >>>>>> jira ticket on https://issues.apache.org/jira/browse/CALCITE, but
> >>>>>> couldn't find anything with my search skill.
> >>>>>>
> >>>>>> Thanks,
> >>>>>> Jihoon
> >>>>>>
> >>>>>
> >>
Re: Question about parallel query planning
Posted by Julian Hyde <jh...@gmail.com>.
Are these, by any chance, pair-wise unions that can be flattened to n-way unions? That kind of transformation is almost always beneficial.
Julian
> On Mar 11, 2021, at 12:34 AM, JiaTao Tao <ta...@gmail.com> wrote:
>
> Hi Jihoon Son
> I met the same problem(hundreds of union), and my advice is to move some
> rules to hep planner, like sub-query remove, union merge, etc. And this
> works for me.
>
> Regards!
>
> Aron Tao
>
>
> Julian Hyde <jh...@apache.org> 于2021年3月10日周三 上午2:59写道：
>
>> At a high level, the Volcano/Cascades planning algorithm is amenable
>> to parallelization. It uses a "work queue" (of matched rules that have
>> not been applied yet) and each task is additive (adds relational
>> expressions to the graph of relational expressions and their
>> equivalence sets, and things are immutable once added to the graph).
>>
>> The devil will be in the details: making sure that the shared data
>> structures work correctly when other threads are modifying them. For
>> example, what happens when I try to add a RelNode to a set that is
>> currently being merged merged with another set?
>>
>> Other shared data structures include metadata (aka statistics) and
>> type factories. I think that their APIs are in fairly good shape for
>> making them parallel.
>>
>> Julian
>>
>>
>>> On Tue, Mar 9, 2021 at 10:45 AM Jihoon Son <ji...@apache.org> wrote:
>>>
>>> Hi Vladimir, thank you for your reply.
>>>
>>> 5 sec might not be bad from a technical point of view, but our user
>>> wants their queries to finish in 2 - 3 seconds including planning
>>> time. The actual query execution time for this particular query was 2
>>> seconds which can be improved to 20 ms in my testing. However, the
>>> planning time is the bottleneck and thus improving execution time did
>>> not help much in this case.
>>>
>>>> Did you have a chance to check which exact rules contributed to the
>> planning time? You may inject a listener to VolcanoPlanner to check that.
>>>
>>> I didn't before, so I just looked at the code to learn how to inject a
>>> listener to VolcanoPlanner. But I'm not sure how I can do it. We are
>>> creating a org.apache.calcite.prepare.PlannerImpl using
>>> org.apache.calcite.tools.Frameworks.getPlanner()
>>> (
>> https://github.com/apache/druid/blob/master/sql/src/main/java/org/apache/druid/sql/calcite/planner/DruidPlanner.java#L89
>> ).
>>> This PlannerImpl has VolcanoPlanner in it, but neither expose it to
>>> outside nor provide an interface for adding a listener. I guess I can
>>> add an interface in PlannerImpl (and Planner) and make a custom build
>>> of Calcite. But I'm wondering if there is a way that I can inject a
>>> listener without making a custom build.
>>>
>>> Jihoon
>>>
>>> On Tue, Mar 9, 2021 at 12:03 AM Vladimir Ozerov <pp...@gmail.com>
>> wrote:
>>>>
>>>> *at such = at such scale
>>>>
>>>> Вт, 9 марта 2021 г. в 11:01, Vladimir Ozerov <pp...@gmail.com>:
>>>>
>>>>> Hi Jihoon,
>>>>>
>>>>> I would say that 5 sec could be actually a pretty good result at
>> such. Did
>>>>> you have a chance to check which exact rules contributed to the
>> planning
>>>>> time? You may inject a listener to VolcanoPlanner to check that.
>>>>>
>>>>> Regards,
>>>>> Vladimir
>>>>>
>>>>> Вт, 9 марта 2021 г. в 05:37, Jihoon Son <ji...@apache.org>:
>>>>>
>>>>>> Hi all,
>>>>>>
>>>>>> I posted the same question on the ASF slack channel, but am posting
>>>>>> here as well to get a quicker response.
>>>>>>
>>>>>> I'm seeing an issue in query planning that it takes a long time (+5
>>>>>> sec) for a giant union query that has 120 subqueries in it. I
>> captured
>>>>>> a flame graph (attached in this email) to see where the bottleneck
>> is,
>>>>>> and based on the flame graph, I believe the query planner spent most
>>>>>> of time to explore the search space of candidate plans to find the
>>>>>> best plan. This seems because of those many subqueries in the same
>>>>>> union query. Is my understanding correct? If so, for this particular
>>>>>> case, it seems possible to parallelize exploring the search space.
>> Do
>>>>>> you have any plan for parallelizing this part? I'm not sure whether
>>>>>> it's already done though in the master branch. I tried to search
>> for a
>>>>>> jira ticket on https://issues.apache.org/jira/browse/CALCITE, but
>>>>>> couldn't find anything with my search skill.
>>>>>>
>>>>>> Thanks,
>>>>>> Jihoon
>>>>>>
>>>>>
>>
Re: Question about parallel query planning
Posted by JiaTao Tao <ta...@gmail.com>.
Hi Jihoon Son
I met the same problem(hundreds of union), and my advice is to move some
rules to hep planner, like sub-query remove, union merge, etc. And this
works for me.
Regards!
Aron Tao
Julian Hyde <jh...@apache.org> 于2021年3月10日周三 上午2:59写道：
> At a high level, the Volcano/Cascades planning algorithm is amenable
> to parallelization. It uses a "work queue" (of matched rules that have
> not been applied yet) and each task is additive (adds relational
> expressions to the graph of relational expressions and their
> equivalence sets, and things are immutable once added to the graph).
>
> The devil will be in the details: making sure that the shared data
> structures work correctly when other threads are modifying them. For
> example, what happens when I try to add a RelNode to a set that is
> currently being merged merged with another set?
>
> Other shared data structures include metadata (aka statistics) and
> type factories. I think that their APIs are in fairly good shape for
> making them parallel.
>
> Julian
>
>
> On Tue, Mar 9, 2021 at 10:45 AM Jihoon Son <ji...@apache.org> wrote:
> >
> > Hi Vladimir, thank you for your reply.
> >
> > 5 sec might not be bad from a technical point of view, but our user
> > wants their queries to finish in 2 - 3 seconds including planning
> > time. The actual query execution time for this particular query was 2
> > seconds which can be improved to 20 ms in my testing. However, the
> > planning time is the bottleneck and thus improving execution time did
> > not help much in this case.
> >
> > > Did you have a chance to check which exact rules contributed to the
> planning time? You may inject a listener to VolcanoPlanner to check that.
> >
> > I didn't before, so I just looked at the code to learn how to inject a
> > listener to VolcanoPlanner. But I'm not sure how I can do it. We are
> > creating a org.apache.calcite.prepare.PlannerImpl using
> > org.apache.calcite.tools.Frameworks.getPlanner()
> > (
> https://github.com/apache/druid/blob/master/sql/src/main/java/org/apache/druid/sql/calcite/planner/DruidPlanner.java#L89
> ).
> > This PlannerImpl has VolcanoPlanner in it, but neither expose it to
> > outside nor provide an interface for adding a listener. I guess I can
> > add an interface in PlannerImpl (and Planner) and make a custom build
> > of Calcite. But I'm wondering if there is a way that I can inject a
> > listener without making a custom build.
> >
> > Jihoon
> >
> > On Tue, Mar 9, 2021 at 12:03 AM Vladimir Ozerov <pp...@gmail.com>
> wrote:
> > >
> > > *at such = at such scale
> > >
> > > Вт, 9 марта 2021 г. в 11:01, Vladimir Ozerov <pp...@gmail.com>:
> > >
> > > > Hi Jihoon,
> > > >
> > > > I would say that 5 sec could be actually a pretty good result at
> such. Did
> > > > you have a chance to check which exact rules contributed to the
> planning
> > > > time? You may inject a listener to VolcanoPlanner to check that.
> > > >
> > > > Regards,
> > > > Vladimir
> > > >
> > > > Вт, 9 марта 2021 г. в 05:37, Jihoon Son <ji...@apache.org>:
> > > >
> > > >> Hi all,
> > > >>
> > > >> I posted the same question on the ASF slack channel, but am posting
> > > >> here as well to get a quicker response.
> > > >>
> > > >> I'm seeing an issue in query planning that it takes a long time (+5
> > > >> sec) for a giant union query that has 120 subqueries in it. I
> captured
> > > >> a flame graph (attached in this email) to see where the bottleneck
> is,
> > > >> and based on the flame graph, I believe the query planner spent most
> > > >> of time to explore the search space of candidate plans to find the
> > > >> best plan. This seems because of those many subqueries in the same
> > > >> union query. Is my understanding correct? If so, for this particular
> > > >> case, it seems possible to parallelize exploring the search space.
> Do
> > > >> you have any plan for parallelizing this part? I'm not sure whether
> > > >> it's already done though in the master branch. I tried to search
> for a
> > > >> jira ticket on https://issues.apache.org/jira/browse/CALCITE, but
> > > >> couldn't find anything with my search skill.
> > > >>
> > > >> Thanks,
> > > >> Jihoon
> > > >>
> > > >
>
Re: Question about parallel query planning
Posted by Julian Hyde <jh...@apache.org>.
At a high level, the Volcano/Cascades planning algorithm is amenable
to parallelization. It uses a "work queue" (of matched rules that have
not been applied yet) and each task is additive (adds relational
expressions to the graph of relational expressions and their
equivalence sets, and things are immutable once added to the graph).
The devil will be in the details: making sure that the shared data
structures work correctly when other threads are modifying them. For
example, what happens when I try to add a RelNode to a set that is
currently being merged merged with another set?
Other shared data structures include metadata (aka statistics) and
type factories. I think that their APIs are in fairly good shape for
making them parallel.
Julian
On Tue, Mar 9, 2021 at 10:45 AM Jihoon Son <ji...@apache.org> wrote:
>
> Hi Vladimir, thank you for your reply.
>
> 5 sec might not be bad from a technical point of view, but our user
> wants their queries to finish in 2 - 3 seconds including planning
> time. The actual query execution time for this particular query was 2
> seconds which can be improved to 20 ms in my testing. However, the
> planning time is the bottleneck and thus improving execution time did
> not help much in this case.
>
> > Did you have a chance to check which exact rules contributed to the planning time? You may inject a listener to VolcanoPlanner to check that.
>
> I didn't before, so I just looked at the code to learn how to inject a
> listener to VolcanoPlanner. But I'm not sure how I can do it. We are
> creating a org.apache.calcite.prepare.PlannerImpl using
> org.apache.calcite.tools.Frameworks.getPlanner()
> (https://github.com/apache/druid/blob/master/sql/src/main/java/org/apache/druid/sql/calcite/planner/DruidPlanner.java#L89).
> This PlannerImpl has VolcanoPlanner in it, but neither expose it to
> outside nor provide an interface for adding a listener. I guess I can
> add an interface in PlannerImpl (and Planner) and make a custom build
> of Calcite. But I'm wondering if there is a way that I can inject a
> listener without making a custom build.
>
> Jihoon
>
> On Tue, Mar 9, 2021 at 12:03 AM Vladimir Ozerov <pp...@gmail.com> wrote:
> >
> > *at such = at such scale
> >
> > Вт, 9 марта 2021 г. в 11:01, Vladimir Ozerov <pp...@gmail.com>:
> >
> > > Hi Jihoon,
> > >
> > > I would say that 5 sec could be actually a pretty good result at such. Did
> > > you have a chance to check which exact rules contributed to the planning
> > > time? You may inject a listener to VolcanoPlanner to check that.
> > >
> > > Regards,
> > > Vladimir
> > >
> > > Вт, 9 марта 2021 г. в 05:37, Jihoon Son <ji...@apache.org>:
> > >
> > >> Hi all,
> > >>
> > >> I posted the same question on the ASF slack channel, but am posting
> > >> here as well to get a quicker response.
> > >>
> > >> I'm seeing an issue in query planning that it takes a long time (+5
> > >> sec) for a giant union query that has 120 subqueries in it. I captured
> > >> a flame graph (attached in this email) to see where the bottleneck is,
> > >> and based on the flame graph, I believe the query planner spent most
> > >> of time to explore the search space of candidate plans to find the
> > >> best plan. This seems because of those many subqueries in the same
> > >> union query. Is my understanding correct? If so, for this particular
> > >> case, it seems possible to parallelize exploring the search space. Do
> > >> you have any plan for parallelizing this part? I'm not sure whether
> > >> it's already done though in the master branch. I tried to search for a
> > >> jira ticket on https://issues.apache.org/jira/browse/CALCITE, but
> > >> couldn't find anything with my search skill.
> > >>
> > >> Thanks,
> > >> Jihoon
> > >>
> > >
Re: Question about parallel query planning
Posted by Jihoon Son <ji...@apache.org>.
Hi Vladimir, thank you for your reply.
5 sec might not be bad from a technical point of view, but our user
wants their queries to finish in 2 - 3 seconds including planning
time. The actual query execution time for this particular query was 2
seconds which can be improved to 20 ms in my testing. However, the
planning time is the bottleneck and thus improving execution time did
not help much in this case.
> Did you have a chance to check which exact rules contributed to the planning time? You may inject a listener to VolcanoPlanner to check that.
I didn't before, so I just looked at the code to learn how to inject a
listener to VolcanoPlanner. But I'm not sure how I can do it. We are
creating a org.apache.calcite.prepare.PlannerImpl using
org.apache.calcite.tools.Frameworks.getPlanner()
(https://github.com/apache/druid/blob/master/sql/src/main/java/org/apache/druid/sql/calcite/planner/DruidPlanner.java#L89).
This PlannerImpl has VolcanoPlanner in it, but neither expose it to
outside nor provide an interface for adding a listener. I guess I can
add an interface in PlannerImpl (and Planner) and make a custom build
of Calcite. But I'm wondering if there is a way that I can inject a
listener without making a custom build.
Jihoon
On Tue, Mar 9, 2021 at 12:03 AM Vladimir Ozerov <pp...@gmail.com> wrote:
>
> *at such = at such scale
>
> Вт, 9 марта 2021 г. в 11:01, Vladimir Ozerov <pp...@gmail.com>:
>
> > Hi Jihoon,
> >
> > I would say that 5 sec could be actually a pretty good result at such. Did
> > you have a chance to check which exact rules contributed to the planning
> > time? You may inject a listener to VolcanoPlanner to check that.
> >
> > Regards,
> > Vladimir
> >
> > Вт, 9 марта 2021 г. в 05:37, Jihoon Son <ji...@apache.org>:
> >
> >> Hi all,
> >>
> >> I posted the same question on the ASF slack channel, but am posting
> >> here as well to get a quicker response.
> >>
> >> I'm seeing an issue in query planning that it takes a long time (+5
> >> sec) for a giant union query that has 120 subqueries in it. I captured
> >> a flame graph (attached in this email) to see where the bottleneck is,
> >> and based on the flame graph, I believe the query planner spent most
> >> of time to explore the search space of candidate plans to find the
> >> best plan. This seems because of those many subqueries in the same
> >> union query. Is my understanding correct? If so, for this particular
> >> case, it seems possible to parallelize exploring the search space. Do
> >> you have any plan for parallelizing this part? I'm not sure whether
> >> it's already done though in the master branch. I tried to search for a
> >> jira ticket on https://issues.apache.org/jira/browse/CALCITE, but
> >> couldn't find anything with my search skill.
> >>
> >> Thanks,
> >> Jihoon
> >>
> >
Re: Question about parallel query planning
Posted by Vladimir Ozerov <pp...@gmail.com>.
*at such = at such scale
Вт, 9 марта 2021 г. в 11:01, Vladimir Ozerov <pp...@gmail.com>:
> Hi Jihoon,
>
> I would say that 5 sec could be actually a pretty good result at such. Did
> you have a chance to check which exact rules contributed to the planning
> time? You may inject a listener to VolcanoPlanner to check that.
>
> Regards,
> Vladimir
>
> Вт, 9 марта 2021 г. в 05:37, Jihoon Son <ji...@apache.org>:
>
>> Hi all,
>>
>> I posted the same question on the ASF slack channel, but am posting
>> here as well to get a quicker response.
>>
>> I'm seeing an issue in query planning that it takes a long time (+5
>> sec) for a giant union query that has 120 subqueries in it. I captured
>> a flame graph (attached in this email) to see where the bottleneck is,
>> and based on the flame graph, I believe the query planner spent most
>> of time to explore the search space of candidate plans to find the
>> best plan. This seems because of those many subqueries in the same
>> union query. Is my understanding correct? If so, for this particular
>> case, it seems possible to parallelize exploring the search space. Do
>> you have any plan for parallelizing this part? I'm not sure whether
>> it's already done though in the master branch. I tried to search for a
>> jira ticket on https://issues.apache.org/jira/browse/CALCITE, but
>> couldn't find anything with my search skill.
>>
>> Thanks,
>> Jihoon
>>
>
Re: Question about parallel query planning
Posted by Vladimir Ozerov <pp...@gmail.com>.
Hi Jihoon,
I would say that 5 sec could be actually a pretty good result at such. Did
you have a chance to check which exact rules contributed to the planning
time? You may inject a listener to VolcanoPlanner to check that.
Regards,
Vladimir
Вт, 9 марта 2021 г. в 05:37, Jihoon Son <ji...@apache.org>:
> Hi all,
>
> I posted the same question on the ASF slack channel, but am posting
> here as well to get a quicker response.
>
> I'm seeing an issue in query planning that it takes a long time (+5
> sec) for a giant union query that has 120 subqueries in it. I captured
> a flame graph (attached in this email) to see where the bottleneck is,
> and based on the flame graph, I believe the query planner spent most
> of time to explore the search space of candidate plans to find the
> best plan. This seems because of those many subqueries in the same
> union query. Is my understanding correct? If so, for this particular
> case, it seems possible to parallelize exploring the search space. Do
> you have any plan for parallelizing this part? I'm not sure whether
> it's already done though in the master branch. I tried to search for a
> jira ticket on https://issues.apache.org/jira/browse/CALCITE, but
> couldn't find anything with my search skill.
>
> Thanks,
> Jihoon
>