iceberg scan with deletes slow anti hash join

[eshishki]

before compactions
we scan 51 mil without delete files, 73 mil with delete files

mysql> explain analyze select count(*) from landing_cbs_tj.credit_schedule_payments;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Explain String                                                                                                                                              |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Summary                                                                                                                                             |
|     QueryId: 74ca3838-eae3-11ef-bfaa-02f604b9c98d                                                                                                   |
|     Version: my34_0211-b0eaefb                                                                                                                      |
|     State: Finished                                                                                                                                 |
|     TotalTime: 3s574ms                                                                                                                              |
|         ExecutionTime: 3s43ms [Scan: 1s445ms (47.51%), Network: 1s204ms (39.59%), ResultDeliverTime: 0ns (0.00%), ScheduleTime: 390.585ms (12.83%)] |
|         CollectProfileTime: 3ms                                                                                                                     |
|         FrontendProfileMergeTime: 6.310ms                                                                                                           |
|     QueryPeakMemoryUsage: ?, QueryAllocatedMemoryUsage: 13.612 GB                                                                                   |
|     Top Most Time-consuming Nodes:                                                                                                                  |
|         1. ICEBERG_SCAN (id=3) : 1s305ms (30.96%)                                                                                              |
|         2. HASH_JOIN (id=6) [BROADCAST, LEFT ANTI JOIN]: 1s112ms (26.36%)                                                        |
|         3. EXCHANGE (id=8) : 722.392ms (17.13%)                                                                                  |
|         4. EXCHANGE (id=10) [GATHER]: 489.550ms (11.61%)                                                                                            |
|         5. ICEBERG_SCAN (id=1) : 236.987ms (5.62%)                                                                                                  |
|         6. EXCHANGE (id=2) : 193.704ms (4.59%)                                                                                                      |
|         7. AGGREGATION (id=11) [finalize, update]: 60.865ms (1.44%)                                                                                 |
|         8. ICEBERG_SCAN (id=4) : 42.738ms (1.01%)                                                                                                   |
|         9. PROJECT (id=9) : 22.901ms (0.54%)                                                                                                        |
|         10. UNION (id=0) : 22.517ms (0.53%)                                                                                                         |
|     Top Most Memory-consuming Nodes:                                                                                                                |
|     NonDefaultVariables:                                                                                                                            |
|         catalog: default_catalog -> iceberg                                                                                                         |
|         cboPushDownAggregateMode_v1: 0 -> -1                                                                                                        |
|         cbo_eq_base_type: decimal -> varchar                                                                                                        |
|         cbo_prune_json_subfield: true -> false                                                                                                      |
|         connector_huge_file_size: 536870912 -> 1073741824                                                                                           |
|         consistent_hash_virtual_number: 256 -> 128                                                                                                  |
|         enable_async_profile: true -> false                                                                                                         |
|         enable_connector_incremental_scan_ranges: true -> false                                                                                     |
|         enable_profile: false -> true                                                                                                               |
|         enable_query_cache: false -> true                                                                                                           |
|         enable_query_trigger_analyze: true -> false                                                                                                 |
|         enable_sort_aggregate: false -> true                                                                                                        |
|         enable_spill: false -> true                                                                                                                 |
|         enable_stats_to_optimize_skew_join: false -> true                                                                                           |
|         enable_view_based_mv_rewrite: true -> false                                                                                                 |
|         full_sort_max_buffered_bytes: 268435456 -> 16777216                                                                                         |
|         full_sort_max_buffered_rows: 1073741824 -> 1024000                                                                                          |
|         profile_timeout: 10 -> 2                                                                                                                    |
|         sql_mode_v2: 32 -> 290                                                                                                                      |
|         transmission_compression_type: AUTO -> NO_COMPRESSION                                                                                       |
| Fragment 0                                                                                                                                          |
| │   BackendNum: 1                                                                                                                                   |
| │   InstancePeakMemoryUsage: 5.315 MB, InstanceAllocatedMemoryUsage: 265.955 MB                                                                     |
| │   PrepareTime: ?                                                                                                                                  |
| └──RESULT_SINK                                                                                                                                      |
|    │   TotalTime: 75.480us (0.00%) [CPUTime: 75.480us]                                                                                              |
|    │   OutputRows: 1                                                                                                                                |
|    │   SinkType: MYSQL_PROTOCAL                                                                                                                     |
|    └──AGGREGATION (id=11) [finalize, update]                                                                                                        |
|       │   Estimates: [row: 1, cpu: 4.00, memory: 8.00, network: 0.00, cost: 203.00]                                                                 |
|       │   TotalTime: 60.865ms (1.44%) [CPUTime: 60.865ms]                                                                                           |
|       │   OutputRows: 1                                                                                                                             |
|       │   PeakMemory: ?, AllocatedMemory: ?                                                                                                         |
|       │   AggExprs: [count(*)]                                                                                                                      |
|       │   SubordinateOperators:                                                                                                                     |
|       │       LOCAL_EXCHANGE [Passthrough]                                                                                                          |
|       └──EXCHANGE (id=10) [GATHER]                                                                                                                  |
|              Estimates: [row: 2, cpu: 2.00, memory: 0.00, network: 2.00, cost: 185.00]                                                              |
|              TotalTime: 489.550ms (11.61%) [CPUTime: 63.569ms, NetworkTime: 425.981ms]                                                              |
|              OutputRows: 124.396M (124396112)                                                                                                       |
|              PeakMemory: ?, AllocatedMemory: ?                                                                                                      |
|                                                                                                                                                         |
| Fragment 1                                                                                                                                          |
| │   BackendNum: 1                                                                                                                                   |
| │   InstancePeakMemoryUsage: 8.385 MB, InstanceAllocatedMemoryUsage: 676.336 MB                                                                     |
| │   PrepareTime: ?                                                                                                                                  |
| └──DATA_STREAM_SINK (id=10)                                                                                                                         |
|    │   PartitionType: UNPARTITIONED                                                                                                                 |
|    └──PROJECT (id=9)                                                                                                                                |
|       │   Estimates: [row: ?, cpu: ?, memory: ?, network: ?, cost: ?]                                                                               |
|       │   TotalTime: 22.901ms (0.54%) [CPUTime: 22.901ms]                                                                                           |
|       │   OutputRows: 124.396M (124396112)                                                                                                          |
|       │   Expression: [1]                                                                                                                           |
|       └──UNION (id=0)                                                                                                                               |
|          │   Estimates: [row: 2, cpu: 0.00, memory: 0.00, network: 0.00, cost: 181.00]                                                              |
|          │   TotalTime: 22.517ms (0.53%) [CPUTime: 22.517ms]                                                                                        |
|          │   OutputRows: 124.396M (124396112)                                                                                                       |
|          │   SubordinateOperators:                                                                                                                  |
|          │       LOCAL_EXCHANGE [Passthrough]                                                                                                       |
|          ├──EXCHANGE (id=2)                                                                                                                         |
|          │      Estimates: [row: ?, cpu: ?, memory: ?, network: ?, cost: ?]                                                                         |
|          │      TotalTime: 193.704ms (4.59%) [CPUTime: 34.729ms, NetworkTime: 158.975ms]                                                            |
|          │      OutputRows: 51.071M (51070932)                                                                                                      |
|          │      PeakMemory: ?, AllocatedMemory: ?                                                                                                   |
|          └──EXCHANGE (id=8)                                                                                                      |
|                 Estimates: [row: ?, cpu: ?, memory: ?, network: ?, cost: ?]                                                      |
|                 TotalTime: 722.392ms (17.13%) [CPUTime: 105.215ms, NetworkTime: 617.176ms]                                       |
|                 OutputRows: 73.325M (73325180)                                                                                   |
|                 PeakMemory: ?, AllocatedMemory: ?                                                                                |
|                 Detail Timers:                                                                                                   |
|                     OverallTime: 2s470ms                                                                                         |
|                                                                                                                                                         |
| Fragment 2                                                                                                                                          |
| │   BackendNum: 1                                                                                                                                   |
| │   InstancePeakMemoryUsage: 1.724 GB, InstanceAllocatedMemoryUsage: 12.027 GB                                                                      |
| │   PrepareTime: ?                                                                                                                                  |
| └──DATA_STREAM_SINK (id=8)                                                                                                                          |
|    │   PartitionType: RANDOM                                                                                                                        |
|    └──PROJECT (id=7)                                                                                                                                |
|       │   Estimates: [row: ?, cpu: ?, memory: ?, network: ?, cost: ?]                                                                               |
|       │   TotalTime: 5.642ms (0.13%) [CPUTime: 5.642ms]                                                                                             |
|       │   OutputRows: 73.325M (73325180)                                                                                                            |
|       │   Expression: [58: is_correction]                                                                                                           |
|       └──HASH_JOIN (id=6) [BROADCAST, LEFT ANTI JOIN]                                                                            |
|          │   Estimates: [row: 1, cpu: ?, memory: ?, network: ?, cost: 181.0]                                                     |
|          │   TotalTime: 1s112ms (26.36%) [CPUTime: 1s112ms]                                                                      |
|          │   OutputRows: 73.325M (73325180)                                                                                      |
|          │   PeakMemory: ?, AllocatedMemory: ?                                                                                   |
|          │   BuildTime: 108.852us                                                                                                |
|          │   ProbeTime: 1s107ms                                                                                                  |
|          │   EqJoinConjuncts: [35: id = 67: id]                                                                                  |
|          │   SubordinateOperators:                                                                                               |
|          │       CHUNK_ACCUMULATE                                                                                                |
|          │       LOCAL_EXCHANGE [Passthrough]                                                                                    |
|          │       NOOP                                                                                                            |
|          │       SPILL_PROCESS                                                                                                   |
|          │   Detail Timers:                                                                                                      |
|          │       SearchHashTableTime: 607.722ms                                                                                  |
|          ├──<PROBE> ICEBERG_SCAN (id=3)                                                                                                        |
|          │      Estimates: [row: 1, cpu: ?, memory: ?, network: ?, cost: 0.0]                                                                  |
|          │      TotalTime: 1s305ms (30.96%) [CPUTime: 116.503ms, ScanTime: 1s189ms]                                                            |
|          │      OutputRows: 73.326M (73326329)                                                                                                 |
|          │      TABLE: landing_cbs_tj.credit_schedule_payments_with_delete_file                                                                |
|          │      Detail Timers: [ScanTime = IOTaskExecTime + IOTaskWaitTime]                                                                    |
|          │          IOTaskExecTime: 1s151ms                                                                                                    |
|          │              ColumnReadTime: 1s26ms                                                                                                 |
|          │              InputStream:                                                                                                           |
|          │                  AppIOTime: 822.140ms                                                                                               |
|          │                  FSIOTime: 821.922ms                                                                                                |
|          │              Parquet:                                                                                                               |
|          │                  GroupChunkRead: 1s30ms                                                                                             |
|          │                  PageReadTime: 962.555ms                                                                                            |
|          │              SharedBuffered:                                                                                                        |
|          │                  SharedIOTime: 741.740ms                                                                                            |
|          │          IOTaskWaitTime: 37.431ms                                                                                                   |
|          └──<BUILD> EXCHANGE (id=5) [BROADCAST]                                                                                                     |
|                 Estimates: [row: 1, cpu: ?, memory: ?, network: ?, cost: 84.0]                                                                      |
|                 TotalTime: 2.678ms (0.06%) [CPUTime: 89.715us, NetworkTime: 2.589ms]                                                                |
|                 OutputRows: 1.895K (1895)                                                                                                           |
|                 PeakMemory: ?, AllocatedMemory: ?                                                                                                   |
|                                                                                                                                                         |
| Fragment 3                                                                                                                                          |
| │   BackendNum: 1                                                                                                                                   |
| │   InstancePeakMemoryUsage: 10.294 MB, InstanceAllocatedMemoryUsage: 12.315 MB                                                                     |
| │   PrepareTime: ?                                                                                                                                  |
| └──DATA_STREAM_SINK (id=5)                                                                                                                          |
|    │   PartitionType: UNPARTITIONED                                                                                                                 |
|    └──ICEBERG_SCAN (id=4)                                                                                                                           |
|           Estimates: [row: 1, cpu: ?, memory: ?, network: ?, cost: 0.0]                                                                             |
|           TotalTime: 42.738ms (1.01%) [CPUTime: 1.686ms, ScanTime: 41.052ms]                                                                        |
|           OutputRows: 1.895K (1895)                                                                                                                 |
|           TABLE: landing_cbs_tj.credit_schedule_payments_eq_delete_id                                                                               |
|                                                                                                                                                         |
| Fragment 4                                                                                                                                          |
| │   BackendNum: 1                                                                                                                                   |
| │   InstancePeakMemoryUsage: 112.363 MB, InstanceAllocatedMemoryUsage: 668.934 MB                                                                   |
| │   PrepareTime: ?                                                                                                                                  |
| └──DATA_STREAM_SINK (id=2)                                                                                                                          |
|    │   PartitionType: RANDOM                                                                                                                        |
|    └──ICEBERG_SCAN (id=1)                                                                                                                           |
|           Estimates: [row: 1, cpu: ?, memory: ?, network: ?, cost: 0.0]                                                                             |
|           TotalTime: 236.987ms (5.62%) [CPUTime: 21.457ms, ScanTime: 215.530ms]                                                                     |
|           OutputRows: 51.071M (51070932)                                                                                                            |
|           TABLE: landing_cbs_tj.credit_schedule_payments                                                                                            |
|                                                                                                                                                         |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
167 rows in set (3.66 sec)

