Re: How to create a cost-optimized plan from the Framework

[Γιώργος Θεοδωράκης <gi...@gmail.com>]

Hello Jordan,

I try to create my custom cost functions, and as you suggested I should
built my own relational algebra and override computeSelfCost. After I have
created my CustomRelNodes, I want to add some of the rules Calcite has to
optimize my initial plan. Till now, I am using heuristic planner (I can't
make volcano to run as I posted here https://mail-archives.apache.org/mod_
mbox/calcite-dev/201610.mbox/browser Subject :How to use VolcanoPlanner) and
20+ of your built in rules. When trying to optimize my CustomRelNodes,
should I rewrite all these rules from the start? Another problem I face, is
that I want to find the maximum (I would like to use rate-based cost model
and take the plan withe maximum output rate). Is it possible to change the
optimization goal of the planner? Finally, could I still use the heuristic
planner or should I use the cost-based Volcano?

Any piece of advice would be very helpful.

Thank you in advance,
George

2016-09-20 20:08 GMT+03:00 jordan.halterman@gmail.com <
jordan.halterman@gmail.com>:

> You got most of the way there, but to optimize the plan you need to add
> programs to your framework configuration. See the programs() method of the
> framework config.
>
> A Program is essentially a RelOptPlanner and a set of rules to apply. You
> can add several Programs to your Planner by using the various static
> Programs methods, e.g. Programs.ofRules(rules) to use the VolcanoPlanner or
> Programs.hep to use the HepPlanner. Once properly configured, you use the
> Planner's transform method to apply one of those Programs to the converted
> RelNode, also passing a set of required output traits. You can find some
> good examples of this in PlannerTest:
>
> https://github.com/apache/calcite/blob/master/core/src/
> test/java/org/apache/calcite/tools/PlannerTest.java
>
> planner.transform(0, planner.getEmptyTraitSet().
> replace(EumerableConvention.INSTANCE), rel)
>
> The PlannerTest uses something like that to convert the logical plan into
> a physical plan that outputs Enumerable. Often, queries may go through
> several phases of optimization that include some heuristic optimizations
> and some use of the cost-based optimizer.
>
> As for implementing custom cost functions, you need to implement your own
> relational algebra for that. Look at some of the examples in Calcite like
> JdbcJoin, JdbcPoject, etc. You create your own custom calling Convention
> that registers converter rules for your relational algebra. So, you might
> be using MyCustomConvention.INSTANCE rather than Calcite's
> EnumerableConvention to get the planner to convert to your desired algebra.
> Your MyCustomConvention registers rules that create your own algebra like
> MyCustomProject or MyCustomFilter. Then, in MyCustomProject or any of your
> other custom RelNodes, you override computeSelfCost to change the cost of
> the expression, and use Programs.ofRules to construct a cost based
> optimizer with any additional rules you want to use.
>
> > On Sep 20, 2016, at 5:51 AM, Hiroyuki Yamada <mo...@gmail.com> wrote:
> >
> > Hi,
> >
> > I'm wondering how to create a cost-optimized plan from the Framework.
> > I am currently in the following phase and successfully created a
> > logical plan (, which I think is not optimized based on costs),
> > but I'm not sure how to create a plan which is optimized based on
> > costs from the logical plan.
> >
> > FrameworkConfig calciteFrameworkConfig = Frameworks.newConfigBuilder()
> >    .parserConfig(SqlParser.configBuilder()
> >    .setLex(Lex.MYSQL)
> >    .build())
> >    .defaultSchema(schema)
> >    .traitDefs(traitDefs)
> >    .context(Contexts.EMPTY_CONTEXT)
> >    .ruleSets(RuleSets.ofList())
> >    .costFactory(null)
> >    .typeSystem(RelDataTypeSystem.DEFAULT)
> >    .build();
> >
> > planner = Frameworks.getPlanner(calciteFrameworkConfig);
> > sqlNode = planner.parse(query);
> > SqlNode validatedSqlNode = planner.validate(sqlNode);
> > RelRoot root = planner.rel(validatedSqlNode)
> > // I don't know what to do to optimize the logical plan
> > // I also don't know how to modify the existing costs for each node
> >
> >
> > Would any teach me how to do that ?
> > (If there are some examples or documents somewhere, it would be great
> > if you point them.)
> > Also, would anyone give me some idea about how to modify the default
> costs ?
> >
> >
> > Best regards,
> > Hiro
>