Skip to content
New issue

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

Sign up for GitHub

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

Already on GitHub? Sign in to your account

Jump to bottom

Optimizer can not generate mpp plan when the index on generated column is visible #47766

Closed
dbsid opened this issue Oct 18, 2023 · 4 comments · Fixed by #48041
Closed

Optimizer can not generate mpp plan when the index on generated column is visible #47766

dbsid opened this issue Oct 18, 2023 · 4 comments · Fixed by #48041
Assignees
@AilinKid
Labels
severity/moderate sig/planner SIG: Planner type/question The issue belongs to a question.

Comments

@dbsid
Copy link
Contributor

dbsid commented Oct 18, 2023

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

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

mpp plan

3. What did you see instead (Required)

the table trades is access by index rather than by TiFlash mpp plan.

mysql> explain analyze with logs_summary as (
    ->  select date(test_time) as test_date,
    ->   count(*) as log_count,
    ->   count(distinct transaction_hash) as tx_count_log
    ->  from logs 
    ->  where date(test_time) in ('2023-09-11', '2023-09-12')
    ->  group by 1 
    -> ),
    -> trades_summary as (
    ->  select date(test_time) as test_date,
    ->   count(*) as trace_count,
    ->   count(distinct transaction_hash) as tx_count_trace
    ->  from trades
    ->  where date(test_time) in ('2023-09-11', '2023-09-12') 
    ->  group by 1
    -> )
    -> select l.test_date, l.log_count, l.tx_count_log, t.trace_count, t.tx_count_trace
    -> from logs_summary l 
    -> inner join trades_summary t on l.test_date = t.test_date;

