Planning time for queries with many columns with union and order by is very slow #17261

Activity

1. Omega359

   added

   bugSomething isn't workingSomething isn't working

   on Aug 20, 2025
2. alamb commented on Aug 20, 2025

   

   alamb

   on Aug 20, 2025

   Contributor

   More actions

   Thank you @Omega359 -- I think other people are interested in this topic too and we should make things better in this respect

   Here are some related tickets:

   • Improve Planning Time #13015
   • Bad performance on wide tables (1000+ columns) due to planning overhead #7698 (from @karlovnv)

   My suggested next steps are:

   1. Make a PR with the new benchmark
   2. Then we can profile the benchmark and identify potential improvements
3. alamb commented on Sep 9, 2025

   

   alamb

   on Sep 9, 2025

   Contributor

   More actions

   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

   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)

   samply record target/profiling/deps/sql_planner-e9cfb82114a43daf --bench physical_sorted_union_order_by_50

   Analysis

   

   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

   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
4. Omega359 commented on Sep 9, 2025

   

   Omega359

   on Sep 9, 2025

   Last edited by Omega359

   ContributorAuthor

   More actions

   My short term solution was to split the dag in two and do two separate calls writing out and reading back the results in between. That cut the number of items in each of the logical plans down significantly and thus planning time down tremendously. It's not an ideal solution but it worked.
5. xudong963 commented on Sep 10, 2025

   

   xudong963

   on Sep 10, 2025

   Member

   More actions

   FYI, our team is also verifying the "Planning time for queries with many columns" issue, will give some feedback later.

   👍React with 👍1Reacted by Andrew Lamb
6. alamb commented on Sep 10, 2025

   

   alamb

   on Sep 10, 2025

   Contributor

   More actions

   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

   To be clear, I am suggesting instead of:

   Union
     Select
     Union
        Select
        Union
           Select
   

   To make the plan like this (Union already supports more than one input)

   Union
     Select
     Select
     Select
   