after compaction
we scan 124 mil with delete files
it is slower than before compaction

mysql> explain analyze select count(*) from landing_cbs_tj.credit_schedule_payments;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Explain String                                                                                                                                              |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Summary                                                                                                                                             |
|     QueryId: c7f80cf9-eae4-11ef-bfaa-02f604b9c98d                                                                                                   |
|     Version: my34_0211-b0eaefb                                                                                                                      |
|     State: Finished                                                                                                                                 |
|     TotalTime: 5s581ms                                                                                                                              |
|         ExecutionTime: 5s537ms [Scan: 1s77ms (19.47%), Network: 2s903ms (52.44%), ResultDeliverTime: 0ns (0.00%), ScheduleTime: 679.696ms (12.27%)] |
|         CollectProfileTime: 29ms                                                                                                                    |
|         FrontendProfileMergeTime: 6.551ms                                                                                                           |
|     QueryPeakMemoryUsage: ?, QueryAllocatedMemoryUsage: 22.044 GB                                                                                   |
|     Top Most Time-consuming Nodes:                                                                                                                  |
|         1. HASH_JOIN (id=6) [BROADCAST, LEFT ANTI JOIN]: 2s911ms (39.38%)                                                                      |
|         2. EXCHANGE (id=8) : 2s194ms (29.69%)                                                                                    |
|         3. ICEBERG_SCAN (id=3) : 1s164ms (15.75%)                                                                                |
|         4. EXCHANGE (id=10) [GATHER]: 985.030ms (13.32%)                                                                                            |
|         5. AGGREGATION (id=11) [finalize, update]: 65.369ms (0.88%)                                                                                 |
|         6. PROJECT (id=9) : 23.031ms (0.31%)                                                                                                        |
|         7. PROJECT (id=7) : 16.886ms (0.23%)                                                                                                        |
|         8. UNION (id=0) : 16.563ms (0.22%)                                                                                                          |
|         9. RESULT_SINK: 4.040ms (0.05%)                                                                                                             |
|         10. EXCHANGE (id=2) : 3.941ms (0.05%)                                                                                                       |
|     Top Most Memory-consuming Nodes:                                                                                                                |
|     NonDefaultVariables:                                                                                                                            |
|         catalog: default_catalog -> iceberg                                                                                                         |
|         cboPushDownAggregateMode_v1: 0 -> -1                                                                                                        |
|         cbo_eq_base_type: decimal -> varchar                                                                                                        |
|         cbo_prune_json_subfield: true -> false                                                                                                      |
|         connector_huge_file_size: 536870912 -> 1073741824                                                                                           |
|         consistent_hash_virtual_number: 256 -> 128                                                                                                  |
|         enable_async_profile: true -> false                                                                                                         |
|         enable_connector_incremental_scan_ranges: true -> false                                                                                     |
|         enable_profile: false -> true                                                                                                               |
|         enable_query_cache: false -> true                                                                                                           |
|         enable_query_trigger_analyze: true -> false                                                                                                 |
|         enable_sort_aggregate: false -> true                                                                                                        |
|         enable_spill: false -> true                                                                                                                 |
|         enable_stats_to_optimize_skew_join: false -> true                                                                                           |
|         enable_view_based_mv_rewrite: true -> false                                                                                                 |
|         full_sort_max_buffered_bytes: 268435456 -> 16777216                                                                                         |
|         full_sort_max_buffered_rows: 1073741824 -> 1024000                                                                                          |
|         profile_timeout: 10 -> 2                                                                                                                    |
|         sql_mode_v2: 32 -> 290                                                                                                                      |
|         transmission_compression_type: AUTO -> NO_COMPRESSION                                                                                       |
| Fragment 0                                                                                                                                          |
| │   BackendNum: 1                                                                                                                                   |
| │   InstancePeakMemoryUsage: 1.986 MB, InstanceAllocatedMemoryUsage: 265.923 MB                                                                     |
| │   PrepareTime: ?                                                                                                                                  |
| └──RESULT_SINK                                                                                                                                      |
|    │   TotalTime: 4.040ms (0.05%) [CPUTime: 4.040ms]                                                                                                |
|    │   OutputRows: 1                                                                                                                                |
|    │   SinkType: MYSQL_PROTOCAL                                                                                                                     |
|    └──AGGREGATION (id=11) [finalize, update]                                                                                                        |
|       │   Estimates: [row: 1, cpu: 4.00, memory: 8.00, network: 0.00, cost: 203.00]                                                                 |
|       │   TotalTime: 65.369ms (0.88%) [CPUTime: 65.369ms]                                                                                           |
|       │   OutputRows: 1                                                                                                                             |
|       │   PeakMemory: ?, AllocatedMemory: ?                                                                                                         |
|       │   AggExprs: [count(*)]                                                                                                                      |
|       │   SubordinateOperators:                                                                                                                     |
|       │       LOCAL_EXCHANGE [Passthrough]                                                                                                          |
|       └──EXCHANGE (id=10) [GATHER]                                                                                                                  |
|              Estimates: [row: 2, cpu: 2.00, memory: 0.00, network: 2.00, cost: 185.00]                                                              |
|              TotalTime: 985.030ms (13.32%) [CPUTime: 74.109ms, NetworkTime: 910.920ms]                                                              |
|              OutputRows: 124.397M (124396636)                                                                                                       |
|              PeakMemory: ?, AllocatedMemory: ?                                                                                                      |
|                                                                                                                                                         |
| Fragment 1                                                                                                                                          |
| │   BackendNum: 1                                                                                                                                   |
| │   InstancePeakMemoryUsage: 2.722 MB, InstanceAllocatedMemoryUsage: 676.229 MB                                                                     |
| │   PrepareTime: ?                                                                                                                                  |
| └──DATA_STREAM_SINK (id=10)                                                                                                                         |
|    │   PartitionType: UNPARTITIONED                                                                                                                 |
|    └──PROJECT (id=9)                                                                                                                                |
|       │   Estimates: [row: ?, cpu: ?, memory: ?, network: ?, cost: ?]                                                                               |
|       │   TotalTime: 23.031ms (0.31%) [CPUTime: 23.031ms]                                                                                           |
|       │   OutputRows: 124.397M (124396636)                                                                                                          |
|       │   Expression: [1]                                                                                                                           |
|       └──UNION (id=0)                                                                                                                               |
|          │   Estimates: [row: 2, cpu: 0.00, memory: 0.00, network: 0.00, cost: 181.00]                                                              |
|          │   TotalTime: 16.563ms (0.22%) [CPUTime: 16.563ms]                                                                                        |
|          │   OutputRows: 124.397M (124396636)                                                                                                       |
|          │   SubordinateOperators:                                                                                                                  |
|          │       LOCAL_EXCHANGE [Passthrough]                                                                                                       |
|          ├──EXCHANGE (id=2)                                                                                                                         |
|          │      Estimates: [row: ?, cpu: ?, memory: ?, network: ?, cost: ?]                                                                         |
|          │      TotalTime: 3.941ms (0.05%) [CPUTime: 119.007us, NetworkTime: 3.822ms]                                                               |
|          │      OutputRows: 1.609K (1609)                                                                                                           |
|          │      PeakMemory: ?, AllocatedMemory: ?                                                                                                   |
|          └──EXCHANGE (id=8)                                                                                                      |
|                 Estimates: [row: ?, cpu: ?, memory: ?, network: ?, cost: ?]                                                      |
|                 TotalTime: 2s194ms (29.69%) [CPUTime: 206.437ms, NetworkTime: 1s988ms]                                           |
|                 OutputRows: 124.395M (124395027)                                                                                 |
|                 PeakMemory: ?, AllocatedMemory: ?                                                                                |
|                 Detail Timers:                                                                                                   |
|                     OverallTime: 5s458ms                                                                                         |
|                                                                                                                                                         |
| Fragment 2                                                                                                                                          |
| │   BackendNum: 1                                                                                                                                   |
| │   InstancePeakMemoryUsage: 963.213 MB, InstanceAllocatedMemoryUsage: 21.097 GB                                                                    |
| │   PrepareTime: ?                                                                                                                                  |
| └──DATA_STREAM_SINK (id=8)                                                                                                                          |
|    │   PartitionType: RANDOM                                                                                                                        |
|    └──PROJECT (id=7)                                                                                                                                |
|       │   Estimates: [row: ?, cpu: ?, memory: ?, network: ?, cost: ?]                                                                               |
|       │   TotalTime: 16.886ms (0.23%) [CPUTime: 16.886ms]                                                                                           |
|       │   OutputRows: 124.395M (124395027)                                                                                                          |
|       │   Expression: [58: is_correction]                                                                                                           |
|       └──HASH_JOIN (id=6) [BROADCAST, LEFT ANTI JOIN]                                                                                          |
|          │   Estimates: [row: 1, cpu: ?, memory: ?, network: ?, cost: 181.0]                                                                   |
|          │   TotalTime: 2s911ms (39.38%) [CPUTime: 2s911ms]                                                                                    |
|          │   OutputRows: 124.395M (124395027)                                                                                                  |
|          │   PeakMemory: ?, AllocatedMemory: ?                                                                                                 |
|          │   BuildTime: 152.444us                                                                                                              |
|          │   ProbeTime: 2s902ms                                                                                                                |
|          │   EqJoinConjuncts: [35: id = 67: id]                                                                                                |
|          │   SubordinateOperators:                                                                                                             |
|          │       CHUNK_ACCUMULATE                                                                                                              |
|          │       LOCAL_EXCHANGE [Passthrough]                                                                                                  |
|          │       NOOP                                                                                                                          |
|          │       SPILL_PROCESS                                                                                                                 |
|          │   Detail Timers:                                                                                                                    |
|          │       SearchHashTableTime: 1s730ms                                                                                                  |
|          ├──<PROBE> ICEBERG_SCAN (id=3)                                                                                          |
|          │      Estimates: [row: 1, cpu: ?, memory: ?, network: ?, cost: 0.0]                                                    |
|          │      TotalTime: 1s164ms (15.75%) [CPUTime: 92.018ms, ScanTime: 1s72ms]                                                |
|          │      OutputRows: 124.397M (124397261)                                                                                 |
|          │      TABLE: landing_cbs_tj.credit_schedule_payments_with_delete_file                                                  |
|          │      Detail Timers: [ScanTime = IOTaskExecTime + IOTaskWaitTime]                                                      |
|          │          IOTaskExecTime: 1s59ms                                                                                       |
|          │              ColumnReadTime: 1s38ms                                                                                   |
|          │              DataCache:                                                                                               |
|          │                  DataCacheReadTimer: 421.507ms                                                                        |
|          │              InputStream:                                                                                             |
|          │                  AppIOTime: 716.528ms                                                                                 |
|          │              Parquet:                                                                                                 |
|          │                  GroupChunkRead: 1s43ms                                                                               |
|          │                  PageReadTime: 953.953ms                                                                              |
|          │          IOTaskWaitTime: 12.682ms                                                                                     |
|          └──<BUILD> EXCHANGE (id=5) [BROADCAST]                                                                                                     |
|                 Estimates: [row: 1, cpu: ?, memory: ?, network: ?, cost: 84.0]                                                                      |
|                 TotalTime: 613.995us (0.01%) [CPUTime: 144.314us, NetworkTime: 469.681us]                                                           |
|                 OutputRows: 2.980K (2980)                                                                                                           |
|                 PeakMemory: ?, AllocatedMemory: ?                                                                                                   |
|                                                                                                                                                         |
| Fragment 3                                                                                                                                          |
| │   BackendNum: 1                                                                                                                                   |
| │   InstancePeakMemoryUsage: 15.396 MB, InstanceAllocatedMemoryUsage: 16.903 MB                                                                     |
| │   PrepareTime: ?                                                                                                                                  |
| └──DATA_STREAM_SINK (id=5)                                                                                                                          |
|    │   PartitionType: UNPARTITIONED                                                                                                                 |
|    └──ICEBERG_SCAN (id=4)                                                                                                                           |
|           Estimates: [row: 1, cpu: ?, memory: ?, network: ?, cost: 0.0]                                                                             |
|           TotalTime: 2.977ms (0.04%) [CPUTime: 374.685us, ScanTime: 2.602ms]                                                                        |
|           OutputRows: 2.980K (2980)                                                                                                                 |
|           TABLE: landing_cbs_tj.credit_schedule_payments_eq_delete_id                                                                               |
|                                                                                                                                                         |
| Fragment 4                                                                                                                                          |
| │   BackendNum: 1                                                                                                                                   |
| │   InstancePeakMemoryUsage: 5.146 MB, InstanceAllocatedMemoryUsage: 10.609 MB                                                                      |
| │   PrepareTime: ?                                                                                                                                  |
| └──DATA_STREAM_SINK (id=2)                                                                                                                          |
|    │   PartitionType: RANDOM                                                                                                                        |
|    └──ICEBERG_SCAN (id=1)                                                                                                                           |
|           Estimates: [row: 1, cpu: ?, memory: ?, network: ?, cost: 0.0]                                                                             |
|           TotalTime: 3.671ms (0.05%) [CPUTime: 1.087ms, ScanTime: 2.583ms]                                                                          |
|           OutputRows: 1.609K (1609)                                                                                                                 |
|           TABLE: landing_cbs_tj.credit_schedule_payments                                                                                            |
|                                                                                                                                                         |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
166 rows in set (5.76 sec)

