[Improvement] The execution time of spark connector is 4 times that of native spark3.3.2 when running tpcds sql99

[yangyuxia]

What would you like to be improved?

A comparative test using tpcds 1000G data found that the execution time of spark connector is 4 times that of native spark 3.3.2.
Comparing the physical execution plans of the two, the Gravitino Spark Connector does not use the predicate pushdown and dynamic partition pruning optimization strategies.

spark-gravitino-tpcds性能对比测试.xlsx
tpcds-query1-gravitino执行计划.txt
tpcds-query1-spark执行计划.txt

How should we improve?

No response

[shaofengshi]

Thanks for the feedback! Yes, Gravitino Spark connector is based on kyuubi, so its behavior is more closed with kyuubi. @FANNG1 xiaojing, can you confirm this, and is there any change to enhance the performance?

[FANNG1]

There are some known limitations for the kyubbi spark connector, like #1578 , #1568. Predication pushdown seems also a critical issue.

[shaofengshi]

There are two new enhancements in Kyuubi can enhance Gravitino spark connector performance:
apache/kyuubi#7129
apache/kyuubi#7122

Thanks to the contributor @flaming-archer ! From his data, the performance gain is at least 2x!

[flaming-archer]

At present, our optimization has achieved a reading data rate of approximately 66% for ORC and 80%+ for Parquet compared to native Spark.

We have made the following optimizations:

1. ORC Pusheddown filter. [FEATURE] Spark Hive connector supports ORC hive table pushdown filter kyuubi#7122
2. Parquet Pusheddown filter. [FEATURE] Spark Hive connector supports Parquet hive table pushdown filter kyuubi#7129
3. ORC Dynamic Partition Pruning. [SPARK-52969][SQL] Support DSv2 OrcScan Dynamic Partition Pruning spark#52009
4. Parquet Dynamic Partition Pruning. [SPARK-53439][SQL] Support DSv2 ParquetScan Dynamic Partition Pruning spark#52180
5. Fix kyuubi spark connector FileStatusCache connot cached. [KYUUBI #7192] Fix filestatus not cached kyuubi#7191

[flaming-archer]

At present, our optimization has achieved a reading data rate of approximately 66% for ORC and 80%+ for Parquet compared to native Spark.

We have made the following optimizations:

1. ORC Pusheddown filter. [FEATURE] Spark Hive connector supports ORC hive table pushdown filter kyuubi#7122
2. Parquet Pusheddown filter. [FEATURE] Spark Hive connector supports Parquet hive table pushdown filter kyuubi#7129
3. ORC Dynamic Partition Pruning. [SPARK-52969][SQL] Support DSv2 OrcScan Dynamic Partition Pruning spark#52009
4. Parquet Dynamic Partition Pruning. [SPARK-53439][SQL] Support DSv2 ParquetScan Dynamic Partition Pruning spark#52180
5. Fix kyuubi spark connector FileStatusCache connot cached. [KYUUBI #7192] Fix filestatus not cached kyuubi#7191

After 5 merged, #9110 is better to merged.

[jerqi]

At present, our optimization has achieved a reading data rate of approximately 66% for ORC and 80%+ for Parquet compared to native Spark.
We have made the following optimizations:

1. ORC Pusheddown filter. [FEATURE] Spark Hive connector supports ORC hive table pushdown filter kyuubi#7122
2. Parquet Pusheddown filter. [FEATURE] Spark Hive connector supports Parquet hive table pushdown filter kyuubi#7129
3. ORC Dynamic Partition Pruning. [SPARK-52969][SQL] Support DSv2 OrcScan Dynamic Partition Pruning spark#52009
4. Parquet Dynamic Partition Pruning. [SPARK-53439][SQL] Support DSv2 ParquetScan Dynamic Partition Pruning spark#52180
5. Fix kyuubi spark connector FileStatusCache connot cached. [KYUUBI #7192] Fix filestatus not cached kyuubi#7191

After 5 merged, #9110 is better to merged.

OK, I can help review this PR. But we need necessary unit tests, too.