You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "andygrove (via GitHub)" <gi...@apache.org> on 2025/01/13 19:44:41 UTC

[I] Improve performance of `dropDuplicates` [datafusion-comet]

andygrove opened a new issue, #1275:
URL: https://github.com/apache/datafusion-comet/issues/1275

   ### What is the problem the feature request solves?
   
   Comet is 4x slower than Spark for the following query:
   
   ```python
   spark.read.parquet("/mnt/bigdata/tpcds/sf100/store_sales.parquet") \
       .repartition("ss_item_sk") \
       .dropDuplicates(["ss_item_sk", "ss_quantity"]) \
       .write.parquet("output.parquet")
   ```
   
   If I remove the `dropDuplicates` call, I see similar performance between Comet and Spark.
   
   ### Describe the potential solution
   
   _No response_
   
   ### Additional context
   
   _No response_


-- 
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-unsubscribe@datafusion.apache.org.apache.org

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


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [I] Improve performance of `dropDuplicates` [datafusion-comet]

Posted by "andygrove (via GitHub)" <gi...@apache.org>.
andygrove commented on issue #1275:
URL: https://github.com/apache/datafusion-comet/issues/1275#issuecomment-2813972103

   This should be resolved now that we have upgraded to DF 47


-- 
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-unsubscribe@datafusion.apache.org

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


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [I] Improve performance of `dropDuplicates` [datafusion-comet]

Posted by "andygrove (via GitHub)" <gi...@apache.org>.
andygrove commented on issue #1275:
URL: https://github.com/apache/datafusion-comet/issues/1275#issuecomment-2787795808

   Running the same benchmark in a branch where I am working on the upgrade to DataFusion 47.0.0, I see that performance is greatly improved and is actually faster than Spark ๐Ÿš€ 
   
   ```
   scala> spark.time(bench("first_value"))
   Time taken: 69429 ms   
   ```
   
   Thanks @2010YOUY01 ๐Ÿ˜„ 


-- 
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-unsubscribe@datafusion.apache.org

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


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [I] Improve performance of `dropDuplicates` [datafusion-comet]

Posted by "andygrove (via GitHub)" <gi...@apache.org>.
andygrove commented on issue #1275:
URL: https://github.com/apache/datafusion-comet/issues/1275#issuecomment-2787785593

   There was a recent improvement in DataFusion that may help. I will test.
   
   https://github.com/apache/datafusion/pull/15266


-- 
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-unsubscribe@datafusion.apache.org

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


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [I] Improve performance of `dropDuplicates` [datafusion-comet]

Posted by "andygrove (via GitHub)" <gi...@apache.org>.
andygrove commented on issue #1275:
URL: https://github.com/apache/datafusion-comet/issues/1275#issuecomment-2787665609

   Sample native plan with metrics:
   
   ```
   25/04/08 15:06:01 INFO core/src/execution/jni_api.rs: Comet native query plan with metrics (Plan #185 Stage 7 Partition 6):
    plan creation (including CometScans fetching first batches) took 3.022557ms:
   ProjectionExec: expr=[first@2 as col_0, first@3 as col_1, col_0@0 as col_2, first@4 as col_3, first@5 as col_4, first@6 as col_5, first@7 as col_6, first@8 as col_7, first@9 as col_8, first@10 as col_9, col_1@1 as col_10, first@11 as col_11, first@12 as col_12, first@13 as col_13, first@14 as col_14, first@15 as col_15, first@16 as col_16, first@17 as col_17, first@18 as col_18, first@19 as col_19, first@20 as col_20, first@21 as col_21, first@22 as col_22], metrics=[output_rows=105687, elapsed_compute=67.075ยตs]
     AggregateExec: mode=Final, gby=[col_0@0 as col_0, col_1@1 as col_1], aggr=[first, first, first, first, first, first, first, first, first, first, first, first, first, first, first, first, first, first, first, first, first], metrics=[output_rows=105687, elapsed_compute=994.964965ms, spill_count=0, spilled_bytes=0, spilled_rows=0, peak_mem_used=485980792]
       AggregateExec: mode=Partial, gby=[col_2@2 as col_0, col_10@10 as col_1], aggr=[first, first, first, first, first, first, first, first, first, first, first, first, first, first, first, first, first, first, first, first, first], metrics=[output_rows=105687, elapsed_compute=9.759887803s, spill_count=0, spilled_bytes=0, spilled_rows=0, skipped_aggregation_rows=0, peak_mem_used=485980792]
         ScanExec: source=[ShuffleQueryStage (unknown), Statistics(sizeInBytes=64.1 GiB, rowCount=2.88E+8)], schema=[col_0: Int32, col_1: Int32, col_2: Int32, col_3: Int32, col_4: Int32, col_5: Int32, col_6: Int32, col_7: Int32, col_8: Int32, col_9: Int32, col_10: Int32, col_11: Decimal128(7, 2), col_12: Decimal128(7, 2), col_13: Decimal128(7, 2), col_14: Decimal128(7, 2), col_15: Decimal128(7, 2), col_16: Decimal128(7, 2), col_17: Decimal128(7, 2), col_18: Decimal128(7, 2), col_19: Decimal128(7, 2), col_20: Decimal128(7, 2), col_21: Decimal128(7, 2), col_22: Decimal128(7, 2)], metrics=[output_rows=1508245, elapsed_compute=262.520531ms, arrow_ffi_time=12.049335ms, cast_time=1ns, jvm_fetch_time=249.312038ms]
   ```