7. logan-keede commented on Nov 7, 2025

   

   logan-keede

   on Nov 7, 2025

   Contributor

   More actions

   +-------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | plan_type         | plan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
   +-------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | Plan with Metrics | SortPreservingMergeExec: [c0@0 ASC NULLS LAST, c1@1 ASC NULLS LAST, c2@2 ASC NULLS LAST, c3@3 ASC NULLS LAST, c4@4 ASC NULLS LAST, c5@5 ASC NULLS LAST, c6@6 ASC NULLS LAST, c7@7 ASC NULLS LAST, c8@8 ASC NULLS LAST, c9@9 ASC NULLS LAST], metrics=[output_rows=10000, elapsed_compute=5.645754ms, output_bytes=1562.5 KB]                                                                                                                                                         |
   |                   |   UnionExec, metrics=[output_rows=10000, elapsed_compute=665.791µs, output_bytes=1562.5 KB]                                                                                                                                                                                                                                                                                                                                                                                          |
   |                   |     SortExec: expr=[c0@0 ASC NULLS LAST, c1@1 ASC NULLS LAST, c2@2 ASC NULLS LAST, c3@3 ASC NULLS LAST, c4@4 ASC NULLS LAST, c5@5 ASC NULLS LAST, c6@6 ASC NULLS LAST, c7@7 ASC NULLS LAST, c8@8 ASC NULLS LAST], preserve_partitioning=[true], metrics=[output_rows=9000, elapsed_compute=12.662881ms, output_bytes=1406.2 KB, spill_count=0, spilled_bytes=0.0 B, spilled_rows=0, batches_split=0]                                                                                 |
   |                   |       ProjectionExec: expr=[c0@0 as c0, c1@1 as c1, c2@2 as c2, c3@3 as c3, c4@4 as c4, c5@5 as c5, c6@6 as c6, c7@7 as c7, c8@8 as c8, CAST(c9@9 AS Decimal128(20, 0)) as c9], metrics=[output_rows=9000, elapsed_compute=838.677µs, output_bytes=1406.2 KB]                                                                                                                                                                                                                        |
   |                   |         RepartitionExec: partitioning=RoundRobinBatch(12), input_partitions=9, metrics=[spill_count=0, spilled_bytes=0.0 B, spilled_rows=0, fetch_time=8.041255ms, repartition_time=9ns, send_time=44.691µs]                                                                                                                                                                                                                                                                         |
   |                   |           UnionExec, metrics=[output_rows=9000, elapsed_compute=162.148µs, output_bytes=1335.9 KB]                                                                                                                                                                                                                                                                                                                                                                                   |
   |                   |             ProjectionExec: expr=[c0@0 as c0, c1@1 as c1, c2@2 as c2, c3@3 as c3, c4@4 as c4, c5@5 as c5, c6@6 as c6, c7@7 as c7, CAST(c8@8 AS Decimal128(20, 0)) as c8, c9@9 as c9], metrics=[output_rows=8000, elapsed_compute=733.787µs, output_bytes=1187.5 KB]                                                                                                                                                                                                                  |
   |                   |               UnionExec, metrics=[output_rows=8000, elapsed_compute=132.399µs, output_bytes=1125.0 KB]                                                                                                                                                                                                                                                                                                                                                                               |
   |                   |                 ProjectionExec: expr=[c0@0 as c0, c1@1 as c1, c2@2 as c2, c3@3 as c3, c4@4 as c4, c5@5 as c5, c6@6 as c6, CAST(c7@7 AS Decimal128(20, 0)) as c7, c8@8 as c8, c9@9 as c9], metrics=[output_rows=7000, elapsed_compute=687.809µs, output_bytes=984.4 KB]                                                                                                                                                                                                               |
   |                   |                   UnionExec, metrics=[output_rows=7000, elapsed_compute=111.441µs, output_bytes=929.7 KB]                                                                                                                                                                                                                                                                                                                                                                            |
   |                   |                     ProjectionExec: expr=[c0@0 as c0, c1@1 as c1, c2@2 as c2, c3@3 as c3, c4@4 as c4, c5@5 as c5, CAST(c6@6 AS Decimal128(20, 0)) as c6, c7@7 as c7, c8@8 as c8, c9@9 as c9], metrics=[output_rows=6000, elapsed_compute=642.729µs, output_bytes=796.9 KB]                                                                                                                                                                                                           |
   |                   |                       UnionExec, metrics=[output_rows=6000, elapsed_compute=91.198µs, output_bytes=750.0 KB]                                                                                                                                                                                                                                                                                                                                                                         |
   |                   |                         ProjectionExec: expr=[c0@0 as c0, c1@1 as c1, c2@2 as c2, c3@3 as c3, c4@4 as c4, CAST(c5@5 AS Decimal128(20, 0)) as c5, c6@6 as c6, c7@7 as c7, c8@8 as c8, c9@9 as c9], metrics=[output_rows=5000, elapsed_compute=569.976µs, output_bytes=625.0 KB]                                                                                                                                                                                                       |
   |                   |                           UnionExec, metrics=[output_rows=5000, elapsed_compute=72.586µs, output_bytes=585.9 KB]                                                                                                                                                                                                                                                                                                                                                                     |
   |                   |                             ProjectionExec: expr=[c0@0 as c0, c1@1 as c1, c2@2 as c2, c3@3 as c3, CAST(c4@4 AS Decimal128(20, 0)) as c4, c5@5 as c5, c6@6 as c6, c7@7 as c7, c8@8 as c8, c9@9 as c9], metrics=[output_rows=4000, elapsed_compute=456.798µs, output_bytes=468.8 KB]                                                                                                                                                                                                   |
   |                   |                               UnionExec, metrics=[output_rows=4000, elapsed_compute=42.078µs, output_bytes=437.5 KB]                                                                                                                                                                                                                                                                                                                                                                 |
   |                   |                                 ProjectionExec: expr=[c0@0 as c0, c1@1 as c1, c2@2 as c2, CAST(c3@3 AS Decimal128(20, 0)) as c3, c4@4 as c4, c5@5 as c5, c6@6 as c6, c7@7 as c7, c8@8 as c8, c9@9 as c9], metrics=[output_rows=3000, elapsed_compute=343.243µs, output_bytes=328.1 KB]                                                                                                                                                                                               |
   |                   |                                   UnionExec, metrics=[output_rows=3000, elapsed_compute=31.691µs, output_bytes=304.7 KB]                                                                                                                                                                                                                                                                                                                                                             |
   |                   |                                     ProjectionExec: expr=[c0@0 as c0, c1@1 as c1, CAST(c2@2 AS Decimal128(20, 0)) as c2, c3@3 as c3, c4@4 as c4, c5@5 as c5, c6@6 as c6, c7@7 as c7, c8@8 as c8, c9@9 as c9], metrics=[output_rows=2000, elapsed_compute=227.115µs, output_bytes=203.1 KB]                                                                                                                                                                                           |
   |                   |                                       UnionExec, metrics=[output_rows=2000, elapsed_compute=18.204µs, output_bytes=187.5 KB]                                                                                                                                                                                                                                                                                                                                                         |
   |                   |                                         ProjectionExec: expr=[CAST(c0@0 AS Decimal128(20, 0)) as c0, CAST(1 AS Decimal128(20, 0)) as c1, 2 as c2, 3 as c3, 4 as c4, 5 as c5, 6 as c6, 7 as c7, 8 as c8, 9 as c9], metrics=[output_rows=1000, elapsed_compute=331.999µs, output_bytes=93.8 KB]                                                                                                                                                                                        |
   |                   |                                           DataSourceExec: partitions=1, partition_sizes=[1], output_ordering=c0@0 ASC, metrics=[]                                                                                                                                                                                                                                                                                                                                                    |
   |                   |                                         ProjectionExec: expr=[CAST(0 AS Decimal128(20, 0)) as c0, CAST(c1@0 AS Decimal128(20, 0)) as c1, 2 as c2, 3 as c3, 4 as c4, 5 as c5, 6 as c6, 7 as c7, 8 as c8, 9 as c9], metrics=[output_rows=1000, elapsed_compute=235.608µs, output_bytes=93.8 KB]                                                                                                                                                                                        |
   |                   |                                           DataSourceExec: partitions=1, partition_sizes=[1], metrics=[]                                                                                                                                                                                                                                                                                                                                                                              |
   |                   |                                     ProjectionExec: expr=[CAST(0 AS Decimal128(20, 0)) as c0, CAST(1 AS Decimal128(20, 0)) as c1, CAST(c2@0 AS Decimal128(20, 0)) as c2, 3 as c3, 4 as c4, 5 as c5, 6 as c6, 7 as c7, 8 as c8, 9 as c9], metrics=[output_rows=1000, elapsed_compute=274.968µs, output_bytes=101.6 KB]                                                                                                                                                                |
   |                   |                                       DataSourceExec: partitions=1, partition_sizes=[1], metrics=[]                                                                                                                                                                                                                                                                                                                                                                                  |
   |                   |                                 ProjectionExec: expr=[CAST(0 AS Decimal128(20, 0)) as c0, CAST(1 AS Decimal128(20, 0)) as c1, CAST(2 AS Decimal128(20, 0)) as c2, CAST(c3@0 AS Decimal128(20, 0)) as c3, 4 as c4, 5 as c5, 6 as c6, 7 as c7, 8 as c8, 9 as c9], metrics=[output_rows=1000, elapsed_compute=278.882µs, output_bytes=109.4 KB]                                                                                                                                         |
   |                   |                                   DataSourceExec: partitions=1, partition_sizes=[1], metrics=[]                                                                                                                                                                                                                                                                                                                                                                                      |
   |                   |                             ProjectionExec: expr=[CAST(0 AS Decimal128(20, 0)) as c0, CAST(1 AS Decimal128(20, 0)) as c1, CAST(2 AS Decimal128(20, 0)) as c2, CAST(3 AS Decimal128(20, 0)) as c3, CAST(c4@0 AS Decimal128(20, 0)) as c4, 5 as c5, 6 as c6, 7 as c7, 8 as c8, 9 as c9], metrics=[output_rows=1000, elapsed_compute=357.672µs, output_bytes=117.2 KB]                                                                                                                  |
   |                   |                               DataSourceExec: partitions=1, partition_sizes=[1], metrics=[]                                                                                                                                                                                                                                                                                                                                                                                          |
   |                   |                         ProjectionExec: expr=[CAST(0 AS Decimal128(20, 0)) as c0, CAST(1 AS Decimal128(20, 0)) as c1, CAST(2 AS Decimal128(20, 0)) as c2, CAST(3 AS Decimal128(20, 0)) as c3, CAST(4 AS Decimal128(20, 0)) as c4, CAST(c5@0 AS Decimal128(20, 0)) as c5, 6 as c6, 7 as c7, 8 as c8, 9 as c9], metrics=[output_rows=1000, elapsed_compute=290.957µs, output_bytes=125.0 KB]                                                                                           |
   |                   |                           DataSourceExec: partitions=1, partition_sizes=[1], metrics=[]                                                                                                                                                                                                                                                                                                                                                                                              |
   |                   |                     ProjectionExec: expr=[CAST(0 AS Decimal128(20, 0)) as c0, CAST(1 AS Decimal128(20, 0)) as c1, CAST(2 AS Decimal128(20, 0)) as c2, CAST(3 AS Decimal128(20, 0)) as c3, CAST(4 AS Decimal128(20, 0)) as c4, CAST(5 AS Decimal128(20, 0)) as c5, CAST(c6@0 AS Decimal128(20, 0)) as c6, 7 as c7, 8 as c8, 9 as c9], metrics=[output_rows=1000, elapsed_compute=350.579µs, output_bytes=132.8 KB]                                                                    |
   |                   |                       DataSourceExec: partitions=1, partition_sizes=[1], metrics=[]                                                                                                                                                                                                                                                                                                                                                                                                  |
   |                   |                 ProjectionExec: expr=[CAST(0 AS Decimal128(20, 0)) as c0, CAST(1 AS Decimal128(20, 0)) as c1, CAST(2 AS Decimal128(20, 0)) as c2, CAST(3 AS Decimal128(20, 0)) as c3, CAST(4 AS Decimal128(20, 0)) as c4, CAST(5 AS Decimal128(20, 0)) as c5, CAST(6 AS Decimal128(20, 0)) as c6, CAST(c7@0 AS Decimal128(20, 0)) as c7, 8 as c8, 9 as c9], metrics=[output_rows=1000, elapsed_compute=215.061µs, output_bytes=140.6 KB]                                             |
   |                   |                   DataSourceExec: partitions=1, partition_sizes=[1], metrics=[]                                                                                                                                                                                                                                                                                                                                                                                                      |
   |                   |             ProjectionExec: expr=[CAST(0 AS Decimal128(20, 0)) as c0, CAST(1 AS Decimal128(20, 0)) as c1, CAST(2 AS Decimal128(20, 0)) as c2, CAST(3 AS Decimal128(20, 0)) as c3, CAST(4 AS Decimal128(20, 0)) as c4, CAST(5 AS Decimal128(20, 0)) as c5, CAST(6 AS Decimal128(20, 0)) as c6, CAST(7 AS Decimal128(20, 0)) as c7, CAST(c8@0 AS Decimal128(20, 0)) as c8, 9 as c9], metrics=[output_rows=1000, elapsed_compute=310.342µs, output_bytes=148.4 KB]                      |
   |                   |               DataSourceExec: partitions=1, partition_sizes=[1], metrics=[]                                                                                                                                                                                                                                                                                                                                                                                                          |
   |                   |     SortExec: expr=[c9@9 ASC NULLS LAST], preserve_partitioning=[false], metrics=[output_rows=1000, elapsed_compute=381.008µs, output_bytes=0.0 B, spill_count=0, spilled_bytes=0.0 B, spilled_rows=0, batches_split=0]                                                                                                                                                                                                                                                              |
   |                   |       ProjectionExec: expr=[CAST(0 AS Decimal128(20, 0)) as c0, CAST(1 AS Decimal128(20, 0)) as c1, CAST(2 AS Decimal128(20, 0)) as c2, CAST(3 AS Decimal128(20, 0)) as c3, CAST(4 AS Decimal128(20, 0)) as c4, CAST(5 AS Decimal128(20, 0)) as c5, CAST(6 AS Decimal128(20, 0)) as c6, CAST(7 AS Decimal128(20, 0)) as c7, CAST(8 AS Decimal128(20, 0)) as c8, CAST(c9@0 AS Decimal128(20, 0)) as c9], metrics=[output_rows=1000, elapsed_compute=243.051µs, output_bytes=156.2 KB] |
   |                   |         DataSourceExec: partitions=1, partition_sizes=[1], metrics=[]                                                                                                                                                                                                                                                                                                                                                                                                                |
   |                   |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
   +-------------------+------------------------------------------------------------------------------------------------------------------
   

   This is the result I get by using EXPLAIN ANALYZE with ctx.sql where ctx is an instance of SessionContext , on the other hand using datafusion-cli I get:-

   ORDER BY c0, c1, c2, c3, c4, c5, c6, c7, c8, c9;
   +-------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | plan_type         | plan                                                                                                                                                                                                                                                                                                                 |
   +-------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | Plan with Metrics | SortPreservingMergeExec: [c0@0 ASC NULLS LAST, c1@1 ASC NULLS LAST, c2@2 ASC NULLS LAST, c3@3 ASC NULLS LAST, c4@4 ASC NULLS LAST, c5@5 ASC NULLS LAST, c6@6 ASC NULLS LAST, c7@7 ASC NULLS LAST, c8@8 ASC NULLS LAST, c9@9 ASC NULLS LAST], metrics=[output_rows=30, elapsed_compute=202.88µs, output_bytes=2.3 KB] |
   |                   |   UnionExec, metrics=[output_rows=30, elapsed_compute=1.117171ms, output_bytes=2.3 KB]                                                                                                                                                                                                                               |
   |                   |     SortExec: expr=[c0@0 ASC NULLS LAST], preserve_partitioning=[false], metrics=[output_rows=3, elapsed_compute=1.712165ms, output_bytes=0.0 B, spill_count=0, spilled_bytes=0.0 B, spilled_rows=0, batches_split=0]                                                                                                |
   |                   |       ProjectionExec: expr=[CAST(c0@0 AS Int64) as c0, 1 as c1, 2 as c2, 3 as c3, 4 as c4, 5 as c5, 6 as c6, 7 as c7, 8 as c8, 9 as c9], metrics=[output_rows=3, elapsed_compute=79.9µs, output_bytes=280.0 B]                                                                                                       |
   |                   |         DataSourceExec: partitions=1, partition_sizes=[1], metrics=[]                                                                                                                                                                                                                                                |
   |                   |     SortExec: expr=[c1@1 ASC NULLS LAST], preserve_partitioning=[false], metrics=[output_rows=3, elapsed_compute=1.244665ms, output_bytes=0.0 B, spill_count=0, spilled_bytes=0.0 B, spilled_rows=0, batches_split=0]                                                                                                |
   |                   |       ProjectionExec: expr=[0 as c0, CAST(c1@0 AS Int64) as c1, 2 as c2, 3 as c3, 4 as c4, 5 as c5, 6 as c6, 7 as c7, 8 as c8, 9 as c9], metrics=[output_rows=3, elapsed_compute=86.887µs, output_bytes=280.0 B]                                                                                                     |
   |                   |         DataSourceExec: partitions=1, partition_sizes=[1], metrics=[]                                                                                                                                                                                                                                                |
   |                   |     SortExec: expr=[c2@2 ASC NULLS LAST], preserve_partitioning=[false], metrics=[output_rows=3, elapsed_compute=1.217714ms, output_bytes=0.0 B, spill_count=0, spilled_bytes=0.0 B, spilled_rows=0, batches_split=0]                                                                                                |
   |                   |       ProjectionExec: expr=[0 as c0, 1 as c1, CAST(c2@0 AS Int64) as c2, 3 as c3, 4 as c4, 5 as c5, 6 as c6, 7 as c7, 8 as c8, 9 as c9], metrics=[output_rows=3, elapsed_compute=59.552µs, output_bytes=280.0 B]                                                                                                     |
   |                   |         DataSourceExec: partitions=1, partition_sizes=[1], metrics=[]                                                                                                                                                                                                                                                |
   |                   |     SortExec: expr=[c3@3 ASC NULLS LAST], preserve_partitioning=[false], metrics=[output_rows=3, elapsed_compute=136.622µs, output_bytes=0.0 B, spill_count=0, spilled_bytes=0.0 B, spilled_rows=0, batches_split=0]                                                                                                 |
   |                   |       ProjectionExec: expr=[0 as c0, 1 as c1, 2 as c2, CAST(c3@0 AS Int64) as c3, 4 as c4, 5 as c5, 6 as c6, 7 as c7, 8 as c8, 9 as c9], metrics=[output_rows=3, elapsed_compute=90.253µs, output_bytes=280.0 B]                                                                                                     |
   |                   |         DataSourceExec: partitions=1, partition_sizes=[1], metrics=[]                                                                                                                                                                                                                                                |
   |                   |     SortExec: expr=[c4@4 ASC NULLS LAST], preserve_partitioning=[false], metrics=[output_rows=3, elapsed_compute=1.248026ms, output_bytes=0.0 B, spill_count=0, spilled_bytes=0.0 B, spilled_rows=0, batches_split=0]                                                                                                |
   |                   |       ProjectionExec: expr=[0 as c0, 1 as c1, 2 as c2, 3 as c3, CAST(c4@0 AS Int64) as c4, 5 as c5, 6 as c6, 7 as c7, 8 as c8, 9 as c9], metrics=[output_rows=3, elapsed_compute=100.374µs, output_bytes=280.0 B]                                                                                                    |
   |                   |         DataSourceExec: partitions=1, partition_sizes=[1], metrics=[]                                                                                                                                                                                                                                                |
   |                   |     SortExec: expr=[c5@5 ASC NULLS LAST], preserve_partitioning=[false], metrics=[output_rows=3, elapsed_compute=1.01873ms, output_bytes=0.0 B, spill_count=0, spilled_bytes=0.0 B, spilled_rows=0, batches_split=0]                                                                                                 |
   |                   |       ProjectionExec: expr=[0 as c0, 1 as c1, 2 as c2, 3 as c3, 4 as c4, CAST(c5@0 AS Int64) as c5, 6 as c6, 7 as c7, 8 as c8, 9 as c9], metrics=[output_rows=3, elapsed_compute=70.435µs, output_bytes=280.0 B]                                                                                                     |
   |                   |         DataSourceExec: partitions=1, partition_sizes=[1], metrics=[]                                                                                                                                                                                                                                                |
   |                   |     SortExec: expr=[c6@6 ASC NULLS LAST], preserve_partitioning=[false], metrics=[output_rows=3, elapsed_compute=512.958µs, output_bytes=0.0 B, spill_count=0, spilled_bytes=0.0 B, spilled_rows=0, batches_split=0]                                                                                                 |
   |                   |       ProjectionExec: expr=[0 as c0, 1 as c1, 2 as c2, 3 as c3, 4 as c4, 5 as c5, CAST(c6@0 AS Int64) as c6, 7 as c7, 8 as c8, 9 as c9], metrics=[output_rows=3, elapsed_compute=64.86µs, output_bytes=280.0 B]                                                                                                      |
   |                   |         DataSourceExec: partitions=1, partition_sizes=[1], metrics=[]                                                                                                                                                                                                                                                |
   |                   |     SortExec: expr=[c7@7 ASC NULLS LAST], preserve_partitioning=[false], metrics=[output_rows=3, elapsed_compute=584.371µs, output_bytes=0.0 B, spill_count=0, spilled_bytes=0.0 B, spilled_rows=0, batches_split=0]                                                                                                 |
   |                   |       ProjectionExec: expr=[0 as c0, 1 as c1, 2 as c2, 3 as c3, 4 as c4, 5 as c5, 6 as c6, CAST(c7@0 AS Int64) as c7, 8 as c8, 9 as c9], metrics=[output_rows=3, elapsed_compute=64.695µs, output_bytes=280.0 B]                                                                                                     |
   |                   |         DataSourceExec: partitions=1, partition_sizes=[1], metrics=[]                                                                                                                                                                                                                                                |
   |                   |     SortExec: expr=[c8@8 ASC NULLS LAST], preserve_partitioning=[false], metrics=[output_rows=3, elapsed_compute=161.32µs, output_bytes=0.0 B, spill_count=0, spilled_bytes=0.0 B, spilled_rows=0, batches_split=0]                                                                                                  |
   |                   |       ProjectionExec: expr=[0 as c0, 1 as c1, 2 as c2, 3 as c3, 4 as c4, 5 as c5, 6 as c6, 7 as c7, CAST(c8@0 AS Int64) as c8, 9 as c9], metrics=[output_rows=3, elapsed_compute=72.97µs, output_bytes=280.0 B]                                                                                                      |
   |                   |         DataSourceExec: partitions=1, partition_sizes=[1], metrics=[]                                                                                                                                                                                                                                                |
   |                   |     SortExec: expr=[c9@9 ASC NULLS LAST], preserve_partitioning=[false], metrics=[output_rows=3, elapsed_compute=48.301µs, output_bytes=0.0 B, spill_count=0, spilled_bytes=0.0 B, spilled_rows=0, batches_split=0]                                                                                                  |
   |                   |       ProjectionExec: expr=[0 as c0, 1 as c1, 2 as c2, 3 as c3, 4 as c4, 5 as c5, 6 as c6, 7 as c7, 8 as c8, CAST(c9@0 AS Int64) as c9], metrics=[output_rows=3, elapsed_compute=24.715µs, output_bytes=280.0 B]                                                                                                     |
   |                   |         DataSourceExec: partitions=1, partition_sizes=[1], metrics=[]                                                                                                                                                                                                                                                |
   |                   |                                                                                                                                                                                                                                                                                                                      |
   +-------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   

   Which is much cleaner and I believe that's what @alamb is referring to in the above comment.

   Is this difference between ctx.sql and datafusion-cli expected?
   It seems like benchmarks is skipping over some optimizations somehow.
   Perhaps making sure that benchmarks and datafusion-cli execute queries in the same way will be enough to solve this issue. WDYT?
