Ruben,
Il giorno gio 17 set 2020 alle ore 14:57 Ruben Q L <rube...@gmail.com> ha
scritto:
> Enrico,
>
> I cannot understand how you can arrive at scenario C. Theoretically, if
> there are not enough rules to implement a certain plan, you should get a
> CannotPlanException from RelOptPlanner#findBest. I would expect this
> outcome if e.g. you do not have "rules that handle the LIMIT". Moreover,
> having "wrong plans computed for joins" seems like a bug. But it is not
> easy to assess the situation and evaluate what is going on without the
> actual query and rules that are applied.
>
I will send a demo when I have cycles.
Thanks for your response.
Enrico
>
> Best regards,
> Ruben
>
>
> Le mer. 16 sept. 2020 à 17:24, Enrico Olivelli <eolive...@gmail.com> a
> écrit :
>
> > Ruben,
> > thank you very much, using your snippet I got the planner working !
> >
> > In HerdDB we are currently using
> > Programs.ofRules(Programs.RULE_SET) +RelOptUtils#registerDefaultRules
> (that
> > add lots of rules) and we are able to handle several cases of very
> complex
> > generic SQL.
> >
> > But If I try to reduce the number of rules I see this behaviour:
> > a) if I am lucky I get some error from the planner that tells that it
> can't
> > find a way to compute the plan -> I can switch to the full planner
> ruleset
> > b) If I am less lucky I have a plan that works but it is less efficient
> ->
> > (this is expected, and this is what I want, that is to trade planner
> speed
> > vs efficiency of plans
> > c) If I am unlucky the planner returns a plan that leads to wrong
> results !
> >
> > For instance you can see c) if you do not add rules that handle "LIMIT"
> and
> > simply the plan sometimes won't pick the LIMIT and the database will
> return
> > unexpected results
> > but I saw worse cases, like wrong plans computed for joins.
> >
> > So my next question is:
> > is there a way to create a minimal set of rules that covers simple SQL
> > (think about basic JPA stuff....) and eventually fails the plan in case
> of
> > unhandled cases ?
> >
> > Best regards
> > Enrico
> >
> > Il giorno mer 16 set 2020 alle ore 09:57 Ruben Q L <rube...@gmail.com>
> ha
> > scritto:
> >
> > > Hi Enrico,
> > >
> > > not sure if this can be applied to your case, but I also have a use
> case
> > > with HepPlanner and VolcanoPlanner.
> > > In my case I have an initial phase of rules applied with HepPlanner.
> > These
> > > are rules that optimize the logical plan and are always a "quickwin"
> > (they
> > > always generate a better version of the plan, so it makes sense to
> apply
> > > them with a Hep).
> > > Then, I have a "main" phase, applied with VolcanoPlanner, that takes
> care
> > > of converting the plan into the most optimized Enumerable result.
> > > A simplified code snippet would be:
> > >
> > > private static final Program PHASE_1 =
> > > Programs.of(HepProgram.builder().addRuleCollection(
> > > Arrays.asList(
> > > CoreRules.FILTER_INTO_JOIN,
> > > CoreRules.SORT_PROJECT_TRANSPOSE,
> > > CoreRules.SORT_JOIN_TRANSPOSE,
> > > CoreRules.FILTER_REDUCE_EXPRESSIONS,
> > > ...
> > > )).build(), false, MyRelMetadataProvider.INSTANCE);
> > >
> > > private static final List<RelOptRule> PHASE_2_RULES = Arrays.asList(
> > > EnumerableRules.ENUMERABLE_VALUES_RULE,
> > > EnumerableRules.ENUMERABLE_UNION_RULE,
> > > EnumerableRules.ENUMERABLE_FILTER_TO_CALC_RULE,
> > > EnumerableRules.ENUMERABLE_PROJECT_TO_CALC_RULE,
> > > EnumerableRules.ENUMERABLE_COLLECT_RULE,
> > > EnumerableRules.ENUMERABLE_UNCOLLECT_RULE,
> > > ...);
> > > private static final Program PHASE_2 =
> > > Programs.of(RuleSets.ofList(PHASE_2_RULES));
> > >
> > >
> > > EnumerableRel optimizeLogicalPlan(RelNode query)
> > > {
> > > Program optPhases = Programs.sequence(PHASE_1, PHASE_2);
> > > RelTraitSet desiredTraitSet = query.getTraitSet()
> > > .replace(EnumerableConvention.INSTANCE)
> > > .simplify();
> > > EnumerableRel result = (EnumerableRel) optPhases.run(
> > > query.getCluster().getPlanner(), // this is a VolcanoPlanner
> > > query,
> > > desiredTraitSet,
> > > Collections.emptyList(),
> > > Collections.emptyList());
> > > return result;
> > > }
> > >
> > >
> > > I hope it helps.
> > >
> > > Best regards,
> > > Ruben
> > >
> > >
> > > Le mar. 15 sept. 2020 à 17:43, Enrico Olivelli <eolive...@gmail.com> a
> > > écrit :
> > >
> > > > Hi,
> > > >
> > > > I am trying to create a two stage planner using HepPlanner and then
> > > > VolcanoPlanner
> > > >
> > > > Which is the correct sequence of steps to pass from SQL to
> Enumerable ?
> > > > My goal is to use Hep for very simple queries like simple INSERTs,
> > > SELECT *
> > > > FROM TABLE, SELECT * FROM TABLE WHERE pk=?....
> > > > Volcano is overkill for such stuff.
> > > >
> > > > This is my idea:
> > > > 1) get a Planner (that's Volcano)
> > > > Planner planner = Frameworks.getPlanner(config);
> > > >
> > > > 2) Get the logical plan
> > > > SqlNode n = planner.parse(query);
> > > > n = planner.validate(n);
> > > > RelNode logicalPlan = planner.rel(n).project();
> > > > 3) Create HepPlanner
> > > > HepProgram hepProgram =
> > > > HepProgram.
> > > > builder()
> > > > . ?? which Rules ?
> > > > .build();
> > > >
> > > > HepPlanner hepPlanner = new HepPlanner(hepProgram);
> > > > hepPlanner.addRelTraitDef(ConventionTraitDef.INSTANCE);
> > > >
> > > > hepPlanner.setRoot(logicalPlan);
> > > >
> > > > 4) Run HepPlanner
> > > > RelNode bestForHep = hepPlanner.findBestExp();
> > > >
> > > > 5) Pick Volcano
> > > >
> > > > final RelOptPlanner optPlanner = cluster.getPlanner();
> > > >
> > > > 6) Convert to Enumerable
> > > >
> > > > final RelOptPlanner optPlanner = cluster.getPlanner();
> > > >
> > > > optPlanner.addRule(CoreRules.FILTER_REDUCE_EXPRESSIONS);
> > > > RelTraitSet desiredTraits =
> > > > cluster.traitSet()
> > > > .replace(EnumerableConvention.INSTANCE);
> > > > final RelCollation collation =
> > > > logicalPlan instanceof Sort
> > > > ? ((Sort) logicalPlan).collation
> > > > : null;
> > > > if (collation != null) {
> > > > desiredTraits = desiredTraits.replace(collation);
> > > > }
> > > > final RelNode newRoot =
> > optPlanner.changeTraits(logicalPlan,
> > > > desiredTraits);
> > > > optPlanner.setRoot(newRoot);
> > > > RelNode bestExp = optPlanner.findBestExp();
> > > >
> > > > Any hint/pointer is very appreciated
> > > >
> > > > The alternative is to detect such simple queries and use a little set
> > of
> > > > Rules and not Programs.ofRules(Programs.RULE_SET)
> > > >
> > > > Best regards
> > > > Enrico
> > > >
> > >
> >
>