please note that there are only a handful of delete row records now

mysql> select * from landing_cbs_tj.credit_schedule_payments$partitions;
+--------------+------------+-------------------------------+------------------------------+----------------------------+------------------------------+----------------------------+----------------------------+
| record_count | file_count | total_data_file_size_in_bytes | position_delete_record_count | position_delete_file_count | equality_delete_record_count | equality_delete_file_count | last_updated_at            |
+--------------+------------+-------------------------------+------------------------------+----------------------------+------------------------------+----------------------------+----------------------------+
|    124398890 |         24 |                    9530256107 |                          154 |                          4 |                         3817 |                          4 | 2025-02-14 14:54:03.847000 |
+--------------+------------+-------------------------------+------------------------------+----------------------------+------------------------------+----------------------------+----------------------------+
1 row in set (20.22 sec)

@stephen-shelby

[eshishki]

DEBUG BaseDistributedDataScan: Total Scan Metrics:
Total Data Records: 124415520
Records with no deletes: 58
Records with only equality deletes: 124
Records with only position deletes: 0
Records with both delete types: 124415338
Unique Delete Files:
Equality delete files: 21, total records: 2665
Position delete files: 13, total records: 189

3 seconds scan because we pass all data files through hashjoin
maybe we should apply some parquet header filter to only match files that could contain the keys from eq deletes?

