[EPIC] Improve query planning speed

[alamb]

Is your feature request related to a problem or challenge?

DataFusion can theoretically plan queries of almost any complexity. However, for moderately complex queries, especially those involving UNIONS or deeply nested plans, it can sometimes take seconds to plan they query

At InfluxData we have had to disable certain equivalence checks because without doing so the planning time becomes exponential (and effectively locks up one of our cores)

Basically I would like to use this ticket to coordinate / start making meaningful progress reducing the planning time for DataFusion.

Describe the solution you'd like

We have fairly thorough benchmarks at this point that I think covers many use cases.

# don't forget --profile=profiling otherwise you'll end up with no symbol names
cargo bench --profile=profiling  --bench sql_planner 

If anyone wants to help with this EPIC an excellent first step to do would be

1. Contribute example queries that are slow to plan in your environment
2. Spend time profiling the existing benchmarks to identify potential performance improvements

Describe alternatives you've considered

Issues reporting slow planning time

• Bad performance on wide tables (1000+ columns) due to planning overhead #7698
• Planning time for queries with many columns with union and order by is very slow #17261

Ideas to potentially increase performance

• Optimize planner to avoid excessive schema transform functions #9144
• Replace Boxes with Arc in the Expr enum. #9577
• Avoid recomputing PlanProperties redundently #19796

Prior Epics

• Improve Planning Time #13015
• [Epic] A collection of issues to improve planning performance / speed / efficiency #5637

Additional context

No response

[tobixdev]

In our SPARQL query engine we also often encounter some queries where the planning time is quite large compared to execution time. We have deeply netsted joins in largery graph patterns and large union types.

The low hanging fruits would be looking into which passes of the DF optimizer are actually needed and it's been on my TODO list for quite some time. But improving the base speed on the optimizations will surely help.

Testing them in DataFusion directly will be complicated as we're using a lot of custom operators (most of them get mapped to a DF operator immediately, but still the parser relies on that). I am happy to run further tests and all benchmarks are available on Codspeed.

If I can find the time I would also like to help out but realistically this won't be possible till End of February / March. If I can help wth something feel free to ping me.

[alamb]

Testing them in DataFusion directly will be complicated as we're using a lot of custom operators (most of them get mapped to a DF operator immediately, but still the parser relies on that). I am happy to run further tests and all benchmarks are available on Codspeed.
Testing them in DataFusion directly will be complicated as we're using a lot of custom operators (most of them get mapped to a DF operator immediately, but still the parser relies on that). I am happy to run further tests and all benchmarks are available on Codspeed.

Thank you!

One thing that will help is getting some profiles of the queries you are working with so we can analyze where the time is going. I hope to refresh the DataFusion documentation in this area over the next week or two which might make it easier to provide this data