Optimization rules related to limit push-downs may cause unexpected optimization behavior

[czpmango]

Your Environments (required)
All versions for now.

Describe the bug (required)
Normal limit push-downs don't cause problems, such as PushLimitDownProjectRule.This rule pushes the limit completely down under the project operator, and the plan transforms as follows:
Old plan: ... -> Limit -> Project -> ...
Optimized plan: ... -> Project -> Limit -> ...

However, the operator related to storage access may cause problems, such as PushLimitDownAppendVerticesRule. The plan transforms as follows:
Old plan: ... -> Limit -> AppendVertices -> ...
Optimized plan: ... -> Limit -> AppendVertices(limit) -> ...

This kind of rules is essentially embedding rather than push-down.
The remained limit operator may block the possibility of other optimization rules being applied, and the order of rules loading may result in different execution plans.

For example, we discuss the optimization of rules involving PushLimitDownProjectRule, PushLimitDownAppendVerticesRule and EliminateAppendVerticesRule as follows:

Old plan: ... -> Limit -> Project -> AppendVertices -> ...

If rule EliminateAppendVerticesRule is applied first, the optimized plan looks like this:
Optimized plan: ... -> Project -> Limit -> ...

If rule PushLimitDownProjectRule is applied first, the optimized plan looks like this:
Optimized plan: ... -> Project -> Limit -> AppendVertices(limit) -> ...

As we can see, different orders in which rules are applied can lead to unexpected plan transformations.This can even cause some correctness problems, such as those related to hanging edges.

Expected behavior

1. Avoid correctness or self-consistency problems caused by optimizations
2. Avoid situations where one optimization rule blocks the application of other optimization rules
3. Avoid rule load order impact on final optimization behavior

Additional context
Similar issues may exist for other rules.

[xtcyclist]

This issue looks more like an enhancement. To address it, I think we need to first establish a set of theorems on the types of transformations that the optimizer could safely apply without causing bugs or losing good optimziation opportunities. I think it's ok to have exclusive optimization branches, but false exclusion is to be fixed. The optimization maneuvers we want should be subset of all such safe transformations.

I recommend to address similar issues after 3.4. I added it to v3.5.0 for now.

[czpmango]

This issue looks more like an enhancement. To address it, I think we need to first establish a set of theorems on the types of transformations that the optimizer could safely apply without causing bugs or losing good optimziation opportunities. I think it's ok to have exclusive optimization branches, but false exclusion is to be fixed. The optimization maneuvers we want should be subset of all such safe transformations.

I recommend to address similar issues after 3.4. I added it to v3.5.0 for now.

Basically agree, but there may be a self-consistency problem for scenarios with hanging edges.

[xtcyclist]

I'm closing this issue and removing it from the 3.5 milestone due to resource constraints. We welcome anyone from the community to address this issue lateron.