mysql> explain analyze select count(*) from landing_cbs_tj.credit_schedule_payments ;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Explain String                                                                                                                                                 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Summary                                                                                                                                                |
|     QueryId: 2ef8b76b-eb1f-11ef-bfaa-02f604b9c98d                                                                                                      |
|     Version: my34_0211-b0eaefb                                                                                                                         |
|     State: Finished                                                                                                                                    |
|     TotalTime: 3s182ms                                                                                                                                 |
|         ExecutionTime: 3s145ms [Scan: 419.237ms (13.33%), Network: 1s897ms (60.34%), ResultDeliverTime: 0ns (0.00%), ScheduleTime: 541.259ms (17.21%)] |
|         CollectProfileTime: 6ms                                                                                                                        |
|         FrontendProfileMergeTime: 5.862ms                                                                                                              |
|     QueryPeakMemoryUsage: ?, QueryAllocatedMemoryUsage: 25.903 GB                                                                                      |
|     Top Most Time-consuming Nodes:                                                                                                                     |
|         1. HASH_JOIN (id=6) [BROADCAST, LEFT ANTI JOIN]: 2s248ms (44.52%)                                                                         |
|         2. EXCHANGE (id=8) : 1s575ms (31.20%)                                                                                                     |
|         3. EXCHANGE (id=10) [GATHER]: 598.163ms (11.85%)                                                                                               |
|         4. ICEBERG_SCAN (id=3) : 530.698ms (10.51%)                                                                                                    |
|         5. AGGREGATION (id=11) [finalize, update]: 42.023ms (0.83%)                                                                                    |
|         6. PROJECT (id=9) : 25.405ms (0.50%)                                                                                                           |
|         7. UNION (id=0) : 17.444ms (0.35%)                                                                                                             |
|         8. PROJECT (id=7) : 8.897ms (0.18%)                                                                                                            |
|         9. ICEBERG_SCAN (id=4) : 1.721ms (0.03%)                                                                                                       |
|         10. EXCHANGE (id=5) [BROADCAST]: 712.253us (0.01%)                                                                                             |
|     Top Most Memory-consuming Nodes:                                                                                                                   |
|     NonDefaultVariables:                                                                                                                               |
|         catalog: default_catalog -> iceberg                                                                                                            |
|         cboPushDownAggregateMode_v1: 0 -> -1                                                                                                           |
|         cbo_eq_base_type: decimal -> varchar                                                                                                           |
|         cbo_prune_json_subfield: true -> false                                                                                                         |
|         connector_huge_file_size: 536870912 -> 1073741824                                                                                              |
|         consistent_hash_virtual_number: 256 -> 128                                                                                                     |
|         enable_async_profile: true -> false                                                                                                            |
|         enable_connector_incremental_scan_ranges: true -> false                                                                                        |
|         enable_profile: false -> true                                                                                                                  |
|         enable_query_cache: false -> true                                                                                                              |
|         enable_query_trigger_analyze: true -> false                                                                                                    |
|         enable_sort_aggregate: false -> true                                                                                                           |
|         enable_spill: false -> true                                                                                                                    |
|         enable_stats_to_optimize_skew_join: false -> true                                                                                              |
|         enable_view_based_mv_rewrite: true -> false                                                                                                    |
|         full_sort_max_buffered_bytes: 268435456 -> 16777216                                                                                            |
|         full_sort_max_buffered_rows: 1073741824 -> 1024000                                                                                             |
|         profile_timeout: 10 -> 2                                                                                                                       |
|         sql_mode_v2: 32 -> 290                                                                                                                         |
|         transmission_compression_type: AUTO -> NO_COMPRESSION                                                                                          |
| Fragment 0                                                                                                                                             |
| │   BackendNum: 1                                                                                                                                      |
| │   InstancePeakMemoryUsage: 1.662 MB, InstanceAllocatedMemoryUsage: 265.951 MB                                                                        |
| │   PrepareTime: ?                                                                                                                                     |
| └──RESULT_SINK                                                                                                                                         |
|    │   TotalTime: 71.381us (0.00%) [CPUTime: 71.381us]                                                                                                 |
|    │   OutputRows: 1                                                                                                                                   |
|    │   SinkType: MYSQL_PROTOCAL                                                                                                                        |
|    └──AGGREGATION (id=11) [finalize, update]                                                                                                           |
|       │   Estimates: [row: 1, cpu: 4.00, memory: 8.00, network: 0.00, cost: 203.00]                                                                    |
|       │   TotalTime: 42.023ms (0.83%) [CPUTime: 42.023ms]                                                                                              |
|       │   OutputRows: 1                                                                                                                                |
|       │   PeakMemory: ?, AllocatedMemory: ?                                                                                                            |
|       │   AggExprs: [count(*)]                                                                                                                         |
|       │   SubordinateOperators:                                                                                                                        |
|       │       LOCAL_EXCHANGE [Passthrough]                                                                                                             |
|       └──EXCHANGE (id=10) [GATHER]                                                                                                                     |
|              Estimates: [row: 2, cpu: 2.00, memory: 0.00, network: 2.00, cost: 185.00]                                                                 |
|              TotalTime: 598.163ms (11.85%) [CPUTime: 51.855ms, NetworkTime: 546.307ms]                                                                 |
|              OutputRows: 124.413M (124412990)                                                                                                          |
|              PeakMemory: ?, AllocatedMemory: ?                                                                                                         |
|                                                                                                                                                            |
| Fragment 1                                                                                                                                             |
| │   BackendNum: 1                                                                                                                                      |
| │   InstancePeakMemoryUsage: 3.404 MB, InstanceAllocatedMemoryUsage: 676.299 MB                                                                        |
| │   PrepareTime: ?                                                                                                                                     |
| └──DATA_STREAM_SINK (id=10)                                                                                                                            |
|    │   PartitionType: UNPARTITIONED                                                                                                                    |
|    └──PROJECT (id=9)                                                                                                                                   |
|       │   Estimates: [row: ?, cpu: ?, memory: ?, network: ?, cost: ?]                                                                                  |
|       │   TotalTime: 25.405ms (0.50%) [CPUTime: 25.405ms]                                                                                              |
|       │   OutputRows: 124.413M (124412990)                                                                                                             |
|       │   Expression: [1]                                                                                                                              |
|       └──UNION (id=0)                                                                                                                                  |
|          │   Estimates: [row: 2, cpu: 0.00, memory: 0.00, network: 0.00, cost: 181.00]                                                                 |
|          │   TotalTime: 17.444ms (0.35%) [CPUTime: 17.444ms]                                                                                           |
|          │   OutputRows: 124.413M (124412990)                                                                                                          |
|          │   SubordinateOperators:                                                                                                                     |
|          │       LOCAL_EXCHANGE [Passthrough]                                                                                                          |
|          ├──EXCHANGE (id=2)                                                                                                                            |
|          │      Estimates: [row: ?, cpu: ?, memory: ?, network: ?, cost: ?]                                                                            |
|          │      TotalTime: 333.003us (0.01%) [CPUTime: 69.668us, NetworkTime: 263.335us]                                                               |
|          │      OutputRows: 58                                                                                                                         |
|          │      PeakMemory: ?, AllocatedMemory: ?                                                                                                      |
|          └──EXCHANGE (id=8)                                                                                                                       |
|                 Estimates: [row: ?, cpu: ?, memory: ?, network: ?, cost: ?]                                                                       |
|                 TotalTime: 1s575ms (31.20%) [CPUTime: 224.868ms, NetworkTime: 1s350ms]                                                            |
|                 OutputRows: 124.413M (124412932)                                                                                                  |
|                 PeakMemory: ?, AllocatedMemory: ?                                                                                                 |
|                 Detail Timers:                                                                                                                    |
|                     OverallTime: 3s97ms                                                                                                           |
|                                                                                                                                                            |
| Fragment 2                                                                                                                                             |
| │   BackendNum: 1                                                                                                                                      |
| │   InstancePeakMemoryUsage: 605.984 MB, InstanceAllocatedMemoryUsage: 24.868 GB                                                                       |
| │   PrepareTime: ?                                                                                                                                     |
| └──DATA_STREAM_SINK (id=8)                                                                                                                             |
|    │   PartitionType: RANDOM                                                                                                                           |
|    └──PROJECT (id=7)                                                                                                                                   |
|       │   Estimates: [row: ?, cpu: ?, memory: ?, network: ?, cost: ?]                                                                                  |
|       │   TotalTime: 8.897ms (0.18%) [CPUTime: 8.897ms]                                                                                                |
|       │   OutputRows: 124.413M (124412932)                                                                                                             |
|       │   Expression: [58: is_correction]                                                                                                              |
|       └──HASH_JOIN (id=6) [BROADCAST, LEFT ANTI JOIN]                                                                                             |
|          │   Estimates: [row: 1, cpu: ?, memory: ?, network: ?, cost: 181.0]                                                                      |
|          │   TotalTime: 2s248ms (44.52%) [CPUTime: 2s248ms]                                                                                       |
|          │   OutputRows: 124.413M (124412932)                                                                                                     |
|          │   PeakMemory: ?, AllocatedMemory: ?                                                                                                    |
|          │   BuildTime: 175.562us                                                                                                                 |
|          │   ProbeTime: 2s240ms                                                                                                                   |
|          │   EqJoinConjuncts: [35: id = 67: id]                                                                                                   |
|          │   SubordinateOperators:                                                                                                                |
|          │       CHUNK_ACCUMULATE                                                                                                                 |
|          │       LOCAL_EXCHANGE [Passthrough]                                                                                                     |
|          │       NOOP                                                                                                                             |
|          │       SPILL_PROCESS                                                                                                                    |
|          │   Detail Timers:                                                                                                                       |
|          │       SearchHashTableTime: 1s331ms                                                                                                     |
|          ├──<PROBE> ICEBERG_SCAN (id=3)                                                                                                                |
|          │      Estimates: [row: 1, cpu: ?, memory: ?, network: ?, cost: 0.0]                                                                          |
|          │      TotalTime: 530.698ms (10.51%) [CPUTime: 112.234ms, ScanTime: 418.463ms]                                                                |
|          │      OutputRows: 124.415M (124415448)                                                                                                       |
|          │      TABLE: landing_cbs_tj.credit_schedule_payments_with_delete_file                                                                        |
|          └──<BUILD> EXCHANGE (id=5) [BROADCAST]                                                                                                        |
|                 Estimates: [row: 1, cpu: ?, memory: ?, network: ?, cost: 84.0]                                                                         |
|                 TotalTime: 712.253us (0.01%) [CPUTime: 116.951us, NetworkTime: 595.302us]                                                              |
|                 OutputRows: 2.665K (2665)                                                                                                              |
|                 PeakMemory: ?, AllocatedMemory: ?                                                                                                      |
|                                                                                                                                                            |
| Fragment 3                                                                                                                                             |
| │   BackendNum: 1                                                                                                                                      |
| │   InstancePeakMemoryUsage: 30.815 MB, InstanceAllocatedMemoryUsage: 112.814 MB                                                                       |
| │   PrepareTime: ?                                                                                                                                     |
| └──DATA_STREAM_SINK (id=5)                                                                                                                             |
|    │   PartitionType: UNPARTITIONED                                                                                                                    |
|    └──ICEBERG_SCAN (id=4)                                                                                                                              |
|           Estimates: [row: 1, cpu: ?, memory: ?, network: ?, cost: 0.0]                                                                                |
|           TotalTime: 1.721ms (0.03%) [CPUTime: 1.208ms, ScanTime: 512.945us]                                                                           |
|           OutputRows: 2.665K (2665)                                                                                                                    |
|           TABLE: landing_cbs_tj.credit_schedule_payments_eq_delete_id                                                                                  |
|                                                                                                                                                            |
| Fragment 4                                                                                                                                             |
| │   BackendNum: 1                                                                                                                                      |
| │   InstancePeakMemoryUsage: 5.135 MB, InstanceAllocatedMemoryUsage: 5.195 MB                                                                          |
| │   PrepareTime: ?                                                                                                                                     |
| └──DATA_STREAM_SINK (id=2)                                                                                                                             |
|    │   PartitionType: RANDOM                                                                                                                           |
|    └──ICEBERG_SCAN (id=1)                                                                                                                              |
|           Estimates: [row: 1, cpu: ?, memory: ?, network: ?, cost: 0.0]                                                                                |
|           TotalTime: 655.215us (0.01%) [CPUTime: 394.379us, ScanTime: 260.836us]                                                                       |
|           OutputRows: 58                                                                                                                               |
|           TABLE: landing_cbs_tj.credit_schedule_payments                                                                                               |
|                                                                                                                                                            |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
155 rows in set (3.27 sec)

