Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: tiup bench tpcc 对SQL加了HINT
|
username: h5n1
When using tiup bench tpcc for testing, I found that some SQL statements were automatically added with HINT. Is this pre-added by tiup?
I feel this is somewhat cheating, as it is not relying on the optimizer to generate the plan by itself. What is the consideration for setting this SQL separately like this?
The INL_JOIN(t1_name [, tl_name ...]) hint instructs the optimizer to use the Index Nested Loop Join algorithm for the specified tables. This algorithm may be faster and consume fewer system resources in some scenarios, while in others it may be slower and consume more resources. It is advisable to try using this hint in scenarios where the result set of the outer table, after being filtered by the WHERE condition, is relatively small (less than 10,000 rows).
The parameters in INL_JOIN() are the candidate tables for the inner table when constructing the query plan. For example, INL_JOIN(t1) will only consider using t1 as the inner table when constructing the query plan. If a table has an alias, only the alias can be used as the parameter for INL_JOIN(). If no alias is specified, the table’s original name should be used as the parameter. For instance, in SELECT /*+ INL_JOIN(t1) */ * FROM t t1, t t2 WHERE t1.a = t2.b;, the parameters for INL_JOIN() can only be the aliases t1 or t2, not the original table name t.
Cheat first 
, and then remove it when the optimizer comes out 
When performing the TPC-H test, I found that all generated SQL statements have 0 actrows, resulting in very fast displayed execution times.
Commands:
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: tiup bench tpch prepare --sf 10 -H10.125 -P4000 -Uolap -p4xxxxx -D olap --threads 24
tiup bench tpch run --sf 10 -H10.125 -P4000 -Uolap -p4xxxxx -D olap --threads 24
/*PLACEHOLDER*/ select c_custkey, c_name, sum(l_extendedprice * (1 - l_discount)) as revenue, c_acctbal, n_name, c_address, c_phone, c_comment from customer, orders, lineitem, nation where c_custkey = o_custkey and l_orderkey = o_orderkey and o_orderdate >= '1993-08-01' and o_orderdate < date_add('1993-08-01', interval '3' month) and l_returnflag = 'R' and c_nationkey = n_nationkey group by c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment order by revenue desc limit 20;
| id | estRows | estCost | actRows | task | access object | execution info | operator info | memory | disk |
|---------------------------------------|-----------|-------------|----------|------------|----------------|------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|-----------|-------|
| Projection_18 | 19.53 | 305549.12 | 0 | root | | time:487.6ms, loops:1, Concurrency:OFF | olap.customer.c_custkey, olap.customer.c_name, Column#38, olap.customer.c_acctbal, olap.nation.n_name, olap.customer.c_address, olap.customer.c_phone, olap.customer.c_comment | 92.3 KB | N/A |
| └─TopN_21 | 19.53 | 305533.53 | 0 | root | | time:487.6ms, loops:1 | Column#38:desc, offset:0, count:20 | 0 Bytes | N/A |
| └─HashAgg_26 | 19.53 | 300269.34 | 0 | root | | time:487.6ms, loops:2, partial_worker:{wall_time:487.59384ms, concurrency:5, task_num:0, tot_wait:2.43784645s, tot_exec:0s, tot_time:2.43785308s, max:487.57611ms, p95:487.57611ms}, final_worker:{wall_time:487.61164ms, concurrency:5, task_num:0, tot_wait:2.43793196s, tot_exec:3.16µs, tot_time:2.43793817s, max:487.59142ms, p95:487.59142ms} | group by:Column#47, Column#48, Column#49, Column#50, Column#51, Column#52, Column#53, funcs:sum(Column#39)->Column#38, funcs:firstrow(Column#40)->olap.customer.c_custkey, funcs:firstrow(Column#41)->olap.customer.c_name, funcs:firstrow(Column#42)->olap.customer.c_address, funcs:firstrow(Column#43)->olap.customer.c_phone, funcs:firstrow(Column#44)->olap.customer.c_acctbal, funcs:firstrow(Column#45)->olap.customer.c_comment, funcs:firstrow(Column#46)->olap.nation.n_name | 774.4 KB | N/A |
| └─Projection_96 | 19.53 | 294097.31 | 0 | root | | time:487.6ms, loops:1, Concurrency:OFF | mul(olap.lineitem.l_extendedprice, minus(1, olap.lineitem.l_discount))->Column#39, olap.customer.c_custkey->Column#40, olap.customer.c_name->Column#41, olap.customer.c_address->Column#42, olap.customer.c_phone->Column#43, olap.customer.c_acctbal->Column#44, olap.customer.c_comment->Column#45, olap.nation.n_name->Column#46, olap.customer.c_custkey->Column#47, olap.customer.c_name->Column#48, olap.customer.c_acctbal->Column#49, olap.customer.c_phone->Column#50, olap.nation.n_name->Column#51, olap.customer.c_address->Column#52, olap.customer.c_comment->Column#53 | 93.7 KB | N/A |
| └─Projection_27 | 19.53 | 293875.10 | 0 | root | | time:487.6ms, loops:1, Concurrency:OFF | olap.customer.c_custkey, olap.customer.c_name, olap.customer.c_address, olap.customer.c_phone, olap.customer.c_acctbal, olap.customer.c_comment, olap.lineitem.l_extendedprice, olap.lineitem.l_discount, olap.nation.n_name | 93.7 KB | N/A |
| └─IndexJoin_32 | 19.53 | 293857.56 | 0 | root | | time:487.5ms, loops:1 | inner join, inner:TableReader_29, outer key:olap.customer.c_nationkey, inner key:olap.nation.n_nationkey, equal cond:eq(olap.customer.c_nationkey, olap.nation.n_nationkey) | 0 Bytes | N/A |
| ├─IndexHashJoin_45(Build) | 15.62 | 284372.61 | 0 | root | | time:487.5ms, loops:1 | inner join, inner:TableReader_40, outer key:olap.orders.o_custkey, inner key:olap.customer.c_custkey, equal cond:eq(olap.orders.o_custkey, olap.customer.c_custkey) | 0 Bytes | N/A |
| │ ├─IndexJoin_57(Build) | 12.50 | 276437.93 | 0 | root | | time:487.4ms, loops:1 | inner join, inner:TableReader_53, outer key:olap.lineitem.l_orderkey, inner key:olap.orders.o_orderkey, equal cond:eq(olap.lineitem.l_orderkey, olap.orders.o_orderkey) | 0 Bytes | N/A |
| │ │ ├─TableReader_83(Build) | 10.00 | 269939.32 | 0 | root | | time:487.3ms, loops:1, cop_task: {num: 7, max: 459.9µs, min: 344.1µs, avg: 406.1µs, p95: 459.9µs, tot_proc: 142.8µs, tot_wait: 390.2µs, rpc_num: 8, rpc_time: 3.15ms, copr_cache_hit_ratio: 0.43, build_task_duration: 23.7µs, max_distsql_concurrency: 7} | data:Selection_82 | 983 Bytes | N/A |
| │ │ │ └─Selection_82 | 10.00 | 4044298.22 | 0 | cop[tikv] | | tikv_task:{proc max:50ms, min:0s, avg: 20ms, p80:50ms, p95:50ms, iters:7, tasks:7}, scan_detail: {total_keys: 4, get_snapshot_time: 96.4µs, rocksdb: {block: {}}} | eq(olap.lineitem.l_returnflag, "R") | N/A | N/A |
| │ │ │ └─TableFullScan_81 | 10000.00 | 3545298.22 | 0 | cop[tikv] | table:lineitem | tikv_task:{proc max:50ms, min:0s, avg: 20ms, p80:50ms, p95:50ms, iters:7, tasks:7} | keep order:false, stats:pseudo | N/A | N/A |
| │ │ └─TableReader_53(Probe) | 0.25 | 26.63 | 0 | root | | | data:Selection_52 | N/A | N/A |
| │ │ └─Selection_52 | 0.25 | 397.06 | 0 | cop[tikv] | | | ge(olap.orders.o_orderdate, 1993-08-01 00:00:00.000000), lt(olap.orders.o_orderdate, 1993-11-01 00:00:00.000000) | N/A | N/A |
| │ │ └─TableRangeScan_51 | 10.00 | 297.26 | 0 | cop[tikv] | table:orders | | range: decided by [olap.lineitem.l_orderkey], keep order:false, stats:pseudo | N/A | N/A |
| │ └─TableReader_40(Probe) | 12.50 | 75.43 | 0 | root | | | data:TableRangeScan_39 | N/A | N/A |
| │ └─TableRangeScan_39 | 12.50 | 315.62 | 0 | cop[tikv] | table:customer | | range: decided by [olap.orders.o_custkey], keep order:false, stats:pseudo | N/A | N/A |
| └─TableReader_29(Probe) | 15.62 | 27.86 | 0 | root | | | data:TableRangeScan_28 | N/A | N/A |
| └─TableRangeScan_28 | 15.62 | 287.15 | 0 | cop[tikv] | table:nation | | range: decided by [olap.customer.c_nationkey], keep order:false, stats:pseudo | N/A | N/A |