alamb commented on issue #17261: URL: https://github.com/apache/datafusion/issues/17261#issuecomment-3271665814
We also saw something similar to this while upgrading to 49.0.2 internally at InfluxData. I did some basic profiling and I have some ideas on how to make this faster ## Methodology ```shell cargo bench --profile=profiling --bench sql_planner -- physical_sorted_union_order_by_200 ``` I looked at this with XCode instruments (and didn't let it run to completion) I also ran samply like this on a smaller example (50 cols) ```shell samply record target/profiling/deps/sql_planner-e9cfb82114a43daf --bench physical_sorted_union_order_by_50 ``` ## Analysis <img width="1622" height="746" alt="Image" src="https://github.com/user-attachments/assets/81c60d4f-083f-4506-aa7b-5f73bb164c3b"; /> I calculated the breakdown like this ``` 58% of the time is creating the physical plan, (45% in physical optimizer) 29% EnforceDistribution 13% create_initial_plan 8% in EnforceSorting 3% in projectionPushdown 3% in LimitPushdown 35% in SessionState::optimize (logical optimizer) 22% in rewrite_with_subqueries??? 7% in OptimizeProjections 3% in TypeCoercion 6% of the time in SQL parser/planner ``` And then I dug into the different users. One interesting pattern that emerges is that they all have very deep callstacks. For example, in planning, there is a very deep callstack for `datafusion_sql::set_expr::::set_expr_to_plan::{{closure}}. In rewrite with subqueries, there is also a very deep callstack. I think this comes from the fact that a query like ```sql SELECT ... UNION ALL SELECT ... UNION ALL SELECT ... ``` Is planned like ``` Union Select Union Select Union Select ``` I didn't find any one callstack with a massive smoking gun Thus, I hypothesize there is some non trivial overhead due to the number of nodes / depth of plan (due to the rewriting that occurs). So one potential idea to try is to unnest unions earlier in the plan (maybe even directly in the planner) which would reduce both the number of nodes in the plan as well as the depth Note that @crepererum implemented a version of this rewrite for influxdb_iox (though it is in the physical optimizer). https://github.com/influxdata/influxdb3_core/blob/d14943e575e8a4692aa915ae3fab016712e6e3b3/iox_query/src/physical_optimizer/union/nested_union.rs#L11 -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: github-unsubscr...@datafusion.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org --------------------------------------------------------------------- To unsubscribe, e-mail: github-unsubscr...@datafusion.apache.org For additional commands, e-mail: github-h...@datafusion.apache.org
