Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Hash join statistic is incorrect when spill is triggered #58571

Open
xzhangxian1008 opened this issue Dec 27, 2024 · 10 comments · May be fixed by #58669
Open

Hash join statistic is incorrect when spill is triggered #58571

xzhangxian1008 opened this issue Dec 27, 2024 · 10 comments · May be fixed by #58669
Assignees
@xzhangxian1008
Labels
affects-8.5 This bug affects the 8.5.x(LTS) versions. severity/major sig/execution SIG execution type/bug The issue is confirmed as a bug.

Comments

@xzhangxian1008
Copy link
Contributor

xzhangxian1008 commented Dec 27, 2024 •
edited
Loading

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

Trigger spill in hash join.

2. What did you expect to see? (Required)

correct statistic

3. What did you see instead (Required)

+------------------------------------+--------------+-----------+-----------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+
| id                                 | estRows      | actRows   | task      | access object  | execution info | operator info | memory   | disk    |
+------------------------------------+--------------+-----------+-----------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+
| Sort_10                            | 1.00         | 5         | root      |                | time:1m15.8s, open:657.4µs, close:29.5µs, loops:2, RU:1199689.70                                                                                                                                                                                                                                                                         | test.orders.o_orderpriority                                                                                                               | 1.21 KB  | 0 Bytes |
| └─Projection_12                    | 1.00         | 5         | root      |                | time:1m15.8s, open:645.4µs, close:28.6µs, loops:5, Concurrency:OFF | test.orders.o_orderpriority, Column#26                                                                                                    | 34.0 KB  | N/A     |
|   └─HashAgg_13                     | 1.00         | 5         | root      |                | time:1m15.8s, open:641.8µs, close:27µs, loops:5, partial_worker:{wall_time:1m15.813126507s, concurrency:5, task_num:2588, tot_wait:6m18.325465785s, tot_exec:611.285923ms, tot_time:6m19.065554441s, max:1m15.813112828s, p95:1m15.813112828s}, final_worker:{wall_time:1m15.813146976s, concurrency:5, task_num:21, tot_wait:16.761µs, tot_exec:13.302µs, tot_time:6m19.065664816s, max:1m15.813135023s, p95:1m15.813135023s}                                             | group by:test.orders.o_orderpriority, funcs:count(1)->Column#26, funcs:firstrow(test.orders.o_orderpriority)->test.orders.o_orderpriority | 481.9 KB | 0 Bytes |
|     └─HashJoin_26                  | 2281928.20   | 2574425   | root      |                | time:1m15.8s, open:585.6µs, close:24.4µs, loops:2589, build_hash_table:{total:160.9ms, fetch:-7.013241164s, build:7.51s}, probe:{concurrency:5, total:3m25s, max:40.5s, probe:3.09s, fetch and wait:3m21.9s, probe_collision:19336081}                                                                                                                                                                                                                                     | semi join, left side:TableReader_29, equal:[eq(test.orders.o_orderkey, test.lineitem.l_orderkey)]                                         | 1.00 GB  | 9.45 GB |                                                                                                                                                       
|       ├─TableReader_32(Build)      | 240004648.80 | 189670348 | root      |                | time:17.3s, open:425.7µs, close:3.61µs, loops:184597, cop_task: {num: 5628, max: 309.5ms, min: 268.9µs, avg: 58.1ms, p95: 143.3ms, max_proc_keys: 80864, p95_proc_keys: 79840, tot_proc: 5m15.4s, tot_wait: 747.8ms, copr_cache_hit_ratio: 0.00, build_task_duration: 383.2µs, max_distsql_concurrency: 15}, rpc_info:{Cop:{num_rpc:5628, total_time:5m26.7s}}                                                                                                             | data:Selection_31 | 18.5 MB  | N/A     |                                                                                                                                                       
|       │ └─Selection_31             | 240004648.80 | 189670348 | cop[tikv] |                | tikv_task:{proc max:0s, min:0s, avg: 54.1ms, p80:78ms, p95:133ms, iters:315458, tasks:5628}, scan_detail: {total_process_keys: 299982707, total_process_keys_size: 58990940562, total_keys: 299988321, get_snapshot_time: 403.4ms, rocksdb: {key_skipped_count: 299982707, block: {cache_hit_count: 1883561}}}, time_detail: {total_process_time: 5m15.4s, total_suspend_time: 1.11s, total_wait_time: 747.8ms, total_kv_read_wall_time: 4m37.7s, tikv_wall_time: 5m18.8s} | lt(test.lineitem.l_commitdate, test.lineitem.l_receiptdate)                                                                               | N/A      | N/A     |                                                                                                                                                       
|       │   └─TableFullScan_30       | 300005811.00 | 300005811 | cop[tikv] | table:lineitem | tikv_task:{proc max:0s, min:0s, avg: 49.4ms, p80:71.6ms, p95:121.7ms, iters:315458, tasks:5628}                                                                                                                                                                                                                                                                                                                                                                            | keep order:false | N/A      | N/A     |
|       └─TableReader_29(Probe)      | 2852410.25   | 2807525   | root      |                | time:4.4s, open:143.5µs, close:15.2µs, loops:2734, cop_task: {num: 395, max: 1.04s, min: 214.8µs, avg: 150.6ms, p95: 658.5ms, max_proc_keys: 879577, p95_proc_keys: 871392, tot_proc: 59s, tot_wait: 54.9ms, copr_cache_hit_ratio: 0.21, build_task_duration: 81.4µs, max_distsql_concurrency: 15}, rpc_info:{Cop:{num_rpc:395, total_time:59.5s}}                                                                                                                         | data:Selection_28 | 2.08 MB  | N/A     |                                                                                                                                                       
|         └─Selection_28             | 2852410.25   | 2807525   | cop[tikv] |                | tikv_task:{proc max:1.02s, min:1ms, avg: 151.1ms, p80:291ms, p95:652ms, iters:74858, tasks:395}, scan_detail: {total_process_keys: 74173248, total_process_keys_size: 11266342171, total_keys: 74173559, get_snapshot_time: 39.4ms, rocksdb: {key_skipped_count: 74173248, block: {cache_hit_count: 376619}}}, time_detail: {total_process_time: 59s, total_suspend_time: 115.9ms, total_wait_time: 54.9ms, total_kv_read_wall_time: 54.6s, tikv_wall_time: 59.2s}         | ge(test.orders.o_orderdate, 1995-01-01 00:00:00.000000), lt(test.orders.o_orderdate, 1995-04-01 00:00:00.000000)                          | N/A      | N/A     |                                                                                                                                                       
|           └─TableFullScan_27       | 75000000.00  | 75000000  | cop[tikv] | table:orders   | tikv_task:{proc max:970ms, min:1ms, avg: 141.5ms, p80:275ms, p95:611ms, iters:74858, tasks:395} | keep order:false                                                                                                                          | N/A      | N/A     |                                                                                                                                                       
+------------------------------------+--------------+-----------+-----------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+
10 rows in set (1 min 15.82 sec)