[eshishki]

maybe we should apply some parquet header filter to only match files that could contain the keys from eq deletes?

looks like iceberg scan has to do it internally

[eshishki]

it is an issue of eq delete range covering multiple data files, not sure if SR could do it better wo some bloom filter stuff

[eshishki]

lets say this is a performance improvement request now

[eshishki]

why iceberg anti join hash probe is so slow

here is olap example
explain analyze select count(*) from transactions t left anti join small using (id)
300 ms for LEFT ANTI JOIN

| Fragment 1                                                                                                                                        |
| │   BackendNum: 1                                                                                                                                 |
| │   InstancePeakMemoryUsage: 18.707 MB, InstanceAllocatedMemoryUsage: 2.657 GB                                                                    |
| │   PrepareTime: ?                                                                                                                                |
| └──DATA_STREAM_SINK (id=6)                                                                                                                        |
|    │   PartitionType: UNPARTITIONED                                                                                                               |
|    └──AGGREGATION (id=5) [serialize, update]                                                                                                      |
|       │   Estimates: [row: 1, cpu: 245970490.97, memory: 0.80, network: 0.00, cost: 2510382469.94]                                                |
|       │   TotalTime: 4.874ms (1.40%) [CPUTime: 4.874ms]                                                                                           |
|       │   OutputRows: 4                                                                                                                           |
|       │   PeakMemory: ?, AllocatedMemory: ?                                                                                                       |
|       │   AggExprs: [count(*)]                                                                                                                    |
|       └──PROJECT (id=4)                                                                                                                           |
|          │   Estimates: [row: ?, cpu: ?, memory: ?, network: ?, cost: ?]                                                                          |
|          │   TotalTime: 12.573ms (3.61%) [CPUTime: 12.573ms]                                                                                      |
|          │   OutputRows: 144.689M (144688971)                                                                                                     |
|          │   Expression: [1]                                                                                                                      |
|          └──HASH_JOIN (id=3) [BUCKET_SHUFFLE, LEFT ANTI JOIN]                                                                                |
|             │   Estimates: [row: 144688524, cpu: 3617232677.70, memory: 8000.00, network: 0.00, cost: 2387397222.85]                         |
|             │   TotalTime: 279.105ms (80.08%) [CPUTime: 279.105ms]                                                                           |
|             │   OutputRows: 144.689M (144688971)                                                                                             |
|             │   PeakMemory: ?, AllocatedMemory: ?                                                                                            |
|             │   BuildTime: 182.763us                                                                                                         |
|             │   ProbeTime: 277.334ms                                                                                                         |
|             │   EqJoinConjuncts: [1: id = 52: id]                                                                                            |
|             │   SubordinateOperators:                                                                                                        |
|             │       CHUNK_ACCUMULATE                                                                                                         |
|             │       NOOP                                                                                                                     |
|             │       SPILL_PROCESS                                                                                                            |
|             │   Detail Timers:                                                                                                               |
|             │       SearchHashTableTime: 242.208ms                                                                                           |
|             ├──<PROBE> OLAP_SCAN (id=0)                                                                                                           |
|             │      Estimates: [row: 144689971, cpu: 1157519768.00, memory: 0.00, network: 0.00, cost: 578759884.00]                               |
|             │      TotalTime: 49.892ms (14.32%) [CPUTime: 14.011ms, ScanTime: 35.880ms]                                                           |
|             │      OutputRows: 144.690M (144689971)                                                                                               |
|             │      TABLE: transactions                                                                                                            |
|             └──<BUILD> EXCHANGE (id=2) [SHUFFLE]                                                                                                  |
|                    Estimates: [row: 1000, cpu: 8000.00, memory: 0.00, network: 0.00, cost: 8000.00]                                               |
|                    TotalTime: 341.362us (0.10%) [CPUTime: 158.039us, NetworkTime: 183.323us]                                                      |
|                    OutputRows: 1.000K (1000)                                                                                                      |
|                    PeakMemory: ?, AllocatedMemory: ?                                                                                              |
|                                                                                                                                                       |
| Fragment 2                                                                                                                                        |
| │   BackendNum: 1                                                                                                                                 |
| │   InstancePeakMemoryUsage: 103.828 KB, InstanceAllocatedMemoryUsage: 282.430 KB                                                                 |
| │   PrepareTime: ?                                                                                                                                |
| └──DATA_STREAM_SINK (id=2)                                                                                                                        |
|    │   PartitionType: BUCKET_SHUFFLE_HASH_PARTITIONED                                                                                             |
|    │   PartitionExprs: [52: id]                                                                                                                   |
|    └──OLAP_SCAN (id=1)                                                                                                                            |
|           Estimates: [row: 1000, cpu: 8000.00, memory: 0.00, network: 0.00, cost: 4000.00]                                                        |
|           TotalTime: 782.209us (0.22%) [CPUTime: 429.541us, ScanTime: 352.668us]                                                                  |
|           OutputRows: 1.000K (1000)                                                                                                               |
|           TABLE: small                                                                                                                            |