alter table trades alter index trades_date_idx visible;
+------------------------------------------------+----------------+--------------+-----------+--------------+-----------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+
| id                                             | estCost        | estRows      | actRows   | task         | access object                                       | execution info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       | memory    | disk    |
+------------------------------------------------+----------------+--------------+-----------+--------------+-----------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+
| Projection_27                                  | 23571646873.87 | 1.00         | 2         | root         |                                                     | time:4m45.3s, loops:2, RU:1238157.818600, Concurrency:OFF                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            | 1.86 KB   | N/A     |
| └─HashJoin_31                                  | 23571646873.37 | 1.00         | 2         | root         |                                                     | time:4m45.3s, loops:2, build_hash_table:{total:4m45.3s, fetch:4m45.3s, build:9.01µs}, probe:{concurrency:5, total:23m46.7s, max:4m45.3s, probe:15.3µs, fetch:23m46.7s}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               | 25.9 KB   | 0 Bytes |
|   ├─HashAgg_32(Build)                          | 22587947900.68 | 1.00         | 2         | root         |                                                     | time:4m45.3s, loops:2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                | 247.2 MB  | 0 Bytes |
|   │ └─Projection_46                            | 22036888876.61 | 11021194.21  | 10978886  | root         |                                                     | time:4m39.1s, loops:10723, Concurrency:5                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             | 1.54 MB   | N/A     |
|   │   └─IndexLookUp_45                         | 22034689046.24 | 11021194.21  | 10978886  | root         |                                                     | time:4m41.5s, loops:10723, index_task: {total_time: 4m44s, fetch_handle: 12s, build: 1.3ms, wait: 4m32s}, table_task: {total_time: 23m44.6s, num: 540, concurrency: 5}, next: {wait_index: 15.2ms, wait_table_lookup_build: 3.56ms, wait_table_lookup_resp: 4m40.2s}                                                                                                                                                                                                                                                                                                                                                                                                                                                 | 95.3 MB   | N/A     |
|   │     ├─Selection_44(Build)                  | 4630994555.75  | 11021194.21  | 10978886  | cop[tikv]    |                                                     | time:51.3ms, loops:10763, cop_task: {num: 352, max: 2.27s, min: 1.57ms, avg: 195.2ms, p95: 1.11s, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 15.8s, tot_wait: 601.4ms, rpc_num: 352, rpc_time: 1m8.7s, copr_cache: disabled, build_task_duration: 9.29ms, max_distsql_concurrency: 15}, tikv_task:{proc max:2.24s, min:0s, avg: 179ms, p80:110ms, p95:1.08s, iters:12120, tasks:352}, scan_detail: {total_process_keys: 10978886, total_process_keys_size: 1831781141, total_keys: 10979239, get_snapshot_time: 492.7ms, rocksdb: {key_skipped_count: 10978886, test: {cache_hit_count: 4093, read_count: 31980, read_byte: 124.5 MB, read_time: 613ms}}}                                                | N/A       | N/A     |
|   │     │ └─IndexRangeScan_42                  | 4081036964.77  | 11021194.21  | 10978886  | cop[tikv]    | table:trades, index:trades_date_idx(test_time_gen) | tikv_task:{proc max:2.24s, min:0s, avg: 177.6ms, p80:110ms, p95:1.08s, iters:12120, tasks:352}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       | N/A       | N/A     |
|   │     └─TableRowIDScan_43(Probe)             | 5846215848.45  | 11021194.21  | 10978886  | cop[tikv]    | table:trades                                        | time:23m37.4s, loops:12806, cop_task: {num: 6132, max: 5.69s, min: 0s, avg: 1.14s, p95: 3.42s, max_proc_keys: 11122, p95_proc_keys: 3051, tot_proc: 43m39.6s, tot_wait: 29s, rpc_num: 6131, rpc_time: 1h56m21.6s, copr_cache: disabled, build_task_duration: 3s, max_distsql_concurrency: 15, max_extra_concurrency: 1, store_batch_num: 1}, tikv_task:{proc max:1.11s, min:0s, avg: 1.12s, p80:2.05s, p95:3.4s, iters:37928, tasks:6132}, scan_detail: {total_process_keys: 10978886, total_process_keys_size: 21636960857, total_keys: 21957772, get_snapshot_time: 25s, rocksdb: {key_skipped_count: 10978886, test: {cache_hit_count: 240313674, read_count: 2516914, read_byte: 21.5 GB, read_time: 22m9.5s}}} | N/A       | N/A     |
|   └─TableReader_85(Probe)                      | 977137453.36   | 314715.41    | 2         | root         |                                                     | time:1.72s, loops:2, cop_task: {num: 3, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache: disabled}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    | 707 Bytes | N/A     |
|     └─ExchangeSender_84                        | 14639708911.78 | 314715.41    | 2         | mpp[tiflash] |                                                     | tiflash_task:{time:1.72s, loops:2, threads:8}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        | N/A       | N/A     |
|       └─Projection_53                          | 14639708911.78 | 314715.41    | 2         | mpp[tiflash] |                                                     | tiflash_task:{time:1.72s, loops:2, threads:8}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        | N/A       | N/A     |
|         └─Selection_83                         | 14639548003.18 | 314715.41    | 2         | mpp[tiflash] |                                                     | tiflash_task:{time:1.72s, loops:2, threads:8}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        | N/A       | N/A     |
|           └─Projection_77                      | 14638562043.60 | 393394.26    | 2         | mpp[tiflash] |                                                     | tiflash_task:{time:1.72s, loops:2, threads:8}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        | N/A       | N/A     |
|             └─HashAgg_78                       | 14638556127.85 | 393394.26    | 2         | mpp[tiflash] |                                                     | tiflash_task:{time:1.72s, loops:2, threads:8}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        | N/A       | N/A     |
|               └─ExchangeReceiver_80            | 14637075144.56 | 393394.26    | 1246460   | mpp[tiflash] |                                                     | tiflash_task:{time:1.65s, loops:66, threads:8}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       | N/A       | N/A     |
|                 └─ExchangeSender_79            | 14415234239.39 | 393394.26    | 1246460   | mpp[tiflash] |                                                     | tiflash_task:{time:1.64s, loops:256, threads:8}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      | N/A       | N/A     |
|                   └─HashAgg_76                 | 14415234239.39 | 393394.26    | 1246460   | mpp[tiflash] |                                                     | tiflash_task:{time:1.6s, loops:256, threads:8}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       | N/A       | N/A     |
|                     └─Projection_88            | 15473877639.22 | 139337405.60 | 4841107   | mpp[tiflash] |                                                     | tiflash_task:{time:1.33s, loops:82, threads:8}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       | N/A       | N/A     |
|                       └─Selection_66           | 13992662032.60 | 139337405.60 | 4841107   | mpp[tiflash] |                                                     | tiflash_task:{time:1.32s, loops:82, threads:8}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       | N/A       | N/A     |
|                         └─TableFullScan_65     | 13556137334.20 | 174171757.00 | 174174202 | mpp[tiflash] | table:logs                                          | tiflash_task:{time:1.19s, loops:2866, threads:8}, tiflash_scan:{dtfile:{total_scanned_packs:22828, total_skipped_packs:300, total_scanned_rows:181479788, total_skipped_rows:2447654, total_rs_index_load_time: 5ms, total_read_time: 7992ms}, total_create_snapshot_time: 1ms, total_local_region_num: 0, total_remote_region_num: 0}                                                                                                                                                                                                                                                                                                                                                                               | N/A       | N/A     |
+------------------------------------------------+----------------+--------------+-----------+--------------+-----------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+
20 rows in set, 3 warnings (4 min 46.45 sec)


