opensearch-project
[FEATURE] Investigation on enable top-down optimizer in Calcite
Is your feature request related to a problem? https://issues.apache.org/jira/browse/CALCITE-3916
Calcite has introduced top-down optimizer, which is more efficient than the old version. It will accelerate the optimizing process a lot by pruning. Let’s see if we should turn it on by default for our PPL optimization.
What solution would you like? A clear and concise description of what you want to happen.
What alternatives have you considered? A clear and concise description of any alternative solutions or features you've considered.
Do you have any additional context?
- ~~Enable top_down optimizer in Calcite will eliminate the issue https://github.com/opensearch-project/sql/issues/4842.~~ It works because
FilterMergeRuleisTransformationRulewhile our push down rules are not. So it happens to avoid intersection for this case. However it's a bigger problem if keeping push down rules non-transformation rule when using top down optimizer. See more explain in https://github.com/opensearch-project/sql/issues/4859#issuecomment-3588417949
It will accelerate the optimizing process a lot by pruning.
Can we have some benchmark on this? At least big5, clickbench and tpch that we've had.
After some investigation and test, the conclusion is we cannot leverage TOP_DOWN optimizer to both avoid the issue https://github.com/opensearch-project/sql/issues/4842 while also make it work well with push down.
In Calcite’s TOP_DOWN optimizer, there is logically 2 kinds of status when applying rules to a group(i.e. RelSubSet).
- EXPLORING/EXPLORED: When exploring, only applying transformation rules for mExpr(i.e. logical plan). Transformation rules only accept logical plan as its operands while also generate logical plan. Once a group is explored, it won’t apply any transformation rules anymore.
- OPTIMIZING/COMPLETED: Once a group produces a physical plan, it starts to optimizing its input groups. When optimizing, it allows to apply all rules, including converting rules for generating a physical plan. Basically, at this point, It also means the current group has been explored and already in optimizing phase, otherwise it won’t produce a physical plan.
In exploring, it's actually a deep search process, which means the process are triggered from top to down while the actual transformation happens from down to top conversely. In optimizing, it's more closed to top-2-down process. The pruning only takes effect in this process.
The problem is whether we should make our push down rules to be transformation rules. I’ve tried different ways:
- All transformation rules, which makes more sense because all push down rules in Calcite like
FilterProjectTransposeRule,SortProjectTransposeRule,FilterSortTransposeRuleare allTransformationRule. However, it will face the same issue of OOM likeIterativeRuleDriversinceFilterMergeRuleis alsoTransformationRuleand will get intersection with our filter push down rule. - All non-transformation rules. Then in our IT, many operators cannot be pushed down. That’s because, non-transformation rules can only apply in optimizing group whose parent group has already been explored and right in optimizing. It has no chance to fire rules again after its input group performs a push down rule. So continuous push down cannot happen.
- Only make filter push down non-transformation rule, which aims to avoid intersection with
FilterMergeRule. It also cannot work. Once we fails to push down a filter as it cannot be used in exploring, it will block other operators to be pushed down.