for iceberg plan it is seconds

|       └──HASH_JOIN (id=6) [BROADCAST, LEFT ANTI JOIN]                                                                                             |
|          │   Estimates: [row: 1, cpu: ?, memory: ?, network: ?, cost: 181.0]                                                                      |
|          │   TotalTime: 2s248ms (44.52%) [CPUTime: 2s248ms]                                                                                       |
|          │   OutputRows: 124.413M (124412932)                                                                                                     |
|          │   PeakMemory: ?, AllocatedMemory: ?                                                                                                    |
|          │   BuildTime: 175.562us                                                                                                                 |
|          │   ProbeTime: 2s240ms                                                                                                                   |
|          │   EqJoinConjuncts: [35: id = 67: id]                                                                                                   |
|          │   SubordinateOperators:                                                                                                                |
|          │       CHUNK_ACCUMULATE                                                                                                                 |
|          │       LOCAL_EXCHANGE [Passthrough]                                                                                                     |
|          │       NOOP                                                                                                                             |
|          │       SPILL_PROCESS                                                                                                                    |
|          │   Detail Timers:                                                                                                                       |
|          │       SearchHashTableTime: 1s331ms                                                                                                     |
|          ├──<PROBE> ICEBERG_SCAN (id=3)                                                                                                                |
|          │      Estimates: [row: 1, cpu: ?, memory: ?, network: ?, cost: 0.0]                                                                          |
|          │      TotalTime: 530.698ms (10.51%) [CPUTime: 112.234ms, ScanTime: 418.463ms]                                                                |
|          │      OutputRows: 124.415M (124415448)                                                                                                       |
|          │      TABLE: landing_cbs_tj.credit_schedule_payments_with_delete_file                                                                        |
|          └──<BUILD> EXCHANGE (id=5) [BROADCAST]                                                                                                        |
|                 Estimates: [row: 1, cpu: ?, memory: ?, network: ?, cost: 84.0]                                                                         |
|                 TotalTime: 712.253us (0.01%) [CPUTime: 116.951us, NetworkTime: 595.302us]                                                              |
|                 OutputRows: 2.665K (2665)                                                                                                              |
|                 PeakMemory: ?, AllocatedMemory: ?                                                                                                      |

attaching

q0.csv

query profile for iceberg

[github-actions]

We have marked this issue as stale because it has been inactive for 6 months. If this issue is still relevant, removing the stale label or adding a comment will keep it active. Otherwise, we'll close it in 10 days to keep the issue queue tidy. Thank you for your contribution to StarRocks!