mysql> show warnings;
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                    |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1105 | Aggregation can not be pushed to tiflash because expressions of AggFunc `firstrow` contain virtual column or correlated column, which is not supported now |
| Warning | 1105 | Aggregation can not be pushed to tiflash because expressions of AggFunc `firstrow` contain virtual column or correlated column, which is not supported now |
| Warning | 1105 | Aggregation can not be pushed to tiflash because expressions of AggFunc `firstrow` contain virtual column or correlated column, which is not supported now |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.07 sec)

alter table trades alter index trades_date_idx invisible;

| id                                                   | estCost        | estRows      | actRows   | task         | access object | execution info                                                                                                                                                                                                                                                                                                                          | memory  | disk |
+------------------------------------------------------+----------------+--------------+-----------+--------------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
| TableReader_118                                      | 1892486558.44  | 218439.68    | 2         | root         |               | time:4.11s, loops:2, RU:0.000000, cop_task: {num: 3, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache: disabled}                                                                                                                                                                                                                          | 1.51 KB | N/A  |
| └─ExchangeSender_117                                 | 28368075684.72 | 218439.68    | 2         | mpp[tiflash] |               | tiflash_task:{time:4.11s, loops:2, threads:8}                                                                                                                                                                                                                                                                                           | N/A     | N/A  |
|   └─Projection_116                                   | 28368070442.17 | 218439.68    | 2         | mpp[tiflash] |               | tiflash_task:{time:4.11s, loops:2, threads:8}                                                                                                                                                                                                                                                                                           | N/A     | N/A  |
|     └─HashJoin_113                                   | 28368065199.62 | 218439.68    | 2         | mpp[tiflash] |               | tiflash_task:{time:4.11s, loops:2, threads:8}                                                                                                                                                                                                                                                                                           | N/A     | N/A  |
|       ├─ExchangeReceiver_45(Build)                   | 13716407884.74 | 218439.68    | 2         | mpp[tiflash] |               | tiflash_task:{time:4.11s, loops:2, threads:8}                                                                                                                                                                                                                                                                                           | N/A     | N/A  |
|       │ └─ExchangeSender_44                          | 13711165332.42 | 218439.68    | 2         | mpp[tiflash] |               | tiflash_task:{time:4.11s, loops:2, threads:8}                                                                                                                                                                                                                                                                                           | N/A     | N/A  |
|       │   └─Projection_34                            | 13711165332.42 | 218439.68    | 2         | mpp[tiflash] |               | tiflash_task:{time:4.11s, loops:2, threads:8}                                                                                                                                                                                                                                                                                           | N/A     | N/A  |
|       │     └─Selection_36                           | 13711058384.35 | 218439.68    | 2         | mpp[tiflash] |               | tiflash_task:{time:4.11s, loops:2, threads:8}                                                                                                                                                                                                                                                                                           | N/A     | N/A  |
|       │       └─Projection_40                        | 13710403065.31 | 273049.60    | 2         | mpp[tiflash] |               | tiflash_task:{time:4.11s, loops:2, threads:8}                                                                                                                                                                                                                                                                                           | N/A     | N/A  |
|       │         └─HashAgg_41                         | 13710399133.39 | 273049.60    | 2         | mpp[tiflash] |               | tiflash_task:{time:4.11s, loops:2, threads:8}                                                                                                                                                                                                                                                                                           | N/A     | N/A  |
|       │           └─ExchangeReceiver_43              | 13709414772.20 | 273049.60    | 2114576   | mpp[tiflash] |               | tiflash_task:{time:3.94s, loops:68, threads:8}                                                                                                                                                                                                                                                                                          | N/A     | N/A  |
|       │             └─ExchangeSender_42              | 13684840308.20 | 273049.60    | 2114576   | mpp[tiflash] |               | tiflash_task:{time:3.92s, loops:256, threads:8}                                                                                                                                                                                                                                                                                         | N/A     | N/A  |
|       │               └─HashAgg_37                   | 13684840308.20 | 273049.60    | 2114576   | mpp[tiflash] |               | tiflash_task:{time:3.88s, loops:256, threads:8}                                                                                                                                                                                                                                                                                         | N/A     | N/A  |
|       │                 └─Projection_119             | 14692217819.84 | 138182886.40 | 10978886  | mpp[tiflash] |               | tiflash_task:{time:3.43s, loops:2987, threads:8}                                                                                                                                                                                                                                                                                        | N/A     | N/A  |
|       │                   └─Selection_39             | 13285571309.44 | 138182886.40 | 10978886  | mpp[tiflash] |               | tiflash_task:{time:3.43s, loops:2987, threads:8}                                                                                                                                                                                                                                                                                        | N/A     | N/A  |
|       │                     └─TableFullScan_38       | 12871022650.24 | 172728608.00 | 172728608 | mpp[tiflash] | table:trades  | tiflash_task:{time:3.21s, loops:2988, threads:8}, tiflash_scan:{dtfile:{total_scanned_packs:22138, total_skipped_packs:93, total_scanned_rows:172752507, total_skipped_rows:733099, total_rs_index_load_time: 10ms, total_read_time: 9488ms}, total_create_snapshot_time: 2ms, total_local_region_num: 0, total_remote_region_num: 0}   | N/A     | N/A  |
|       └─ExchangeReceiver_58(Probe)                   | 14650694466.47 | 327531.34    | 2         | mpp[tiflash] |               | tiflash_task:{time:1.13ms, loops:2, threads:8}                                                                                                                                                                                                                                                                                          | N/A     | N/A  |
|         └─ExchangeSender_57                          | 14642804930.69 | 327531.34    | 2         | mpp[tiflash] |               | tiflash_task:{time:3.3s, loops:2, threads:8}                                                                                                                                                                                                                                                                                            | N/A     | N/A  |
|           └─Projection_47                            | 14642804930.69 | 327531.34    | 2         | mpp[tiflash] |               | tiflash_task:{time:3.3s, loops:2, threads:8}                                                                                                                                                                                                                                                                                            | N/A     | N/A  |
|             └─Selection_49                           | 14642643984.16 | 327531.34    | 2         | mpp[tiflash] |               | tiflash_task:{time:3.3s, loops:2, threads:8}                                                                                                                                                                                                                                                                                            | N/A     | N/A  |
|               └─Projection_53                        | 14641657792.19 | 409414.17    | 2         | mpp[tiflash] |               | tiflash_task:{time:3.3s, loops:2, threads:8}                                                                                                                                                                                                                                                                                            | N/A     | N/A  |
|                 └─HashAgg_54                         | 14641651875.04 | 409414.17    | 2         | mpp[tiflash] |               | tiflash_task:{time:3.3s, loops:2, threads:8}                                                                                                                                                                                                                                                                                            | N/A     | N/A  |
|                   └─ExchangeReceiver_56              | 14640170542.69 | 409414.17    | 1246460   | mpp[tiflash] |               | tiflash_task:{time:3.23s, loops:66, threads:8}                                                                                                                                                                                                                                                                                          | N/A     | N/A  |
|                     └─ExchangeSender_55              | 14418277348.84 | 409414.17    | 1246460   | mpp[tiflash] |               | tiflash_task:{time:3.23s, loops:256, threads:8}                                                                                                                                                                                                                                                                                         | N/A     | N/A  |
|                       └─HashAgg_50                   | 14418277348.84 | 409414.17    | 1246460   | mpp[tiflash] |               | tiflash_task:{time:3.21s, loops:256, threads:8}                                                                                                                                                                                                                                                                                         | N/A     | N/A  |
|                         └─Projection_120             | 15477170274.65 | 145011542.40 | 4841107   | mpp[tiflash] |               | tiflash_task:{time:2.73s, loops:82, threads:8}                                                                                                                                                                                                                                                                                          | N/A     | N/A  |
|                           └─Selection_52             | 13995605540.33 | 145011542.40 | 4841107   | mpp[tiflash] |               | tiflash_task:{time:2.73s, loops:82, threads:8}                                                                                                                                                                                                                                                                                          | N/A     | N/A  |
|                             └─TableFullScan_51       | 13558977951.53 | 181264428.00 | 181298674 | mpp[tiflash] | table:logs    | tiflash_task:{time:2.62s, loops:3149, threads:8}, tiflash_scan:{dtfile:{total_scanned_packs:25957, total_skipped_packs:466, total_scanned_rows:206890743, total_skipped_rows:3807526, total_rs_index_load_time: 5ms, total_read_time: 10265ms}, total_create_snapshot_time: 1ms, total_local_region_num: 0, total_remote_region_num: 0} | N/A     | N/A  |
+------------------------------------------------------+----------------+--------------+-----------+--------------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
28 rows in set (7.01 sec)

