[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 FilterMergeRule is TransformationRule while 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

Start by reading Apache Calcite issue CALCITE-3916 and the discussion in issues #4842 and #4859 about the top-down optimizer and push-down rules. Determine whether enabling it by default is compatible with the project's rules, and document or implement a clear decision for PPL optimization.