8. alamb commented on Nov 7, 2025

   

   alamb

   on Nov 7, 2025

   Contributor

   More actions

   It seems like benchmarks is skipping over some optimizations somehow.
   Perhaps making sure that benchmarks and datafusion-cli execute queries in the same way will be enough to solve this issue. WDYT?

   Making sure the benchmark is doing the same thing as users (and datafusion-cli) sounds like a very wise first step to me
9. logan-keede commented on Nov 7, 2025

   

   logan-keede

   on Nov 7, 2025

   Contributor

   More actions

   After some testing to narrow the cause of the difference between benchmark and datafusion-cli, I found out that the cause is the fact that UInt64 somehow does not get optimised by eliminate_nested_union optimiser(which is being used in benchmarks), after changing it to Int64 it seems to work fine.
   It reduces the time by about half for 50 columns and 100 columns scenario.

   Int64
   Benchmarking physical_sorted_union_order_by_50: Collecting 100 samples in estimated 11.081 s (physical_sorted_union_order_by_50
                           time:   [105.39 ms 105.57 ms 105.78 ms]
                           change: [−1.4254% −0.8674% −0.3407%] (p = 0.00 < 0.05)
                           Change within noise threshold.
   Found 5 outliers among 100 measurements (5.00%)
     3 (3.00%) high mild
     2 (2.00%) high severe
   
   UInt64
   Benchmarking physical_sorted_union_order_by_50: Warming up for 3.0000 s
   Warning: Unable to complete 100 samples in 5.0s. You may wish to increase target time to 25.7s, or reduce sample count to 10.
   Benchmarking physical_sorted_union_order_by_50: Collecting 100 samples in estimated 25.740 s (physical_sorted_union_order_by_50
                           time:   [252.76 ms 254.23 ms 255.73 ms]
                           change: [+139.23% +140.81% +142.27%] (p = 0.00 < 0.05)
                           Performance has regressed.
   Found 2 outliers among 100 measurements (2.00%)
     1 (1.00%) high mild
     1 (1.00%) high severe
   
10. logan-keede commented on Nov 9, 2025

    

    logan-keede

    on Nov 9, 2025

    Contributor

    More actions

    For UInt64 scenario logical-plan optimizer is unable to optimize/detect nested union because each union is wrapped up in a projection like-

    Projection: c0, c1, c2, CAST(c3 AS UInt64) AS c3, c4, c5, c6, c7, c8, c9
      Union
        Projection: c0, c1, CAST(c2 AS UInt64) AS c2, c3, c4, c5, c6, c7, c8, c9
          Union
            Projection: t1.c0, CAST(c1 AS UInt64) AS c1, c2, c3, c4, c5, c6, c7, c8, c9
              Sort: t1.c0 ASC NULLS LAST
                Projection: t1.c0, Int64(1) AS c1, Int64(2) AS c2, Int64(3) AS c3, Int64(4) AS c4, Int64(5) AS c5, Int64(6)
    

    This is not the case for other scenarios (tested for UInt32, Int64), so they are optimized.

    Since, this is an edge case. I think we should temporarily shift the benchmark to Int64, while we raise a separate bug ticket or (change the description of this one).
11. alamb commented on Nov 9, 2025

    

    alamb

    on Nov 9, 2025

    Contributor

    More actions

    Since, this is an edge case. I think we should temporarily shift the benchmark to Int64, while we raise a separate bug ticket or (change the description of this one).

    This makes a lot of sense to me -- thank you @logan-keede

    So your proposal is to file a ticket for supporting nested_unions when there is a projection between them? (this makes a lot of sense to me and I am hopeful that it would improve performance substantially)
12. logan-keede commented on Nov 9, 2025

    

    logan-keede

    on Nov 9, 2025

    Contributor

    More actions

    So your proposal is to file a ticket for supporting nested_unions when there is a projection between them?

    Yes, I can make a PR to shift the benchmark to Int64 format, if that seems fine to you.
13. Omega359 commented on Nov 9, 2025

    

    Omega359

    on Nov 9, 2025

    Last edited by Omega359

    ContributorAuthor

    More actions

    So ... instead of fixing the issue we're just going to modify the benchmark to ignore the problem?

    Err, nm, I just reread previous comments. I still think a benchmark showcasing the issue should be left in though.
14. logan-keede commented on Nov 9, 2025

    

    logan-keede

    on Nov 9, 2025

    Contributor

    More actions

    what do you suggest? My understanding is that the only benchmark showcasing this is the worst case scenario, which does not showcase the improvement or regression over the general case, thus creating a blind spot.

    I am not saying that we ignore this case, and their maybe other cases too that suffer the same problem.
    I just thought that having a ticket tracking the issue would be enough, maybe we can have multiple versions of the same benchmark for different Datatypes?
15. Omega359 commented on Nov 10, 2025

    

    Omega359

    on Nov 10, 2025

    ContributorAuthor

    More actions

    While this may be the worst case scenario it is also my general case unfortunately. I perhaps selfishly would like the benchmark to still showcase that there is a lot of room for improvement here, especially once you increase the column count to 200+ columns.

    That being said I want to say that I really do appreciate your investigation and isolation of one of the causes of the performance issue here. I am completely fine with potentially adjusting the benchmark to showcase the difference in performance among various datatypes - it shouldn't be difficult to do that. If you want I can do that early this week assuming nothing else comes up at work.

    👍React with 👍1Reacted by Vedic Chawla

    ❤️React with ❤️1Reacted by Vedic Chawla
16. 1 remaining item

    Load more
17. github-merge-queue

    added a commit that references this issue on Nov 11, 2025

    Separating Benchmarks for physical sorted union over large columns in…

    ...

    03d1974
18. logan-keede commented on Nov 12, 2025

    

    logan-keede

    on Nov 12, 2025

    Contributor

    More actions

    After some more sleuthing, I think I have narrowed down on the cause of this problem

    Consider the following query and its unoptimized Logical Plan:-

      UNION ALL 
    (SELECT 0 as c0, c1, 2 as c2, 3 as c3, 4 as c4, 5 as c5, 6 as c6, 7 as c7, 8 as c8, 9 as c9 FROM t1 ORDER BY c1)
      UNION ALL 
    (SELECT 0 as c0, 1 as c1, c2, 3 as c3, 4 as c4, 5 as c5, 6 as c6, 7 as c7, 8 as c8, 9 as c9 FROM t1 ORDER BY c2)
      UNION ALL 
    (SELECT 0 as c0, 1 as c1, 2 as c2, c3, 4 as c4, 5 as c5, 6 as c6, 7 as c7, 8 as c8, 9 as c9 FROM t1 ORDER BY c3)
      UNION ALL 
    (SELECT 0 as c0, 1 as c1, 2 as c2, 3 as c3, c4, 5 as c5, 6 as c6, 7 as c7, 8 as c8, 9 as c9 FROM t1 ORDER BY c4)
      UNION ALL 
    (SELECT 0 as c0, 1 as c1, 2 as c2, 3 as c3, 4 as c4, c5, 6 as c6, 7 as c7, 8 as c8, 9 as c9 FROM t1 ORDER BY c5)
      UNION ALL 
    (SELECT 0 as c0, 1 as c1, 2 as c2, 3 as c3, 4 as c4, 5 as c5, c6, 7 as c7, 8 as c8, 9 as c9 FROM t1 ORDER BY c6)
      UNION ALL 
    (SELECT 0 as c0, 1 as c1, 2 as c2, 3 as c3, 4 as c4, 5 as c5, 6 as c6, c7, 8 as c8, 9 as c9 FROM t1 ORDER BY c7)
      UNION ALL 
    (SELECT 0 as c0, 1 as c1, 2 as c2, 3 as c3, 4 as c4, 5 as c5, 6 as c6, 7 as c7, c8, 9 as c9 FROM t1 ORDER BY c8)
      UNION ALL 
    (SELECT 0 as c0, 1 as c1, 2 as c2, 3 as c3, 4 as c4, 5 as c5, 6 as c6, 7 as c7, 8 as c8, c9 FROM t1 ORDER BY c9)
    ORDER BY c0, c1, c2, c3, c4, c5, c6, c7, c8, c9;
    
    Unoptimized Logical Plan
    Analyze
      Sort: c0 ASC NULLS LAST, c1 ASC NULLS LAST, c2 ASC NULLS LAST, c3 ASC NULLS LAST, c4 ASC NULLS LAST, c5 ASC NULLS LAST, c6 ASC NULLS LAST, c7 ASC NULLS LAST, c8 ASC NULLS LAST, c9 ASC NULLS LAST
        Union
          Union
            Union
              Union
                Union
                  Union
                    Union
                      Union
                        Union
                          Sort: t1.c0 ASC NULLS LAST
                            Projection: t1.c0, Int64(1) AS c1, Int64(2) AS c2, Int64(3) AS c3, Int64(4) AS c4, Int64(5) AS c5, Int64(6) AS c6, Int64(7) AS c7, Int64(8) AS c8, Int64(9) AS c9
                              TableScan: t1
                          Sort: t1.c1 ASC NULLS LAST
                            Projection: Int64(0) AS c0, t1.c1, Int64(2) AS c2, Int64(3) AS c3, Int64(4) AS c4, Int64(5) AS c5, Int64(6) AS c6, Int64(7) AS c7, Int64(8) AS c8, Int64(9) AS c9
                              TableScan: t1
                        Sort: t1.c2 ASC NULLS LAST
                          Projection: Int64(0) AS c0, Int64(1) AS c1, t1.c2, Int64(3) AS c3, Int64(4) AS c4, Int64(5) AS c5, Int64(6) AS c6, Int64(7) AS c7, Int64(8) AS c8, Int64(9) AS c9
                            TableScan: t1
                      Sort: t1.c3 ASC NULLS LAST
                        Projection: Int64(0) AS c0, Int64(1) AS c1, Int64(2) AS c2, t1.c3, Int64(4) AS c4, Int64(5) AS c5, Int64(6) AS c6, Int64(7) AS c7, Int64(8) AS c8, Int64(9) AS c9
                          TableScan: t1
                    Sort: t1.c4 ASC NULLS LAST
                      Projection: Int64(0) AS c0, Int64(1) AS c1, Int64(2) AS c2, Int64(3) AS c3, t1.c4, Int64(5) AS c5, Int64(6) AS c6, Int64(7) AS c7, Int64(8) AS c8, Int64(9) AS c9
                        TableScan: t1
                  Sort: t1.c5 ASC NULLS LAST
                    Projection: Int64(0) AS c0, Int64(1) AS c1, Int64(2) AS c2, Int64(3) AS c3, Int64(4) AS c4, t1.c5, Int64(6) AS c6, Int64(7) AS c7, Int64(8) AS c8, Int64(9) AS c9
                      TableScan: t1
                Sort: t1.c6 ASC NULLS LAST
                  Projection: Int64(0) AS c0, Int64(1) AS c1, Int64(2) AS c2, Int64(3) AS c3, Int64(4) AS c4, Int64(5) AS c5, t1.c6, Int64(7) AS c7, Int64(8) AS c8, Int64(9) AS c9
                    TableScan: t1
              Sort: t1.c7 ASC NULLS LAST
                Projection: Int64(0) AS c0, Int64(1) AS c1, Int64(2) AS c2, Int64(3) AS c3, Int64(4) AS c4, Int64(5) AS c5, Int64(6) AS c6, t1.c7, Int64(8) AS c8, Int64(9) AS c9
                  TableScan: t1
            Sort: t1.c8 ASC NULLS LAST
              Projection: Int64(0) AS c0, Int64(1) AS c1, Int64(2) AS c2, Int64(3) AS c3, Int64(4) AS c4, Int64(5) AS c5, Int64(6) AS c6, Int64(7) AS c7, t1.c8, Int64(9) AS c9
                TableScan: t1
          Sort: t1.c9 ASC NULLS LAST
            Projection: Int64(0) AS c0, Int64(1) AS c1, Int64(2) AS c2, Int64(3) AS c3, Int64(4) AS c4, Int64(5) AS c5, Int64(6) AS c6, Int64(7) AS c7, Int64(8) AS c8, t1.c9
              TableScan: t1
    

    Here, 0 which is a reference to the tables column number is interpreted as an literal Int64(0) before being referred.
    TypeCoercionRewriter using coerce_union:-

    datafusion/datafusion/optimizer/src/analyzer/type_coercion.rs

    Lines 216 to 243 in cf03574

    	pub fn coerce_union(union_plan: Union) -> Result<LogicalPlan> {
    	let union_schema = Arc::new(coerce_union_schema_with_schema(
    	&union_plan.inputs,
    	&union_plan.schema,
    	)?);
    	let new_inputs = union_plan
    	.inputs
    	.into_iter()
    	.map(|p| {
    	let plan =
    	coerce_plan_expr_for_schema(Arc::unwrap_or_clone(p), &union_schema)?;
    	match plan {
    	LogicalPlan::Projection(Projection { expr, input, .. }) => {
    	Ok(Arc::new(project_with_column_index(
    	expr,
    	input,
    	Arc::clone(&union_schema),
    	)?))
    	}
    	other_plan => Ok(Arc::new(other_plan)),
    	}
    	})
    	.collect::<Result<Vec<_>>>()?;
    	Ok(LogicalPlan::Union(Union {
    	inputs: new_inputs,
    	schema: union_schema,
    	}))
    	}

    Which tries to coerce union's Input into same time. It sees that it has an Int64 literal as an argument and other UInt64 columns and tries to coerce it using coercion Rules mentioned here:-

    datafusion/datafusion/expr-common/src/type_coercion/binary.rs

    Lines 1293 to 1299 in cf03574

    	(UInt64, Int64 | Int32 | Int16 | Int8)
    	| (Int64 | Int32 | Int16 | Int8, UInt64) => Some(Decimal128(20, 0)),
    	(UInt64, _) | (_, UInt64) => Some(UInt64),
    	(Int64, _)
    	| (_, Int64)
    	| (UInt32, Int32 | Int16 | Int8)
    	| (Int32 | Int16 | Int8, UInt32) => Some(Int64),

    Which results in a projection layer being added on top of union to coerce its output
    something like:-

    Projection: c0, c1, CAST(c2 AS Decimal128(20, 0)) AS c2, c3, c4, c5, c6, c7, c8, c9
      Union
        Projection: CAST(t1.c0 AS Decimal128(20, 0)) AS c0, CAST(c1 AS Decimal128(20, 0)) AS c1, c2, c3, c4, c5, c6, c7, c8, c9
          Sort: t1.c0 ASC NULLS LAST
    

    Now, union does not have its descendant unions as direct children so, EliminateNestedUnion fails to eliminate this Union.

    After I remove above mentioned two coercion rules(2nd one also tries to coerce UInt64, and adds a projection layer)
    I get an optimized logical plan like the Int64 datatype scenario.

    > ORDER BY c0, c1, c2, c3, c4, c5, c6, c7, c8, c9;
    > +-------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    > | plan_type         | plan                                                                                                                                                                                                                                                                                                                 |
    > +-------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    > | Plan with Metrics | SortPreservingMergeExec: [c0@0 ASC NULLS LAST, c1@1 ASC NULLS LAST, c2@2 ASC NULLS LAST, c3@3 ASC NULLS LAST, c4@4 ASC NULLS LAST, c5@5 ASC NULLS LAST, c6@6 ASC NULLS LAST, c7@7 ASC NULLS LAST, c8@8 ASC NULLS LAST, c9@9 ASC NULLS LAST], metrics=[output_rows=30, elapsed_compute=202.88µs, output_bytes=2.3 KB] |
    > |                   |   UnionExec, metrics=[output_rows=30, elapsed_compute=1.117171ms, output_bytes=2.3 KB]                                                                                                                                                                                                                               |
    > |                   |     SortExec: expr=[c0@0 ASC NULLS LAST], preserve_partitioning=[false], metrics=[output_rows=3, elapsed_compute=1.712165ms, output_bytes=0.0 B, spill_count=0, spilled_bytes=0.0 B, spilled_rows=0, batches_split=0]                                                                                                |
    > |                   |       ProjectionExec: expr=[CAST(c0@0 AS Int64) as c0, 1 as c1, 2 as c2, 3 as c3, 4 as c4, 5 as c5, 6 as c6, 7 as c7, 8 as c8, 9 as c9], metrics=[output_rows=3, elapsed_compute=79.9µs, output_bytes=280.0 B]                                                                                                       |
    > |                   |         DataSourceExec: partitions=1, partition_sizes=[1], metrics=[]                                                                                                                                                                                                                                                |
    > |                   |     SortExec: expr=[c1@1 ASC NULLS LAST], preserve_partitioning=[false], metrics=[output_rows=3, elapsed_compute=1.244665ms, output_bytes=0.0 B, spill_count=0, spilled_bytes=0.0 B, spilled_rows=0, batches_split=0]                                                                                                |
    > |                   |       ProjectionExec: expr=[0 as c0, CAST(c1@0 AS Int64) as c1, 2 as c2, 3 as c3, 4 as c4, 5 as c5, 6 as c6, 7 as c7, 8 as c8, 9 as c9], metrics=[output_rows=3, elapsed_compute=86.887µs, output_bytes=280.0 B]                                                                                                     |
    > |                   |         DataSourceExec: partitions=1, partition_sizes=[1], metrics=[]                                                                                                                                                                                                                                                |
    > |                   |     SortExec: expr=[c2@2 ASC NULLS LAST], preserve_partitioning=[false], metrics=[output_rows=3, elapsed_compute=1.217714ms, output_bytes=0.0 B, spill_count=0, spilled_bytes=0.0 B, spilled_rows=0, batches_split=0]                                                                                                |
    > |                   |       ProjectionExec: expr=[0 as c0, 1 as c1, CAST(c2@0 AS Int64) as c2, 3 as c3, 4 as c4, 5 as c5, 6 as c6, 7 as c7, 8 as c8, 9 as c9], metrics=[output_rows=3, elapsed_compute=59.552µs, output_bytes=280.0 B]                                                                                                     |
    > |                   |         DataSourceExec: partitions=1, partition_sizes=[1], metrics=[]                                                                                                                                                                                                                                                |
    > |                   |     SortExec: expr=[c3@3 ASC NULLS LAST], preserve_partitioning=[false], metrics=[output_rows=3, elapsed_compute=136.622µs, output_bytes=0.0 B, spill_count=0, spilled_bytes=0.0 B, spilled_rows=0, batches_split=0]                                                                                                 |
    > |                   |       ProjectionExec: expr=[0 as c0, 1 as c1, 2 as c2, CAST(c3@0 AS Int64) as c3, 4 as c4, 5 as c5, 6 as c6, 7 as c7, 8 as c8, 9 as c9], metrics=[output_rows=3, elapsed_compute=90.253µs, output_bytes=280.0 B]                                                                                                     |
    > |                   |         DataSourceExec: partitions=1, partition_sizes=[1], metrics=[]                                                                                                                                                                                                                                                |
    > |                   |     SortExec: expr=[c4@4 ASC NULLS LAST], preserve_partitioning=[false], metrics=[output_rows=3, elapsed_compute=1.248026ms, output_bytes=0.0 B, spill_count=0, spilled_bytes=0.0 B, spilled_rows=0, batches_split=0]                                                                                                |
    > |                   |       ProjectionExec: expr=[0 as c0, 1 as c1, 2 as c2, 3 as c3, CAST(c4@0 AS Int64) as c4, 5 as c5, 6 as c6, 7 as c7, 8 as c8, 9 as c9], metrics=[output_rows=3, elapsed_compute=100.374µs, output_bytes=280.0 B]                                                                                                    |
    > |                   |         DataSourceExec: partitions=1, partition_sizes=[1], metrics=[]                                                                                                                                                                                                                                                |
    > |                   |     SortExec: expr=[c5@5 ASC NULLS LAST], preserve_partitioning=[false], metrics=[output_rows=3, elapsed_compute=1.01873ms, output_bytes=0.0 B, spill_count=0, spilled_bytes=0.0 B, spilled_rows=0, batches_split=0]                                                                                                 |
    > |                   |       ProjectionExec: expr=[0 as c0, 1 as c1, 2 as c2, 3 as c3, 4 as c4, CAST(c5@0 AS Int64) as c5, 6 as c6, 7 as c7, 8 as c8, 9 as c9], metrics=[output_rows=3, elapsed_compute=70.435µs, output_bytes=280.0 B]                                                                                                     |
    > |                   |         DataSourceExec: partitions=1, partition_sizes=[1], metrics=[]                                                                                                                                                                                                                                                |
    > |                   |     SortExec: expr=[c6@6 ASC NULLS LAST], preserve_partitioning=[false], metrics=[output_rows=3, elapsed_compute=512.958µs, output_bytes=0.0 B, spill_count=0, spilled_bytes=0.0 B, spilled_rows=0, batches_split=0]                                                                                                 |
    > |                   |       ProjectionExec: expr=[0 as c0, 1 as c1, 2 as c2, 3 as c3, 4 as c4, 5 as c5, CAST(c6@0 AS Int64) as c6, 7 as c7, 8 as c8, 9 as c9], metrics=[output_rows=3, elapsed_compute=64.86µs, output_bytes=280.0 B]                                                                                                      |
    > |                   |         DataSourceExec: partitions=1, partition_sizes=[1], metrics=[]                                                                                                                                                                                                                                                |
    > |                   |     SortExec: expr=[c7@7 ASC NULLS LAST], preserve_partitioning=[false], metrics=[output_rows=3, elapsed_compute=584.371µs, output_bytes=0.0 B, spill_count=0, spilled_bytes=0.0 B, spilled_rows=0, batches_split=0]                                                                                                 |
    > |                   |       ProjectionExec: expr=[0 as c0, 1 as c1, 2 as c2, 3 as c3, 4 as c4, 5 as c5, 6 as c6, CAST(c7@0 AS Int64) as c7, 8 as c8, 9 as c9], metrics=[output_rows=3, elapsed_compute=64.695µs, output_bytes=280.0 B]                                                                                                     |
    > |                   |         DataSourceExec: partitions=1, partition_sizes=[1], metrics=[]                                                                                                                                                                                                                                                |
    > |                   |     SortExec: expr=[c8@8 ASC NULLS LAST], preserve_partitioning=[false], metrics=[output_rows=3, elapsed_compute=161.32µs, output_bytes=0.0 B, spill_count=0, spilled_bytes=0.0 B, spilled_rows=0, batches_split=0]                                                                                                  |
    > |                   |       ProjectionExec: expr=[0 as c0, 1 as c1, 2 as c2, 3 as c3, 4 as c4, 5 as c5, 6 as c6, 7 as c7, CAST(c8@0 AS Int64) as c8, 9 as c9], metrics=[output_rows=3, elapsed_compute=72.97µs, output_bytes=280.0 B]                                                                                                      |
    > |                   |         DataSourceExec: partitions=1, partition_sizes=[1], metrics=[]                                                                                                                                                                                                                                                |
    > |                   |     SortExec: expr=[c9@9 ASC NULLS LAST], preserve_partitioning=[false], metrics=[output_rows=3, elapsed_compute=48.301µs, output_bytes=0.0 B, spill_count=0, spilled_bytes=0.0 B, spilled_rows=0, batches_split=0]                                                                                                  |
    > |                   |       ProjectionExec: expr=[0 as c0, 1 as c1, 2 as c2, 3 as c3, 4 as c4, 5 as c5, 6 as c6, 7 as c7, 8 as c8, CAST(c9@0 AS Int64) as c9], metrics=[output_rows=3, elapsed_compute=24.715µs, output_bytes=280.0 B]                                                                                                     |
    > |                   |         DataSourceExec: partitions=1, partition_sizes=[1], metrics=[]                                                                                                                                                                                                                                                |
    > |                   |                                                                                                                                                                                                                                                                                                                      |
    > +-------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    

    Proposed Solution:-

    Stop TypeCoercercionRewriter from trying to coerce on the basis of column reference's inferred type.
19. Omega359 commented on Nov 12, 2025

    

    Omega359

    on Nov 12, 2025

    ContributorAuthor

    More actions

    If you have a branch for this somewhere I'd love to run some tests with it.
20. logan-keede commented on Nov 12, 2025

    

    logan-keede

    on Nov 12, 2025

    Contributor

    More actions

    You can use this branch: - https://github.com/logan-keede/datafusion/tree/experimental_union_planning
    Please note that I am not proposing removing coercion rules as a solution. I don't have the branch with a solution yet this is just for the experimental stuff I did to reach the above-mentioned conclusions.
21. alamb commented on Nov 13, 2025

    

    alamb

    on Nov 13, 2025

    Contributor

    More actions

    This is great 🕵️ @logan-keede 🙏

    One potential way to improve this would be to teach the optimizer to combine the Projections somehow -- specifically I think the current https://github.com/apache/datafusion/blob/967384e16257bab4deb48bbeff38507ea13c4abd/datafusion/optimizer/src/eliminate_nested_union.rs#L39-L38 rewrites this pattern:

    Union 
      <plan1>
      Union
        <plan2>
    

    Into this pattern:

    Union 
      <plan1>
      <plan2>
    

    Maybe we could extend the code somehow to rewrite this pattern too:

    Union 
      <plan1>
      Projection <...>
        Union
          <plan2>
    

    Union 
      <plan1>
      Projection <...>
        <plan2>
    

    (aka "look through" the projection)

    Actually now that I type this, it might hold for any number of single input children between the Union and their children (not just for Projections)

    👍React with 👍1Reacted by Nick Karlov
22. github-merge-queue

    added a commit that references this issue on Nov 17, 2025

    Consolidate `EliminateNestedUnion` and `EliminateOneUnion` optimizer …

    ...

    7fa6378
23. logan-keede

    added a commit that references this issue on Nov 23, 2025

    Consolidate `EliminateNestedUnion` and `EliminateOneUnion` optimizer …

    ...

    dc7c84f
24. github-merge-queue

    added a commit that references this issue on Nov 25, 2025

    Optimize planning for projected nested union (#18713)

    ...

    48cc4c8
25. Omega359 commented on Jan 19, 2026

    

    Omega359

    on Jan 19, 2026

    Last edited by Omega359

    ContributorAuthor

    More actions

    I ran samply last night against a slightly modified sql_planner_extended benchmark which had some interesting results.

    fn criterion_benchmark(c: &mut Criterion) {
        env_logger::builder().is_test(true).try_init().unwrap();
    
        // added these 3 lines for this test
        let mut session_config = SessionConfig::new();                                    
        session_config.options_mut().optimizer.top_down_join_key_reordering = true;
        session_config.options_mut().optimizer.enable_dynamic_filter_pushdown = false;
    
    
        let ctx = SessionContext::new_with_config(session_config);
        let rt = Runtime::new().unwrap();
    
        // validate logical plan optimize performance
        // https://github.com/apache/datafusion/issues/17261
    
        let df = build_test_data_frame(&ctx, &rt);
    
        c.bench_function("logical_plan_optimize", |b| {
            b.iter(|| {
                let df_clone = df.clone();
                // physical instead of just logical
                black_box(rt.block_on(async { let _ = df_clone.create_physical_plan().await; }));
            })
        });
    }

    Benchmarking logical_plan_optimize: Warming up for 3.0000 s
    Warning: Unable to complete 100 samples in 5.0s. You may wish to increase target time to 20397.1s, or reduce sample count to 10.
    logical_plan_optimize   time:   [198.72 s 199.08 s 199.48 s]
                            change: [+11.006% +12.045% +12.717%] (p = 0.00 < 0.05)
                            Performance has regressed.
    Found 6 outliers among 100 measurements (6.00%)
      2 (2.00%) high mild
      4 (4.00%) high severe
    
    Lost 391 events.
    Local server listening at http://127.0.0.1:3000
    Press Ctrl+C to stop.
    

    

    I'm going to add logging for timings per optimizer shortly and retest.
26. alamb commented on Jan 22, 2026

    

    alamb

    on Jan 22, 2026

    Contributor

    More actions

    that is quite fasinating (I have not seen PredicateBoundsEvaluator before -- I wonder if that is something that got newly added)

    Note that @askalt also has a PR that improves some of the deeply nested union planning benchmarks by 50%

    • Cache PlanProperties, add fast-path for with_new_children #19792
27. Omega359 commented on Jan 22, 2026

    

    Omega359

    on Jan 22, 2026

    ContributorAuthor

    More actions

    I just saw that PR earlier today @alamb - I am going to see if I can redo the test this weekend with that PR in place to see how much if affects things.

    ❤️React with ❤️1Reacted by Andrew Lamb
28. Omega359 commented on Jan 25, 2026

    

    Omega359

    on Jan 25, 2026

    ContributorAuthor

    More actions

    Benchmarking logical_plan_optimize: Warming up for 3.0000 s
    Warning: Unable to complete 100 samples in 5.0s. You may wish to increase target time to 19863.9s, or reduce sample count to 10.
    logical_plan_optimize   time:   [196.82 s 197.50 s 198.43 s]
                            change: [−1.2142% −0.7912% −0.3420%] (p = 0.00 < 0.05)
                            Change within noise threshold.
    Found 10 outliers among 100 measurements (10.00%)
      6 (6.00%) high mild
      4 (4.00%) high severe
    
    Lost 341 events.
    
    bruce@fedora:~/dev/datafusion$ git branch
      date_with_interval
      fork/Weijun-H/18384-reset-config
      fork/alamb/alamb/union_clean
    * fork/askalt/askalt/with_new_children_fast_path
      fork/kosiew/timestamp-17998
      fork/kumarUjjawal/fix/date_duration_type
      fork/logan-keede/optimize_projected_union
      fork/pepijnve/nullable_interval
      jiff-proto
      main
      optimize_projected_union
      regex_query_perf
      timestamp-17998
      upstream_main
    
    

    Unless there is a flag that is required the changes in #19792 seemed to have had no impact on the sql_planner_extended benchmark as modified above.
29. dexhunter commented on Jul 29, 2026

    

    dexhunter

    on Jul 29, 2026

    More actions

    take
30. github-actions

    assigned

    dexhunter

    on Jul 29, 2026
31. kosiew

    added a commit that references this issue on Oct 28, 2026

    perf: avoid quadratic metadata merging in union schemas (apache#26790)

    ...

    7ca68cf