4. What is your TiDB version? (Required)

v7.1.1
@dbsid dbsid added the type/bug The issue is confirmed as a bug. label Oct 18, 2023
@fixdb
Copy link
Contributor

fixdb commented Oct 19, 2023

/assign @AilinKid

@AilinKid
Copy link
Contributor

since the table schema is unclear, my possible guess is that: once the index path is visible, on our first optimizing rule: gcSubstituter, it will collect all the virtual column from all possible index path, obviously, this index column may contain a virtual column.

func collectGenerateColumn(lp LogicalPlan, exprToColumn ExprColumnMap) {
	for _, child := range lp.Children() {
		collectGenerateColumn(child, exprToColumn)
	}
	ds, ok := lp.(*DataSource)
	if !ok {
		return
	}
	for _, p := range ds.possibleAccessPaths {
		if p.IsTablePath() {
			continue
		}
		for _, idxPart := range p.Index.Columns {
			colInfo := ds.tableInfo.Columns[idxPart.Offset]
			if colInfo.IsGenerated() && !colInfo.GeneratedStored {
				s := ds.schema.Columns
				col := expression.ColInfo2Col(s, colInfo)
				if col != nil && col.GetType().PartialEqual(col.VirtualExpr.GetType(), lp.SCtx().GetSessionVars().EnableUnsafeSubstitute) {
					exprToColumn[col.VirtualExpr] = col
				}
			}
		}
	}
}

Then, it will use this virtual column map, and try to substitute all the usage place down from tree root node, obviously the agg's arg has been substituted with the generated column to leverage the index if possible, but for latter check of tiflash path, we found that the agg's arg is a generate column is tiflash property enumeration is abandoned.

@AilinKid
Copy link
Contributor

KEY traces_date_idx (block_time_gen)

and we see

block_time_gen date GENERATED ALWAYS AS (date(block_time)) VIRTUAL,

and we see in the query select list, there is a item

date(block_time)

so it is clear as I said before, In the original query with KEY traces_date_idx (block_time_gen) is visible, there is possible access index path of traces_date_idx. So in the logical rule of gcSubstituter, we try to substitute all the expression of date(block_time) in the plan tree to the generated column as block_time_gen in case of the leveraging of index scan. The idea is good, and indeed it works with index range scan of trades_date_idx with task type as tikv.

Note that, when this generated column substitution is finished, the frist_row(date(block_time)) in aggregate will be shown as first_row(block_time_gen), the arg is a virtual column, which couldn't be pushed to tiflash just as the warnings said.

@AilinKid
Copy link
Contributor

AilinKid commented Oct 19, 2023 •
edited
Loading

Just show other plan enumeration with nth_plan, after searching, we found /*+ nth_plan(12) */

MySQL [test]> explain with logs_summary as (   select date(block_time) as block_date,    count(*) as log_count,    count(distinct transaction_hash) as transctiion_count_log   from logs    where date(block_time) in ('2023-09-11', '2023-09-12')   group by 1   ),  traces_summary as (   select date(block_time) as block_date,    count(*) as trace_count,    count(distinct transaction_hash) as 
transctiion_count_trace   from traces   where date(block_time) in ('2023-09-11', '2023-09-12')    group by 1  )  select /*+ nth_plan(12) */ l.block_date, l.log_count, l.transctiion_count_log, t.trace_count, t.transctiion_count_trace  from logs_summary l   inner join traces_summary t on l.block_date = t.block_date;
+--------------------------------------------------+----------+--------------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                                               | estRows  | task         | access object | operator info                                                                                                                                                                                           |
+--------------------------------------------------+----------+--------------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Projection_27                                    | 12.80    | root         |               | Column#90, Column#88, Column#89, Column#132, Column#133                                                                                                                                                 |
| └─HashJoin_30                                    | 12.80    | root         |               | inner join, equal:[eq(test.traces.block_time_gen, Column#90)]                                                                                                                                           |
|   ├─TableReader_75(Build)                        | 5120.00  | root         |               | MppVersion: 1, data:ExchangeSender_74                                                                                                                                                                   |
|   │ └─ExchangeSender_74                          | 5120.00  | mpp[tiflash] |               | ExchangeType: PassThrough                                                                                                                                                                               |
|   │   └─Projection_43                            | 5120.00  | mpp[tiflash] |               | date(test.logs.block_time)->Column#90, Column#88, Column#89                                                                                                                                             |
|   │     └─Selection_73                           | 5120.00  | mpp[tiflash] |               | not(isnull(date(test.logs.block_time)))                                                                                                                                                                 |
|   │       └─Projection_67                        | 6400.00  | mpp[tiflash] |               | Column#88, Column#89, test.logs.block_time                                                                                                                                                              |
|   │         └─HashAgg_68                         | 6400.00  | mpp[tiflash] |               | group by:Column#170, funcs:sum(Column#171)->Column#88, funcs:count(distinct test.logs.transaction_hash)->Column#89, funcs:firstrow(Column#172)->test.logs.block_time, stream_count: 10                  |
|   │           └─ExchangeReceiver_70              | 6400.00  | mpp[tiflash] |               | stream_count: 10                                                                                                                                                                                        |
|   │             └─ExchangeSender_69              | 6400.00  | mpp[tiflash] |               | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: Column#170, collate: binary], stream_count: 10                                                                                        |
|   │               └─HashAgg_66                   | 6400.00  | mpp[tiflash] |               | group by:Column#174, Column#175, funcs:count(1)->Column#171, funcs:firstrow(Column#173)->Column#172                                                                                                     |
|   │                 └─Projection_76              | 8000.00  | mpp[tiflash] |               | test.logs.block_time, date(test.logs.block_time)->Column#174, test.logs.transaction_hash                                                                                                                |
|   │                   └─Selection_56             | 8000.00  | mpp[tiflash] |               | in(date(test.logs.block_time), 2023-09-11 00:00:00.000000, 2023-09-12 00:00:00.000000)                                                                                                                  |
|   │                     └─TableFullScan_55       | 10000.00 | mpp[tiflash] | table:logs    | pushed down filter:empty, keep order:false, stats:pseudo                                                                                                                                                |
|   └─HashAgg_32(Probe)                            | 12.80    | root         |               | group by:test.traces.block_time_gen, funcs:count(1)->Column#132, funcs:count(distinct test.traces.transaction_hash)->Column#133, funcs:firstrow(test.traces.block_time_gen)->test.traces.block_time_gen |
|     └─Selection_41                               | 20.00    | root         |               | in(test.traces.block_time_gen, 2023-09-11 00:00:00.000000, 2023-09-12 00:00:00.000000), not(isnull(test.traces.block_time_gen))                                                                         |
|       └─Projection_40                            | 10000.00 | root         |               | test.traces.transaction_hash, test.traces.block_time_gen                                                                                                                                                |
|         └─TableReader_39                         | 10000.00 | root         |               | data:TableFullScan_38                                                                                                                                                                                   |
|           └─TableFullScan_38                     | 10000.00 | cop[tiflash] | table:traces  | keep order:false, stats:pseudo                                                                                                                                                                          |
+--------------------------------------------------+----------+--------------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
19 rows in set, 10 warnings (0.017 sec)

this plan can be generated to leverage the most capability of tiflash, rather than use index in tikv, since block_time_gen is a virtual column, which should be filled in TiDB ROOT TABLE_READER, so the sub-tree of table traces is what shows above even with tiflash property, but it fails in cost model with TiKV index path, because it just a full scan and most operator is done in TiDB

@seiya-annie seiya-annie added type/question The issue belongs to a question. and removed type/bug The issue is confirmed as a bug. labels Oct 23, 2023
@AilinKid AilinKid mentioned this issue Oct 27, 2023
Merged
13 tasks
ti-chi-bot bot pushed a commit that referenced this issue Oct 27, 2023
@AilinKid
planner: fix mpp task spreading blocked by virtual column substitution (
bf1913e 
#48041)

close #47766
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@AilinKid AilinKid

Labels
severity/moderate sig/planner SIG: Planner type/question The issue belongs to a question.
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

planner: fix mpp task spreading blocked by virtual column substitution AilinKid/tidb
4 participants
@fixdb @dbsid @AilinKid @seiya-annie