4. What is your TiDB version? (Required)

nightly(9b70321)
@xzhangxian1008 xzhangxian1008 added the type/bug The issue is confirmed as a bug. label Dec 27, 2024
@hawkingrei hawkingrei self-assigned this Dec 30, 2024
@hawkingrei
Copy link
Member 

explain select /*+ tidb_hj(orders) */
        o_orderpriority,
        count(*) as order_count
from
        orders use index()
where
        o_orderdate >= '1995-01-01'
        and o_orderdate < date_add('1995-01-01', interval '3' month)
        and exists (
                select
                        *
                from
                        lineitem use index()
                where
                        l_orderkey = o_orderkey
                        and l_commitdate < l_receiptdate
        )
group by
        o_orderpriority
order by
        o_orderpriority

the sql looks like this.

@hawkingrei hawkingrei mentioned this issue Dec 30, 2024
Merged
13 tasks
@xzhangxian1008
Copy link
Contributor Author

/assign

@hawkingrei hawkingrei removed their assignment Jan 2, 2025
@xzhangxian1008
Copy link
Contributor Author

/remove-label may-affectes-5.4

@ti-chi-bot ti-chi-bot
Copy link

ti-chi-bot bot commented Jan 2, 2025

@xzhangxian1008: The label(s) may-affectes-5.4 cannot be applied. These labels are supported: fuzz/sqlancer, fuzz/comp, challenge-program, compatibility-breaker, first-time-contributor, contribution, good first issue, correctness, duplicate, proposal, security, needs-more-info, needs-cherry-pick-release-5.4, needs-cherry-pick-release-6.1, needs-cherry-pick-release-6.5, needs-cherry-pick-release-7.1, needs-cherry-pick-release-7.5, needs-cherry-pick-release-8.1, needs-cherry-pick-release-8.5, affects-5.4, affects-6.1, affects-6.5, affects-7.1, affects-7.5, affects-8.1, affects-8.4, affects-8.5, may-affects-5.4, may-affects-6.1, may-affects-6.5, may-affects-7.1, may-affects-7.5, may-affects-8.1, may-affects-8.5.

In response to this:

/remove-label may-affectes-5.4

Instructions for interacting with me using PR comments are available here. If you have questions or suggestions related to my behavior, please file an issue against the ti-community-infra/tichi repository.

@xzhangxian1008
Copy link
Contributor Author

/remove-label may-affects-5.4

@ti-chi-bot ti-chi-bot bot removed the may-affects-5.4 This bug maybe affects 5.4.x versions. label Jan 2, 2025
@xzhangxian1008
Copy link
Contributor Author

/remove-label may-affects-6.1

@xzhangxian1008
Copy link
Contributor Author

/remove-label may-affects-6.5

@xzhangxian1008
Copy link
Contributor Author

/remove-label may-affects-7.1

@xzhangxian1008
Copy link
Contributor Author

/remove-label may-affects-7.5

@xzhangxian1008
Copy link
Contributor Author

/remove-label may-affects-8.1

@xzhangxian1008 xzhangxian1008 linked a pull request Jan 2, 2025 that will close this issue
Open
13 tasks
@windtalker windtalker added affects-8.5 This bug affects the 8.5.x(LTS) versions. and removed may-affects-8.5 labels Jan 3, 2025
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@xzhangxian1008 xzhangxian1008

Labels
affects-8.5 This bug affects the 8.5.x(LTS) versions. severity/major sig/execution SIG execution type/bug The issue is confirmed as a bug.
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

executor: fix hash join analyze statistic when spill is triggered xzhangxian1008/tidb
4 participants
@windtalker @hawkingrei @jebter @xzhangxian1008