You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@gluten.apache.org by "lgbo-ustc (via GitHub)" <gi...@apache.org> on 2025/02/06 09:48:46 UTC

[I] [CH] Rewrite union of multiple aggregates [incubator-gluten]

lgbo-ustc opened a new issue, #8675:
URL: https://github.com/apache/incubator-gluten/issues/8675

   ### Description
   
   Following query needs to scan the table `nation` multiple times.
   ```sql
   select n_regionkey , n_name, '0' as t, avg(n_nationkey) + count(n_name) as s from  nation where n_nationkey % 3 = 0 group by n_name,  n_regionkey 
   union all
   select n_regionkey , n_name, '1' as t, avg(n_nationkey) + count(n_name) as s from nation where n_nationkey % 2 = 1 group by n_name, n_regionkey 
   ```
   
   Following is the physical plan
   ```
   AdaptiveSparkPlan isFinalPlan=false
   +- Union
      :- HashAggregate(keys=[n_name#5, _groupingexpression#24L], functions=[avg(cast(n_nationkey#4L as double)), count(n_name#5)], output=[(n_regionkey + 1)#18L, n_name#5, t#0, s#1])
      :  +- Exchange hashpartitioning(n_name#5, _groupingexpression#24L, 5), ENSURE_REQUIREMENTS, [plan_id=38]
      :     +- HashAggregate(keys=[n_name#5, _groupingexpression#24L], functions=[partial_avg(cast(n_nationkey#4L as double)), partial_count(n_name#5)], output=[n_name#5, _groupingexpression#24L, sum#29, count#30L, count#31L])
      :        +- Project [n_nationkey#4L, n_name#5, (n_regionkey#6L + cast(1 as bigint)) AS _groupingexpression#24L]
      :           +- Filter (isnotnull(n_nationkey#4L) AND ((n_nationkey#4L % cast(2 as bigint)) = cast(0 as bigint)))
      :              +- FileScan parquet tpch_pq.nation[n_nationkey#4L,n_name#5,n_regionkey#6L]
      +- HashAggregate(keys=[n_name#9, _groupingexpression#25L], functions=[avg(cast(n_nationkey#8L as double)), count(n_name#9)], output=[(n_regionkey + 1)#19L, n_name#9, t#2, s#3])
         +- Exchange hashpartitioning(n_name#9, _groupingexpression#25L, 5), ENSURE_REQUIREMENTS, [plan_id=40]
            +- HashAggregate(keys=[n_name#9, _groupingexpression#25L], functions=[partial_avg(cast(n_nationkey#8L as double)), partial_count(n_name#9)], output=[n_name#9, _groupingexpression#25L, sum#35, count#36L, count#37L])
               +- Project [n_nationkey#8L, n_name#9, (n_regionkey#10L + cast(1 as bigint)) AS _groupingexpression#25L]
                  +- Filter (isnotnull(n_nationkey#8L) AND ((n_nationkey#8L % cast(2 as bigint)) = cast(1 as bigint)))
                     +- FileScan parquet tpch_pq.nation[n_nationkey#8L,n_name#9,n_regionkey#10L]
   ```
   
   When the table is too large, the `scan` takes up most of the execution time.
   
   We could rewrite the query as following, to make `nation` be scanned only once.
   ```sql
   
   select n_regionkey, n_name, t, avg(n_nationkey) + count(n_name) from (
     select st.n_regionkey as n_regionkey, st.n_name as n_name, st.t as t, st.n_nationkey as n_nationkey, st.gid as gid from (
       select st from (
           select explode(a) as st from (
             select array(
               if( n_nationkey % 3 = 0, named_struct('n_regionkey', n_regionkey, 'n_nationkey', n_nationkey, 'n_name', n_name, 't', '0', 'gid', 0), null), 
               if(n_nationkey % 2, named_struct('n_regionkey', n_regionkey, 'n_nationkey', n_nationkey, 'n_name', n_name, 't', '1', 'gid', 1), null)) as a from (
               select * from nation where n_nationkey % 3 = 0 or n_nationkey % 2
             )
           )
       ) where st is not null
     )
   ) group by n_regionkey, n_nationkey, t, gid
   
   ```
   
   


-- 
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: commits-unsubscribe@gluten.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@gluten.apache.org
For additional commands, e-mail: commits-help@gluten.apache.org


Re: [I] [CH] Rewrite union of multiple aggregates [incubator-gluten]

Posted by "lgbo-ustc (via GitHub)" <gi...@apache.org>.
lgbo-ustc closed issue #8675: [CH] Rewrite union of multiple aggregates
URL: https://github.com/apache/incubator-gluten/issues/8675


-- 
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: commits-unsubscribe@gluten.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@gluten.apache.org
For additional commands, e-mail: commits-help@gluten.apache.org