-- 
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-unsubscribe@datafusion.apache.org

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


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [I] Improve performance of `dropDuplicates` [datafusion-comet]

Posted by "andygrove (via GitHub)" <gi...@apache.org>.
andygrove commented on issue #1275:
URL: https://github.com/apache/datafusion-comet/issues/1275#issuecomment-2787767219

   The performance issue seems specific to the `first_value` aggregate function. The `dropDuplicates` call is equivalent to:
   
   ```sql
   select ss_item_sk, ss_quantity, first_value(ss_sold_date_sk), first_value(ss_sold_time_sk), first_value(ss_customer_sk), first_value(ss_cdemo_sk), first_value(ss_hdemo_sk), first_value(ss_addr_sk), first_value(ss_store_sk), first_value(ss_promo_sk), first_value(ss_ticket_number), first_value(ss_wholesale_cost), first_value(ss_list_price), first_value(ss_sales_price), first_value(ss_ext_discount_amt), first_value(ss_ext_sales_price), first_value(ss_ext_wholesale_cost), first_value(ss_ext_list_price), first_value(ss_ext_tax), first_value(ss_coupon_amt), first_value(ss_net_paid), first_value(ss_net_paid_inc_tax), first_value(ss_net_profit) from store_sales group by ss_item_sk, ss_quantity
   ```
   
   Using `min` instead of `first_value` is much faster.
   
   Here is how I tested:
   
   ```scala
   def bench(aggr: String) {
     val df = spark.read.parquet("/mnt/bigdata/tpcds/sf100/store_sales.parquet")
     val cols = df.schema.fieldNames.filterNot(name => Seq("ss_item_sk", "ss_quantity").contains(name))
     df.repartition($"ss_item_sk").createOrReplaceTempView("store_sales")
     val sql = s"select ss_item_sk, ss_quantity, ${cols.map(name => s"$aggr($name)").mkString(", ")} from store_sales group by ss_item_sk, ss_quantity"
     spark.sql(sql).write.parquet("output2.parquet")
   }
   
   scala> spark.time(bench("first_value"))
   Time taken: 320671 ms 
   
   scala> spark.time(bench("min"))
   Time taken: 64462 ms
   ```
   
   I don't understand why there would be such a significant performance difference between `first_value` and `min`. I will try to reproduce this in DataFusion next.


-- 
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-unsubscribe@datafusion.apache.org

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


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [I] Improve performance of `dropDuplicates` [datafusion-comet]

Posted by "andygrove (via GitHub)" <gi...@apache.org>.
andygrove commented on issue #1275:
URL: https://github.com/apache/datafusion-comet/issues/1275#issuecomment-2588054784

   Disabling Comet aggregation improves performance from 230s to 64s and is slightly faster than Spark at 78s.


-- 
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-unsubscribe@datafusion.apache.org

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


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [I] Improve performance of `dropDuplicates` [datafusion-comet]

Posted by "andygrove (via GitHub)" <gi...@apache.org>.
andygrove commented on issue #1275:
URL: https://github.com/apache/datafusion-comet/issues/1275#issuecomment-2787387798

   I filed https://github.com/apache/datafusion-comet/issues/1625 for implementing a native Parquet writer so that we can avoid the C2R conversion.


