Consolidate optimizer passes to improve planning speed

@jayzhan211

Is your feature request related to a problem or challenge?

@jayzhan211 removed the UnwrapCastInComparison optimizer (and combined it with the simplifier) In

Move UnwrapCastInComparison into Simplifier #15012

Doing so seemed to make a non trivial difference for planning speed:

Move UnwrapCastInComparison into Simplifier #15012 (comment)

I think the improvement came from reducing the number of Optimizer passes (and this rewrites/copies of the plan and all expressions) that happened

Here were my mesurements about speed

Describe the solution you'd like

I would like to make planning faster by potentially combining other passes from this list:

datafusion/datafusion/optimizer/src/optimizer.rs

Lines 243 to 272 in 43ecd9b

    
           let rules: Vec<Arc<dyn OptimizerRule + Sync + Send>> = vec![ 
        
               Arc::new(EliminateNestedUnion::new()), 
        
               Arc::new(SimplifyExpressions::new()), 
        
               Arc::new(UnwrapCastInComparison::new()), 
        
               Arc::new(ReplaceDistinctWithAggregate::new()), 
        
               Arc::new(EliminateJoin::new()), 
        
               Arc::new(DecorrelatePredicateSubquery::new()), 
        
               Arc::new(ScalarSubqueryToJoin::new()), 
        
               Arc::new(ExtractEquijoinPredicate::new()), 
        
               Arc::new(EliminateDuplicatedExpr::new()), 
        
               Arc::new(EliminateFilter::new()), 
        
               Arc::new(EliminateCrossJoin::new()), 
        
               Arc::new(CommonSubexprEliminate::new()), 
        
               Arc::new(EliminateLimit::new()), 
        
               Arc::new(PropagateEmptyRelation::new()), 
        
               // Must be after PropagateEmptyRelation 
        
               Arc::new(EliminateOneUnion::new()), 
        
               Arc::new(FilterNullJoinKeys::default()), 
        
               Arc::new(EliminateOuterJoin::new()), 
        
               // Filters can't be pushed down past Limits, we should do PushDownFilter after PushDownLimit 
        
               Arc::new(PushDownLimit::new()), 
        
               Arc::new(PushDownFilter::new()), 
        
               Arc::new(SingleDistinctToGroupBy::new()), 
        
               // The previous optimizations added expressions and projections, 
        
               // that might benefit from the following rules 
        
               Arc::new(SimplifyExpressions::new()), 
        
               Arc::new(UnwrapCastInComparison::new()), 
        
               Arc::new(CommonSubexprEliminate::new()), 
        
               Arc::new(EliminateGroupByConstant::new()), 
        
               Arc::new(OptimizeProjections::new()),

Describe alternatives you've considered

Some potential candidates to try consolidating:

EliminateNestedUnion + EliminateOneUnion
EliminateJoin and EliminateJoin

You can run the planning benchmarks like

cargo bench --bench sql_planner

Additional context

No response

If we can move type coercion into LogicalPlan building stage #14618, I think Union logic is able to be resolved at that time #14380, then we don't need EliminateNestedUnion or EliminateOneUnion anymore.

But before that, we can merge these rules

This seems like an interesting task, I'd like to work on it !

@clflushopt

This seems like an interesting task, I'd like to work on it !

Thanks @clflushopt

Figuring out how to avoid the two distinct union passes would be great.

Maybe we could just update

datafusion/datafusion/expr/src/logical_plan/builder.rs

Lines 1632 to 1637 in e221a2c

    
           pub fn union(left_plan: LogicalPlan, right_plan: LogicalPlan) -> Result<LogicalPlan> { 
        
               Ok(LogicalPlan::Union(Union::try_new_with_loose_types(vec![ 
        
                   Arc::new(left_plan), 
        
                   Arc::new(right_plan), 
        
               ])?)) 
        
           }

To check if the existing plan was already a union

Navigation Menu

Repository navigation

Consolidate optimizer passes to improve planning speed #15045

Is your feature request related to a problem or challenge?

Describe the solution you'd like

Describe alternatives you've considered

Additional context

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

	let rules: Vec<Arc<dyn OptimizerRule + Sync + Send>> = vec![
	Arc::new(EliminateNestedUnion::new()),
	Arc::new(SimplifyExpressions::new()),
	Arc::new(UnwrapCastInComparison::new()),
	Arc::new(ReplaceDistinctWithAggregate::new()),
	Arc::new(EliminateJoin::new()),
	Arc::new(DecorrelatePredicateSubquery::new()),
	Arc::new(ScalarSubqueryToJoin::new()),
	Arc::new(ExtractEquijoinPredicate::new()),
	Arc::new(EliminateDuplicatedExpr::new()),
	Arc::new(EliminateFilter::new()),
	Arc::new(EliminateCrossJoin::new()),
	Arc::new(CommonSubexprEliminate::new()),
	Arc::new(EliminateLimit::new()),
	Arc::new(PropagateEmptyRelation::new()),
	// Must be after PropagateEmptyRelation
	Arc::new(EliminateOneUnion::new()),
	Arc::new(FilterNullJoinKeys::default()),
	Arc::new(EliminateOuterJoin::new()),
	// Filters can't be pushed down past Limits, we should do PushDownFilter after PushDownLimit
	Arc::new(PushDownLimit::new()),
	Arc::new(PushDownFilter::new()),
	Arc::new(SingleDistinctToGroupBy::new()),
	// The previous optimizations added expressions and projections,
	// that might benefit from the following rules
	Arc::new(SimplifyExpressions::new()),
	Arc::new(UnwrapCastInComparison::new()),
	Arc::new(CommonSubexprEliminate::new()),
	Arc::new(EliminateGroupByConstant::new()),
	Arc::new(OptimizeProjections::new()),

	pub fn union(left_plan: LogicalPlan, right_plan: LogicalPlan) -> Result<LogicalPlan> {
	Ok(LogicalPlan::Union(Union::try_new_with_loose_types(vec![
	Arc::new(left_plan),
	Arc::new(right_plan),
	])?))
	}

Consolidate optimizer passes to improve planning speed #15045

Description

Is your feature request related to a problem or challenge?

Describe the solution you'd like

Describe alternatives you've considered

Additional context

Activity

jayzhan211 commented on Mar 8, 2025

clflushopt commented on Mar 8, 2025

alamb commented on Mar 13, 2025

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions