search

pingcap / tidb

TiDB - the open-source, cloud-native, distributed SQL database designed for modern applications.
https://pingcap.com
Apache License 2.0
37.42k stars 5.85k forks source link

tpch q17 plan is not optimal #55330

Open guo-shaoge opened 3 months ago

guo-shaoge commented 3 months ago

Enhancement

TiDB plan: two hash_join, two hash_agg.

But actually only one hash_join and only one hash_agg is enough. (check starrocks plan)

TiDB root@10.2.12.57:test> explain analyze select
                        ->       sum(l_extendedprice) / 7.0 as avg_yearly
                        ->   from
                        ->       lineitem,
                        ->       part
                        ->   where
                        ->       p_partkey = l_partkey
                        ->       and p_brand = 'Brand#44'
                        ->       and p_container = 'WRAP PKG'
                        ->       and l_quantity < (
                        ->           select
                        ->               0.2 * avg(l_quantity)
                        ->           from
                        ->               lineitem
                        ->           where
                        ->               l_partkey = p_partkey
                        ->       );
                        ->

+----------------------------------------------------+--------------+-----------+--------------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+
| id                                                 | estRows      | actRows   | task         | access object  | execution info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       | operator info                                                                                                                                                                         | memory  | disk    |
+----------------------------------------------------+--------------+-----------+--------------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+
| Projection_17                                      | 1.00         | 1         | root         |                | time:8.18s, loops:2, RU:253867.208806, Concurrency:OFF                                                                                                                                                                                                                                                                                                                                                                                                                                                               | div(Column#44, 7.0)->Column#45                                                                                                                                                        | 1.40 KB | N/A     |
| └─HashAgg_121                                      | 1.00         | 1         | root         |                | time:8.18s, loops:2, partial_worker:{wall_time:8.183941506s, concurrency:5, task_num:1, tot_wait:40.91952013s, tot_exec:13.775µs, tot_time:40.919540154s, max:8.183909789s, p95:8.183909789s}, final_worker:{wall_time:8.183975219s, concurrency:5, task_num:5, tot_wait:4.924µs, tot_exec:157ns, tot_time:40.91969156s, max:8.183947868s, p95:8.183947868s}                                                                                                                                                         | funcs:sum(Column#69)->Column#44                                                                                                                                                       | 19.1 KB | 0 Bytes |
|   └─TableReader_123                                | 1.00         | 2         | root         |                | time:8.18s, loops:2, cop_task: {num: 4, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00}                                                                                                                                                                                                                                                                                                                                                                                                              | MppVersion: 2, data:ExchangeSender_122                                                                                                                                                | 2.70 KB | N/A     |
|     └─ExchangeSender_122                           | 1.00         | 2         | mpp[tiflash] |                | tiflash_task:{proc max:8.18s, min:8.14s, avg: 8.16s, p80:8.18s, p95:8.18s, iters:2, tasks:2, threads:2}                                                                                                                                                                                                                                                                                                                                                                                                              | ExchangeType: PassThrough                                                                                                                                                             | N/A     | N/A     |
|       └─HashAgg_22                                 | 1.00         | 2         | mpp[tiflash] |                | tiflash_task:{proc max:8.18s, min:8.14s, avg: 8.16s, p80:8.18s, p95:8.18s, iters:2, tasks:2, threads:2}                                                                                                                                                                                                                                                                                                                                                                                                              | funcs:sum(test.lineitem.l_extendedprice)->Column#69                                                                                                                                   | N/A     | N/A     |
|         └─Projection_120                           | 294615.14    | 26963     | mpp[tiflash] |                | tiflash_task:{proc max:8.09s, min:8.07s, avg: 8.08s, p80:8.09s, p95:8.09s, iters:2048, tasks:2, threads:16}                                                                                                                                                                                                                                                                                                                                                                                                          | test.lineitem.l_extendedprice                                                                                                                                                         | N/A     | N/A     |
|           └─Projection_117                         | 294615.14    | 26963     | mpp[tiflash] |                | tiflash_task:{proc max:8.03s, min:8.02s, avg: 8.03s, p80:8.03s, p95:8.03s, iters:2048, tasks:2, threads:16}                                                                                                                                                                                                                                                                                                                                                                                                          | test.lineitem.l_extendedprice, test.lineitem.l_partkey                                                                                                                                | N/A     | N/A     |
|             └─HashJoin_116                         | 294615.14    | 26963     | mpp[tiflash] |                | tiflash_task:{proc max:8s, min:7.96s, avg: 7.98s, p80:8s, p95:8s, iters:2048, tasks:2, threads:16}                                                                                                                                                                                                                                                                                                                                                                                                                   | inner join, equal:[eq(test.part.p_partkey, test.lineitem.l_partkey)], other cond:lt(test.lineitem.l_quantity, mul(0.2, Column#42)), stream_count: 8                                   | N/A     | N/A     |
|               ├─ExchangeReceiver_38(Build)         | 294615.14    | 299664    | mpp[tiflash] |                | tiflash_task:{proc max:690ms, min:623.1ms, avg: 656.6ms, p80:690ms, p95:690ms, iters:48, tasks:2, threads:16}                                                                                                                                                                                                                                                                                                                                                                                                        | stream_count: 8                                                                                                                                                                       | N/A     | N/A     |
|               │ └─ExchangeSender_37                | 294615.14    | 299664    | mpp[tiflash] |                | tiflash_task:{proc max:929.4ms, min:0s, avg: 464.7ms, p80:929.4ms, p95:929.4ms, iters:4917, tasks:2, threads:32}                                                                                                                                                                                                                                                                                                                                                                                                     | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: test.part.p_partkey, collate: binary], stream_count: 8                                                              | N/A     | N/A     |
|               │   └─Projection_36                  | 294615.14    | 299664    | mpp[tiflash] |                | tiflash_task:{proc max:913.3ms, min:0s, avg: 456.7ms, p80:913.3ms, p95:913.3ms, iters:4917, tasks:2, threads:32}                                                                                                                                                                                                                                                                                                                                                                                                     | test.part.p_partkey, test.lineitem.l_quantity, test.lineitem.l_extendedprice                                                                                                          | N/A     | N/A     |
|               │     └─HashJoin_30                  | 294615.14    | 299664    | mpp[tiflash] |                | tiflash_task:{proc max:906.6ms, min:0s, avg: 453.3ms, p80:906.6ms, p95:906.6ms, iters:4917, tasks:2, threads:32}                                                                                                                                                                                                                                                                                                                                                                                                     | inner join, equal:[eq(test.part.p_partkey, test.lineitem.l_partkey)]                                                                                                                  | N/A     | N/A     |
|               │       ├─ExchangeReceiver_34(Build) | 9786.50      | 19978     | mpp[tiflash] |                | tiflash_task:{proc max:133ms, min:0s, avg: 66.5ms, p80:133ms, p95:133ms, iters:32, tasks:2, threads:32}                                                                                                                                                                                                                                                                                                                                                                                                              |                                                                                                                                                                                       | N/A     | N/A     |
|               │       │ └─ExchangeSender_33        | 9786.50      | 9989      | mpp[tiflash] |                | tiflash_task:{proc max:133.2ms, min:0s, avg: 66.6ms, p80:133.2ms, p95:133.2ms, iters:164, tasks:2, threads:32}                                                                                                                                                                                                                                                                                                                                                                                                       | ExchangeType: Broadcast, Compression: FAST                                                                                                                                            | N/A     | N/A     |
|               │       │   └─Selection_32           | 9786.50      | 9989      | mpp[tiflash] |                | tiflash_task:{proc max:97.2ms, min:0s, avg: 48.6ms, p80:97.2ms, p95:97.2ms, iters:164, tasks:2, threads:32}                                                                                                                                                                                                                                                                                                                                                                                                          | eq(test.part.p_brand, "Brand#44")                                                                                                                                                     | N/A     | N/A     |
|               │       │     └─TableFullScan_31     | 245361.47    | 250571    | mpp[tiflash] | table:part     | tiflash_task:{proc max:72.4ms, min:0s, avg: 36.2ms, p80:72.4ms, p95:72.4ms, iters:164, tasks:2, threads:32}, tiflash_scan:{mvcc_input_rows:0, mvcc_input_bytes:0, mvcc_output_rows:0, lm_skip_rows:0, local_regions:18, remote_regions:0, tot_learner_read:0ms, region_balance:{instance_num: 2, max/min: 9/9=1.000000}, delta_rows:0, delta_bytes:0, segments:18, stale_read_regions:0, tot_build_snapshot:0ms, tot_build_bitmap:9ms, tot_build_inputstream:12ms, min_local_stream:49ms, max_local_stream:70ms, ... | pushed down filter:eq(test.part.p_container, "WRAP PKG"), keep order:false                                                                                                            | N/A     | N/A     |
|               │       └─TableFullScan_35(Probe)    | 300005811.00 | 300005811 | mpp[tiflash] | table:lineitem | tiflash_task:{proc max:291.1ms, min:0s, avg: 145.5ms, p80:291.1ms, p95:291.1ms, iters:4917, tasks:2, threads:32}, tiflash_scan:{mvcc_input_rows:0, mvcc_input_bytes:0, mvcc_output_rows:0, lm_skip_rows:0, local_regions:649, remote_regions:0, tot_learner_read:53ms, region_balance:{instance_num: 2, max/min: 329/320=1.028125}, delta_rows:0, delta_bytes:0, segments:382, stale_read_regions:0, tot_build_snapshot:1ms, tot_build_bitmap:80ms, tot_build_inputstream:168ms, min_local_stream:169ms, max_loca... | keep order:false                                                                                                                                                                      | N/A     | N/A     |
|               └─Projection_48(Probe)               | 9965568.00   | 10000000  | mpp[tiflash] |                | tiflash_task:{proc max:7.9s, min:7.86s, avg: 7.88s, p80:7.9s, p95:7.9s, iters:4096, tasks:2, threads:16}                                                                                                                                                                                                                                                                                                                                                                                                             | div(Column#42, cast(case(eq(Column#49, 0), 1, Column#49), decimal(20,0) BINARY))->Column#42, test.lineitem.l_partkey                                                                  | N/A     | N/A     |
|                 └─HashAgg_49                       | 9965568.00   | 10000000  | mpp[tiflash] |                | tiflash_task:{proc max:7.69s, min:7.65s, avg: 7.67s, p80:7.69s, p95:7.69s, iters:4096, tasks:2, threads:16}                                                                                                                                                                                                                                                                                                                                                                                                          | group by:test.lineitem.l_partkey, funcs:sum(Column#50)->Column#49, funcs:sum(Column#51)->Column#42, funcs:firstrow(test.lineitem.l_partkey)->test.lineitem.l_partkey, stream_count: 8 | N/A     | N/A     |
|                   └─ExchangeReceiver_51            | 9965568.00   | 295885707 | mpp[tiflash] |                | tiflash_task:{proc max:2.19s, min:2.11s, avg: 2.15s, p80:2.19s, p95:2.19s, iters:29112, tasks:2, threads:16}                                                                                                                                                                                                                                                                                                                                                                                                         | stream_count: 8                                                                                                                                                                       | N/A     | N/A     |
|                     └─ExchangeSender_50            | 9965568.00   | 295885707 | mpp[tiflash] |                | tiflash_task:{proc max:7.79s, min:0s, avg: 3.9s, p80:7.79s, p95:7.79s, iters:12235, tasks:2, threads:32}                                                                                                                                                                                                                                                                                                                                                                                                             | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: test.lineitem.l_partkey, collate: binary], stream_count: 8                                                          | N/A     | N/A     |
|                       └─HashAgg_40                 | 9965568.00   | 295885707 | mpp[tiflash] |                | tiflash_task:{proc max:2.56s, min:0s, avg: 1.28s, p80:2.56s, p95:2.56s, iters:12235, tasks:2, threads:32}                                                                                                                                                                                                                                                                                                                                                                                                            | group by:test.lineitem.l_partkey, funcs:count(test.lineitem.l_quantity)->Column#50, funcs:sum(test.lineitem.l_quantity)->Column#51                                                    | N/A     | N/A     |
|                         └─TableFullScan_47         | 300005811.00 | 300005811 | mpp[tiflash] | table:lineitem | tiflash_task:{proc max:309.8ms, min:0s, avg: 154.9ms, p80:309.8ms, p95:309.8ms, iters:4914, tasks:2, threads:32}, tiflash_scan:{mvcc_input_rows:0, mvcc_input_bytes:0, mvcc_output_rows:0, lm_skip_rows:0, local_regions:649, remote_regions:0, tot_learner_read:55ms, region_balance:{instance_num: 2, max/min: 329/320=1.028125}, delta_rows:0, delta_bytes:0, segments:376, stale_read_regions:0, tot_build_snapshot:1ms, tot_build_bitmap:105ms, tot_build_inputstream:165ms, min_local_stream:0ms, max_local... | keep order:false                                                                                                                                                                      | N/A     | N/A     |
+----------------------------------------------------+--------------+-----------+--------------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+
23 rows in set
StarRocks > explain analyze select       sum(l_extendedprice) / 7.0 as avg_yearly   from       lineitem,       part   where       p_partkey = l_partkey       and p_brand = 'Brand#44'       and p_container = 'WRAP PKG'       and l_quantity < (           select               0.2 * avg(l_quantity)           from               lineitem           where               l_partkey = p_partkey       );
+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Explain String                                                                                                                                                   |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Summary                                                                                                                                                  |
|     QueryId: b7fa5438-4a80-11ef-b4e5-2a48bf5ea1d9                                                                                                        |
|     Version: 3.2.3-a40e2f8                                                                                                                               |
|     State: Finished                                                                                                                                      |
|     TotalTime: 337ms                                                                                                                                     |
|         ExecutionTime: 311.190ms [Scan: 184.023ms (59.14%), Network: 80.285ms (25.80%), ResultDeliverTime: 0ns (0.00%), ScheduleTime: 40.165ms (12.91%)] |
|         CollectProfileTime: 13ms                                                                                                                         |
|         FrontendProfileMergeTime: 8.746ms                                                                                                                |
|     QueryPeakMemoryUsage: 216.233 MB, QueryAllocatedMemoryUsage: 5.992 GB                                                                                |
|     Top Most Time-consuming Nodes:                                                                                                                       |
|         1. OLAP_SCAN (id=0) : 404.092ms (71.93%)                                                                                                    |
|         2. EXCHANGE (id=6) [SHUFFLE]: 114.127ms (20.31%)                                                                              |
|         3. HASH_JOIN (id=4) [BROADCAST, INNER JOIN]: 26.491ms (4.72%)                                                                                    |
|         4. OLAP_SCAN (id=1) : 5.878ms (1.05%)                                                                                                            |
|         5. EXCHANGE (id=12) [GATHER]: 4.217ms (0.75%)                                                                                                    |
|         6. SORT (id=7) [ROW_NUMBER, SORT]: 1.839ms (0.33%)                                                                                               |
|         7. EXCHANGE (id=3) [BROADCAST]: 1.549ms (0.28%)                                                                                                  |
|         8. ANALYTIC_EVAL (id=8) : 1.508ms (0.27%)                                                                                                        |
|         9. SELECT (id=9) : 674.462us (0.12%)                                                                                                             |
|         10. AGGREGATION (id=13) [finalize, merge]: 615.417us (0.11%)                                                                                     |
|     Top Most Memory-consuming Nodes:                                                                                                                     |
|         1. EXCHANGE (id=6) [SHUFFLE]: 14.477 MB                                                                                                          |
|         2. ANALYTIC_EVAL (id=8) : 10.574 MB                                                                                                              |
|         3. SORT (id=7) [ROW_NUMBER, SORT]: 3.067 MB                                                                                                      |
|         4. HASH_JOIN (id=4) [BROADCAST, INNER JOIN]: 1.730 MB                                                                                            |
|         5. AGGREGATION (id=11) [serialize, update]: 290.188 KB                                                                                           |
|         6. EXCHANGE (id=12) [GATHER]: 215.313 KB                                                                                                         |
|         7. EXCHANGE (id=3) [BROADCAST]: 146.445 KB                                                                                                       |
|         8. AGGREGATION (id=13) [finalize, merge]: 1.000 KB                                                                                               |
|     NonDefaultVariables:                                                                                                                                 |
|         enable_adaptive_sink_dop: false -> true                                                                                                          |
|         enable_async_profile: true -> false                                                                                                              |
|         enable_profile: false -> true                                                                                                                    |
| Fragment 0                                                                                                                                               |
| │   BackendNum: 1                                                                                                                                      |
| │   InstancePeakMemoryUsage: 269.875 KB, InstanceAllocatedMemoryUsage: 504.008 KB                                                                      |
| │   PrepareTime: 447.028us                                                                                                                             |
| └──RESULT_SINK                                                                                                                                     |
|    │   TotalTime: 62.106us (0.01%) [CPUTime: 62.106us]                                                                                                 |
|    │   OutputRows: 1                                                                                                                                   |
|    │   SinkType: MYSQL_PROTOCAL                                                                                                                        |
|    └──PROJECT (id=14)                                                                                                                              |
|       │   Estimates: [row: ?, cpu: ?, memory: ?, network: ?, cost: ?]                                                                                  |
|       │   TotalTime: 17.159us (0.00%) [CPUTime: 17.159us]                                                                                              |
|       │   OutputRows: 1                                                                                                                                |
|       │   Expression: [45: sum / 7.0]                                                                                                                  |
|       └──AGGREGATION (id=13) [finalize, merge]                                                                                                     |
|          │   Estimates: [row: 1, cpu: 8.00, memory: 24.00, network: 0.00, cost: 1807603882.69]                                                         |
|          │   TotalTime: 615.417us (0.11%) [CPUTime: 615.417us]                                                                                         |
|          │   OutputRows: 1                                                                                                                             |
|          │   PeakMemory: 1.000 KB, AllocatedMemory: 14.078 KB                                                                                          |
|          │   AggExprs: [sum(45: sum)]                                                                                                                  |
|          │   SubordinateOperators:                                                                                                                     |
|          │       LOCAL_EXCHANGE [Passthrough]                                                                                                          |
|          └──EXCHANGE (id=12) [GATHER]                                                                                                              |
|                 Estimates: [row: 1, cpu: 8.00, memory: 0.00, network: 8.00, cost: 1807603830.69]                                                         |
|                 TotalTime: 4.217ms (0.75%) [CPUTime: 1.183ms, NetworkTime: 3.034ms]                                                                      |
|                 OutputRows: 72                                                                                                                           |
|                 PeakMemory: 215.313 KB, AllocatedMemory: 649.016 KB                                                                                      |
|                                                                                                                                                              |
| Fragment 1                                                                                                                                               |
| │   BackendNum: 2                                                                                                                                      |
| │   InstancePeakMemoryUsage: 15.768 MB, InstanceAllocatedMemoryUsage: 88.963 MB                                                                        |
| │   PrepareTime: 1.613ms                                                                                                                               |
| └──DATA_STREAM_SINK (id=12)                                                                                                                        |
|    │   PartitionType: UNPARTITIONED                                                                                                                    |
|    └──AGGREGATION (id=11) [serialize, update]                                                                                                      |
|       │   Estimates: [row: 1, cpu: 1439668.12, memory: 8.00, network: 0.00, cost: 1807603814.69]                                                       |
|       │   TotalTime: 138.033us (0.02%) [CPUTime: 138.033us]                                                                                            |
|       │   OutputRows: 72                                                                                                                               |
|       │   PeakMemory: 290.188 KB, AllocatedMemory: 726.953 KB                                                                                          |
|       │   AggExprs: [sum(7: l_extendedprice)]                                                                                                          |
|       └──PROJECT (id=10)                                                                                                                           |
|          │   Estimates: [row: ?, cpu: ?, memory: ?, network: ?, cost: ?]                                                                               |
|          │   TotalTime: 531.893us (0.09%) [CPUTime: 531.893us]                                                                                         |
|          │   OutputRows: 26.963K (26963)                                                                                                               |
|          │   Expression: [7: l_extendedprice]                                                                                                          |
|          └──SELECT (id=9)                                                                                                                          |
|             │   Estimates: [row: 44989, cpu: 0.00, memory: 0.00, network: 0.00, cost: 1806883964.63]                                                   |
|             │   TotalTime: 674.462us (0.12%) [CPUTime: 674.462us]                                                                                      |
|             │   OutputRows: 26.963K (26963)                                                                                                            |
|             │   Predicates: [CAST(6: l_quantity AS DECIMAL128(38,9)) < 0.2 * 47: avg]                                                                  |
|             └──ANALYTIC_EVAL (id=8)                                                                                                                |
|                │   Estimates: [row: 89979, cpu: 2879336.25, memory: 0.00, network: 0.00, cost: 1806883964.63]                                          |
|                │   TotalTime: 1.508ms (0.27%) [CPUTime: 1.508ms]                                                                                       |
|                │   OutputRows: 299.664K (299664)                                                                                                       |
|                │   PeakMemory: 10.574 MB, AllocatedMemory: 24.455 MB                                                                                   |
|                │   Functions: [avg(6: l_quantity)]                                                                                                     |
|                │   PartitionExprs: [17: p_partkey]                                                                                                     |
|                └──SORT (id=7) [ROW_NUMBER, SORT]                                                                                                   |
|                   │   Estimates: [row: 89979, cpu: 2159502.19, memory: 2159502.19, network: 2159502.19, cost: 1805444296.50]                           |
|                   │   TotalTime: 1.839ms (0.33%) [CPUTime: 1.839ms]                                                                                    |
|                   │   OutputRows: 299.664K (299664)                                                                                                    |
|                   │   PeakMemory: 3.067 MB, AllocatedMemory: 49.128 MB                                                                                 |
|                   │   OrderByExprs: [<slot 17> 17: p_partkey]                                                                                          |
|                   └──EXCHANGE (id=6) [SHUFFLE]                                                                                  |
|                          Estimates: [row: 89979, cpu: 1799585.15, memory: 0.00, network: 1799585.15, cost: 1805444296.50]             |
|                          TotalTime: 114.127ms (20.31%) [CPUTime: 38.183ms, NetworkTime: 75.943ms]                                     |
|                          OutputRows: 299.664K (299664)                                                                                |
|                          PeakMemory: 14.477 MB, AllocatedMemory: 71.186 MB                                                            |
|                          Detail Timers:                                                                                               |
|                              OverallTime: 104.864ms [min=103.820ms, max=105.908ms]                                                    |
|                                                                                                                                                              |
| Fragment 2                                                                                                                                               |
| │   BackendNum: 2                                                                                                                                      |
| │   InstancePeakMemoryUsage: 208.678 MB, InstanceAllocatedMemoryUsage: 5.894 GB                                                                        |
| │   PrepareTime: 1.442ms                                                                                                                               |
| └──DATA_STREAM_SINK (id=6)                                                                                                                         |
|    │   PartitionType: HASH_PARTITIONED                                                                                                                 |
|    │   PartitionExprs: [17: p_partkey]                                                                                                                 |
|    └──PROJECT (id=5)                                                                                                                               |
|       │   Estimates: [row: ?, cpu: ?, memory: ?, network: ?, cost: ?]                                                                                  |
|       │   TotalTime: 32.553us (0.01%) [CPUTime: 32.553us]                                                                                              |
|       │   OutputRows: 299.664K (299664)                                                                                                                |
|       │   Expression: [6: l_quantity, 7: l_extendedprice, 17: p_partkey]                                                                               |
|       └──HASH_JOIN (id=4) [BROADCAST, INNER JOIN]                                                                                                  |
|          │   Estimates: [row: 89979, cpu: 1802367002.19, memory: 40000.00, network: 0.00, cost: 1801845126.19]                                         |
|          │   TotalTime: 26.491ms (4.72%) [CPUTime: 26.491ms]                                                                                           |
|          │   OutputRows: 299.664K (299664)                                                                                                             |
|          │   PeakMemory: 1.730 MB, AllocatedMemory: 37.678 MB                                                                                          |
|          │   BuildTime: 233.030us                                                                                                                      |
|          │   ProbeTime: 19.233ms                                                                                                                       |
|          │   EqJoinConjuncts: [4: l_partkey = 17: p_partkey]                                                                                           |
|          │   SubordinateOperators:                                                                                                                     |
|          │       CHUNK_ACCUMULATE                                                                                                                      |
|          │       LOCAL_EXCHANGE [Passthrough]                                                                                                          |
|          ├──<PROBE> OLAP_SCAN (id=0)                                                                                                          |
|          │      Estimates: [row: 90008375, cpu: 1800167500.00, memory: 0.00, network: 0.00, cost: 900083750.00]                                   |
|          │      TotalTime: 404.092ms (71.93%) [CPUTime: 222.242ms, ScanTime: 181.850ms]                                                           |
|          │      OutputRows: 1.361M (1361221)                                                                                                      |
|          │      RuntimeFilter: 300.006M (300005811) -> 1.361M (1361221) (99.55%)                                                                  |
|          │      Table: : lineitem                                                                                                                 |
|          │      SubordinateOperators:                                                                                                             |
|          │          CHUNK_ACCUMULATE                                                                                                              |
|          │          NOOP                                                                                                                          |
|          │          OLAP_SCAN_PREPARE                                                                                                             |
|          │      Detail Timers: [ScanTime = IOTaskExecTime + IOTaskWaitTime]                                                                       |
|          │          IOTaskExecTime: 94.874ms [min=53.509ms, max=147.580ms]                                                                        |
|          │              SegmentRead: 79.437ms [min=40.283ms, max=127.540ms]                                                                       |
|          │                  BlockFetch: 78.984ms [min=39.959ms, max=127.218ms]                                                                    |
|          │          IOTaskWaitTime: 31.231ms [min=4.431ms, max=72.544ms]                                                                          |
|          └──<BUILD> EXCHANGE (id=3) [BROADCAST]                                                                                                    |
|                 Estimates: [row: 10000, cpu: 80000.00, memory: 80000.00, network: 80000.00, cost: 417875.10]                                             |
|                 TotalTime: 1.549ms (0.28%) [CPUTime: 242.585us, NetworkTime: 1.307ms]                                                                    |
|                 OutputRows: 9.989K (9989)                                                                                                                |
|                 PeakMemory: 146.445 KB, AllocatedMemory: 692.094 KB                                                                                      |
|                                                                                                                                                              |
| Fragment 3                                                                                                                                               |
| │   BackendNum: 2                                                                                                                                      |
| │   InstancePeakMemoryUsage: 3.511 MB, InstanceAllocatedMemoryUsage: 11.550 MB                                                                         |
| │   PrepareTime: 254.500us                                                                                                                             |
| └──DATA_STREAM_SINK (id=3)                                                                                                                         |
|    │   PartitionType: UNPARTITIONED                                                                                                                    |
|    └──PROJECT (id=2)                                                                                                                               |
|       │   Estimates: [row: ?, cpu: ?, memory: ?, network: ?, cost: ?]                                                                                  |
|       │   TotalTime: 17.032us (0.00%) [CPUTime: 17.032us]                                                                                              |
|       │   OutputRows: 9.989K (9989)                                                                                                                    |
|       │   Expression: [17: p_partkey]                                                                                                                  |
|       └──OLAP_SCAN (id=1)                                                                                                                          |
|              Estimates: [row: 10000, cpu: 195750.20, memory: 0.00, network: 0.00, cost: 0.00]                                                            |
|              TotalTime: 5.878ms (1.05%) [CPUTime: 3.705ms, ScanTime: 2.173ms]                                                                            |
|              OutputRows: 9.989K (9989)                                                                                                                   |
|              Table: : part                                                                                                                               |
|              SubordinateOperators:                                                                                                                       |
|                  CHUNK_ACCUMULATE                                                                                                                        |
|                  NOOP                                                                                                                                    |
|                  OLAP_SCAN_PREPARE                                                                                                                       |
|                                                                                                                                                              |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
168 rows in set (0.36 sec)
AilinKid commented 3 months ago

The current tidb cannot detect if the self-joining target table is already in p (already built sub-plan), then eliminate the current join and leave basic p to produce one more thing. In this case, we can ask for internal llineitem to generate HashAgg_49 for us, rather than do another join to get it. Acked.