-- 
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-unsubscribe@datafusion.apache.org

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


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [I] Improve performance of `dropDuplicates` [datafusion-comet]

Posted by "andygrove (via GitHub)" <gi...@apache.org>.
andygrove commented on issue #1275:
URL: https://github.com/apache/datafusion-comet/issues/1275#issuecomment-2787315942

   I ran this test again with latest from main. I used this script to launch spark-shell:
   
   ```shell
   $SPARK_HOME/bin/spark-shell \
     --master $SPARK_MASTER \
     --conf spark.driver.memory=4G \
     --conf spark.executor.instances=1 \
     --conf spark.executor.cores=8 \
     --conf spark.cores.max=8 \
     --conf spark.executor.memory=16g \
     --conf spark.memory.offHeap.enabled=true \
     --conf spark.memory.offHeap.size=16g \
     --conf spark.eventLog.enabled=false \
     --jars $COMET_JAR \
     --driver-class-path $COMET_JAR \
     --conf spark.driver.extraClassPath=$COMET_JAR \
     --conf spark.executor.extraClassPath=$COMET_JAR \
     --conf spark.plugins=org.apache.spark.CometPlugin \
     --conf spark.shuffle.manager=org.apache.spark.sql.comet.execution.shuffle.CometShuffleManager \
     --conf spark.comet.enabled=true \
     --conf spark.comet.cast.allowIncompatible=true \
     --conf spark.comet.exec.replaceSortMergeJoin=true \
     --conf spark.comet.exec.shuffle.enabled=true \
     --conf spark.comet.exec.shuffle.fallbackToColumnar=true \
     --conf spark.comet.exec.shuffle.compression.codec=lz4 \
     --conf spark.comet.exec.shuffle.compression.level=1
   ```
   
   I ran this query
   
   ```scala
   spark.time(spark.read.parquet("/mnt/bigdata/tpcds/sf100/store_sales.parquet").repartition($"ss_item_sk").dropDuplicates(Seq("ss_item_sk", "ss_quantity")).write.parquet("output.parquet"))
   ```
   
   With Comet: 325s
   With Spark: 77s
   
   Observations:
   
   - Scan is fast (45 seconds)
   - Shuffle is fast (4.3 mins)
   - Aggregate is slow (31.9 mins)
   - ColumnarToRow is slow (36.8 mins)
   - Aggregate takes 


-- 
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-unsubscribe@datafusion.apache.org

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


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [I] Improve performance of `dropDuplicates` [datafusion-comet]

Posted by "andygrove (via GitHub)" <gi...@apache.org>.
andygrove commented on issue #1275:
URL: https://github.com/apache/datafusion-comet/issues/1275#issuecomment-2787808120

   Here is a run with the original query that took 77 seconds in Spark:
   
   ```
   scala> spark.time(spark.read.parquet("/mnt/bigdata/tpcds/sf100/store_sales.parquet").repartition($"ss_item_sk").dropDuplicates(Seq("ss_item_sk", "ss_quantity")).write.parquet("output44.parquet"))
   Time taken: 62956 ms  
   ```


-- 
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-unsubscribe@datafusion.apache.org

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


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [I] Improve performance of `dropDuplicates` [datafusion-comet]

Posted by "andygrove (via GitHub)" <gi...@apache.org>.
andygrove closed issue #1275: Improve performance of `dropDuplicates`
URL: https://github.com/apache/datafusion-comet/issues/1275


-- 
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-unsubscribe@datafusion.apache.org

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


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [I] Improve performance of `dropDuplicates` [datafusion-comet]

Posted by "2010YOUY01 (via GitHub)" <gi...@apache.org>.
2010YOUY01 commented on issue #1275:
URL: https://github.com/apache/datafusion-comet/issues/1275#issuecomment-2788165594

   > Running the same benchmark in a branch where I am working on the upgrade to DataFusion 47.0.0, I see that performance is greatly improved and is actually faster than Spark ๐Ÿš€
   > 
   > ```
   > scala> spark.time(bench("first_value"))
   > Time taken: 69429 ms   
   > ```
   > 
   > Thanks [@2010YOUY01](https://github.com/2010YOUY01) ๐Ÿ˜„
   
   The credit should go to @UBarney ๐Ÿš€ 


-- 
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-unsubscribe@datafusion.apache.org

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


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org