tidb
tidb copied to clipboard
pingcap
Data inconsistency when queries using TiFlash
Bug Report
Please answer these questions before submitting your issue. Thanks!
1. Minimal reproduce step (Required)
We have a TiDB cluster replicating from a MySQL master. The cluster consists of 2 TiDB, 3 TiKV, 2 TiFlash, and 3 PD nodes. For the past few days we have observed data inconsistencies in queries served by TiFlash. The query is pasted below for reference.
mysql> select tr.TXNID from sfn_db.t_report tr where tr.REPORT_TYPE in (0, 3) and tr.TXN_TYPE = 'Sale' and tr.BANK_TYPE = 'Gross' and tr.TXN_DATE between '2025-11-01' and '2025-11-30' and tr.BANK_NAME = 'netbnk' order by tr.TXNID into outfile '/tmp/tiflash.csv';
Query OK, 1087 rows affected (1.01 sec)
This query utilized the TiFlash and the rows returned in this result is 1087 which is the wrong count when comparing the data with it's MySQL master. At the same time when running the same query in TiKV using the optimizer hint we can get the same result set as in the Master.
mysql> select /*+ READ_FROM_STORAGE(TIKV[sfn_db.tr]) */ tr.TXNID from sfn_db.t_report tr where tr.REPORT_TYPE in (0, 3) and tr.TXN_TYPE = 'Sale' and tr.BANK_TYPE = 'Gross' and tr.TXN_DATE between '2025-11-01' and '2025-11-30' and tr.BANK_NAME = 'netbnk' order by tr.TXNID into outfile '/tmp/tikv.csv';
Query OK, 1114 rows affected (13.03 sec)
2. What did you expect to see? (Required)
Query OK, 1114 rows affected (13.03 sec)
3. What did you see instead (Required)
Query OK, 1087 rows affected (1.01 sec)
Note : We tried remove the TiFlash replca by running alter table sfn_db.t_report set tiflash replica 0; and then recreated it using alter table sfn_db.t_report set tiflash replica 2; however, still it returning the wrong data.
4. What is your TiDB version? (Required)
We upgraded the cluster last night but it couldn't help.
mysql> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v8.5.4
Edition: Community
Git Commit Hash: e4e814fdc0afe9c3a6e5e96f129d83df802ab820
Git Branch: HEAD
UTC Build Time: 2025-11-26 15:56:49
GoVersion: go1.23.12
Race Enabled: false
Check Table Before Drop: false
Store: tikv
1 row in set (0.00 sec)
@abisshekk5 seems the query is just a simple single table filter, can you paste the explain analyze result of the query reading from tikv and reading from tiflash? And also can you have same sample data to reproduce this issue?
Hi @windtalker,
Use the below query since we disables the TiFlash replica for the previously used table due to the data inconsistency.
mysql> SELECT /*+ READ_FROM_STORAGE(TIKV[sfn_db.rat]) */ pts.TXN_TYPE, sum(rat.anb_charge), sum(rat.bank_charge), count(*) from payments pts left join profiles pls on pls.PROFILEID = pts.PROFILEID left join sfn_db.rates rat ON rat.txnid = pts.TXNID where pts.TXN_STATUS = 1 and pls.MERCHANT_TYPE in ('AQ',1) and pts.MERCHANT_TXN_DATE_TIME BETWEEN '1759257000' and '1761935399' and pts.TXN_TYPE = 'enach' group by pts.TXN_TYPE;
+----------+------------------------+----------------------+----------+
| TXN_TYPE | sum(rat.anb_charge) | sum(rat.bank_charge) | count(*) |
+----------+------------------------+----------------------+----------+
| enach | 189.00 | 11350.00 | 1136 |
+----------+------------------------+----------------------+----------+
1 row in set (0.09 sec)
mysql> SELECT /*+ READ_FROM_STORAGE(TIFLASH[sfn_db.rat]) */ pts.TXN_TYPE, sum(rat.anb_charge), sum(rat.bank_charge), count(*) from payments pts left join profiles pls on pls.PROFILEID = pts.PROFILEID left join sfn_db.rates rat ON rat.txnid = pts.TXNID where pts.TXN_STATUS = 1 and pls.MERCHANT_TYPE in ('AQ',1) and pts.MERCHANT_TXN_DATE_TIME BETWEEN '1759257000' and '1761935399' and pts.TXN_TYPE = 'enach' group by pts.TXN_TYPE;
+----------+------------------------+----------------------+----------+
| TXN_TYPE | sum(rat.anb_charge) | sum(rat.bank_charge) | count(*) |
+----------+------------------------+----------------------+----------+
| enach | 161.00 | 8320.00 | 1136 |
+----------+------------------------+----------------------+----------+
1 row in set (16.40 sec)
Below is the explain plan
mysql> explain analyze SELECT /*+ READ_FROM_STORAGE(TIKV[sfn_db.rat]) */ pts.TXN_TYPE, sum(rat.anb_charge), sum(rat.bank_charge), count(*) from payments pts left join profiles pls on pls.PROFILEID = pts.PROFILEID left join sfn_db.rates rat ON rat.txnid = pts.TXNID where
pts.TXN_STATUS = 1 and pls.MERCHANT_TYPE in ('AQ',1) and pts.MERCHANT_TXN_DATE_TIME BETWEEN '1759257000' and '1761935399' and pts.TXN_TYPE = 'enach' group by pts.TXN_TYPE;
+---------------------------------------------+-----------+---------+--------------+-----------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+---------------------------------------------+-----------+---------+--------------+-----------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+
| Projection_12 | 1.00 | 1 | root | | time:76.3ms, loops:2, RU:357.19, Concurrency:OFF | anb.payments.txn_type, Column#109, Column#110, Column#111 | 3.80 KB | N/A |
| └─HashAgg_16 | 1.00 | 1 | root | | time:76.3ms, loops:2, partial_worker:{wall_time:76.23851ms, concurrency:5, task_num:2, tot_wait:151.595466ms, tot_exec:490.627µs, tot_time:380.928455ms, max:76.189328ms, p95:76.189328ms}, final_worker:{wall_time:76.268844ms, concurrency:5, task_num:6, tot_wait:5.737µs, tot_exec:2.207µs, tot_time:381.05653ms, max:76.224996ms, p95:76.224996ms} | group by:anb.payments.txn_type, funcs:sum(sfn_db.rates.anb_charge)->Column#109, funcs:sum(sfn_db.rates.bank_charge)->Column#110, funcs:count(1)->Column#111, funcs:firstrow(anb.payments.txn_type)->anb.payments.txn_type | 167.1 KB | 0 Bytes |
| └─IndexHashJoin_33 | 49090.97 | 1136 | root | | time:75.9ms, loops:3, inner:{total:44.5ms, concurrency:5, task:1, construct:850.2µs, fetch:42.8ms, build:174.7µs, join:814.9µs} | left outer join, inner:IndexLookUp_30, outer key:anb.payments.txnid, inner key:sfn_db.rates.txnid, equal cond:eq(anb.payments.txnid, sfn_db.rates.txnid) | 462.6 KB | N/A |
| ├─HashJoin_91(Build) | 48628.59 | 1136 | root | | time:31.2ms, loops:6, build_hash_table:{total:19.2ms, fetch:19ms, build:158.2µs}, probe:{concurrency:5, total:155.1ms, max:31ms, probe:3.51ms, fetch and wait:151.6ms} | inner join, equal:[eq(anb.payments.profileid, anb.profiles.profileid)] | 129.6 KB | 0 Bytes |
| │ ├─IndexLookUp_101(Build) | 47410.18 | 1136 | root | | time:19ms, loops:3, index_task: {total_time: 3.86ms, fetch_handle: 3.85ms, build: 1.14µs, wait: 2.59µs}, table_task: {total_time: 14.5ms, num: 1, concurrency: 5}, next: {wait_index: 4.35ms, wait_table_lookup_build: 363.4µs, wait_table_lookup_resp: 14.1ms} | | 152.5 KB | N/A |
| │ │ ├─IndexRangeScan_99(Build) | 47410.18 | 1136 | cop[tikv] | table:pts, index:idx_txn_status_type_date(TXN_STATUS, TXN_TYPE, MERCHANT_TXN_DATE_TIME) | time:3.77ms, loops:4, cop_task: {num: 1, max: 3.72ms, proc_keys: 1136, tot_proc: 3.07ms, tot_wait: 291.4µs, copr_cache_hit_ratio: 0.00, build_task_duration: 427.7µs, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:1, total_time:3.7ms}}, tikv_task:{time:0s, loops:6}, scan_detail: {total_process_keys: 1136, total_process_keys_size: 115872, total_keys: 1137, get_snapshot_time: 268.6µs, rocksdb: {key_skipped_count: 1136, block: {cache_hit_count: 10, read_count: 5, read_byte: 39.0 KB, read_time: 2.01ms}}}, time_detail: {total_process_time: 3.07ms, total_suspend_time: 7.88µs, total_wait_time: 291.4µs, tikv_wall_time: 3.48ms} | range:[1 "enach" 1.759257e+09,1 "enach" 1.761935399e+09], keep order:false | N/A | N/A |
| │ │ └─TableRowIDScan_100(Probe) | 47410.18 | 1136 | cop[tikv] | table:pts | time:14.1ms, loops:3, cop_task: {num: 33, max: 7.14ms, min: 2.19ms, avg: 4.81ms, p95: 6.19ms, max_proc_keys: 84, p95_proc_keys: 67, tot_proc: 122.5ms, tot_wait: 19.6ms, copr_cache_hit_ratio: 0.00, build_task_duration: 140.9µs, max_distsql_concurrency: 15, max_extra_concurrency: 7}, rpc_info:{Cop:{num_rpc:33, total_time:158.1ms}}, tikv_task:{proc max:10ms, min:0s, avg: 1.52ms, p80:0s, p95:10ms, iters:51, tasks:33}, scan_detail: {total_process_keys: 1136, total_process_keys_size: 911940, total_keys: 1361, get_snapshot_time: 19ms, rocksdb: {delete_skipped_count: 106, key_skipped_count: 1959, block: {cache_hit_count: 5682, read_count: 225, read_byte: 1.38 MB, read_time: 81.9ms}}}, time_detail: {total_process_time: 122.5ms, total_suspend_time: 480.7µs, total_wait_time: 19.6ms, total_kv_read_wall_time: 50ms, tikv_wall_time: 146.8ms} | keep order:false | N/A | N/A |
| │ └─TableReader_108(Probe) | 266543.82 | 28779 | root | | time:30.4ms, loops:32, cop_task: {num: 21, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00} | MppVersion: 2, data:ExchangeSender_107 | 206.2 KB | N/A |
| │ └─ExchangeSender_107 | 266543.82 | 28779 | mpp[tiflash] | | tiflash_task:{proc max:33.8ms, min:23.3ms, avg: 28.6ms, p80:33.8ms, p95:33.8ms, iters:26, tasks:2, threads:64} | ExchangeType: PassThrough | N/A | N/A |
| │ └─Selection_106 | 266543.82 | 28779 | mpp[tiflash] | | tiflash_task:{proc max:33.8ms, min:23.3ms, avg: 28.6ms, p80:33.8ms, p95:33.8ms, iters:26, tasks:2, threads:64} | or(eq(anb.profiles.merchant_type, "AQ"), eq(cast(anb.profiles.merchant_type, double BINARY), 1)) | N/A | N/A |
| │ └─TableFullScan_105 | 332625.00 | 332618 | mpp[tiflash] | table:pls | tiflash_task:{proc max:23.8ms, min:23.3ms, avg: 23.6ms, p80:23.8ms, p95:23.8ms, iters:33, tasks:2, threads:64}, tiflash_scan:{mvcc_input_rows:222296, mvcc_input_bytes:3779032, mvcc_output_rows:204850, lm_skip_rows:0, local_regions:2, remote_regions:0, tot_learner_read:2ms, region_balance:{instance_num: 2, max/min: 1/1=1.000000}, delta_rows:40851, delta_bytes:17755882, segments:2, stale_read_regions:0, tot_build_snapshot:0ms, tot_build_bitmap:21ms, tot_build_inputstream:21ms, min_local_stream:19ms, max_local_stream:19ms, dtfile:{data_scanned_rows:333916, data_skipped_rows:325720, mvcc_scanned_rows:206148, mvcc_skipped_rows:453488, lm_filter_scanned_rows:0, lm_filter_skipped_rows:0, tot_rs_index_check:0ms, tot_read:15ms}} | pushed down filter:empty, keep order:false | N/A | N/A |
| └─IndexLookUp_30(Probe) | 48628.59 | 1136 | root | | time:41.2ms, loops:3, index_task: {total_time: 22.2ms, fetch_handle: 22.2ms, build: 1.86µs, wait: 3.57µs}, table_task: {total_time: 26.8ms, num: 2, concurrency: 5}, next: {wait_index: 24.7ms, wait_table_lookup_build: 1.17ms, wait_table_lookup_resp: 15.2ms} | | 193.1 KB | N/A |
| ├─IndexRangeScan_28(Build) | 48628.59 | 1136 | cop[tikv] | table:rat, index:txnid_unique_key(txnid) | time:22.1ms, loops:4, cop_task: {num: 13, max: 22ms, min: 1.25ms, avg: 12.8ms, p95: 22ms, max_proc_keys: 231, p95_proc_keys: 231, tot_proc: 150.5ms, tot_wait: 8.39ms, copr_cache_hit_ratio: 0.08, build_task_duration: 2.49ms, max_distsql_concurrency: 13}, rpc_info:{Cop:{num_rpc:13, total_time:166.6ms}}, tikv_task:{proc max:20ms, min:10ms, avg: 12.3ms, p80:20ms, p95:20ms, iters:29, tasks:13}, scan_detail: {total_process_keys: 1108, total_process_keys_size: 48752, total_keys: 2216, get_snapshot_time: 7.79ms, rocksdb: {key_skipped_count: 1108, block: {cache_hit_count: 10976, read_count: 142, read_byte: 2.91 MB, read_time: 96.4ms}}}, time_detail: {total_process_time: 150.5ms, total_suspend_time: 436.8µs, total_wait_time: 8.39ms, total_kv_read_wall_time: 150ms, tikv_wall_time: 161.6ms} | range: decided by [eq(sfn_db.rates.txnid, anb.payments.txnid)], keep order:false | N/A | N/A |
| └─TableRowIDScan_29(Probe) | 48628.59 | 1136 | cop[tikv] | table:rat | time:24.9ms, loops:4, cop_task: {num: 28, max: 9.92ms, min: 2.98ms, avg: 6.19ms, p95: 9.85ms, max_proc_keys: 146, p95_proc_keys: 93, tot_proc: 144.2ms, tot_wait: 17.3ms, copr_cache_hit_ratio: 0.00, build_task_duration: 1.44ms, max_distsql_concurrency: 12, max_extra_concurrency: 6}, rpc_info:{Cop:{num_rpc:28, total_time:173ms}}, tikv_task:{proc max:10ms, min:0s, avg: 6.79ms, p80:10ms, p95:10ms, iters:44, tasks:28}, scan_detail: {total_process_keys: 1136, total_process_keys_size: 268088, total_keys: 1191, get_snapshot_time: 16.7ms, rocksdb: {key_skipped_count: 1082, block: {cache_hit_count: 788, read_count: 163, read_byte: 2.33 MB, read_time: 99.5ms}}}, time_detail: {total_process_time: 144.2ms, total_suspend_time: 230.9µs, total_wait_time: 17.3ms, total_kv_read_wall_time: 190ms, tikv_wall_time: 164.7ms} | keep order:false | N/A | N/A |
+---------------------------------------------+-----------+---------+--------------+-----------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+
14 rows in set (0.08 sec)
mysql>
mysql> explain analyze SELECT /*+ READ_FROM_STORAGE(TIFLASH[sfn_db.rat]) */ pts.TXN_TYPE, sum(rat.anb_charge), sum(rat.bank_charge), count(*) from payments pts left join profiles pls on pls.PROFILEID = pts.PROFILEID left join sfn_db.rates rat ON rat.txnid = pts.TXNID where pts.TXN_STATUS = 1 and pls.MERCHANT_TYPE in ('AQ',1) and pts.MERCHANT_TXN_DATE_TIME BETWEEN '1759257000' and '1761935399' and pts.TXN_TYPE = 'enach' group by pts.TXN_TYPE;
+--------------------------------------------------------------+--------------+-----------+--------------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+--------------------------------------------------------------+--------------+-----------+--------------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
| TableReader_138 | 1.00 | 1 | root | | time:59.4s, loops:2, RU:822499.38, cop_task: {num: 3, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00} | MppVersion: 2, data:ExchangeSender_137 | 3.80 KB | N/A |
| └─ExchangeSender_137 | 1.00 | 1 | mpp[tiflash] | | tiflash_task:{proc max:59.4s, min:59.4s, avg: 59.4s, p80:59.4s, p95:59.4s, iters:1, tasks:2, threads:64} | ExchangeType: PassThrough | N/A | N/A |
| └─Projection_13 | 1.00 | 1 | mpp[tiflash] | | tiflash_task:{proc max:59.4s, min:59.4s, avg: 59.4s, p80:59.4s, p95:59.4s, iters:1, tasks:2, threads:64} | anb.payments.txn_type, Column#109, Column#110, Column#111 | N/A | N/A |
| └─Projection_133 | 1.00 | 1 | mpp[tiflash] | | tiflash_task:{proc max:59.4s, min:59.4s, avg: 59.4s, p80:59.4s, p95:59.4s, iters:1, tasks:2, threads:64} | Column#109, Column#110, Column#111, anb.payments.txn_type | N/A | N/A |
| └─HashAgg_134 | 1.00 | 1 | mpp[tiflash] | | tiflash_task:{proc max:59.4s, min:59.4s, avg: 59.4s, p80:59.4s, p95:59.4s, iters:1, tasks:2, threads:64} | group by:anb.payments.txn_type, funcs:sum(Column#120)->Column#109, funcs:sum(Column#121)->Column#110, funcs:sum(Column#122)->Column#111, funcs:firstrow(anb.payments.txn_type)->anb.payments.txn_type, stream_count: 32 | N/A | N/A |
| └─ExchangeReceiver_136 | 1.00 | 2 | mpp[tiflash] | | tiflash_task:{proc max:59.4s, min:59.4s, avg: 59.4s, p80:59.4s, p95:59.4s, iters:1, tasks:2, threads:64} | stream_count: 32 | N/A | N/A |
| └─ExchangeSender_135 | 1.00 | 2 | mpp[tiflash] | | tiflash_task:{proc max:59.4s, min:0s, avg: 29.7s, p80:59.4s, p95:59.4s, iters:2, tasks:2, threads:2} | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: anb.payments.txn_type, collate: utf8_bin], stream_count: 32 | N/A | N/A |
| └─HashAgg_131 | 1.00 | 2 | mpp[tiflash] | | tiflash_task:{proc max:59.4s, min:0s, avg: 29.7s, p80:59.4s, p95:59.4s, iters:2, tasks:2, threads:2} | group by:anb.payments.txn_type, funcs:sum(sfn_db.rates.anb_charge)->Column#120, funcs:sum(sfn_db.rates.bank_charge)->Column#121, funcs:count(1)->Column#122 | N/A | N/A |
| └─Projection_117 | 49090.97 | 1136 | mpp[tiflash] | | tiflash_task:{proc max:59.4s, min:0s, avg: 29.7s, p80:59.4s, p95:59.4s, iters:20418, tasks:2, threads:64} | anb.payments.txn_type, sfn_db.rates.bank_charge, sfn_db.rates.anb_charge, anb.payments.txnid | N/A | N/A |
| └─HashJoin_116 | 49090.97 | 1136 | mpp[tiflash] | | tiflash_task:{proc max:59.4s, min:0s, avg: 29.7s, p80:59.4s, p95:59.4s, iters:20418, tasks:2, threads:64} | left outer join, equal:[eq(anb.payments.txnid, sfn_db.rates.txnid)], stream_count: 32 | N/A | N/A |
| ├─ExchangeReceiver_39(Build) | 48628.59 | 1136 | mpp[tiflash] | | tiflash_task:{proc max:35.1s, min:0s, avg: 17.6s, p80:35.1s, p95:35.1s, iters:64, tasks:2, threads:64}, tiflash_wait: {pipeline_queue_wait: 9ms} | stream_count: 32 | N/A | N/A |
| │ └─ExchangeSender_38 | 48628.59 | 1136 | mpp[tiflash] | | tiflash_task:{proc max:35s, min:0s, avg: 17.5s, p80:35s, p95:35s, iters:25, tasks:2, threads:64} | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: anb.payments.txnid, collate: binary], stream_count: 32 | N/A | N/A |
| │ └─Projection_37 | 48628.59 | 1136 | mpp[tiflash] | | tiflash_task:{proc max:35s, min:0s, avg: 17.5s, p80:35s, p95:35s, iters:25, tasks:2, threads:64} | anb.payments.txnid, anb.payments.txn_type | N/A | N/A |
| │ └─HashJoin_30 | 48628.59 | 1136 | mpp[tiflash] | | tiflash_task:{proc max:35s, min:0s, avg: 17.5s, p80:35s, p95:35s, iters:25, tasks:2, threads:64} | inner join, equal:[eq(anb.payments.profileid, anb.profiles.profileid)] | N/A | N/A |
| │ ├─ExchangeReceiver_34(Build) | 47410.18 | 2272 | mpp[tiflash] | | tiflash_task:{proc max:34.9s, min:0s, avg: 17.5s, p80:34.9s, p95:34.9s, iters:28, tasks:2, threads:64}, tiflash_wait: {pipeline_queue_wait: 19ms} | | N/A | N/A |
| │ │ └─ExchangeSender_33 | 47410.18 | 1136 | mpp[tiflash] | | tiflash_task:{proc max:34.7s, min:0s, avg: 17.4s, p80:34.7s, p95:34.7s, iters:40, tasks:2, threads:64} | ExchangeType: Broadcast, Compression: FAST | N/A | N/A |
| │ │ └─Selection_32 | 47410.18 | 1136 | mpp[tiflash] | | tiflash_task:{proc max:34.7s, min:0s, avg: 17.4s, p80:34.7s, p95:34.7s, iters:40, tasks:2, threads:64} | eq(anb.payments.txn_status, 1), ge(anb.payments.merchant_txn_date_time, 1.759257e+09), le(anb.payments.merchant_txn_date_time, 1.761935399e+09) | N/A | N/A |
| │ │ └─TableFullScan_31 | 408726.07 | 339447 | mpp[tiflash] | table:pts | tiflash_task:{proc max:34.7s, min:0s, avg: 17.4s, p80:34.7s, p95:34.7s, iters:13591, tasks:2, threads:64}, tiflash_wait: {pipeline_queue_wait: 49ms}, tiflash_scan:{mvcc_input_rows:1184126995, mvcc_input_bytes:20130158915, mvcc_output_rows:1135154986, lm_skip_rows:752605476, local_regions:19735, remote_regions:0, tot_learner_read:1108ms, region_balance:{instance_num: 2, max/min: 9875/9860=1.001521}, delta_rows:41283732, delta_bytes:31200394356, segments:2970, stale_read_regions:0, tot_build_snapshot:15ms, tot_build_bitmap:79505ms, tot_build_inputstream:80475ms, min_local_stream:2479ms, max_local_stream:33289ms, dtfile:{data_scanned_rows:408134424, data_skipped_rows:1823646286, mvcc_scanned_rows:1167818307, mvcc_skipped_rows:1063962403, lm_filter_scanned_rows:1587262448, lm_filter_skipped_rows:644518262, tot_rs_index_check:359ms, tot_read:1016067ms}} | pushed down filter:eq(anb.payments.txn_type, "enach"), keep order:false | N/A | N/A |
| │ └─Selection_36(Probe) | 266543.82 | 28779 | mpp[tiflash] | | tiflash_task:{proc max:35s, min:0s, avg: 17.5s, p80:35s, p95:35s, iters:25, tasks:2, threads:64} | or(eq(anb.profiles.merchant_type, "AQ"), eq(cast(anb.profiles.merchant_type, double BINARY), 1)) | N/A | N/A |
| │ └─TableFullScan_35 | 332625.00 | 332618 | mpp[tiflash] | table:pls | tiflash_task:{proc max:35s, min:0s, avg: 17.5s, p80:35s, p95:35s, iters:32, tasks:2, threads:64}, tiflash_wait: {pipeline_breaker_wait: 34959ms}, tiflash_scan:{mvcc_input_rows:222296, mvcc_input_bytes:3779032, mvcc_output_rows:204850, lm_skip_rows:0, local_regions:2, remote_regions:0, tot_learner_read:1ms, region_balance:{instance_num: 2, max/min: 1/1=1.000000}, delta_rows:40851, delta_bytes:17755882, segments:2, stale_read_regions:0, tot_build_snapshot:0ms, tot_build_bitmap:18ms, tot_build_inputstream:18ms, min_local_stream:34869ms, max_local_stream:34979ms, dtfile:{data_scanned_rows:333916, data_skipped_rows:325720, mvcc_scanned_rows:206148, mvcc_skipped_rows:453488, lm_filter_scanned_rows:0, lm_filter_skipped_rows:0, tot_rs_index_check:0ms, tot_read:13ms}} | pushed down filter:empty, keep order:false | N/A | N/A |
| └─ExchangeReceiver_42(Probe) | 676432901.00 | 665020603 | mpp[tiflash] | | tiflash_task:{proc max:59.2s, min:0s, avg: 29.6s, p80:59.2s, p95:59.2s, iters:20355, tasks:2, threads:64}, tiflash_wait: {pipeline_breaker_wait: 35099ms, pipeline_queue_wait: 49ms} | | N/A | N/A |
| └─ExchangeSender_41 | 676432901.00 | 665020603 | mpp[tiflash] | | tiflash_task:{proc max:59.4s, min:0s, avg: 29.7s, p80:59.4s, p95:59.4s, iters:10445, tasks:2, threads:64} | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: sfn_db.rates.txnid, collate: binary] | N/A | N/A |
| └─TableFullScan_40 | 676432901.00 | 665020603 | mpp[tiflash] | table:rat | tiflash_task:{proc max:25.1s, min:0s, avg: 12.6s, p80:25.1s, p95:25.1s, iters:10445, tasks:2, threads:64}, tiflash_wait: {pipeline_queue_wait: 69ms}, tiflash_scan:{mvcc_input_rows:6518376, mvcc_input_bytes:110812392, mvcc_output_rows:6439430, lm_skip_rows:0, local_regions:1727, remote_regions:0, tot_learner_read:40ms, region_balance:{instance_num: 2, max/min: 867/860=1.008140}, delta_rows:95570, delta_bytes:19114042, segments:911, stale_read_regions:0, tot_build_snapshot:2ms, tot_build_bitmap:27139ms, tot_build_inputstream:48820ms, min_local_stream:18979ms, max_local_stream:25109ms, dtfile:{data_scanned_rows:665052230, data_skipped_rows:26446549, mvcc_scanned_rows:6491561, mvcc_skipped_rows:21129233, lm_filter_scanned_rows:0, lm_filter_skipped_rows:0, tot_rs_index_check:35ms, tot_read:1417716ms}} | keep order:false | N/A | N/A |
+--------------------------------------------------------------+--------------+-----------+--------------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
23 rows in set (59.55 sec)
@abisshekk5 it is really weird because the join result's count is right, only sum(rat.anb_charge), sum(rat.bank_charge) differs. Can you just select all the result like
SELECT pts.TXN_TYPE, rat.anb_charge, rat.bank_charge, count(*), pls.PROFILEID, pts.PROFILEID, rat.txnid, pts.TXNID, pts.TXN_STATUS, pls.MERCHANT_TYPE, pts.MERCHANT_TXN_DATE_TIME , pts.TXN_TYPE from payments pts left join profiles pls on pls.PROFILEID = pts.PROFILEID left join sfn_db.rates rat ON rat.txnid = pts.TXNID where pts.TXN_STATUS = 1 and pls.MERCHANT_TYPE in ('AQ',1) and pts.MERCHANT_TXN_DATE_TIME BETWEEN '1759257000' and '1761935399' and pts.TXN_TYPE = 'enach' ;
to see what is the difference between tikv and tiflash's result? And also can you provide the schema of these tables?
Hi @windtalker,
Please find the results and the table structure below.
In TiKV
mysql> SELECT /*+ READ_FROM_STORAGE(TIKV[snf_db.rat]) */ pts.TXN_TYPE, sum(rat.anb_charge), sum(rat.bank_charge), count(*),pls.PROFILEID,pts.PROFILEID,rat.txnid,pts.TXNID, pts.TXN_STATUS, pls.MERCHANT_TYPE, pts.MERCHANT_TXN_DATE_TIME, pts.TXN_TYPE from payments pts left join profiles pls on pls.PROFILEID = pts.PROFILEID left join snf_db.rates rat ON rat.txnid = pts.TXNID where pts.TXN_STATUS = 1 and pls.MERCHANT_TYPE in ('AQ',1) and pts.MERCHANT_TXN_DATE_TIME BETWEEN '1759257000' and '1761935399' and pts.TXN_TYPE = 'enach';
+----------+------------------------+----------------------+----------+-----------+-----------+------------+------------+------------+---------------+------------------------+----------+
| TXN_TYPE | sum(rat.anb_charge) | sum(rat.bank_charge) | count(*) | PROFILEID | PROFILEID | txnid | TXNID | TXN_STATUS | MERCHANT_TYPE | MERCHANT_TXN_DATE_TIME | TXN_TYPE |
+----------+------------------------+----------------------+----------+-----------+-----------+------------+------------+------------+---------------+------------------------+----------+
| enach | 189.00 | 11350.00 | 1136 | 33614 | 33614 | 1370778853 | 1370778853 | 1 | 1 | 1759523461 | enach |
+----------+------------------------+----------------------+----------+-----------+-----------+------------+------------+------------+---------------+------------------------+----------+
1 row in set (0.05 sec)
mysql> EXPLAIN ANALYZE SELECT /*+ READ_FROM_STORAGE(TIKV[snf_db.rat]) */ pts.TXN_TYPE, sum(rat.anb_charge), sum(rat.bank_charge), count(*),pls.PROFILEID,pts.PROFILEID,rat.txnid,pts.TXNID, pts.TXN_STATUS, pls.MERCHANT_TYPE, pts.MERCHANT_TXN_DATE_TIME, pts.TXN_TYPE from payments pts left join profiles pls on pls.PROFILEID = pts.PROFILEID left join snf_db.rates rat ON rat.txnid = pts.TXNID where pts.TXN_STATUS = 1 and pls.MERCHANT_TYPE in ('AQ',1) and pts.MERCHANT_TXN_DATE_TIME BETWEEN '1759257000' and '1761935399' and pts.TXN_TYPE = 'enach';
+--------------------------------------------+-----------+---------+--------------+-----------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+--------------------------------------------+-----------+---------+--------------+-----------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+
| Projection_12 | 1.00 | 1 | root | | time:38.2ms, loops:2, RU:226.32, Concurrency:OFF | anbme.payments.txn_type, Column#109, Column#110, Column#111, anbme.profiles.profileid, anbme.payments.profileid, snf_db.rates.txnid, anbme.payments.txnid, anbme.payments.txn_status, anbme.profiles.merchant_type, anbme.payments.merchant_txn_date_time, anbme.payments.txn_type | 4.22 KB | N/A |
| └─HashAgg_16 | 1.00 | 1 | root | | time:38.1ms, loops:2, partial_worker:{wall_time:38.112436ms, concurrency:5, task_num:2, tot_wait:75.779884ms, tot_exec:309.227µs, tot_time:190.314093ms, max:38.06858ms, p95:38.06858ms}, final_worker:{wall_time:38.149295ms, concurrency:5, task_num:6, tot_wait:56.14µs, tot_exec:2.691µs, tot_time:190.49875ms, max:38.10241ms, p95:38.10241ms} | funcs:sum(snf_db.rates.anb_charge)->Column#109, funcs:sum(snf_db.rates.bank_charge)->Column#110, funcs:count(1)->Column#111, funcs:firstrow(anbme.payments.txnid)->anbme.payments.txnid, funcs:firstrow(anbme.payments.profileid)->anbme.payments.profileid, funcs:firstrow(anbme.payments.txn_status)->anbme.payments.txn_status, funcs:firstrow(anbme.payments.txn_type)->anbme.payments.txn_type, funcs:firstrow(anbme.payments.merchant_txn_date_time)->anbme.payments.merchant_txn_date_time, funcs:firstrow(anbme.profiles.profileid)->anbme.profiles.profileid, funcs:firstrow(anbme.profiles.merchant_type)->anbme.profiles.merchant_type, funcs:firstrow(snf_db.rates.txnid)->snf_db.rates.txnid | 248.0 KB | 0 Bytes |
| └─IndexHashJoin_29 | 49402.28 | 1136 | root | | time:38ms, loops:3, inner:{total:10.4ms, concurrency:5, task:1, construct:798.1µs, fetch:8.43ms, build:142.7µs, join:1.12ms} | left outer join, inner:IndexLookUp_26, outer key:anbme.payments.txnid, inner key:snf_db.rates.txnid, equal cond:eq(anbme.payments.txnid, snf_db.rates.txnid) | 522.2 KB | N/A |
| ├─HashJoin_86(Build) | 48936.96 | 1136 | root | | time:27.4ms, loops:7, build_hash_table:{total:8.56ms, fetch:8.4ms, build:154.8µs}, probe:{concurrency:5, total:136.3ms, max:27.3ms, probe:3.26ms, fetch and wait:133.1ms} | inner join, equal:[eq(anbme.payments.profileid, anbme.profiles.profileid)] | 129.6 KB | 0 Bytes |
| │ ├─IndexLookUp_95(Build) | 47710.83 | 1136 | root | | time:8.35ms, loops:3, index_task: {total_time: 2.02ms, fetch_handle: 2.01ms, build: 1.19µs, wait: 11.4µs}, table_task: {total_time: 6.12ms, num: 1, concurrency: 5}, next: {wait_index: 2.09ms, wait_table_lookup_build: 395.2µs, wait_table_lookup_resp: 5.72ms} | | 152.5 KB | N/A |
| │ │ ├─IndexRangeScan_93(Build) | 47710.83 | 1136 | cop[tikv] | table:pts, index:idx_txn_status_type_date(TXN_STATUS, TXN_TYPE, MERCHANT_TXN_DATE_TIME) | time:1.93ms, loops:4, cop_task: {num: 1, max: 1.88ms, proc_keys: 1136, tot_proc: 810.7µs, tot_wait: 585.8µs, copr_cache_hit_ratio: 0.00, build_task_duration: 30.4µs, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:1, total_time:1.86ms}}, tikv_task:{time:0s, loops:6}, scan_detail: {total_process_keys: 1136, total_process_keys_size: 115872, total_keys: 1137, get_snapshot_time: 545.3µs, rocksdb: {key_skipped_count: 1136, block: {cache_hit_count: 16}}}, time_detail: {total_process_time: 810.7µs, total_wait_time: 585.8µs, tikv_wall_time: 1.51ms} | range:[1 "enach" 1.759257e+09,1 "enach" 1.761935399e+09], keep order:false | N/A | N/A |
| │ │ └─TableRowIDScan_94(Probe) | 47710.83 | 1136 | cop[tikv] | table:pts | time:5.6ms, loops:3, cop_task: {num: 33, max: 4.41ms, min: 516.9µs, avg: 2.12ms, p95: 3.79ms, max_proc_keys: 84, p95_proc_keys: 67, tot_proc: 28.5ms, tot_wait: 27.6ms, copr_cache_hit_ratio: 0.00, build_task_duration: 142.5µs, max_distsql_concurrency: 15, max_extra_concurrency: 7}, rpc_info:{Cop:{num_rpc:33, total_time:69.4ms}}, tikv_task:{proc max:10ms, min:0s, avg: 1.21ms, p80:0s, p95:10ms, iters:51, tasks:33}, scan_detail: {total_process_keys: 1136, total_process_keys_size: 911940, total_keys: 1333, get_snapshot_time: 26.9ms, rocksdb: {delete_skipped_count: 28, key_skipped_count: 1833, block: {cache_hit_count: 6156, read_count: 33, read_byte: 77.6 KB, read_time: 4.95ms}}}, time_detail: {total_process_time: 28.5ms, total_suspend_time: 141µs, total_wait_time: 27.6ms, total_kv_read_wall_time: 40ms, tikv_wall_time: 60.1ms} | keep order:false | N/A | N/A |
| │ └─TableReader_102(Probe) | 266683.25 | 28825 | root | | time:26.7ms, loops:32, cop_task: {num: 19, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00} | MppVersion: 2, data:ExchangeSender_101 | 206.2 KB | N/A |
| │ └─ExchangeSender_101 | 266683.25 | 28825 | mpp[tiflash] | | tiflash_task:{proc max:23.2ms, min:22.5ms, avg: 22.8ms, p80:23.2ms, p95:23.2ms, iters:23, tasks:2, threads:64} | ExchangeType: PassThrough | N/A | N/A |
| │ └─Selection_100 | 266683.25 | 28825 | mpp[tiflash] | | tiflash_task:{proc max:23.2ms, min:22.5ms, avg: 22.8ms, p80:23.2ms, p95:23.2ms, iters:23, tasks:2, threads:64} | or(eq(anbme.profiles.merchant_type, "AQ"), eq(cast(anbme.profiles.merchant_type, double BINARY), 1)) | N/A | N/A |
| │ └─TableFullScan_99 | 332799.00 | 332792 | mpp[tiflash] | table:pls | tiflash_task:{proc max:23.2ms, min:22.5ms, avg: 22.8ms, p80:23.2ms, p95:23.2ms, iters:32, tasks:2, threads:64}, tiflash_scan:{mvcc_input_rows:223363, mvcc_input_bytes:3797171, mvcc_output_rows:205024, lm_skip_rows:0, local_regions:2, remote_regions:0, tot_learner_read:0ms, region_balance:{instance_num: 2, max/min: 1/1=1.000000}, delta_rows:42985, delta_bytes:18727366, segments:2, stale_read_regions:0, tot_build_snapshot:0ms, tot_build_bitmap:18ms, tot_build_inputstream:20ms, min_local_stream:9ms, max_local_stream:19ms, dtfile:{data_scanned_rows:333916, data_skipped_rows:325720, mvcc_scanned_rows:206148, mvcc_skipped_rows:453488, lm_filter_scanned_rows:0, lm_filter_skipped_rows:0, tot_rs_index_check:0ms, tot_read:14ms}} | pushed down filter:empty, keep order:false | N/A | N/A |
| └─IndexLookUp_26(Probe) | 48936.96 | 1136 | root | | time:6.82ms, loops:3, index_task: {total_time: 1.47ms, fetch_handle: 1.46ms, build: 2.67µs, wait: 8.65µs}, table_task: {total_time: 7.4ms, num: 2, concurrency: 5}, next: {wait_index: 1.65ms, wait_table_lookup_build: 361.6µs, wait_table_lookup_resp: 4.69ms} | | 195.0 KB | N/A |
| ├─IndexRangeScan_24(Build) | 48936.96 | 1136 | cop[tikv] | table:rat, index:txnid_unique_key(txnid) | time:1.39ms, loops:4, cop_task: {num: 13, max: 1.3ms, min: 703.6µs, avg: 1.04ms, p95: 1.3ms, tot_proc: 11µs, tot_wait: 6.75ms, copr_cache_hit_ratio: 1.00, build_task_duration: 126µs, max_distsql_concurrency: 13}, rpc_info:{Cop:{num_rpc:13, total_time:13.3ms}}, tikv_task:{proc max:30ms, min:10ms, avg: 18.5ms, p80:20ms, p95:30ms, iters:29, tasks:13}, scan_detail: {get_snapshot_time: 6.32ms, rocksdb: {block: {}}}, time_detail: {total_process_time: 11µs, total_wait_time: 6.75ms, tikv_wall_time: 8.67ms} | range: decided by [eq(snf_db.rates.txnid, anbme.payments.txnid)], keep order:false | N/A | N/A |
| └─TableRowIDScan_25(Probe) | 48936.96 | 1136 | cop[tikv] | table:rat | time:6.87ms, loops:5, cop_task: {num: 27, max: 3.81ms, min: 282.9µs, avg: 951.8µs, p95: 3.76ms, max_proc_keys: 84, p95_proc_keys: 40, tot_proc: 8.52ms, tot_wait: 8.04ms, copr_cache_hit_ratio: 0.59, build_task_duration: 117.8µs, max_distsql_concurrency: 11, max_extra_concurrency: 5}, rpc_info:{Cop:{num_rpc:27, total_time:25.1ms}}, tikv_task:{proc max:10ms, min:0s, avg: 3.33ms, p80:10ms, p95:10ms, iters:42, tasks:27}, scan_detail: {total_process_keys: 346, total_process_keys_size: 81656, total_keys: 364, get_snapshot_time: 7.52ms, rocksdb: {key_skipped_count: 330, block: {cache_hit_count: 358, read_count: 12, read_byte: 171.8 KB, read_time: 5.72ms}}}, time_detail: {total_process_time: 8.52ms, total_suspend_time: 51.2µs, total_wait_time: 8.04ms, tikv_wall_time: 18.9ms} | keep order:false | N/A | N/A |
+--------------------------------------------+-----------+---------+--------------+-----------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+
14 rows in set (0.04 sec)
In TiFlash
mysql> SELECT /*+ READ_FROM_STORAGE(TIFLASH[snf_db.rat]) */ pts.TXN_TYPE, sum(rat.anb_charge), sum(rat.bank_charge), count(*),pls.PROFILEID,pts.PROFILEID,rat.txnid,pts.TXNID, pts.TXN_STATUS, pls.MERCHANT_TYPE, pts.MERCHANT_TXN_DATE_TIME, pts.TXN_TYPE from payments pts left join profiles pls on pls.PROFILEID = pts.PROFILEID left join snf_db.rates rat ON rat.txnid = pts.TXNID where pts.TXN_STATUS = 1 and pls.MERCHANT_TYPE in ('AQ',1) and pts.MERCHANT_TXN_DATE_TIME BETWEEN '1759257000' and '1761935399' and pts.TXN_TYPE = 'enach';
+----------+------------------------+----------------------+----------+-----------+-----------+-------+------------+------------+---------------+------------------------+----------+
| TXN_TYPE | sum(rat.anb_charge) | sum(rat.bank_charge) | count(*) | PROFILEID | PROFILEID | txnid | TXNID | TXN_STATUS | MERCHANT_TYPE | MERCHANT_TXN_DATE_TIME | TXN_TYPE |
+----------+------------------------+----------------------+----------+-----------+-----------+-------+------------+------------+---------------+------------------------+----------+
| enach | 161.00 | 8320.00 | 1136 | 320674 | 320674 | NULL | 1372599807 | 1 | 1 | 1759955463 | enach |
+----------+------------------------+----------------------+----------+-----------+-----------+-------+------------+------------+---------------+------------------------+----------+
1 row in set (13.01 sec)
mysql> EXPLAIN ANALYZE SELECT /*+ READ_FROM_STORAGE(TIFLASH[snf_db.rat]) */ pts.TXN_TYPE, sum(rat.anb_charge), sum(rat.bank_charge), count(*),pls.PROFILEID,pts.PROFILEID,rat.txnid,pts.TXNID, pts.TXN_STATUS, pls.MERCHANT_TYPE, pts.MERCHANT_TXN_DATE_TIME, pts.TXN_TYPE from payments pts left join profiles pls on pls.PROFILEID = pts.PROFILEID left join snf_db.rates rat ON rat.txnid = pts.TXNID where pts.TXN_STATUS = 1 and pls.MERCHANT_TYPE in ('AQ',1) and pts.MERCHANT_TXN_DATE_TIME BETWEEN '1759257000' and '1761935399' and pts.TXN_TYPE = 'enach';
+----------------------------------------------------+--------------+-----------+--------------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+----------------------------------------------------+--------------+-----------+--------------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+
| Projection_12 | 1.00 | 1 | root | | time:4.51s, loops:2, RU:834945.38, Concurrency:OFF | anbme.payments.txn_type, Column#109, Column#110, Column#111, anbme.profiles.profileid, anbme.payments.profileid, snf_db.rates.txnid, anbme.payments.txnid, anbme.payments.txn_status, anbme.profiles.merchant_type, anbme.payments.merchant_txn_date_time, anbme.payments.txn_type | 6.64 KB | N/A |
| └─HashAgg_103 | 1.00 | 1 | root | | time:4.51s, loops:2, partial_worker:{wall_time:4.509866174s, concurrency:5, task_num:1, tot_wait:4.509767876s, tot_exec:16.82µs, tot_time:22.549000331s, max:4.509808343s, p95:4.509808343s}, final_worker:{wall_time:4.509872155s, concurrency:5, task_num:5, tot_wait:85.499µs, tot_exec:196ns, tot_time:22.5492482s, max:4.509852709s, p95:4.509852709s} | funcs:sum(Column#112)->Column#109, funcs:sum(Column#113)->Column#110, funcs:count(Column#114)->Column#111, funcs:firstrow(Column#115)->anbme.payments.txnid, funcs:firstrow(Column#116)->anbme.payments.profileid, funcs:firstrow(Column#117)->anbme.payments.txn_status, funcs:firstrow(Column#118)->anbme.payments.txn_type, funcs:firstrow(Column#119)->anbme.payments.merchant_txn_date_time, funcs:firstrow(Column#120)->anbme.profiles.profileid, funcs:firstrow(Column#121)->anbme.profiles.merchant_type, funcs:firstrow(Column#122)->snf_db.rates.txnid | 19.0 KB | 0 Bytes |
| └─TableReader_105 | 1.00 | 2 | root | | time:4.51s, loops:2, cop_task: {num: 4, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00} | MppVersion: 2, data:ExchangeSender_104 | 2.74 KB | N/A |
| └─ExchangeSender_104 | 1.00 | 2 | mpp[tiflash] | | tiflash_task:{proc max:4.51s, min:4.51s, avg: 4.51s, p80:4.51s, p95:4.51s, iters:2, tasks:2, threads:2} | ExchangeType: PassThrough | N/A | N/A |
| └─HashAgg_17 | 1.00 | 2 | mpp[tiflash] | | tiflash_task:{proc max:4.51s, min:4.51s, avg: 4.51s, p80:4.51s, p95:4.51s, iters:2, tasks:2, threads:2} | funcs:sum(snf_db.rates.anb_charge)->Column#112, funcs:sum(snf_db.rates.bank_charge)->Column#113, funcs:count(1)->Column#114, funcs:firstrow(anbme.payments.txnid)->Column#115, funcs:firstrow(anbme.payments.profileid)->Column#116, funcs:firstrow(anbme.payments.txn_status)->Column#117, funcs:firstrow(anbme.payments.txn_type)->Column#118, funcs:firstrow(anbme.payments.merchant_txn_date_time)->Column#119, funcs:firstrow(anbme.profiles.profileid)->Column#120, funcs:firstrow(anbme.profiles.merchant_type)->Column#121, funcs:firstrow(snf_db.rates.txnid)->Column#122 | N/A | N/A |
| └─HashJoin_102 | 49402.28 | 1136 | mpp[tiflash] | | tiflash_task:{proc max:4.51s, min:4.5s, avg: 4.51s, p80:4.51s, p95:4.51s, iters:20483, tasks:2, threads:64} | left outer join, equal:[eq(anbme.payments.txnid, snf_db.rates.txnid)], stream_count: 32 | N/A | N/A |
| ├─ExchangeReceiver_34(Build) | 48936.96 | 1136 | mpp[tiflash] | | tiflash_task:{proc max:2.97s, min:2.97s, avg: 2.97s, p80:2.97s, p95:2.97s, iters:64, tasks:2, threads:64} | stream_count: 32 | N/A | N/A |
| │ └─ExchangeSender_33 | 48936.96 | 1136 | mpp[tiflash] | | tiflash_task:{proc max:2.98s, min:0s, avg: 1.49s, p80:2.98s, p95:2.98s, iters:24, tasks:2, threads:64} | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: anbme.payments.txnid, collate: binary], stream_count: 32 | N/A | N/A |
| │ └─HashJoin_26 | 48936.96 | 1136 | mpp[tiflash] | | tiflash_task:{proc max:2.97s, min:0s, avg: 1.49s, p80:2.97s, p95:2.97s, iters:24, tasks:2, threads:64} | inner join, equal:[eq(anbme.payments.profileid, anbme.profiles.profileid)] | N/A | N/A |
| │ ├─ExchangeReceiver_30(Build) | 47710.83 | 2272 | mpp[tiflash] | | tiflash_task:{proc max:2.95s, min:0s, avg: 1.48s, p80:2.95s, p95:2.95s, iters:37, tasks:2, threads:64} | | N/A | N/A |
| │ │ └─ExchangeSender_29 | 47710.83 | 1136 | mpp[tiflash] | | tiflash_task:{proc max:2.93s, min:0s, avg: 1.46s, p80:2.93s, p95:2.93s, iters:39, tasks:2, threads:64} | ExchangeType: Broadcast, Compression: FAST | N/A | N/A |
| │ │ └─Selection_28 | 47710.83 | 1136 | mpp[tiflash] | | tiflash_task:{proc max:2.93s, min:0s, avg: 1.46s, p80:2.93s, p95:2.93s, iters:39, tasks:2, threads:64} | eq(anbme.payments.txn_status, 1), ge(anbme.payments.merchant_txn_date_time, 1.759257e+09), le(anbme.payments.merchant_txn_date_time, 1.761935399e+09) | N/A | N/A |
| │ │ └─TableFullScan_27 | 411317.98 | 339593 | mpp[tiflash] | table:pts | tiflash_task:{proc max:2.93s, min:0s, avg: 1.46s, p80:2.93s, p95:2.93s, iters:13617, tasks:2, threads:64}, tiflash_wait: {pipeline_queue_wait: 49ms}, tiflash_scan:{mvcc_input_rows:1190218149, mvcc_input_bytes:20233708533, mvcc_output_rows:1141339515, lm_skip_rows:762325976, local_regions:19847, remote_regions:0, tot_learner_read:377ms, region_balance:{instance_num: 2, max/min: 9927/9920=1.000706}, delta_rows:41785166, delta_bytes:31593845565, segments:3012, stale_read_regions:0, tot_build_snapshot:14ms, tot_build_bitmap:87904ms, tot_build_inputstream:88997ms, min_local_stream:2329ms, max_local_stream:2679ms, dtfile:{data_scanned_rows:407336474, data_skipped_rows:1859295855, mvcc_scanned_rows:1173807324, mvcc_skipped_rows:1092313690, lm_filter_scanned_rows:1596927143, lm_filter_skipped_rows:669755482, tot_rs_index_check:474ms, tot_read:73975ms}} | pushed down filter:eq(anbme.payments.txn_type, "enach"), keep order:false | N/A | N/A |
| │ └─Selection_32(Probe) | 266683.25 | 28825 | mpp[tiflash] | | tiflash_task:{proc max:2.97s, min:0s, avg: 1.49s, p80:2.97s, p95:2.97s, iters:24, tasks:2, threads:64} | or(eq(anbme.profiles.merchant_type, "AQ"), eq(cast(anbme.profiles.merchant_type, double BINARY), 1)) | N/A | N/A |
| │ └─TableFullScan_31 | 332799.00 | 332792 | mpp[tiflash] | table:pls | tiflash_task:{proc max:2.97s, min:0s, avg: 1.49s, p80:2.97s, p95:2.97s, iters:39, tasks:2, threads:64}, tiflash_wait: {pipeline_breaker_wait: 2899ms, pipeline_queue_wait: 9ms}, tiflash_scan:{mvcc_input_rows:223363, mvcc_input_bytes:3797171, mvcc_output_rows:205024, lm_skip_rows:0, local_regions:2, remote_regions:0, tot_learner_read:55ms, region_balance:{instance_num: 2, max/min: 1/1=1.000000}, delta_rows:42985, delta_bytes:18727366, segments:2, stale_read_regions:0, tot_build_snapshot:0ms, tot_build_bitmap:14ms, tot_build_inputstream:15ms, min_local_stream:2899ms, max_local_stream:2919ms, dtfile:{data_scanned_rows:333916, data_skipped_rows:325720, mvcc_scanned_rows:206148, mvcc_skipped_rows:453488, lm_filter_scanned_rows:0, lm_filter_skipped_rows:0, tot_rs_index_check:0ms, tot_read:12ms}} | pushed down filter:empty, keep order:false | N/A | N/A |
| └─ExchangeReceiver_37(Probe) | 678890218.00 | 667121667 | mpp[tiflash] | | tiflash_task:{proc max:4.19s, min:4.16s, avg: 4.18s, p80:4.19s, p95:4.19s, iters:20420, tasks:2, threads:64}, tiflash_wait: {pipeline_breaker_wait: 2969ms, pipeline_queue_wait: 389ms} | | N/A | N/A |
| └─ExchangeSender_36 | 678890218.00 | 667121667 | mpp[tiflash] | | tiflash_task:{proc max:4.51s, min:0s, avg: 2.25s, p80:4.51s, p95:4.51s, iters:10510, tasks:2, threads:64} | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: snf_db.rates.txnid, collate: binary] | N/A | N/A |
| └─TableFullScan_35 | 678890218.00 | 667121667 | mpp[tiflash] | table:rat | tiflash_task:{proc max:681.5ms, min:0s, avg: 340.8ms, p80:681.5ms, p95:681.5ms, iters:10510, tasks:2, threads:64}, tiflash_wait: {pipeline_queue_wait: 549ms}, tiflash_scan:{mvcc_input_rows:6623755, mvcc_input_bytes:112603835, mvcc_output_rows:6544810, lm_skip_rows:0, local_regions:1733, remote_regions:0, tot_learner_read:37ms, region_balance:{instance_num: 2, max/min: 873/860=1.015116}, delta_rows:171676, delta_bytes:34335242, segments:914, stale_read_regions:0, tot_build_snapshot:2ms, tot_build_bitmap:543ms, tot_build_inputstream:1228ms, min_local_stream:279ms, max_local_stream:659ms, dtfile:{data_scanned_rows:667077257, data_skipped_rows:26833751, mvcc_scanned_rows:6529694, mvcc_skipped_rows:24292572, lm_filter_scanned_rows:0, lm_filter_skipped_rows:0, tot_rs_index_check:81ms, tot_read:15044ms}} | keep order:false | N/A | N/A |
+----------------------------------------------------+--------------+-----------+--------------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+
18 rows in set (4.59 sec)
Table Structure:
mysql> show create table payments\G
*************************** 1. row ***************************
Table: payments
Create Table: CREATE TABLE `payments` (
`TXNID` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'anbid',
`PROFILEID` int NOT NULL DEFAULT '0' COMMENT 'PK profiles',
`MERCHANTID` int unsigned DEFAULT NULL COMMENT 'PK of tbl_merchant_master',
`MERCHANT_CHANNEL_PG_ID` int unsigned DEFAULT NULL COMMENT 'PK of tbl_merchant_channel_pg_rates',
`MERCHANT_CHANNEL_BANKS_MODE_ID` int unsigned NOT NULL DEFAULT '0' COMMENT 'PK of tbl_merchant_channel_banks_mode',
`MERCHANT_TXN_ID` varchar(32) NOT NULL COMMENT 'orderid sent by Merchant',
`AMOUNT` decimal(12,2) unsigned NOT NULL COMMENT 'transaction Amount sent by Merchant',
`CURRENCYCODE` varchar(4) NOT NULL COMMENT 'Transaction currency code sent by merchant e.g. inr',
`TXN_STATUS` int NOT NULL COMMENT 'PK of tbl_transaction_status_master',
`TXN_TYPE` varchar(6) NOT NULL COMMENT 'enach,aloan,aeps,emi,cash,payltr,ppc,btqr,adpay,upi,pg,cod,pos,remit,netbnk,apcrdt,va,rtgs,wallet',
`TXN_SUB_TYPE` varchar(20) DEFAULT NULL COMMENT 'cashwithdraw,authorization,REVERSED,sale,barcode,salewithcashback,cw,void,vpa,reversal,intent,tip,refund,NULL,ap,salecomplete,cashatpos,emi,pos,staticqr,create',
`PG_TXNID` varchar(50) NOT NULL COMMENT 'Bank Transaction Reference',
`RRN` varchar(20) NOT NULL COMMENT 'Bank Transaction Reference',
`AUTH_CODE` varchar(20) NOT NULL COMMENT 'Bank Auth Code',
`RESPONSE_CODE` varchar(16) NOT NULL COMMENT 'Bank Code',
`RESPONSE_MESSAGE` varchar(300) NOT NULL COMMENT 'Bank Message',
`PG_CVR_CODE` varchar(20) NOT NULL COMMENT 'Bank Message',
`PG_TXN_AMOUNT` decimal(12,2) unsigned NOT NULL COMMENT 'Bank Amount',
`MERCHANT_TXN_DATE_TIME` double unsigned NOT NULL COMMENT 'Transaction Creation date time',
`IS_REFUND` char(1) NOT NULL DEFAULT 'N',
`IS_RISK` char(1) NOT NULL DEFAULT 'N',
`MULTI_PAY` char(1) NOT NULL DEFAULT 'N',
`PRI_TYPE` varchar(25) NOT NULL COMMENT 'dinersclub,discover,rupay,mastercard,maestro,visa,jcb,amex',
`PRI_ACC_NO` varchar(100) NOT NULL COMMENT 'Credit Card No',
`ENC_EXPDATE` varchar(100) NOT NULL COMMENT 'CC/DC expirydate encrypted',
`PRI_ACC_NO_START` varchar(32) DEFAULT NULL COMMENT 'CC/DC Bin',
`PRI_ACC_NO_END` varchar(32) DEFAULT NULL COMMENT 'CC/DC Last 4 Digits',
`UDF1` varchar(80) NOT NULL COMMENT 'Email',
`UDF2` varchar(100) NOT NULL COMMENT 'FullName',
`UDF3` varchar(255) NOT NULL COMMENT 'Address',
`UDF4` varchar(50) NOT NULL COMMENT 'City',
`UDF5` varchar(50) NOT NULL COMMENT 'State',
`UDF6` varchar(50) NOT NULL COMMENT 'Pincode',
`UDF7` varchar(4096) DEFAULT NULL COMMENT 'Internal Custom (CASH- PINCODE)',
`UDF8` varchar(50) NOT NULL COMMENT 'IP Address',
`UDF9` varchar(50) DEFAULT NULL COMMENT 'Consumer/Payer Country',
`UDF10` varchar(300) DEFAULT NULL COMMENT 'Merchant Custom Variable',
`TXN_PAYMENT_STATUS` char(1) NOT NULL DEFAULT 'N' COMMENT 'Transaction Payment Status',
`RECON_STATUS` varchar(4) NOT NULL COMMENT 'Bank Recon Received Y/N',
`RECON_DATE` double unsigned NOT NULL DEFAULT '0' COMMENT 'Date Bank Recon Received',
`SETTLEMENT_DATE` double unsigned NOT NULL DEFAULT '0' COMMENT 'Transaction Settled Y/N',
`CONTACT_NO` varchar(15) NOT NULL COMMENT 'Mobile/Contact No',
`USER_ID` varchar(20) NOT NULL DEFAULT '0' COMMENT '//Defines the Merchant User who made the call',
`MERCHANT` varchar(100) NOT NULL COMMENT 'Name of the merchant',
PRIMARY KEY (`TXNID`) /*T![clustered_index] CLUSTERED */,
KEY `fk_TRANSACTION_MERCHANTID` (`MERCHANTID`),
KEY `MERCHANT_CHANNEL_PG_IVR_ID` (`MERCHANT_CHANNEL_BANKS_MODE_ID`),
KEY `fk_TRANSACTION_MERCHANT_CHANNEL_PG_ID` (`MERCHANT_CHANNEL_PG_ID`),
KEY `MERCHANT_TXN_ID` (`MERCHANT_TXN_ID`),
KEY `PROFILEID` (`PROFILEID`),
KEY `MERCHANT_TXN_DATE_TIME` (`MERCHANT_TXN_DATE_TIME`),
KEY `PG_TXNID` (`PG_TXNID`(15)),
KEY `RRN_AUTH_CODE` (`RRN`(12),`AUTH_CODE`(5)),
KEY `MERCHANT_CHANNEL_PG_ID` (`MERCHANT_CHANNEL_PG_ID`),
KEY `TXNID` (`TXNID`),
KEY `TXN_STATUS` (`TXN_STATUS`),
KEY `TXN_PAYMENT_STATUS` (`TXN_PAYMENT_STATUS`),
KEY `inx_txn_status_txn_type` (`TXN_STATUS`,`TXN_TYPE`),
KEY `idx_PRI_ACC_NO_START_PRI_ACC_NO_END_MERCHANT_TXN_DATE_TIME` (`PRI_ACC_NO_START`,`PRI_ACC_NO_END`,`MERCHANT_TXN_DATE_TIME`),
KEY `idx_mer_stat_txn` (`MERCHANT_TXN_DATE_TIME`,`TXN_STATUS`,`TXNID`),
KEY `idx_ttm_merchdatetxnid` (`MERCHANT`,`MERCHANT_TXN_DATE_TIME`,`TXNID`),
KEY `idx_txn_status_type_date` (`TXN_STATUS`,`TXN_TYPE`,`MERCHANT_TXN_DATE_TIME`),
KEY `idx_profile_merchant_type` (`PROFILEID`,`MERCHANT_TXN_DATE_TIME`,`TXN_TYPE`) /*!80000 INVISIBLE */,
KEY `idx_txn_master_datetime_status_type` (`MERCHANT_TXN_DATE_TIME`,`TXN_STATUS`,`TXN_TYPE`,`MERCHANT_CHANNEL_PG_ID`),
KEY `idx_profile_status` (`PROFILEID`,`TXN_STATUS`,`MERCHANT_TXN_DATE_TIME`) /*!80000 INVISIBLE */,
KEY `idx_txn_status_currency_date_profile` (`TXN_STATUS`,`CURRENCYCODE`,`MERCHANT_TXN_DATE_TIME`,`PROFILEID`),
KEY `idx_ttm_status_date` (`TXN_STATUS`,`MERCHANT_TXN_DATE_TIME`),
KEY `idx_ttm2_fast` (`MERCHANT_TXN_DATE_TIME`,`TXN_STATUS`,`PROFILEID`,`CONTACT_NO`),
CONSTRAINT `fk_TRANSACTION_MERCHANTID` FOREIGN KEY (`MERCHANTID`) REFERENCES `tbl_merchant_master` (`MERCHANTID`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_TRANSACTION_MERCHANT_CHANNEL_PG_ID` FOREIGN KEY (`MERCHANT_CHANNEL_PG_ID`) REFERENCES `tbl_merchant_channel_pg_rates` (`MERCHANT_CHANNEL_PG_ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1588812776
1 row in set (0.00 sec)
mysql> show create table profiles\G
*************************** 1. row ***************************
Table: profiles
Create Table: CREATE TABLE `profiles` (
`PROFILEID` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'PK profiles',
`BRANDID` int unsigned NOT NULL COMMENT 'PK tbl_brand_master',
`BUSINESS` varchar(100) NOT NULL COMMENT 'Business Name',
`MERCHANT_LEGAL_NAME` varchar(255) DEFAULT NULL COMMENT 'Merchant Legal Name',
`ADDRESS` varchar(255) DEFAULT NULL COMMENT 'Business Company Address',
`CITY` int NOT NULL DEFAULT '0' COMMENT 'Company City Code from tbl_cities_master',
`STATE` int NOT NULL DEFAULT '0' COMMENT 'Company State Code from tbl_states_master',
`COUNTRY` varchar(75) DEFAULT NULL COMMENT 'Company Country Code from tbl_countries_master',
`PINCODE` varchar(10) DEFAULT NULL COMMENT 'Company Pincode',
`WEBSITE` varchar(200) NOT NULL COMMENT 'Business Website',
`MAIL_VERIFICATION` char(1) NOT NULL DEFAULT 'N' COMMENT 'enum(Y,N)',
`DOC_VERIFICATION` char(1) NOT NULL DEFAULT 'N' COMMENT 'enum(Y,N)',
`SANDBOX` char(1) NOT NULL DEFAULT 'Y' COMMENT 'Y - Active/N - Live , enum(Y,N)',
`PARTNER_ID` int NOT NULL COMMENT 'Partner ID from tbl_partner_master',
`TOKEN_LENGTH` smallint unsigned NOT NULL COMMENT 'Token Length',
`TIER` tinyint unsigned NOT NULL DEFAULT '1' COMMENT 'Tier',
`UPLOAD_DOC_STATUS` double(10,2) unsigned NOT NULL DEFAULT '0' COMMENT 'Upload document percentage',
`DOC_APPROVAL_STATUS` double(10,2) unsigned NOT NULL DEFAULT '0' COMMENT 'Document approval percentage',
`ZONE` varchar(2) DEFAULT NULL COMMENT '1-North/2-South/3-East/4-West',
`GSTSTATEID` smallint unsigned DEFAULT NULL COMMENT 'gst_state_master mapping',
`MERCHANT_TYPE` varchar(10) DEFAULT 'AQ' COMMENT 'AQ For Acquiring / AC for Account Creation',
`SECTOR_MCC_ID` tinyint unsigned NOT NULL DEFAULT '0' COMMENT 'Foreign key of tbl_sector_mcc_master',
`CREATEDON` double unsigned NOT NULL DEFAULT '0' COMMENT 'Record creation date',
`UPDATEDON` double unsigned NOT NULL DEFAULT '0' COMMENT 'Record updation date',
`VOLUME_INTERVAL` char(1) DEFAULT NULL COMMENT 'D=>Daily , W=>Weekly, M=>Monthly , enum(D,W,M)',
`VOLUME_AMOUNT` decimal(16,2) DEFAULT NULL COMMENT 'Set volume amount',
`STORE_ADDRESS` varchar(300) DEFAULT NULL COMMENT 'Store Address for Merchant Tagging',
`MER_TXN_TYPE` enum('1','2','3') DEFAULT NULL COMMENT '1: Online 2: Offline 3:both',
`MER_LONGITUDE` varchar(20) DEFAULT NULL COMMENT 'Longitude is for location store address for Merchant Tagging',
`MER_LATITUDE` varchar(20) DEFAULT NULL COMMENT 'Latitude is for location store address for Merchant Tagging',
`PRIMARY_MID` enum('Y','N') DEFAULT 'N' COMMENT 'Y means this mid is Primary N means Normal MID',
`BRAND_KYC_MAIL` enum('Y','N') DEFAULT 'N' COMMENT 'Y means mail sent for Kyc completed for brand N means not sent',
`MER_KYC_STATUS` enum('Y','N') DEFAULT NULL COMMENT 'Y means KYC Successfully completed N for not completed',
`MER_DEACTIVATE_REASON` enum('1','2') DEFAULT NULL COMMENT '1:KYC Expiry,2:MNRL Active',
`KYC_URL` varchar(1000) DEFAULT NULL COMMENT 'kyc_url from ms side is for accepting url for rekyc process intiate',
`KYC_MAIL_STATUS` enum('0','1') DEFAULT '0' COMMENT '0:mail not send to Merchant for KYC,1:mail sent',
`KYC_ACTIVATION_DATE` double unsigned DEFAULT NULL COMMENT 'KYC_ACTIVATION_DATE is for merchant active date for kyc',
`KYC_DUE_DATE` double unsigned DEFAULT NULL COMMENT 'KYC_DUE_DATE is for accepting re kyc success approved date',
PRIMARY KEY (`PROFILEID`) /*T![clustered_index] CLUSTERED */,
KEY `BRANDID` (`BRANDID`),
KEY `INDXBUSINESSPROFILESBRANDID` (`BRANDID`),
KEY `idx_UPDATEDON` (`UPDATEDON`),
KEY `idx_PARTNER_ID` (`PARTNER_ID`),
KEY `idx_test` (`MERCHANT_TYPE`),
KEY `idx_sector_mcc` (`SECTOR_MCC_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=2902154 COMMENT='Business Master'
1 row in set (0.00 sec)
mysql> show create table snf_db.rates\G
*************************** 1. row ***************************
Table: rates
Create Table: CREATE TABLE `rates` (
`id` int NOT NULL AUTO_INCREMENT COMMENT 'pk',
`txnid` int unsigned NOT NULL COMMENT 'anbid',
`amount` decimal(12,4) unsigned NOT NULL DEFAULT '0.0000' COMMENT 'TXN Amount',
`rate_id` int NOT NULL COMMENT 'PK tbl_rates_merchant_master',
`bank_msf` decimal(7,2) NOT NULL COMMENT 'Bank charges percentage',
`bank_ptc` decimal(7,2) NOT NULL COMMENT 'Bank charges per transaction',
`merc_msf` decimal(7,2) NOT NULL COMMENT 'anb charges percentage',
`merc_ptc` decimal(7,2) NOT NULL COMMENT 'anb charges per transaction',
`bank_interchange_charge` decimal(10,2) NOT NULL COMMENT 'bank interchange charge',
`bank_charge` decimal(10,2) NOT NULL COMMENT 'bank charges',
`anb_interchange_charge` decimal(10,2) NOT NULL COMMENT 'merchant interchange charge',
`anb_charge` decimal(10,2) NOT NULL COMMENT 'anb cjharges',
`bank_service_tax` decimal(10,2) NOT NULL COMMENT 'Banks service Tax',
`anb_service_tax` decimal(10,2) NOT NULL COMMENT 'Merchant service Tax',
`net_amount` decimal(12,2) unsigned NOT NULL COMMENT 'amont - all applicable charges',
`agent_msf` decimal(7,2) NOT NULL COMMENT 'Agent charges percentage',
`agent_ptc` decimal(10,2) NOT NULL COMMENT 'Agent charges in per transaction',
`agent_charge` decimal(12,2) NOT NULL COMMENT 'Agent actual charges',
`hold_txn` char(2) NOT NULL DEFAULT 'N' COMMENT 'Is settlement hold for this',
`hold_reason` varchar(100) DEFAULT NULL COMMENT 'Hold reason',
`manual_update` varchar(64) NOT NULL DEFAULT 'N' COMMENT 'Bank rates updated manually',
`manual_update_anb_charge` varchar(64) NOT NULL DEFAULT 'N' COMMENT 'anb rates updated manually',
`writeoff` varchar(10) NOT NULL DEFAULT 'N' COMMENT 'Flag charges written off',
`last_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'record update dateTime',
`reason_code` tinyint(1) NOT NULL COMMENT 'reason of hold transaction',
`currency_code` varchar(4) NOT NULL DEFAULT 'inr' COMMENT 'payput currency',
PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
UNIQUE KEY `txnid_unique_key` (`txnid`),
KEY `rate_id` (`rate_id`),
KEY `hold_txn` (`hold_txn`),
KEY `last_updated` (`last_updated`),
KEY `txnid` (`txnid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin AUTO_INCREMENT=776940369 COMMENT='bank/anb/agents charges applicable on TXNs'
1 row in set (0.00 sec)
@abisshekk5 actually, i want to check the difference of the original joined data, not the aggregated data, so i want to run this query
SELECT pts.TXN_TYPE, rat.anb_charge, rat.bank_charge, count(*), pls.PROFILEID, pts.PROFILEID, rat.txnid, pts.TXNID, pts.TXN_STATUS, pls.MERCHANT_TYPE, pts.MERCHANT_TXN_DATE_TIME , pts.TXN_TYPE from payments pts left join profiles pls on pls.PROFILEID = pts.PROFILEID left join sfn_db.rates rat ON rat.txnid = pts.TXNID where pts.TXN_STATUS = 1 and pls.MERCHANT_TYPE in ('AQ',1) and pts.MERCHANT_TXN_DATE_TIME BETWEEN '1759257000' and '1761935399' and pts.TXN_TYPE = 'enach' ;
not the aggregated one. And also can you try run the mpp query by disable fine-grained join:
set tiflash_fine_grained_shuffle_stream_count = -1;
Hi @windtalker,
Please find the queries after removing the aggregation and disabled tiflash_fine_grained_shuffle_stream_count.
TiKV
mysql> SELECT pts.TXN_TYPE, rat.anb_charge, rat.bank_charge, count(*),pls.PROFILEID,pts.PROFILEID,rat.txnid,pts.TXNID, pts.TXN_STATUS, pls.MERCHANT_TYPE, pts.MERCHANT_TXN_DATE_TIME, pts.TXN_TYPE from payments pts left join profiles pls on pls.PROFILEID = pts.PROFILEID left join snf_db.rates rat ON rat.txnid = pts.TXNID where pts.TXN_STATUS = 1 and pls.MERCHANT_TYPE in ('AQ',1) and pts.MERCHANT_TXN_DATE_TIME BETWEEN '1759257000' and '1761935399' and pts.TXN_TYPE = 'enach';
+----------+---------------+-------------+----------+-----------+-----------+------------+------------+------------+---------------+------------------------+----------+
| TXN_TYPE | anb_charge | bank_charge | count(*) | PROFILEID | PROFILEID | txnid | TXNID | TXN_STATUS | MERCHANT_TYPE | MERCHANT_TXN_DATE_TIME | TXN_TYPE |
+----------+---------------+-------------+----------+-----------+-----------+------------+------------+------------+---------------+------------------------+----------+
| enach | 0.00 | 10.00 | 1136 | 67204 | 67204 | 1374603392 | 1374603392 | 1 | 1 | 1760301061 | enach |
+----------+---------------+-------------+----------+-----------+-----------+------------+------------+------------+---------------+------------------------+----------+
1 row in set (0.08 sec)
mysql> EXPLAIN ANALYZE SELECT pts.TXN_TYPE, rat.anb_charge, rat.bank_charge, count(*),pls.PROFILEID,pts.PROFILEID,rat.txnid,pts.TXNID, pts.TXN_STATUS, pls.MERCHANT_TYPE, pts.MERCHANT_TXN_DATE_TIME, pts.TXN_TYPE from payments pts left join profiles pls on pls.PROFILEID = pts.PROFILEID left join snf_db.rates rat ON rat.txnid = pts.TXNID where pts.TXN_STATUS = 1 and pls.MERCHANT_TYPE in ('AQ',1) and pts.MERCHANT_TXN_DATE_TIME BETWEEN '1759257000' and '1761935399' and pts.TXN_TYPE = 'enach';
+--------------------------------------------+-----------+---------+--------------+-----------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+--------------------------------------------+-----------+---------+--------------+-----------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+
| Projection_12 | 1.00 | 1 | root | | time:36.6ms, loops:2, RU:223.57, Concurrency:OFF | anbme.payments.txn_type, snf_db.rates.anb_charge, snf_db.rates.bank_charge, Column#109, anbme.profiles.profileid, anbme.payments.profileid, snf_db.rates.txnid, anbme.payments.txnid, anbme.payments.txn_status, anbme.profiles.merchant_type, anbme.payments.merchant_txn_date_time, anbme.payments.txn_type | 99.4 KB | N/A |
| └─HashAgg_16 | 1.00 | 1 | root | | time:36.6ms, loops:2, partial_worker:{wall_time:36.509427ms, concurrency:5, task_num:2, tot_wait:72.590908ms, tot_exec:211.964µs, tot_time:182.262723ms, max:36.455988ms, p95:36.455988ms}, final_worker:{wall_time:36.535471ms, concurrency:5, task_num:6, tot_wait:115.045µs, tot_exec:1.911µs, tot_time:182.527374ms, max:36.522056ms, p95:36.522056ms} | funcs:count(1)->Column#109, funcs:firstrow(anbme.payments.txnid)->anbme.payments.txnid, funcs:firstrow(anbme.payments.profileid)->anbme.payments.profileid, funcs:firstrow(anbme.payments.txn_status)->anbme.payments.txn_status, funcs:firstrow(anbme.payments.txn_type)->anbme.payments.txn_type, funcs:firstrow(anbme.payments.merchant_txn_date_time)->anbme.payments.merchant_txn_date_time, funcs:firstrow(anbme.profiles.profileid)->anbme.profiles.profileid, funcs:firstrow(anbme.profiles.merchant_type)->anbme.profiles.merchant_type, funcs:firstrow(snf_db.rates.txnid)->snf_db.rates.txnid, funcs:firstrow(snf_db.rates.bank_charge)->snf_db.rates.bank_charge, funcs:firstrow(snf_db.rates.anb_charge)->snf_db.rates.anb_charge | 733.5 KB | 0 Bytes |
| └─IndexHashJoin_29 | 49407.23 | 1136 | root | | time:36.4ms, loops:3, inner:{total:8.21ms, concurrency:5, task:1, construct:842.2µs, fetch:6.29ms, build:142.6µs, join:1.07ms} | left outer join, inner:IndexLookUp_26, outer key:anbme.payments.txnid, inner key:snf_db.rates.txnid, equal cond:eq(anbme.payments.txnid, snf_db.rates.txnid) | 588.2 KB | N/A |
| ├─HashJoin_86(Build) | 48941.86 | 1136 | root | | time:28.1ms, loops:7, build_hash_table:{total:4.45ms, fetch:4.28ms, build:165.2µs}, probe:{concurrency:5, total:139.3ms, max:27.9ms, probe:3.36ms, fetch and wait:135.9ms} | inner join, equal:[eq(anbme.payments.profileid, anbme.profiles.profileid)] | 140.6 KB | 0 Bytes |
| │ ├─IndexLookUp_95(Build) | 47715.61 | 1136 | root | | time:4.26ms, loops:3, index_task: {total_time: 591.7µs, fetch_handle: 578.7µs, build: 1.25µs, wait: 11.8µs}, table_task: {total_time: 3.48ms, num: 1, concurrency: 5}, next: {wait_index: 659.5µs, wait_table_lookup_build: 369µs, wait_table_lookup_resp: 3.11ms} | | 201.8 KB | N/A |
| │ │ ├─IndexRangeScan_93(Build) | 47715.61 | 1136 | cop[tikv] | table:pts, index:idx_txn_status_type_date(TXN_STATUS, TXN_TYPE, MERCHANT_TXN_DATE_TIME) | time:499.9µs, loops:4, cop_task: {num: 1, max: 452.5µs, proc_keys: 0, tot_proc: 868ns, tot_wait: 38.6µs, copr_cache_hit_ratio: 1.00, build_task_duration: 28.1µs, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:1, total_time:432.8µs}}, tikv_task:{time:10ms, loops:6}, scan_detail: {get_snapshot_time: 16.4µs, rocksdb: {block: {}}}, time_detail: {total_process_time: 868ns, total_wait_time: 38.6µs, tikv_wall_time: 114.6µs} | range:[1 "enach" 1.759257e+09,1 "enach" 1.761935399e+09], keep order:false | N/A | N/A |
| │ │ └─TableRowIDScan_94(Probe) | 47715.61 | 1136 | cop[tikv] | table:pts | time:3.08ms, loops:3, cop_task: {num: 33, max: 2.08ms, min: 425.1µs, avg: 1.28ms, p95: 1.83ms, max_proc_keys: 84, p95_proc_keys: 67, tot_proc: 23.1ms, tot_wait: 5.12ms, copr_cache_hit_ratio: 0.00, build_task_duration: 142.2µs, max_distsql_concurrency: 15, max_extra_concurrency: 7}, rpc_info:{Cop:{num_rpc:33, total_time:41.7ms}}, tikv_task:{proc max:0s, min:0s, avg: 0s, p80:0s, p95:0s, iters:51, tasks:33}, scan_detail: {total_process_keys: 1136, total_process_keys_size: 911940, total_keys: 1333, get_snapshot_time: 4.42ms, rocksdb: {delete_skipped_count: 69, key_skipped_count: 1874, block: {cache_hit_count: 6362, read_count: 10, read_byte: 8.51 KB, read_time: 27.9µs}}}, time_detail: {total_process_time: 23.1ms, total_suspend_time: 79.6µs, total_wait_time: 5.12ms, tikv_wall_time: 32.3ms} | keep order:false | N/A | N/A |
| │ └─TableReader_102(Probe) | 266683.25 | 28825 | root | | time:27.2ms, loops:32, cop_task: {num: 19, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00} | MppVersion: 2, data:ExchangeSender_101 | 206.2 KB | N/A |
| │ └─ExchangeSender_101 | 266683.25 | 28825 | mpp[tiflash] | | tiflash_task:{proc max:32.9ms, min:21.7ms, avg: 27.3ms, p80:32.9ms, p95:32.9ms, iters:23, tasks:2, threads:64} | ExchangeType: PassThrough | N/A | N/A |
| │ └─Selection_100 | 266683.25 | 28825 | mpp[tiflash] | | tiflash_task:{proc max:22.9ms, min:21.7ms, avg: 22.3ms, p80:22.9ms, p95:22.9ms, iters:23, tasks:2, threads:64} | or(eq(anbme.profiles.merchant_type, "AQ"), eq(cast(anbme.profiles.merchant_type, double BINARY), 1)) | N/A | N/A |
| │ └─TableFullScan_99 | 332799.00 | 332797 | mpp[tiflash] | table:pls | tiflash_task:{proc max:22.9ms, min:21.7ms, avg: 22.3ms, p80:22.9ms, p95:22.9ms, iters:37, tasks:2, threads:64}, tiflash_scan:{mvcc_input_rows:223390, mvcc_input_bytes:3797630, mvcc_output_rows:205029, lm_skip_rows:0, local_regions:2, remote_regions:0, tot_learner_read:0ms, region_balance:{instance_num: 2, max/min: 1/1=1.000000}, delta_rows:43039, delta_bytes:18749546, segments:2, stale_read_regions:0, tot_build_snapshot:0ms, tot_build_bitmap:20ms, tot_build_inputstream:20ms, min_local_stream:9ms, max_local_stream:19ms, dtfile:{data_scanned_rows:333916, data_skipped_rows:325720, mvcc_scanned_rows:206148, mvcc_skipped_rows:453488, lm_filter_scanned_rows:0, lm_filter_skipped_rows:0, tot_rs_index_check:0ms, tot_read:14ms}} | pushed down filter:empty, keep order:false | N/A | N/A |
| └─IndexLookUp_26(Probe) | 48941.86 | 1136 | root | | time:4.68ms, loops:3, index_task: {total_time: 2.69ms, fetch_handle: 2.68ms, build: 2.48µs, wait: 8.17µs}, table_task: {total_time: 4.1ms, num: 2, concurrency: 5}, next: {wait_index: 2.01ms, wait_table_lookup_build: 382.2µs, wait_table_lookup_resp: 2.14ms} | | 201.5 KB | N/A |
| ├─IndexRangeScan_24(Build) | 48941.86 | 1136 | cop[tikv] | table:rat, index:txnid_unique_key(txnid) | time:2.61ms, loops:4, cop_task: {num: 13, max: 2.52ms, min: 419.1µs, avg: 1.03ms, p95: 2.52ms, max_proc_keys: 28, p95_proc_keys: 28, tot_proc: 1.59ms, tot_wait: 4.63ms, copr_cache_hit_ratio: 0.92, build_task_duration: 131.6µs, max_distsql_concurrency: 13}, rpc_info:{Cop:{num_rpc:13, total_time:13.1ms}}, tikv_task:{proc max:20ms, min:0s, avg: 13.1ms, p80:20ms, p95:20ms, iters:29, tasks:13}, scan_detail: {total_process_keys: 28, total_process_keys_size: 1232, total_keys: 56, get_snapshot_time: 4.1ms, rocksdb: {key_skipped_count: 28, block: {cache_hit_count: 365, read_count: 3, read_byte: 14.8 KB, read_time: 660.4µs}}}, time_detail: {total_process_time: 1.59ms, total_suspend_time: 10.3µs, total_wait_time: 4.63ms, tikv_wall_time: 8.22ms} | range: decided by [eq(snf_db.rates.txnid, anbme.payments.txnid)], keep order:false | N/A | N/A |
| └─TableRowIDScan_25(Probe) | 48941.86 | 1136 | cop[tikv] | table:rat | time:3.58ms, loops:4, cop_task: {num: 27, max: 1.04ms, min: 292.8µs, avg: 725.6µs, p95: 976.6µs, max_proc_keys: 102, p95_proc_keys: 93, tot_proc: 4.61ms, tot_wait: 5.47ms, copr_cache_hit_ratio: 0.19, build_task_duration: 124.2µs, max_distsql_concurrency: 12, max_extra_concurrency: 5}, rpc_info:{Cop:{num_rpc:27, total_time:19.2ms}}, tikv_task:{proc max:10ms, min:0s, avg: 1.48ms, p80:0s, p95:10ms, iters:43, tasks:27}, scan_detail: {total_process_keys: 862, total_process_keys_size: 203432, total_keys: 905, get_snapshot_time: 4.93ms, rocksdb: {key_skipped_count: 820, block: {cache_hit_count: 910}}}, time_detail: {total_process_time: 4.61ms, total_wait_time: 5.47ms, total_kv_read_wall_time: 20ms, tikv_wall_time: 12.7ms} | keep order:false | N/A | N/A |
+--------------------------------------------+-----------+---------+--------------+-----------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+
14 rows in set (0.04 sec)
TiFlash
mysql> SELECT /*+ READ_FROM_STORAGE(TIFLASH[snf_db.rat]) */ pts.TXN_TYPE, rat.anb_charge, rat.bank_charge, count(*),pls.PROFILEID,pts.PROFILEID,rat.txnid,pts.TXNID, pts.TXN_STATUS, pls.MERCHANT_TYPE, pts.MERCHANT_TXN_DATE_TIME, pts.TXN_TYPE from payments pts left join profiles pls on pls.PROFILEID = pts.PROFILEID left join snf_db.rates rat ON rat.txnid = pts.TXNID where pts.TXN_STATUS = 1 and pls.MERCHANT_TYPE in ('AQ',1) and pts.MERCHANT_TXN_DATE_TIME BETWEEN '1759257000' and '1761935399' and pts.TXN_TYPE = 'enach';
+----------+---------------+-------------+----------+-----------+-----------+-------+------------+------------+---------------+------------------------+----------+
| TXN_TYPE | anb_charge | bank_charge | count(*) | PROFILEID | PROFILEID | txnid | TXNID | TXN_STATUS | MERCHANT_TYPE | MERCHANT_TXN_DATE_TIME | TXN_TYPE |
+----------+---------------+-------------+----------+-----------+-----------+-------+------------+------------+---------------+------------------------+----------+
| enach | NULL | NULL | 1136 | 320674 | 320674 | NULL | 1372599817 | 1 | 1 | 1759955463 | enach |
+----------+---------------+-------------+----------+-----------+-----------+-------+------------+------------+---------------+------------------------+----------+
1 row in set (30.29 sec)
mysql> EXPLAIN ANALYZE SELECT /*+ READ_FROM_STORAGE(TIFLASH[snf_db.rat]) */ pts.TXN_TYPE, rat.anb_charge, rat.bank_charge, count(*),pls.PROFILEID,pts.PROFILEID,rat.txnid,pts.TXNID, pts.TXN_STATUS, pls.MERCHANT_TYPE, pts.MERCHANT_TXN_DATE_TIME, pts.TXN_TYPE from payments pts left join profiles pls on pls.PROFILEID = pts.PROFILEID left join snf_db.rates rat ON rat.txnid = pts.TXNID where pts.TXN_STATUS = 1 and pls.MERCHANT_TYPE in ('AQ',1) and pts.MERCHANT_TXN_DATE_TIME BETWEEN '1759257000' and '1761935399' and pts.TXN_TYPE = 'enach';
+----------------------------------------------------+--------------+-----------+--------------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+----------------------------------------------------+--------------+-----------+--------------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+
| Projection_12 | 1.00 | 1 | root | | time:20.8s, loops:2, RU:829923.90, Concurrency:OFF | anbme.payments.txn_type, snf_db.rates.anb_charge, snf_db.rates.bank_charge, Column#109, anbme.profiles.profileid, anbme.payments.profileid, snf_db.rates.txnid, anbme.payments.txnid, anbme.payments.txn_status, anbme.profiles.merchant_type, anbme.payments.merchant_txn_date_time, anbme.payments.txn_type | 109.6 KB | N/A |
| └─HashAgg_103 | 1.00 | 1 | root | | time:20.8s, loops:2, partial_worker:{wall_time:20.827937283s, concurrency:5, task_num:1, tot_wait:20.82787165s, tot_exec:12.669µs, tot_time:1m44.139485471s, max:20.827901722s, p95:20.827901722s}, final_worker:{wall_time:20.827988771s, concurrency:5, task_num:5, tot_wait:5.57µs, tot_exec:189ns, tot_time:1m44.139699725s, max:20.827957821s, p95:20.827957821s} | funcs:count(Column#110)->Column#109, funcs:firstrow(Column#111)->anbme.payments.txnid, funcs:firstrow(Column#112)->anbme.payments.profileid, funcs:firstrow(Column#113)->anbme.payments.txn_status, funcs:firstrow(Column#114)->anbme.payments.txn_type, funcs:firstrow(Column#115)->anbme.payments.merchant_txn_date_time, funcs:firstrow(Column#116)->anbme.profiles.profileid, funcs:firstrow(Column#117)->anbme.profiles.merchant_type, funcs:firstrow(Column#118)->snf_db.rates.txnid, funcs:firstrow(Column#119)->snf_db.rates.bank_charge, funcs:firstrow(Column#120)->snf_db.rates.anb_charge | 610.9 KB | 0 Bytes |
| └─TableReader_105 | 1.00 | 2 | root | | time:20.8s, loops:2, cop_task: {num: 4, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00} | MppVersion: 2, data:ExchangeSender_104 | 2.75 KB | N/A |
| └─ExchangeSender_104 | 1.00 | 2 | mpp[tiflash] | | tiflash_task:{proc max:20.8s, min:20.8s, avg: 20.8s, p80:20.8s, p95:20.8s, iters:2, tasks:2, threads:2} | ExchangeType: PassThrough | N/A | N/A |
| └─HashAgg_17 | 1.00 | 2 | mpp[tiflash] | | tiflash_task:{proc max:20.8s, min:20.8s, avg: 20.8s, p80:20.8s, p95:20.8s, iters:2, tasks:2, threads:2} | funcs:count(1)->Column#110, funcs:firstrow(anbme.payments.txnid)->Column#111, funcs:firstrow(anbme.payments.profileid)->Column#112, funcs:firstrow(anbme.payments.txn_status)->Column#113, funcs:firstrow(anbme.payments.txn_type)->Column#114, funcs:firstrow(anbme.payments.merchant_txn_date_time)->Column#115, funcs:firstrow(anbme.profiles.profileid)->Column#116, funcs:firstrow(anbme.profiles.merchant_type)->Column#117, funcs:firstrow(snf_db.rates.txnid)->Column#118, funcs:firstrow(snf_db.rates.bank_charge)->Column#119, funcs:firstrow(snf_db.rates.anb_charge)->Column#120 | N/A | N/A |
| └─HashJoin_102 | 49407.23 | 1136 | mpp[tiflash] | | tiflash_task:{proc max:20.8s, min:20.8s, avg: 20.8s, p80:20.8s, p95:20.8s, iters:20520, tasks:2, threads:64} | left outer join, equal:[eq(anbme.payments.txnid, snf_db.rates.txnid)], stream_count: 32 | N/A | N/A |
| ├─ExchangeReceiver_34(Build) | 48941.86 | 1136 | mpp[tiflash] | | tiflash_task:{proc max:3.18s, min:3.18s, avg: 3.18s, p80:3.18s, p95:3.18s, iters:64, tasks:2, threads:64} | stream_count: 32 | N/A | N/A |
| │ └─ExchangeSender_33 | 48941.86 | 1136 | mpp[tiflash] | | tiflash_task:{proc max:3.2s, min:0s, avg: 1.6s, p80:3.2s, p95:3.2s, iters:23, tasks:2, threads:64} | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: anbme.payments.txnid, collate: binary], stream_count: 32 | N/A | N/A |
| │ └─HashJoin_26 | 48941.86 | 1136 | mpp[tiflash] | | tiflash_task:{proc max:3.19s, min:0s, avg: 1.59s, p80:3.19s, p95:3.19s, iters:23, tasks:2, threads:64} | inner join, equal:[eq(anbme.payments.profileid, anbme.profiles.profileid)] | N/A | N/A |
| │ ├─ExchangeReceiver_30(Build) | 47715.61 | 2272 | mpp[tiflash] | | tiflash_task:{proc max:3.17s, min:0s, avg: 1.58s, p80:3.17s, p95:3.17s, iters:39, tasks:2, threads:64} | | N/A | N/A |
| │ │ └─ExchangeSender_29 | 47715.61 | 1136 | mpp[tiflash] | | tiflash_task:{proc max:3.13s, min:0s, avg: 1.57s, p80:3.13s, p95:3.13s, iters:39, tasks:2, threads:64} | ExchangeType: Broadcast, Compression: FAST | N/A | N/A |
| │ │ └─Selection_28 | 47715.61 | 1136 | mpp[tiflash] | | tiflash_task:{proc max:3.13s, min:0s, avg: 1.57s, p80:3.13s, p95:3.13s, iters:39, tasks:2, threads:64} | eq(anbme.payments.txn_status, 1), ge(anbme.payments.merchant_txn_date_time, 1.759257e+09), le(anbme.payments.merchant_txn_date_time, 1.761935399e+09) | N/A | N/A |
| │ │ └─TableFullScan_27 | 411359.19 | 339593 | mpp[tiflash] | table:pts | tiflash_task:{proc max:3.13s, min:0s, avg: 1.57s, p80:3.13s, p95:3.13s, iters:13606, tasks:2, threads:64}, tiflash_wait: {pipeline_queue_wait: 89ms}, tiflash_scan:{mvcc_input_rows:1191336118, mvcc_input_bytes:20252714006, mvcc_output_rows:1142366131, lm_skip_rows:763127172, local_regions:19849, remote_regions:0, tot_learner_read:492ms, region_balance:{instance_num: 2, max/min: 9929/9920=1.000907}, delta_rows:42021459, delta_bytes:31807255718, segments:3030, stale_read_regions:0, tot_build_snapshot:15ms, tot_build_bitmap:91858ms, tot_build_inputstream:93072ms, min_local_stream:2369ms, max_local_stream:2849ms, dtfile:{data_scanned_rows:407735511, data_skipped_rows:1872134674, mvcc_scanned_rows:1175263634, mvcc_skipped_rows:1104606551, lm_filter_scanned_rows:1597521133, lm_filter_skipped_rows:682349052, tot_rs_index_check:540ms, tot_read:78342ms}} | pushed down filter:eq(anbme.payments.txn_type, "enach"), keep order:false | N/A | N/A |
| │ └─Selection_32(Probe) | 266683.25 | 28825 | mpp[tiflash] | | tiflash_task:{proc max:3.19s, min:0s, avg: 1.59s, p80:3.19s, p95:3.19s, iters:23, tasks:2, threads:64} | or(eq(anbme.profiles.merchant_type, "AQ"), eq(cast(anbme.profiles.merchant_type, double BINARY), 1)) | N/A | N/A |
| │ └─TableFullScan_31 | 332799.00 | 332797 | mpp[tiflash] | table:pls | tiflash_task:{proc max:3.19s, min:0s, avg: 1.59s, p80:3.19s, p95:3.19s, iters:37, tasks:2, threads:64}, tiflash_wait: {pipeline_breaker_wait: 3139ms, pipeline_queue_wait: 9ms}, tiflash_scan:{mvcc_input_rows:223390, mvcc_input_bytes:3797630, mvcc_output_rows:205029, lm_skip_rows:0, local_regions:2, remote_regions:0, tot_learner_read:0ms, region_balance:{instance_num: 2, max/min: 1/1=1.000000}, delta_rows:43039, delta_bytes:18749546, segments:2, stale_read_regions:0, tot_build_snapshot:0ms, tot_build_bitmap:14ms, tot_build_inputstream:15ms, min_local_stream:3139ms, max_local_stream:3159ms, dtfile:{data_scanned_rows:333916, data_skipped_rows:325720, mvcc_scanned_rows:206148, mvcc_skipped_rows:453488, lm_filter_scanned_rows:0, lm_filter_skipped_rows:0, tot_rs_index_check:0ms, tot_read:12ms}} | pushed down filter:empty, keep order:false | N/A | N/A |
| └─ExchangeReceiver_37(Probe) | 678890218.00 | 669277869 | mpp[tiflash] | | tiflash_task:{proc max:20.6s, min:20.6s, avg: 20.6s, p80:20.6s, p95:20.6s, iters:20491, tasks:2, threads:64}, tiflash_wait: {pipeline_breaker_wait: 3179ms, pipeline_queue_wait: 109ms} | | N/A | N/A |
| └─ExchangeSender_36 | 678890218.00 | 669277869 | mpp[tiflash] | | tiflash_task:{proc max:20.8s, min:0s, avg: 10.4s, p80:20.8s, p95:20.8s, iters:10570, tasks:2, threads:64} | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: snf_db.rates.txnid, collate: binary] | N/A | N/A |
| └─TableFullScan_35 | 678890218.00 | 669277869 | mpp[tiflash] | table:rat | tiflash_task:{proc max:17.4s, min:0s, avg: 8.68s, p80:17.4s, p95:17.4s, iters:10570, tasks:2, threads:64}, tiflash_wait: {pipeline_queue_wait: 19ms}, tiflash_scan:{mvcc_input_rows:9797479, mvcc_input_bytes:166557143, mvcc_output_rows:9714016, lm_skip_rows:0, local_regions:1733, remote_regions:0, tot_learner_read:38ms, region_balance:{instance_num: 2, max/min: 873/860=1.015116}, delta_rows:156922, delta_bytes:31384440, segments:925, stale_read_regions:0, tot_build_snapshot:2ms, tot_build_bitmap:3300ms, tot_build_inputstream:97024ms, min_local_stream:14059ms, max_local_stream:17339ms, dtfile:{data_scanned_rows:669270037, data_skipped_rows:26664800, mvcc_scanned_rows:9728867, mvcc_skipped_rows:26650063, lm_filter_scanned_rows:0, lm_filter_skipped_rows:0, tot_rs_index_check:49ms, tot_read:912394ms}} | keep order:false | N/A | N/A |
+----------------------------------------------------+--------------+-----------+--------------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+
18 rows in set (20.92 sec)
mysql> set @@tiflash_fine_grained_shuffle_stream_count = -1; Query OK, 0 rows affected (0.00 sec)
mysql> SELECT /*+ READ_FROM_STORAGE(TIFLASH[snf_db.rat]) */ pts.TXN_TYPE, rat.anb_charge, rat.bank_charge, count(*),pls.PROFILEID,pts.PROFILEID,rat.txnid,pts.TXNID, pts.TXN_STATUS, pls.MERCHANT_TYPE, pts.MERCHANT_TXN_DATE_TIME, pts.TXN_TYPE from payments pts left join profiles pls on pls.PROFILEID = pts.PROFILEID left join snf_db.rates rat ON rat.txnid = pts.TXNID where pts.TXN_STATUS = 1 and pls.MERCHANT_TYPE in ('AQ',1) and pts.MERCHANT_TXN_DATE_TIME BETWEEN '1759257000' and '1761935399' and pts.TXN_TYPE = 'enach';
+----------+---------------+-------------+----------+-----------+-----------+-------+------------+------------+---------------+------------------------+----------+
| TXN_TYPE | anb_charge | bank_charge | count(*) | PROFILEID | PROFILEID | txnid | TXNID | TXN_STATUS | MERCHANT_TYPE | MERCHANT_TXN_DATE_TIME | TXN_TYPE |
+----------+---------------+-------------+----------+-----------+-----------+-------+------------+------------+---------------+------------------------+----------+
| enach | NULL | NULL | 1136 | 320674 | 320674 | NULL | 1372599817 | 1 | 1 | 1759955463 | enach |
+----------+---------------+-------------+----------+-----------+-----------+-------+------------+------------+---------------+------------------------+----------+
1 row in set (4.70 sec)
mysql>
mysql> EXPLAIN ANALYZE SELECT /*+ READ_FROM_STORAGE(TIFLASH[snf_db.rat]) */ pts.TXN_TYPE, rat.anb_charge, rat.bank_charge, count(*),pls.PROFILEID,pts.PROFILEID,rat.txnid,pts.TXNID, pts.TXN_STATUS, pls.MERCHANT_TYPE, pts.MERCHANT_TXN_DATE_TIME, pts.TXN_TYPE from payments pts left join profiles pls on pls.PROFILEID = pts.PROFILEID left join snf_db.rates rat ON rat.txnid = pts.TXNID where pts.TXN_STATUS = 1 and pls.MERCHANT_TYPE in ('AQ',1) and pts.MERCHANT_TXN_DATE_TIME BETWEEN '1759257000' and '1761935399' and pts.TXN_TYPE = 'enach';
+----------------------------------------------------+--------------+-----------+--------------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+----------------------------------------------------+--------------+-----------+--------------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+
| Projection_12 | 1.00 | 1 | root | | time:4.47s, loops:2, RU:833809.30, Concurrency:OFF | anbme.payments.txn_type, snf_db.rates.anb_charge, snf_db.rates.bank_charge, Column#109, anbme.profiles.profileid, anbme.payments.profileid, snf_db.rates.txnid, anbme.payments.txnid, anbme.payments.txn_status, anbme.profiles.merchant_type, anbme.payments.merchant_txn_date_time, anbme.payments.txn_type | 14.4 KB | N/A |
| └─HashAgg_103 | 1.00 | 1 | root | | time:4.47s, loops:2, partial_worker:{wall_time:4.472078027s, concurrency:5, task_num:1, tot_wait:4.471991921s, tot_exec:19.159µs, tot_time:22.360131187s, max:4.472034146s, p95:4.472034146s}, final_worker:{wall_time:4.472088012s, concurrency:5, task_num:5, tot_wait:45.169µs, tot_exec:205ns, tot_time:22.360232472s, max:4.472050302s, p95:4.472050302s} | funcs:count(Column#110)->Column#109, funcs:firstrow(Column#111)->anbme.payments.txnid, funcs:firstrow(Column#112)->anbme.payments.profileid, funcs:firstrow(Column#113)->anbme.payments.txn_status, funcs:firstrow(Column#114)->anbme.payments.txn_type, funcs:firstrow(Column#115)->anbme.payments.merchant_txn_date_time, funcs:firstrow(Column#116)->anbme.profiles.profileid, funcs:firstrow(Column#117)->anbme.profiles.merchant_type, funcs:firstrow(Column#118)->snf_db.rates.txnid, funcs:firstrow(Column#119)->snf_db.rates.bank_charge, funcs:firstrow(Column#120)->snf_db.rates.anb_charge | 28.7 KB | 0 Bytes |
| └─TableReader_105 | 1.00 | 2 | root | | time:4.47s, loops:2, cop_task: {num: 4, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00} | MppVersion: 2, data:ExchangeSender_104 | 2.74 KB | N/A |
| └─ExchangeSender_104 | 1.00 | 2 | mpp[tiflash] | | tiflash_task:{proc max:4.47s, min:4.46s, avg: 4.47s, p80:4.47s, p95:4.47s, iters:2, tasks:2, threads:2} | ExchangeType: PassThrough | N/A | N/A |
| └─HashAgg_17 | 1.00 | 2 | mpp[tiflash] | | tiflash_task:{proc max:4.47s, min:4.46s, avg: 4.47s, p80:4.47s, p95:4.47s, iters:2, tasks:2, threads:2} | funcs:count(1)->Column#110, funcs:firstrow(anbme.payments.txnid)->Column#111, funcs:firstrow(anbme.payments.profileid)->Column#112, funcs:firstrow(anbme.payments.txn_status)->Column#113, funcs:firstrow(anbme.payments.txn_type)->Column#114, funcs:firstrow(anbme.payments.merchant_txn_date_time)->Column#115, funcs:firstrow(anbme.profiles.profileid)->Column#116, funcs:firstrow(anbme.profiles.merchant_type)->Column#117, funcs:firstrow(snf_db.rates.txnid)->Column#118, funcs:firstrow(snf_db.rates.bank_charge)->Column#119, funcs:firstrow(snf_db.rates.anb_charge)->Column#120 | N/A | N/A |
| └─HashJoin_102 | 49408.25 | 1136 | mpp[tiflash] | | tiflash_task:{proc max:4.47s, min:4.46s, avg: 4.47s, p80:4.47s, p95:4.47s, iters:20505, tasks:2, threads:64} | left outer join, equal:[eq(anbme.payments.txnid, snf_db.rates.txnid)] | N/A | N/A |
| ├─ExchangeReceiver_34(Build) | 48942.88 | 1136 | mpp[tiflash] | | tiflash_task:{proc max:3s, min:3s, avg: 3s, p80:3s, p95:3s, iters:9, tasks:2, threads:64} | | N/A | N/A |
| │ └─ExchangeSender_33 | 48942.88 | 1136 | mpp[tiflash] | | tiflash_task:{proc max:3.01s, min:0s, avg: 1.5s, p80:3.01s, p95:3.01s, iters:23, tasks:2, threads:64} | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: anbme.payments.txnid, collate: binary] | N/A | N/A |
| │ └─HashJoin_26 | 48942.88 | 1136 | mpp[tiflash] | | tiflash_task:{proc max:3.01s, min:0s, avg: 1.5s, p80:3.01s, p95:3.01s, iters:23, tasks:2, threads:64} | inner join, equal:[eq(anbme.payments.profileid, anbme.profiles.profileid)] | N/A | N/A |
| │ ├─ExchangeReceiver_30(Build) | 47716.60 | 2272 | mpp[tiflash] | | tiflash_task:{proc max:2.99s, min:0s, avg: 1.49s, p80:2.99s, p95:2.99s, iters:43, tasks:2, threads:64} | | N/A | N/A |
| │ │ └─ExchangeSender_29 | 47716.60 | 1136 | mpp[tiflash] | | tiflash_task:{proc max:2.96s, min:0s, avg: 1.48s, p80:2.96s, p95:2.96s, iters:40, tasks:2, threads:64} | ExchangeType: Broadcast, Compression: FAST | N/A | N/A |
| │ │ └─Selection_28 | 47716.60 | 1136 | mpp[tiflash] | | tiflash_task:{proc max:2.96s, min:0s, avg: 1.48s, p80:2.96s, p95:2.96s, iters:40, tasks:2, threads:64} | eq(anbme.payments.txn_status, 1), ge(anbme.payments.merchant_txn_date_time, 1.759257e+09), le(anbme.payments.merchant_txn_date_time, 1.761935399e+09) | N/A | N/A |
| │ │ └─TableFullScan_27 | 411367.68 | 339593 | mpp[tiflash] | table:pts | tiflash_task:{proc max:2.96s, min:0s, avg: 1.48s, p80:2.96s, p95:2.96s, iters:13611, tasks:2, threads:64}, tiflash_wait: {pipeline_queue_wait: 79ms}, tiflash_scan:{mvcc_input_rows:1190341253, mvcc_input_bytes:20235801301, mvcc_output_rows:1141621277, lm_skip_rows:762311621, local_regions:19849, remote_regions:0, tot_learner_read:503ms, region_balance:{instance_num: 2, max/min: 9929/9920=1.000907}, delta_rows:41311418, delta_bytes:31214630524, segments:3009, stale_read_regions:0, tot_build_snapshot:15ms, tot_build_bitmap:88650ms, tot_build_inputstream:89885ms, min_local_stream:2329ms, max_local_stream:2659ms, dtfile:{data_scanned_rows:408000516, data_skipped_rows:1853265995, mvcc_scanned_rows:1174421121, mvcc_skipped_rows:1086845390, lm_filter_scanned_rows:1597443963, lm_filter_skipped_rows:663822548, tot_rs_index_check:507ms, tot_read:73319ms}} | pushed down filter:eq(anbme.payments.txn_type, "enach"), keep order:false | N/A | N/A |
| │ └─Selection_32(Probe) | 266683.25 | 28825 | mpp[tiflash] | | tiflash_task:{proc max:3.01s, min:0s, avg: 1.5s, p80:3.01s, p95:3.01s, iters:23, tasks:2, threads:64} | or(eq(anbme.profiles.merchant_type, "AQ"), eq(cast(anbme.profiles.merchant_type, double BINARY), 1)) | N/A | N/A |
| │ └─TableFullScan_31 | 332799.00 | 332798 | mpp[tiflash] | table:pls | tiflash_task:{proc max:3.01s, min:0s, avg: 1.5s, p80:3.01s, p95:3.01s, iters:38, tasks:2, threads:64}, tiflash_wait: {pipeline_breaker_wait: 2959ms}, tiflash_scan:{mvcc_input_rows:223395, mvcc_input_bytes:3797715, mvcc_output_rows:205030, lm_skip_rows:0, local_regions:2, remote_regions:0, tot_learner_read:0ms, region_balance:{instance_num: 2, max/min: 1/1=1.000000}, delta_rows:43049, delta_bytes:18753656, segments:2, stale_read_regions:0, tot_build_snapshot:0ms, tot_build_bitmap:14ms, tot_build_inputstream:15ms, min_local_stream:2959ms, max_local_stream:2979ms, dtfile:{data_scanned_rows:333916, data_skipped_rows:325720, mvcc_scanned_rows:206148, mvcc_skipped_rows:453488, lm_filter_scanned_rows:0, lm_filter_skipped_rows:0, tot_rs_index_check:0ms, tot_read:12ms}} | pushed down filter:empty, keep order:false | N/A | N/A |
| └─ExchangeReceiver_37(Probe) | 678890218.00 | 668496465 | mpp[tiflash] | | tiflash_task:{proc max:4.2s, min:4.18s, avg: 4.19s, p80:4.2s, p95:4.2s, iters:20476, tasks:2, threads:64}, tiflash_wait: {pipeline_breaker_wait: 2999ms, pipeline_queue_wait: 569ms} | | N/A | N/A |
| └─ExchangeSender_36 | 678890218.00 | 668496465 | mpp[tiflash] | | tiflash_task:{proc max:4.42s, min:0s, avg: 2.21s, p80:4.42s, p95:4.42s, iters:10527, tasks:2, threads:64} | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: snf_db.rates.txnid, collate: binary] | N/A | N/A |
| └─TableFullScan_35 | 678890218.00 | 668496465 | mpp[tiflash] | table:rat | tiflash_task:{proc max:518.7ms, min:0s, avg: 259.3ms, p80:518.7ms, p95:518.7ms, iters:10527, tasks:2, threads:64}, tiflash_wait: {pipeline_queue_wait: 479ms}, tiflash_scan:{mvcc_input_rows:8062182, mvcc_input_bytes:137057094, mvcc_output_rows:8054213, lm_skip_rows:0, local_regions:1733, remote_regions:0, tot_learner_read:42ms, region_balance:{instance_num: 2, max/min: 873/860=1.015116}, delta_rows:82423, delta_bytes:16484640, segments:918, stale_read_regions:0, tot_build_snapshot:2ms, tot_build_bitmap:797ms, tot_build_inputstream:1087ms, min_local_stream:99ms, max_local_stream:489ms, dtfile:{data_scanned_rows:668479222, data_skipped_rows:26160111, mvcc_scanned_rows:8057852, mvcc_skipped_rows:25826308, lm_filter_scanned_rows:0, lm_filter_skipped_rows:0, tot_rs_index_check:95ms, tot_read:11315ms}} | keep order:false | N/A | N/A |
+----------------------------------------------------+--------------+-----------+--------------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+
18 rows in set (4.55 sec)
@abisshekk5 looks like the top left join HashJoin_102 generate the in-consistency result.
Can you please
- check if there is any rows with
txnid = 1372599817in rat table(using tiflash replica)? - change the left join to inner join and rerun the query?
And aslo i notice that each time rat table has different row count, is it expected?
Hi @windtalker
- The table rat is have data for the txnid
mysql> SELECT /*+ READ_FROM_STORAGE(TIFLASH[snf_db.rat]) */ pts.TXN_TYPE, rat.anb_charge, rat.bank_charge, count(*),pls.PROFILEID,pts.PROFILEID,rat.txnid,pts.TXNID, pts.TXN_STATUS, pls.MERCHANT_TYPE, pts.MERCHANT_TXN_DATE_TIME, pts.TXN_TYPE from payments pts left join profiles pls on pls.PROFILEID = pts.PROFILEID left join snf_db.rates rat ON rat.txnid = pts.TXNID where pts.TXN_STATUS = 1 and pts.MERCHANT_TXN_DATE_TIME BETWEEN '1759257000' and '1761935399' and pts.TXN_TYPE = 'enach' and rat.txnid = 1372599817;
+----------+---------------+-------------+----------+-----------+-----------+------------+------------+------------+---------------+------------------------+----------+
| TXN_TYPE | anb_charge | bank_charge | count(*) | PROFILEID | PROFILEID | txnid | TXNID | TXN_STATUS | MERCHANT_TYPE | MERCHANT_TXN_DATE_TIME | TXN_TYPE |
+----------+---------------+-------------+----------+-----------+-----------+------------+------------+------------+---------------+------------------------+----------+
| enach | 0.00 | 10.00 | 1 | 320674 | 320674 | 1372599817 | 1372599817 | 1 | 1 | 1759955463 | enach |
+----------+---------------+-------------+----------+-----------+-----------+------------+------------+------------+---------------+------------------------+----------+
1 row in set (0.09 sec)
- No valid data set when using Innser join
mysql> SELECT /*+ READ_FROM_STORAGE(TIFLASH[snf_db.rat]) */ pts.TXN_TYPE, rat.anb_charge, rat.bank_charge, count(*),pls.PROFILEID,pts.PROFILEID,rat.txnid,pts.TXNID, pts.TXN_STATUS, pls.MERCHANT_TYPE, pts.MERCHANT_TXN_DATE_TIME, pts.TXN_TYPE from payments pts inner join profiles pls on pls.PROFILEID = pts.PROFILEID inner join snf_db.rates rat ON rat.txnid = pts.TXNID where pts.TXN_STATUS = 1 and pts.MERCHANT_TXN_DATE_TIME BETWEEN '1759257000' and '1761935399' and pts.TXN_TYPE = 'enach' and rat.txnid = 1372599817;
+----------+---------------+-------------+----------+-----------+-----------+-------+-------+------------+---------------+------------------------+----------+
| TXN_TYPE | anb_charge | bank_charge | count(*) | PROFILEID | PROFILEID | txnid | TXNID | TXN_STATUS | MERCHANT_TYPE | MERCHANT_TXN_DATE_TIME | TXN_TYPE |
+----------+---------------+-------------+----------+-----------+-----------+-------+-------+------------+---------------+------------------------+----------+
| NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+----------+---------------+-------------+----------+-----------+-----------+-------+-------+------------+---------------+------------------------+----------+
1 row in set (0.12 sec)
- Count is not changing each time when using TiKV
mysql> Select /*+ READ_FROM_STORAGE(TIKV[snf_db.rates]) */ count(*) from snf_db.rates;
+-----------+
| count(*) |
+-----------+
| 678862699 |
+-----------+
1 row in set (23.09 sec)
mysql> Select /*+ READ_FROM_STORAGE(TIKV[snf_db.rates]) */ count(*) from snf_db.rates;
+-----------+
| count(*) |
+-----------+
| 678862699 |
+-----------+
1 row in set (20.01 sec)
Count Changing when using TiFlash
mysql> Select /*+ READ_FROM_STORAGE(TIFLASH[snf_db.rates]) */ count(*) from snf_db.rates;
+-----------+
| count(*) |
+-----------+
| 667512427 |
+-----------+
1 row in set (0.11 sec)
mysql> Select /*+ READ_FROM_STORAGE(TIFLASH[snf_db.rates]) */ count(*) from snf_db.rates;
+-----------+
| count(*) |
+-----------+
| 669277813 |
+-----------+
1 row in set (0.09 sec)
mysql> Select /*+ READ_FROM_STORAGE(TIFLASH[snf_db.rates]) */ count(*) from snf_db.rates;
+-----------+
| count(*) |
+-----------+
| 669668484 |
+-----------+
1 row in set (0.10 sec)
@abisshekk5 its really weird, and i think i need tiflash log to further investigate this issue, could you please run explain analyze Select /*+ READ_FROM_STORAGE(TIFLASH[snf_db.rates]) */ count(*) from snf_db.rates; multiple times and provide the tiflash log?
And if your TiDB cluster is a newly deployed v8.5.4 cluster or an old cluster upgrade to v8.5.4? if it is upgraded to 8.5.4, is this issue happened before upgrade?
Hi @windtalker ,
The cluster is an old cluster which is upgraded recently from the version v7.5.0 to v8.5.4 and the issue was identified in the v7.5.0. We expect this behaviour is related to the issue https://github.com/pingcap/tidb/issues/49241 and will resolve once upgrade to the latest version.
The logs are attached.
Hi @abisshekk5 ,
I notice that you mention this issue was identified in the v7.5.0. And from your description above, I understand that the row count returned by select /*+ READ_FROM_STORAGE(TIFLASH[snf_db.rates]) */ count(*) from snf_db.rates is inconsistent and consistently lower than the result from TiKV. This typically occurs when some TiFlash regions have lost data on certain replicas—i.e., partial data loss across a subset of nodes hosting region replicas. As a result, different queries may select different combinations of region replicas, leading to randomly missing rows from specific regions in the final aggregated count.
This issue is likely related to known bugs in TiFlash version v7.5.0 (https://github.com/pingcap/tiflash/issues/8777 or https://github.com/pingcap/tiflash/issues/8695). Could you please confirm relevant DDL history by running:
ADMIN SHOW DDL JOBS 10000 WHERE table_name = 'rates';
and look for any entries where JOB_TYPE = "set tiflash replica".
Hi @JaySon-Huang
Yes. But the bug related issue also we experienced before, like when we use the TiFlash hint it returned empty set for some queries. However after the upgrade that bug has been fixed in the latest version. But this data inconsistency is still persists. Below is the ddl job output.
mysql> ADMIN SHOW DDL JOBS 10000 WHERE table_name = 'rates' and job_type ='set tiflash replica';
+--------+--------------+-------------------+---------------------+--------------+-----------+----------+-----------+----------------------------+----------------------------+----------------------------+--------+----------+
| JOB_ID | DB_NAME | TABLE_NAME | JOB_TYPE | SCHEMA_STATE | SCHEMA_ID | TABLE_ID | ROW_COUNT | CREATE_TIME | START_TIME | END_TIME | STATE | COMMENTS |
+--------+--------------+-------------------+---------------------+--------------+-----------+----------+-----------+----------------------------+----------------------------+----------------------------+--------+----------+
| 15474 | snf_db | rates | set tiflash replica | public | 13827 | 13853 | 0 | 2025-12-10 12:58:26.030000 | 2025-12-10 12:58:26.030000 | 2025-12-10 12:58:26.079000 | synced | |
| 15462 | snf_db | rates | set tiflash replica | public | 13827 | 13853 | 0 | 2025-12-10 11:50:43.279000 | 2025-12-10 11:50:43.329000 | 2025-12-10 11:50:43.429000 | synced | |
| 15460 | snf_db | rates | set tiflash replica | public | 13827 | 13853 | 0 | 2025-12-10 11:37:00.729000 | 2025-12-10 11:37:00.779000 | 2025-12-10 11:37:00.829000 | synced | |
| 15429 | snf_db | rates | set tiflash replica | public | 13827 | 13853 | 0 | 2025-12-10 11:31:26.080000 | 2025-12-10 11:31:26.080000 | 2025-12-10 11:31:26.179000 | synced | |
| 14253 | snf_db | rates | set tiflash replica | public | 13827 | 13853 | 0 | 2025-11-14 16:19:59.814000 | 2025-11-14 16:19:59.863000 | 2025-11-14 16:20:00.013000 | synced | |
| 14252 | snf_db | rates | set tiflash replica | public | 13827 | 13853 | 0 | 2025-11-14 16:16:11.963000 | 2025-11-14 16:16:12.013000 | 2025-11-14 16:16:12.113000 | synced | |
| 14186 | snf_db | rates | set tiflash replica | public | 13827 | 13853 | 0 | 2025-11-13 23:50:12.614000 | 2025-11-13 23:50:12.663000 | 2025-11-13 23:50:12.763000 | synced | |
| 14146 | snf_db | rates | set tiflash replica | public | 13827 | 13853 | 0 | 2025-11-13 17:23:22.164000 | 2025-11-13 17:23:22.213000 | 2025-11-13 17:23:23.813000 | synced | |
| 14078 | snf_db | rates | set tiflash replica | public | 13827 | 13853 | 0 | 2025-11-13 16:28:20.013000 | 2025-11-13 16:28:20.063000 | 2025-11-13 16:28:20.313000 | synced | |
@abisshekk5
- Do you remember when do you upgrade the cluster to v8.5.4?
- I see that you wrote you tried remove the TiFlash replica of
sfn_db.t_reportand then recreated it. Have you try to recreatesnf_db.ratestiflash replica after upgraded to v8.5.4?
And here is some tips for you to recreate the tiflash replica:
- Confirm the Region peer distribution of a given table the following sql show the Region peer distribution of a give table. Because your cluster consists of 3 TiKV and 2 TiFlash, for a table with tiflash replica num setting to 2, you should see 3 rows with store_type='tikv' and 2 rows with store_type = 'tiflash'.
-- Non-partitioned table
select TABLE_ID, p.STORE_ID, ADDRESS, count(p.REGION_ID),
CASE WHEN JSON_CONTAINS(s.LABEL, JSON_OBJECT('key', 'engine', 'value', 'tiflash'))
THEN 'tiflash' ELSE 'tikv'END AS store_type, s.LABEL
from
information_schema.tikv_region_status r,
information_schema.tikv_region_peers p,
information_schema.tikv_store_status s
where r.db_name = '<db_name>' and r.table_name = '<table_name>'
and r.region_id = p.region_id and p.store_id = s.store_id
group by TABLE_ID, p.STORE_ID, s.LABEL, ADDRESS;
- Set tiflash replica to 0 and wait until all the Region peers on TiFlash are removed (important)
alter table <db_name>.<table_name> set tiflash replica 0;
-- And check the Region peer distribution until all region of the given table are removed from TiFlash. So there is no rows with store_type = 'tiflash'.
select TABLE_ID, p.STORE_ID, ADDRESS, count(p.REGION_ID),
CASE WHEN JSON_CONTAINS(s.LABEL, JSON_OBJECT('key', 'engine', 'value', 'tiflash'))
THEN 'tiflash' ELSE 'tikv'END AS store_type, s.LABEL
from
information_schema.tikv_region_status r,
information_schema.tikv_region_peers p,
information_schema.tikv_store_status s
where r.db_name = '<db_name>' and r.table_name = '<table_name>'
and r.region_id = p.region_id and p.store_id = s.store_id
group by TABLE_ID, p.STORE_ID, s.LABEL, ADDRESS;
- Re-create the tiflash replica
alter table <db_name>.<table_name> set tiflash replica 2;
We hope that "waiting until all Region peers have been fully removed from TiFlash before re-adding replicas" will resolve this data inconsistency issue.
However, if the problem persists, please use tiup diag to collect monitoring data, logs, and other diagnostic information from the period after the TiFlash replicas were removed until the re-addition of TiFlash replicas is complete. Upload the diag data to PingCAP Clinic, and we will conduct a deeper investigation into the issue. You can checkout the steps of using tiup diag here https://docs.pingcap.com/tidb/stable/clinic-user-guide-for-tiup/
Hi @JaySon-Huang ,
- We have upgraded the cluster on Dec 10.
- Yes. We have recreated the TiFLash replica for that table both before and after the Upgrade.
Do you suggest that we recreate the TiFlash replica for that table again?
Hi @abisshekk5 , Yes, I suspect that during your last attempt to rebuild the TiFlash replicas, some Region peers were not fully removed from the TiFlash instance. As a result, certain Regions may still be in an inconsistent state due to residual data issues introduced in v7.5.0, and this inconsistency hasn’t been properly resolved. Therefore, we recommend following the guidance above: wait until all Regions have been completely removed from the TiFlash instance before attempting to rebuild the TiFlash replicas again.
Hi @JaySon-Huang
Thank you for the information. I will try the recommended steps and update here.