TiFlash TableScan performance regression under wide-sparse table

[JaySon-Huang]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

• Create a table with hundreds of string columns
• The value on the string columns are 99% or above NULL
• execute query select * from tbl from tiflash

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

3. What did you see instead (Required)

• Executing select * from tbl, v7.1.5 costs 1.5 second，v8.5.2 costs 4.8 second
• Executing select count(*) from tbl, v7.1.5 costs 233ms，v8.5.2 costs 107ms
• Executing select other-columns except sparse-columns from tbl, v7.1.5 costs 225ms, v8.5.2 costs 61ms

The CPU flamegraph when running select * from tbl on v8.5.2

TopN_11 100.00  100 root        time:4.67s, loops:2 db_name.tbl_name.oid, offset:0, count:100  335.3 KB    0 Bytes
└─TableReader_24    100.00  200 root        time:4.67s, loops:3, cop_task: {num: 4, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00} MppVersion: 2, data:ExchangeSender_23   301.0 KB    N/A
  └─ExchangeSender_23   100.00  200 mpp[tiflash]        tiflash_task:{proc max:4.58s, min:4.14s, avg: 4.36s, p80:4.58s, p95:4.58s, iters:2, tasks:2, threads:224}   ExchangeType: PassThrough   N/A N/A
    └─TopN_22   100.00  200 mpp[tiflash]        tiflash_task:{proc max:4.58s, min:4.14s, avg: 4.36s, p80:4.58s, p95:4.58s, iters:2, tasks:2, threads:224}, tiflash_wait: {pipeline_queue_wait: 8ms} db_name.tbl_name.oid, offset:0, count:100  N/A N/A
      └─Selection_21    363345.62   324178  mpp[tiflash]        tiflash_task:{proc max:4.52s, min:4.13s, avg: 4.32s, p80:4.52s, p95:4.52s, iters:260, tasks:2, threads:224} eq(db_name.tbl_name.ssite_id, 1617)  N/A N/A
        └─TableFullScan_20  365307.13   326789  mpp[tiflash]    table:m tiflash_task:{proc max:4.51s, min:4.12s, avg: 4.32s, p80:4.51s, p95:4.51s, iters:260, tasks:2, threads:224}, tiflash_wait: {pipeline_queue_wait: 8ms}, tiflash_scan:{mvcc_input_rows:0, mvcc_input_bytes:0, mvcc_output_rows:0, lm_skip_rows:163840, local_regions:1173, remote_regions:0, tot_learner_read:15ms, region_balance:{instance_num: 2, max/min: 593/580=1.022414}, delta_rows:0, delta_bytes:0, segments:2286, stale_read_regions:0, tot_build_snapshot:6ms, tot_build_bitmap:429ms, tot_build_inputstream:845467ms, min_local_stream:3983ms, max_local_stream:4375ms, dtfile:{data_scanned_rows:2078044, data_skipped_rows:696320, mvcc_scanned_rows:0, mvcc_skipped_rows:0, lm_filter_scanned_rows:2241884, lm_filter_skipped_rows:250477589, tot_rs_index_check:543ms, tot_read:9798ms}} pushed down filter:eq(db_name.tbl_name.class_id, 24), gt(db_name.tbl_name.accounts_time, 2025-06-01 00:00:00.000000), le(db_name.tbl_name.accounts_time, 2025-06-07 23:59:59.000000), keep order:false  N/A N/A

-- tot_build_inputstream:845467ms this is abnormal

tiflash_wait: {pipeline_queue_wait: 8ms}, 
tiflash_scan:{mvcc_input_rows:0, mvcc_input_bytes:0, mvcc_output_rows:0, 
lm_skip_rows:163840, local_regions:1173, remote_regions:0, tot_learner_read:15ms, 
region_balance:{instance_num: 2, max/min: 593/580=1.022414}, 
delta_rows:0, delta_bytes:0, segments:2286, stale_read_regions:0, 
tot_build_snapshot:6ms, tot_build_bitmap:429ms, tot_build_inputstream:845467ms, 
min_local_stream:3983ms, max_local_stream:4375ms, 
dtfile:{
data_scanned_rows:2_078_044, data_skipped_rows:696_320,
mvcc_scanned_rows:0, mvcc_skipped_rows:0, 
lm_filter_scanned_rows:2_241_884, lm_filter_skipped_rows:250_477_589, 
tot_rs_index_check:543ms, tot_read:9798ms
}}

4. What is your TiFlash version? (Required)

v8.5.2

[JaySon-Huang]

microbenchmark: #10359

[seiya-annie]

Upgrade branch meets this issue:
source: <=7.1
target: >=7.5.0
blocking stage: post upgrade
workaround: query on tikv or change the "select *" query to "select all non-sparse columns" on tiflash