gengliqi
commented
7 months ago /cc @Lloyd-Pottiger
Open gengliqi opened 7 months ago
gengliqi
commented
7 months ago /cc @Lloyd-Pottiger
JaySon-Huang
commented
2 weeks ago Not reproducible in the master branch. There is no performance gap when running Q19 and running Q19 with explain analyze.
And according to the tiflash log, we can ensure that late materialize is applied no matter running with "explain analyze" or not.
TiDB root@10.2.12.81:test> select type,version,git_hash from information_schema.cluster_info;
+---------+-----------------------------+------------------------------------------+
| type | version | git_hash |
+---------+-----------------------------+------------------------------------------+
| tidb | 8.4.0-alpha-402-g6af2863452 | 6af28634522ea60004561b441d3018c69de43b14 |
| pd | 8.4.0-alpha-58-g982fa22f | 982fa22ffad01ebe640e5c23f3c6d0c67bde6742 |
| tiflash | 8.4.0-alpha-52-gcea728d5d | cea728d5d2bad7766dfc5e97cdf8ef16fe4a791f |
| tikv | 8.4.0-alpha | 18546c296bc70e4abf2ab144b1f3048dbc47024e |
+---------+-----------------------------+------------------------------------------+TiDB root@10.2.12.81:test> select sum(l_extendedprice* (1 - l_discount)) as revenue from lineitem, part where ( p_partkey = l_partkey and p_brand = 'Brand#52' and p_container in ('
-> SM CASE', 'SM BOX', 'SM PACK', 'SM PKG') and l_quantity >= 4 and l_quantity <= 4 + 10 and p_size between 1 and 5 and l_shipmode in ('AIR', 'AIR REG') and
-> l_shipinstruct = 'DELIVER IN PERSON' ) or ( p_partkey = l_partkey and p_brand = 'Brand#11' and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PAC
-> K') and l_quantity >= 18 and l_quantity <= 18 + 10 and p_size between 1 and 10 and l_shipmode in ('AIR', 'AIR REG') and l_shipinstruct = 'DELIVER IN PERS
-> ON' ) or ( p_partkey = l_partkey and p_brand = 'Brand#51' and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG') and l_quantity >= 29 and l_quanti
-> ty <= 29 + 10 and p_size between 1 and 15 and l_shipmode in ('AIR', 'AIR REG') and l_shipinstruct = 'DELIVER IN PERSON' )
+----------------+
| revenue |
+----------------+
| 211553361.2526 |
+----------------+
1 row in set
Time: 0.911s
TiDB root@10.2.12.81:test> select sum(l_extendedprice* (1 - l_discount)) as revenue from lineitem, part where ( p_partkey = l_partkey and p_brand = 'Brand#52' and p_container in ('
-> SM CASE', 'SM BOX', 'SM PACK', 'SM PKG') and l_quantity >= 4 and l_quantity <= 4 + 10 and p_size between 1 and 5 and l_shipmode in ('AIR', 'AIR REG') and
-> l_shipinstruct = 'DELIVER IN PERSON' ) or ( p_partkey = l_partkey and p_brand = 'Brand#11' and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PAC
-> K') and l_quantity >= 18 and l_quantity <= 18 + 10 and p_size between 1 and 10 and l_shipmode in ('AIR', 'AIR REG') and l_shipinstruct = 'DELIVER IN PERS
-> ON' ) or ( p_partkey = l_partkey and p_brand = 'Brand#51' and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG') and l_quantity >= 29 and l_quanti
-> ty <= 29 + 10 and p_size between 1 and 15 and l_shipmode in ('AIR', 'AIR REG') and l_shipinstruct = 'DELIVER IN PERSON' )
+----------------+
| revenue |
+----------------+
| 211553361.2526 |
+----------------+
1 row in set
Time: 0.921s> tailf tiflash.log | rg -i 'pushdown'
[2024/11/08 17:58:41.109 +08:00] [DEBUG] [PushDownFilter.cpp:37] ["Push down filter is empty"] [source="db_1873.t_7221 MPP<gather_id:1, query_ts:1731059921102641968, local_query_id:145, server_id:1252, start_ts:453786971954741249,task_id:1> table_id=7221"] [thread_id=740]
[2024/11/08 17:58:41.127 +08:00] [DEBUG] [PushDownFilter.cpp:124] ["Push down filter: input:\nl_partkey Int64\nl_quantity Decimal(15,2)\nl_extendedprice Decimal(15,2)\nl_discount Decimal(15,2)\nl_shipinstruct String\nl_shipmode String\n\nactions:\nADD ___set1 Set Set\nFUNCTION tidbIn(l_shipmode, ___set1)_collator_46 UInt8 = tidbIn (l_shipmode, ___set1)\nREMOVE ___set1\n\noutput:\nl_partkey Int64\nl_quantity Decimal(15,2)\nl_extendedprice Decimal(15,2)\nl_discount Decimal(15,2)\nl_shipinstruct String\nl_shipmode String\ntidbIn(l_shipmode, ___set1)_collator_46 UInt8\n"] [source="db_1873.t_7220 MPP<gather_id:1, query_ts:1731059921102641968, local_query_id:145, server_id:1252, start_ts:453786971954741249,task_id:2> table_id=7220"] [thread_id=741]
[2024/11/08 17:58:51.547 +08:00] [DEBUG] [PushDownFilter.cpp:37] ["Push down filter is empty"] [source="db_1873.t_7221 MPP<gather_id:1, query_ts:1731059931540226769, local_query_id:146, server_id:1252, start_ts:453786974681300993,task_id:1> table_id=7221"] [thread_id=744]
[2024/11/08 17:58:51.570 +08:00] [DEBUG] [PushDownFilter.cpp:124] ["Push down filter: input:\nl_partkey Int64\nl_quantity Decimal(15,2)\nl_extendedprice Decimal(15,2)\nl_discount Decimal(15,2)\nl_shipinstruct String\nl_shipmode String\n\nactions:\nADD ___set1 Set Set\nFUNCTION tidbIn(l_shipmode, ___set1)_collator_46 UInt8 = tidbIn (l_shipmode, ___set1)\nREMOVE ___set1\n\noutput:\nl_partkey Int64\nl_quantity Decimal(15,2)\nl_extendedprice Decimal(15,2)\nl_discount Decimal(15,2)\nl_shipinstruct String\nl_shipmode String\ntidbIn(l_shipmode, ___set1)_collator_46 UInt8\n"] [source="db_1873.t_7220 MPP<gather_id:1, query_ts:1731059931540226769, local_query_id:146, server_id:1252, start_ts:453786974681300993,task_id:2> table_id=7220"] [thread_id=745]TiDB root@10.2.12.81:test> explain analyze select sum(l_extendedprice* (1 - l_discount)) as revenue from lineitem, part where ( p_partkey = l_partkey and p_brand = 'Brand#52' and p
-> _container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG') and l_quantity >= 4 and l_quantity <= 4 + 10 and p_size between 1 and 5 and l_shipmode in ('AIR'
-> , 'AIR REG') and l_shipinstruct = 'DELIVER IN PERSON' ) or ( p_partkey = l_partkey and p_brand = 'Brand#11' and p_container in ('MED BAG', 'MED BOX', 'ME
-> D PKG', 'MED PACK') and l_quantity >= 18 and l_quantity <= 18 + 10 and p_size between 1 and 10 and l_shipmode in ('AIR', 'AIR REG') and l_shipinstruct =
-> 'DELIVER IN PERSON' ) or ( p_partkey = l_partkey and p_brand = 'Brand#51' and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG') and l_quantity >=
-> 29 and l_quantity <= 29 + 10 and p_size between 1 and 15 and l_shipmode in ('AIR', 'AIR REG') and l_shipinstruct = 'DELIVER IN PERSON' )
...
13 rows in set
Time: 0.914s
TiDB root@10.2.12.81:test> explain analyze select sum(l_extendedprice* (1 - l_discount)) as revenue from lineitem, part where ( p_partkey = l_partkey and p_brand = 'Brand#52' and p
-> _container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG') and l_quantity >= 4 and l_quantity <= 4 + 10 and p_size between 1 and 5 and l_shipmode in ('AIR'
-> , 'AIR REG') and l_shipinstruct = 'DELIVER IN PERSON' ) or ( p_partkey = l_partkey and p_brand = 'Brand#11' and p_container in ('MED BAG', 'MED BOX', 'ME
-> D PKG', 'MED PACK') and l_quantity >= 18 and l_quantity <= 18 + 10 and p_size between 1 and 10 and l_shipmode in ('AIR', 'AIR REG') and l_shipinstruct =
-> 'DELIVER IN PERSON' ) or ( p_partkey = l_partkey and p_brand = 'Brand#51' and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG') and l_quantity >=
-> 29 and l_quantity <= 29 + 10 and p_size between 1 and 15 and l_shipmode in ('AIR', 'AIR REG') and l_shipinstruct = 'DELIVER IN PERSON' )
...
13 rows in set
Time: 0.900s> tailf tiflash.log | rg -i 'pushdown' 2024-11-08 17:59:43
[2024/11/08 17:59:46.591 +08:00] [DEBUG] [PushDownFilter.cpp:37] ["Push down filter is empty"] [source="db_1873.t_7221 MPP<gather_id:1, query_ts:1731059986584197248, local_query_id:147, server_id:1252, start_ts:453786989112328194,task_id:1> table_id=7221"] [thread_id=748]
[2024/11/08 17:59:46.613 +08:00] [DEBUG] [PushDownFilter.cpp:124] ["Push down filter: input:\nl_partkey Int64\nl_quantity Decimal(15,2)\nl_extendedprice Decimal(15,2)\nl_discount Decimal(15,2)\nl_shipinstruct String\nl_shipmode String\n\nactions:\nADD ___set1 Set Set\nFUNCTION tidbIn(l_shipmode, ___set1)_collator_46 UInt8 = tidbIn (l_shipmode, ___set1)\nREMOVE ___set1\n\noutput:\nl_partkey Int64\nl_quantity Decimal(15,2)\nl_extendedprice Decimal(15,2)\nl_discount Decimal(15,2)\nl_shipinstruct String\nl_shipmode String\ntidbIn(l_shipmode, ___set1)_collator_46 UInt8\n"] [source="db_1873.t_7220 MPP<gather_id:1, query_ts:1731059986584197248, local_query_id:147, server_id:1252, start_ts:453786989112328194,task_id:2> table_id=7220"] [thread_id=749]
[2024/11/08 17:59:49.325 +08:00] [DEBUG] [PushDownFilter.cpp:37] ["Push down filter is empty"] [source="db_1873.t_7221 MPP<gather_id:1, query_ts:1731059989319404361, local_query_id:148, server_id:1252, start_ts:453786989833224193,task_id:1> table_id=7221"] [thread_id=752]
[2024/11/08 17:59:49.346 +08:00] [DEBUG] [PushDownFilter.cpp:124] ["Push down filter: input:\nl_partkey Int64\nl_quantity Decimal(15,2)\nl_extendedprice Decimal(15,2)\nl_discount Decimal(15,2)\nl_shipinstruct String\nl_shipmode String\n\nactions:\nADD ___set1 Set Set\nFUNCTION tidbIn(l_shipmode, ___set1)_collator_46 UInt8 = tidbIn (l_shipmode, ___set1)\nREMOVE ___set1\n\noutput:\nl_partkey Int64\nl_quantity Decimal(15,2)\nl_extendedprice Decimal(15,2)\nl_discount Decimal(15,2)\nl_shipinstruct String\nl_shipmode String\ntidbIn(l_shipmode, ___set1)_collator_46 UInt8\n"] [source="db_1873.t_7220 MPP<gather_id:1, query_ts:1731059989319404361, local_query_id:148, server_id:1252, start_ts:453786989833224193,task_id:2> table_id=7220"] [thread_id=753]Neither reproducible in my v7.5.0 cluster.
TiDB root@10.2.12.81:test> select type,version,git_hash from information_schema.cluster_info;
+---------+---------+------------------------------------------+
| type | version | git_hash |
+---------+---------+------------------------------------------+
| tidb | 7.5.0 | 069631e2ecfedc000ffb92c67207bea81380f020 |
| pd | 7.5.0 | ef6ba8551e525a700546d6bdb7ad6766115209cc |
| tikv | 7.5.0 | bd8a0aabd08fd77687f788e0b45858ccd3516e4d |
| tiflash | 7.5.0 | fe6621befaefdfd03a6fa5c10318b7e6583fa3d2 |
+---------+---------+------------------------------------------+TiDB root@10.2.12.81:test> select sum(l_extendedprice* (1 - l_discount)) as revenue from lineitem, part where ( p_partkey = l_partkey and p_brand = 'Brand#52' and p_container in ('
-> SM CASE', 'SM BOX', 'SM PACK', 'SM PKG') and l_quantity >= 4 and l_quantity <= 4 + 10 and p_size between 1 and 5 and l_shipmode in ('AIR', 'AIR REG') and
-> l_shipinstruct = 'DELIVER IN PERSON' ) or ( p_partkey = l_partkey and p_brand = 'Brand#11' and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PAC
-> K') and l_quantity >= 18 and l_quantity <= 18 + 10 and p_size between 1 and 10 and l_shipmode in ('AIR', 'AIR REG') and l_shipinstruct = 'DELIVER IN PERS
-> ON' ) or ( p_partkey = l_partkey and p_brand = 'Brand#51' and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG') and l_quantity >= 29 and l_quanti
-> ty <= 29 + 10 and p_size between 1 and 15 and l_shipmode in ('AIR', 'AIR REG') and l_shipinstruct = 'DELIVER IN PERSON' );
+----------------+
| revenue |
+----------------+
| 211553361.2526 |
+----------------+
1 row in set
Time: 0.867s
TiDB root@10.2.12.81:test> select sum(l_extendedprice* (1 - l_discount)) as revenue from lineitem, part where ( p_partkey = l_partkey and p_brand = 'Brand#52' and p_container in ('
-> SM CASE', 'SM BOX', 'SM PACK', 'SM PKG') and l_quantity >= 4 and l_quantity <= 4 + 10 and p_size between 1 and 5 and l_shipmode in ('AIR', 'AIR REG') and
-> l_shipinstruct = 'DELIVER IN PERSON' ) or ( p_partkey = l_partkey and p_brand = 'Brand#11' and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PAC
-> K') and l_quantity >= 18 and l_quantity <= 18 + 10 and p_size between 1 and 10 and l_shipmode in ('AIR', 'AIR REG') and l_shipinstruct = 'DELIVER IN PERS
-> ON' ) or ( p_partkey = l_partkey and p_brand = 'Brand#51' and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG') and l_quantity >= 29 and l_quanti
-> ty <= 29 + 10 and p_size between 1 and 15 and l_shipmode in ('AIR', 'AIR REG') and l_shipinstruct = 'DELIVER IN PERSON' );
+----------------+
| revenue |
+----------------+
| 211553361.2526 |
+----------------+
1 row in set
Time: 0.822s> tailf tiflash.log | rg -i 'push down' 2024-11-11 12:05:17
[2024/11/11 12:05:24.375 +08:00] [DEBUG] [StorageDeltaMerge.cpp:777] ["Push down filter is empty"] [source="db_2.t_102 MPP<gather_id:<gather_id:1, query_ts:1731297924367875685, local_query_id:14, server_id:1793, start_ts:453849363080806401, resource_group: default>,task_id:1> table_id=102"] [thread_id=326]
[2024/11/11 12:05:24.394 +08:00] [DEBUG] [StorageDeltaMerge.cpp:862] ["Push down filter: input:\nl_partkey Int64\nl_quantity Decimal(15,2)\nl_extendedprice Decimal(15,2)\nl_discount Decimal(15,2)\nl_shipinstruct String\nl_shipmode String\n\nactions:\nADD ___set1 Set Set\nFUNCTION tidbIn(l_shipmode, ___set1)_collator_46 UInt8 = tidbIn(l_shipmode, ___set1)\nREMOVE ___set1\n\noutput:\nl_partkey Int64\nl_quantity Decimal(15,2)\nl_extendedprice Decimal(15,2)\nl_discount Decimal(15,2)\nl_shipinstruct String\nl_shipmode String\ntidbIn(l_shipmode, ___set1)_collator_46 UInt8\n"] [source="db_2.t_111 MPP<gather_id:<gather_id:1, query_ts:1731297924367875685, local_query_id:14, server_id:1793, start_ts:453849363080806401, resource_group: default>,task_id:2> table_id=111"] [thread_id=327]
[2024/11/11 12:05:27.385 +08:00] [DEBUG] [StorageDeltaMerge.cpp:777] ["Push down filter is empty"] [source="db_2.t_102 MPP<gather_id:<gather_id:1, query_ts:1731297927379861106, local_query_id:15, server_id:1793, start_ts:453849363866976257, resource_group: default>,task_id:1> table_id=102"] [thread_id=330]
[2024/11/11 12:05:27.403 +08:00] [DEBUG] [StorageDeltaMerge.cpp:862] ["Push down filter: input:\nl_partkey Int64\nl_quantity Decimal(15,2)\nl_extendedprice Decimal(15,2)\nl_discount Decimal(15,2)\nl_shipinstruct String\nl_shipmode String\n\nactions:\nADD ___set1 Set Set\nFUNCTION tidbIn(l_shipmode, ___set1)_collator_46 UInt8 = tidbIn(l_shipmode, ___set1)\nREMOVE ___set1\n\noutput:\nl_partkey Int64\nl_quantity Decimal(15,2)\nl_extendedprice Decimal(15,2)\nl_discount Decimal(15,2)\nl_shipinstruct String\nl_shipmode String\ntidbIn(l_shipmode, ___set1)_collator_46 UInt8\n"] [source="db_2.t_111 MPP<gather_id:<gather_id:1, query_ts:1731297927379861106, local_query_id:15, server_id:1793, start_ts:453849363866976257, resource_group: default>,task_id:2> table_id=111"] [thread_id=331]TiDB root@10.2.12.81:test> explain analyze select sum(l_extendedprice* (1 - l_discount)) as revenue from lineitem, part where ( p_partkey = l_partkey and p_brand = 'Brand#52' and p
-> _container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG') and l_quantity >= 4 and l_quantity <= 4 + 10 and p_size between 1 and 5 and l_shipmode in ('AIR'
-> , 'AIR REG') and l_shipinstruct = 'DELIVER IN PERSON' ) or ( p_partkey = l_partkey and p_brand = 'Brand#11' and p_container in ('MED BAG', 'MED BOX', 'ME
-> D PKG', 'MED PACK') and l_quantity >= 18 and l_quantity <= 18 + 10 and p_size between 1 and 10 and l_shipmode in ('AIR', 'AIR REG') and l_shipinstruct =
-> 'DELIVER IN PERSON' ) or ( p_partkey = l_partkey and p_brand = 'Brand#51' and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG') and l_quantity >=
-> 29 and l_quantity <= 29 + 10 and p_size between 1 and 15 and l_shipmode in ('AIR', 'AIR REG') and l_shipinstruct = 'DELIVER IN PERSON' );
...
13 rows in set
Time: 0.857s
TiDB root@10.2.12.81:test> explain analyze select sum(l_extendedprice* (1 - l_discount)) as revenue from lineitem, part where ( p_partkey = l_partkey and p_brand = 'Brand#52' and p
-> _container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG') and l_quantity >= 4 and l_quantity <= 4 + 10 and p_size between 1 and 5 and l_shipmode in ('AIR'
-> , 'AIR REG') and l_shipinstruct = 'DELIVER IN PERSON' ) or ( p_partkey = l_partkey and p_brand = 'Brand#11' and p_container in ('MED BAG', 'MED BOX', 'ME
-> D PKG', 'MED PACK') and l_quantity >= 18 and l_quantity <= 18 + 10 and p_size between 1 and 10 and l_shipmode in ('AIR', 'AIR REG') and l_shipinstruct =
-> 'DELIVER IN PERSON' ) or ( p_partkey = l_partkey and p_brand = 'Brand#51' and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG') and l_quantity >=
-> 29 and l_quantity <= 29 + 10 and p_size between 1 and 15 and l_shipmode in ('AIR', 'AIR REG') and l_shipinstruct = 'DELIVER IN PERSON' );
...
13 rows in set
Time: 0.850s> tailf tiflash.log | rg -i 'push down' 2024-11-11 12:05:33
[2024/11/11 12:05:37.012 +08:00] [DEBUG] [StorageDeltaMerge.cpp:777] ["Push down filter is empty"] [source="db_2.t_102 MPP<gather_id:<gather_id:1, query_ts:1731297937006448981, local_query_id:16, server_id:1793, start_ts:453849366396665857, resource_group: default>,task_id:1> table_id=102"] [thread_id=334]
[2024/11/11 12:05:37.030 +08:00] [DEBUG] [StorageDeltaMerge.cpp:862] ["Push down filter: input:\nl_partkey Int64\nl_quantity Decimal(15,2)\nl_extendedprice Decimal(15,2)\nl_discount Decimal(15,2)\nl_shipinstruct String\nl_shipmode String\n\nactions:\nADD ___set1 Set Set\nFUNCTION tidbIn(l_shipmode, ___set1)_collator_46 UInt8 = tidbIn(l_shipmode, ___set1)\nREMOVE ___set1\n\noutput:\nl_partkey Int64\nl_quantity Decimal(15,2)\nl_extendedprice Decimal(15,2)\nl_discount Decimal(15,2)\nl_shipinstruct String\nl_shipmode String\ntidbIn(l_shipmode, ___set1)_collator_46 UInt8\n"] [source="db_2.t_111 MPP<gather_id:<gather_id:1, query_ts:1731297937006448981, local_query_id:16, server_id:1793, start_ts:453849366396665857, resource_group: default>,task_id:2> table_id=111"] [thread_id=335]
[2024/11/11 12:05:39.886 +08:00] [DEBUG] [StorageDeltaMerge.cpp:777] ["Push down filter is empty"] [source="db_2.t_102 MPP<gather_id:<gather_id:1, query_ts:1731297939880787570, local_query_id:17, server_id:1793, start_ts:453849367144038401, resource_group: default>,task_id:1> table_id=102"] [thread_id=338]
[2024/11/11 12:05:39.906 +08:00] [DEBUG] [StorageDeltaMerge.cpp:862] ["Push down filter: input:\nl_partkey Int64\nl_quantity Decimal(15,2)\nl_extendedprice Decimal(15,2)\nl_discount Decimal(15,2)\nl_shipinstruct String\nl_shipmode String\n\nactions:\nADD ___set1 Set Set\nFUNCTION tidbIn(l_shipmode, ___set1)_collator_46 UInt8 = tidbIn(l_shipmode, ___set1)\nREMOVE ___set1\n\noutput:\nl_partkey Int64\nl_quantity Decimal(15,2)\nl_extendedprice Decimal(15,2)\nl_discount Decimal(15,2)\nl_shipinstruct String\nl_shipmode String\ntidbIn(l_shipmode, ___set1)_collator_46 UInt8\n"] [source="db_2.t_111 MPP<gather_id:<gather_id:1, query_ts:1731297939880787570, local_query_id:17, server_id:1793, start_ts:453849367144038401, resource_group: default>,task_id:2> table_id=111"] [thread_id=339]TiDB root@10.2.12.81:test> explain analyze select sum(l_extendedprice* (1 - l_discount)) as revenue from lineitem, part where ( p_partkey = l_partkey and p_brand = 'Brand#52' and p
-> _container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG') and l_quantity >= 4 and l_quantity <= 4 + 10 and p_size between 1 and 5 and l_shipmode in ('AIR'
-> , 'AIR REG') and l_shipinstruct = 'DELIVER IN PERSON' ) or ( p_partkey = l_partkey and p_brand = 'Brand#11' and p_container in ('MED BAG', 'MED BOX', 'ME
-> D PKG', 'MED PACK') and l_quantity >= 18 and l_quantity <= 18 + 10 and p_size between 1 and 10 and l_shipmode in ('AIR', 'AIR REG') and l_shipinstruct =
-> 'DELIVER IN PERSON' ) or ( p_partkey = l_partkey and p_brand = 'Brand#51' and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG') and l_quantity >=
-> 29 and l_quantity <= 29 + 10 and p_size between 1 and 15 and l_shipmode in ('AIR', 'AIR REG') and l_shipinstruct = 'DELIVER IN PERSON' );
+------------------------------------------+-------------+----------+--------------+----------------+-------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+------------------------------------------+-------------+----------+--------------+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
| HashAgg_59 | 1.00 | 1 | root | | time:827.7ms, loops:2, RU:0.000000, partial_worker:{wall_time:827.660912ms, concurrency:5, task_num:1, tot_wait:4.138065479s, tot_exec:17.211µs, tot_time:4.138090907s, max:827.622546ms, p95:827.622546ms}, final_worker:{wall_time:827.717897ms, concurrency:5, task_num:1, tot_wait:4.13817126s, tot_exec:25.371µs, tot_time:4.138200228s, max:827.651927ms, p95:827.651927ms} | funcs:sum(Column#28)->Column#27 | 20.2 KB | N/A |
| └─TableReader_61 | 1.00 | 1 | root | | time:827.6ms, loops:2, cop_task: {num: 2, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00} | MppVersion: 2, data:ExchangeSender_60 | 815 Bytes | N/A |
| └─ExchangeSender_60 | 1.00 | 1 | mpp[tiflash] | | tiflash_task:{time:825.9ms, loops:1, threads:1} | ExchangeType: PassThrough | N/A | N/A |
| └─HashAgg_14 | 1.00 | 1 | mpp[tiflash] | | tiflash_task:{time:825.9ms, loops:1, threads:1} | funcs:sum(Column#30)->Column#28 | N/A | N/A |
| └─Projection_87 | 721385.93 | 5665 | mpp[tiflash] | | tiflash_task:{time:823.9ms, loops:5079, threads:72} | mul(test.lineitem.l_extendedprice, minus(1, test.lineitem.l_discount))->Column#30 | N/A | N/A |
| └─Projection_58 | 721385.93 | 5665 | mpp[tiflash] | | tiflash_task:{time:823.9ms, loops:5079, threads:72} | test.lineitem.l_extendedprice, test.lineitem.l_discount | N/A | N/A |
| └─HashJoin_55 | 721385.93 | 5665 | mpp[tiflash] | | tiflash_task:{time:823.9ms, loops:5079, threads:72} | inner join, equal:[eq(test.part.p_partkey, test.lineitem.l_partkey)], other cond:or(and(and(eq(test.part.p_brand, "Brand#52"), in(test.part.p_container, "SM CASE", "SM BOX", "SM PACK", "SM PKG")), and(ge(test.lineitem.l_quantity, 4), and(le(test.lineitem.l_quantity, 14), le(test.part.p_size, 5)))), or(and(and(eq(test.part.p_brand, "Brand#11"), in(test.part.p_container, "MED BAG", "MED BOX", "MED PKG", "MED PACK")), and(ge(test.lineitem.l_quantity, 18), and(le(test.lineitem.l_quantity, 28), le... | N/A | N/A |
| ├─ExchangeReceiver_36(Build) | 23964.57 | 24150 | mpp[tiflash] | | tiflash_task:{time:55.9ms, loops:47, threads:72} | | N/A | N/A |
| │ └─ExchangeSender_35 | 23964.57 | 24150 | mpp[tiflash] | | tiflash_task:{time:53.3ms, loops:156, threads:72} | ExchangeType: Broadcast, Compression: FAST | N/A | N/A |
| │ └─Selection_34 | 23964.57 | 24150 | mpp[tiflash] | | tiflash_task:{time:52.3ms, loops:156, threads:72} | ge(test.part.p_size, 1), or(and(eq(test.part.p_brand, "Brand#52"), and(in(test.part.p_container, "SM CASE", "SM BOX", "SM PACK", "SM PKG"), le(test.part.p_size, 5))), or(and(eq(test.part.p_brand, "Brand#11"), and(in(test.part.p_container, "MED BAG", "MED BOX", "MED PKG", "MED PACK"), le(test.part.p_size, 10))), and(eq(test.part.p_brand, "Brand#51"), and(in(test.part.p_container, "LG CASE", "LG BOX", "LG PACK", "LG PKG"), le(test.part.p_size, 15))))) | N/A | N/A |
| │ └─TableFullScan_33 | 10000000.00 | 10000000 | mpp[tiflash] | table:part | tiflash_task:{time:50.3ms, loops:157, threads:72}, tiflash_scan:{dtfile:{total_scanned_packs:1234, total_skipped_packs:0, total_scanned_rows:10000000, total_skipped_rows:0, total_rs_index_check_time: 0ms, total_read_time: 542ms, total_disagg_read_cache_hit_size: 0, total_disagg_read_cache_miss_size: 0}, total_create_snapshot_time: 0ms, total_local_region_num: 16, total_remote_region_num: 0, total_learner_read_time: 0ms} | pushed down filter:empty, keep order:false | N/A | N/A |
| └─Selection_38(Probe) | 6885888.79 | 7073612 | mpp[tiflash] | | tiflash_task:{time:802.9ms, loops:5079, threads:72} | eq(test.lineitem.l_shipinstruct, "DELIVER IN PERSON"), or(and(ge(test.lineitem.l_quantity, 4), le(test.lineitem.l_quantity, 14)), or(and(ge(test.lineitem.l_quantity, 18), le(test.lineitem.l_quantity, 28)), and(ge(test.lineitem.l_quantity, 29), le(test.lineitem.l_quantity, 39)))) | N/A | N/A |
| └─TableFullScan_37 | 42299494.42 | 42860070 | mpp[tiflash] | table:lineitem | tiflash_task:{time:743.9ms, loops:5079, threads:72}, tiflash_scan:{dtfile:{total_scanned_packs:73852, total_skipped_packs:0, total_scanned_rows:600011622, total_skipped_rows:0, total_rs_index_check_time: 23ms, total_read_time: 28214ms, total_disagg_read_cache_hit_size: 0, total_disagg_read_cache_miss_size: 0}, total_create_snapshot_time: 2ms, total_local_region_num: 565, total_remote_region_num: 0, total_learner_read_time: 18ms} | pushed down filter:in(test.lineitem.l_shipmode, "AIR", "AIR REG"), keep order:false | N/A | N/A |
+------------------------------------------+-------------+----------+--------------+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
13 rows in set
Time: 0.850sThe previous two comments are based on TPC-H 50GB. And I've another try on TPC-H 100GB, either no different plan of pushed down filter with or without "explain analyze".
Lloyd-Pottiger
commented
1 week ago The expression is not the same
With explain analyze:
| └─Selection_31(Probe) | 14189641.13 | 14145150 | mpp[tiflash] | | tiflash_task:{proc max:2.01s, min:2.01s, avg: 2.01s, p80:2.01s, p95:2.01s, iters:9740, tasks:2, threads:72} | eq(tpch100.lineitem.l_shipinstruct, "DELIVER IN PERSON"), or(and(ge(tpch100.lineitem.l_quantity, 4), le(tpch100.lineitem.l_quantity, 14)), or(and(ge(tpch100.lineitem.l_quantity, 18), le(tpch100.lineitem.l_quantity, 28)), and(ge(tpch100.lineitem.l_quantity, 29), le(tpch100.lineitem.l_quantity, 39)))) | N/A | N/A |
| └─TableFullScan_30 | 86081963.28 | 85729153 | mpp[tiflash] | table:lineitem | tiflash_task:{proc max:1.85s, min:1.84s, avg: 1.84s, p80:1.85s, p95:1.85s, iters:9740, tasks:2, threads:72}, tiflash_scan:{dtfile:{total_scanned_packs:0, total_skipped_packs:0, total_scanned_rows:600064726, total_skipped_rows:11624696, total_rs_index_check_time: 56ms, total_read_time: 75090ms, total_disagg_read_cache_hit_size: 0, total_disagg_read_cache_miss_size: 0}, total_create_snapshot_time: 2ms, total_local_region_num: 1307, total_remote_region_num: 0, total_learner_read_time: 55ms} | pushed down filter:in(tpch100.lineitem.l_shipmode, "AIR", "AIR REG"), keep order:false | N/A | N/A |Without explain analyze:
| └─Selection_31(Probe) | 14078086.84 | 54033837221.40 | 14145150 | mpp[tiflash] | | tiflash_task:{proc max:2.54s, min:2.54s, avg: 2.54s, p80:2.54s, p95:2.54s, iters:9738, tasks:2, threads:72} | or(and(and(ge(tpch100.lineitem.l_quantity, 4), le(tpch100.lineitem.l_quantity, 14)), and(in(tpch100.lineitem.l_shipmode, "AIR", "AIR REG"), eq(tpch100.lineitem.l_shipinstruct, "DELIVER IN PERSON"))), or(and(and(ge(tpch100.lineitem.l_quantity, 18), le(tpch100.lineitem.l_quantity, 28)), and(in(tpch100.lineitem.l_shipmode, "AIR", "AIR REG"), eq(tpch100.lineitem.l_shipinstruct, "DELIVER IN PERSON"))), and(and(ge(tpch100.lineitem.l_quantity, 29), le(tpch100.lineitem.l_quantity, 39)), and(in(tpch100.lineitem.l_shipmode, "AIR", "AIR REG"), eq(tpch100.lineitem.l_shipinstruct, "DELIVER IN PERSON"))))) | N/A | N/A |
Bug Report
My cluster version is v7.5.0. The time of running TPC-H Q19 is different with and without
explain analyze. The root cause is that later materialization is only enabled withexplain analyzefor TPC-H Q19. When running Q19 directly, thein(tpch100.lineitem.l_shipmode, "AIR", "AIR REG")does not pushed down to table scan, leading to 2.6s on my server.However, when running Q19 with
explain analyze, thein(tpch100.lineitem.l_shipmode, "AIR", "AIR REG")can be pushed down to table scan, leading to 2.0s.