This is an automated email from the ASF dual-hosted git repository.
yiguolei pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new 52290fed90 [tools](tpch)update queries for better performance (#11523)
52290fed90 is described below
commit 52290fed9066d2622f7e9db7d694a6ae5da3589c
Author: Dongyang Li <hello_step...@qq.com>
AuthorDate: Fri Aug 5 14:04:26 2022 +0800
[tools](tpch)update queries for better performance (#11523)
---
bin/stop_be.sh | 4 ++--
bin/stop_fe.sh | 2 +-
tools/tpch-tools/queries/q1.sql | 2 +-
tools/tpch-tools/queries/q10.sql | 3 ++-
tools/tpch-tools/queries/q11.sql | 25 +++++++++++----------
tools/tpch-tools/queries/q13.sql | 2 +-
tools/tpch-tools/queries/q15.sql | 2 +-
tools/tpch-tools/queries/q18.sql | 3 ++-
tools/tpch-tools/queries/q2.sql | 47 +++++++++++++++++++++++++---------------
tools/tpch-tools/queries/q20.sql | 2 +-
tools/tpch-tools/queries/q21.sql | 3 ++-
tools/tpch-tools/queries/q22.sql | 2 +-
tools/tpch-tools/queries/q4.sql | 2 +-
tools/tpch-tools/queries/q7.sql | 2 +-
tools/tpch-tools/queries/q8.sql | 2 +-
tools/tpch-tools/queries/q9.sql | 24 ++++++++------------
16 files changed, 68 insertions(+), 59 deletions(-)
diff --git a/bin/stop_be.sh b/bin/stop_be.sh
index f46c0d4702..91b772b8ec 100755
--- a/bin/stop_be.sh
+++ b/bin/stop_be.sh
@@ -56,7 +56,7 @@ if [ -f $pidfile ]; then
fi
# check if pid process exist
- if ! kill -0 $pid; then
+ if ! kill -0 $pid 2>&1; then
echo "ERROR: be process $pid does not exist."
exit 1
fi
@@ -71,7 +71,7 @@ if [ -f $pidfile ]; then
# kill pid process and check it
if kill -${signum} $pid >/dev/null 2>&1; then
while true; do
- if kill -0 $pid >/dev/null; then
+ if kill -0 $pid >/dev/null 2>&1; then
echo "waiting be to stop, pid: $pid"
sleep 2
else
diff --git a/bin/stop_fe.sh b/bin/stop_fe.sh
index 0e6a96d37e..8e1ed9c271 100755
--- a/bin/stop_fe.sh
+++ b/bin/stop_fe.sh
@@ -56,7 +56,7 @@ if [ -f $pidfile ]; then
fi
# check if pid process exist
- if ! kill -0 $pid; then
+ if ! kill -0 $pid 2>&1; then
echo "ERROR: fe process $pid does not exist."
exit 1
fi
diff --git a/tools/tpch-tools/queries/q1.sql b/tools/tpch-tools/queries/q1.sql
index e03baab66b..6075f47ccb 100644
--- a/tools/tpch-tools/queries/q1.sql
+++ b/tools/tpch-tools/queries/q1.sql
@@ -15,7 +15,7 @@
-- specific language governing permissions and limitations
-- under the License.
-select /*+SET_VAR(exec_mem_limit=8589934592,
parallel_fragment_exec_instance_num=8, enable_vectorized_engine=true,
batch_size=4096, disable_join_reorder=false,
enable_cost_based_join_reorder=false, enable_projection=false) */
+select /*+SET_VAR(exec_mem_limit=8589934592,
parallel_fragment_exec_instance_num=16, enable_vectorized_engine=true,
batch_size=4096, disable_join_reorder=false,
enable_cost_based_join_reorder=false, enable_projection=false) */
l_returnflag,
l_linestatus,
sum(l_quantity) as sum_qty,
diff --git a/tools/tpch-tools/queries/q10.sql b/tools/tpch-tools/queries/q10.sql
index 654b4144c5..52a0d313d7 100644
--- a/tools/tpch-tools/queries/q10.sql
+++ b/tools/tpch-tools/queries/q10.sql
@@ -17,7 +17,7 @@
-- Modified
-select /*+SET_VAR(exec_mem_limit=8589934592,
parallel_fragment_exec_instance_num=10, enable_vectorized_engine=true,
batch_size=4096, disable_join_reorder=true,
enable_cost_based_join_reorder=false, enable_projection=true) */
+select /*+SET_VAR(exec_mem_limit=8589934592,
parallel_fragment_exec_instance_num=16, enable_vectorized_engine=true,
batch_size=4096, disable_join_reorder=true,
enable_cost_based_join_reorder=false, enable_projection=true) */
c_custkey,
c_name,
sum(t1.l_extendedprice * (1 - t1.l_discount)) as revenue,
@@ -50,3 +50,4 @@ group by
order by
revenue desc
limit 20;
+
diff --git a/tools/tpch-tools/queries/q11.sql b/tools/tpch-tools/queries/q11.sql
index cbd33f3284..99fbf49149 100644
--- a/tools/tpch-tools/queries/q11.sql
+++ b/tools/tpch-tools/queries/q11.sql
@@ -20,25 +20,26 @@ select /*+SET_VAR(exec_mem_limit=8589934592,
parallel_fragment_exec_instance_num
sum(ps_supplycost * ps_availqty) as value
from
partsupp,
- supplier,
- nation
+ (
+ select s_suppkey
+ from supplier, nation
+ where s_nationkey = n_nationkey and n_name = 'GERMANY'
+ ) B
where
- ps_suppkey = s_suppkey
- and s_nationkey = n_nationkey
- and n_name = 'GERMANY'
+ ps_suppkey = B.s_suppkey
group by
ps_partkey having
sum(ps_supplycost * ps_availqty) > (
select
- sum(ps_supplycost * ps_availqty) * 0.0001000000
+ sum(ps_supplycost * ps_availqty) * 0.000002
from
partsupp,
- supplier,
- nation
+ (select s_suppkey
+ from supplier, nation
+ where s_nationkey = n_nationkey and n_name = 'GERMANY'
+ ) A
where
- ps_suppkey = s_suppkey
- and s_nationkey = n_nationkey
- and n_name = 'GERMANY'
+ ps_suppkey = A.s_suppkey
)
order by
- value desc;
+ value desc;
\ No newline at end of file
diff --git a/tools/tpch-tools/queries/q13.sql b/tools/tpch-tools/queries/q13.sql
index f2f2259153..cf8ef42817 100644
--- a/tools/tpch-tools/queries/q13.sql
+++ b/tools/tpch-tools/queries/q13.sql
@@ -17,7 +17,7 @@
-- Modified
-select /*+SET_VAR(exec_mem_limit=8589934592,
parallel_fragment_exec_instance_num=4, enable_vectorized_engine=true,
batch_size=4096, disable_join_reorder=true,
enable_cost_based_join_reorder=true, enable_projection=true) */
+select /*+SET_VAR(exec_mem_limit=8589934592,
parallel_fragment_exec_instance_num=16, enable_vectorized_engine=true,
batch_size=4096, disable_join_reorder=true,
enable_cost_based_join_reorder=true, enable_projection=true) */
c_count,
count(*) as custdist
from
diff --git a/tools/tpch-tools/queries/q15.sql b/tools/tpch-tools/queries/q15.sql
index e5bb082786..db621695cf 100644
--- a/tools/tpch-tools/queries/q15.sql
+++ b/tools/tpch-tools/queries/q15.sql
@@ -15,7 +15,7 @@
-- specific language governing permissions and limitations
-- under the License.
-select /*+SET_VAR(exec_mem_limit=8589934592,
parallel_fragment_exec_instance_num=4, enable_vectorized_engine=true,
batch_size=4096, disable_join_reorder=false,
enable_cost_based_join_reorder=true, enable_projection=true) */
+select /*+SET_VAR(exec_mem_limit=8589934592,
parallel_fragment_exec_instance_num=8, enable_vectorized_engine=true,
batch_size=4096, disable_join_reorder=false,
enable_cost_based_join_reorder=true, enable_projection=true) */
s_suppkey,
s_name,
s_address,
diff --git a/tools/tpch-tools/queries/q18.sql b/tools/tpch-tools/queries/q18.sql
index e43c7df187..23d1513076 100644
--- a/tools/tpch-tools/queries/q18.sql
+++ b/tools/tpch-tools/queries/q18.sql
@@ -17,7 +17,7 @@
-- Modified
-select /*+SET_VAR(exec_mem_limit=8589934592,
parallel_fragment_exec_instance_num=8, enable_vectorized_engine=true,
batch_size=4096, disable_join_reorder=true,
enable_cost_based_join_reorder=true, enable_projection=true) */
+select /*+SET_VAR(exec_mem_limit=8589934592,
parallel_fragment_exec_instance_num=16, enable_vectorized_engine=true,
batch_size=4096, disable_join_reorder=true,
enable_cost_based_join_reorder=true, enable_projection=true) */
c_name,
c_custkey,
t3.o_orderkey,
@@ -55,3 +55,4 @@ order by
t3.o_totalprice desc,
t3.o_orderdate
limit 100;
+
diff --git a/tools/tpch-tools/queries/q2.sql b/tools/tpch-tools/queries/q2.sql
index 57f6b38053..99b049d25a 100644
--- a/tools/tpch-tools/queries/q2.sql
+++ b/tools/tpch-tools/queries/q2.sql
@@ -27,32 +27,43 @@ select /*+SET_VAR(exec_mem_limit=8589934592,
parallel_fragment_exec_instance_num
s_phone,
s_comment
from
-partsupp,
-(
- select ps_partkey, min(ps_supplycost) as ps_s from
- partsupp, supplier, nation, region
- where s_suppkey = ps_suppkey
- and s_nationkey = n_nationkey
- and n_regionkey = r_regionkey
- and r_name = 'EUROPE'
- group by ps_partkey
-) t1,
-supplier,
-part,
-nation,
-region
-where p_partkey = t1.ps_partkey
- and p_partkey = partsupp.ps_partkey
+ partsupp join
+ (
+ select
+ ps_partkey as a_partkey,
+ min(ps_supplycost) as a_min
+ from
+ partsupp,
+ part,
+ supplier,
+ nation,
+ region
+ where
+ p_partkey = ps_partkey
+ and s_suppkey = ps_suppkey
+ and s_nationkey = n_nationkey
+ and n_regionkey = r_regionkey
+ and r_name = 'EUROPE'
+ and p_size = 15
+ and p_type like '%BRASS'
+ group by a_partkey
+ ) A on ps_partkey = a_partkey and ps_supplycost=a_min ,
+ part,
+ supplier,
+ nation,
+ region
+where
+ p_partkey = ps_partkey
and s_suppkey = ps_suppkey
and p_size = 15
and p_type like '%BRASS'
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'EUROPE'
- and ps_supplycost = t1.ps_s
+
order by
s_acctbal desc,
n_name,
s_name,
p_partkey
-limit 100;
+limit 100;
\ No newline at end of file
diff --git a/tools/tpch-tools/queries/q20.sql b/tools/tpch-tools/queries/q20.sql
index 298bd02953..77a2b9de0a 100644
--- a/tools/tpch-tools/queries/q20.sql
+++ b/tools/tpch-tools/queries/q20.sql
@@ -17,7 +17,7 @@
-- Modified
-select /*+SET_VAR(exec_mem_limit=8589934592,
parallel_fragment_exec_instance_num=8, enable_vectorized_engine=true,
batch_size=4096, disable_join_reorder=true,
enable_cost_based_join_reorder=true, enable_projection=true) */
+select /*+SET_VAR(exec_mem_limit=8589934592,
parallel_fragment_exec_instance_num=16, enable_vectorized_engine=true,
batch_size=4096, disable_join_reorder=true,
enable_cost_based_join_reorder=true, enable_projection=true) */
s_name, s_address from
supplier left semi join
(
diff --git a/tools/tpch-tools/queries/q21.sql b/tools/tpch-tools/queries/q21.sql
index 5218aef038..6b13b1ca77 100644
--- a/tools/tpch-tools/queries/q21.sql
+++ b/tools/tpch-tools/queries/q21.sql
@@ -17,7 +17,7 @@
-- Modified
-select /*+SET_VAR(exec_mem_limit=8589934592,
parallel_fragment_exec_instance_num=8, enable_vectorized_engine=true,
batch_size=4096, disable_join_reorder=true,
enable_cost_based_join_reorder=true, enable_projection=true) */
+select /*+SET_VAR(exec_mem_limit=8589934592,
parallel_fragment_exec_instance_num=16, enable_vectorized_engine=true,
batch_size=4096, disable_join_reorder=true,
enable_cost_based_join_reorder=true, enable_projection=true) */
s_name, count(*) as numwait
from orders join
(
@@ -48,3 +48,4 @@ order by
numwait desc,
t4.s_name
limit 100;
+
diff --git a/tools/tpch-tools/queries/q22.sql b/tools/tpch-tools/queries/q22.sql
index 0027caf35b..44a663a0b1 100644
--- a/tools/tpch-tools/queries/q22.sql
+++ b/tools/tpch-tools/queries/q22.sql
@@ -15,7 +15,7 @@
-- specific language governing permissions and limitations
-- under the License.
-select /*+SET_VAR(exec_mem_limit=8589934592,
parallel_fragment_exec_instance_num=8, enable_vectorized_engine=true,
batch_size=4096, disable_join_reorder=false,
enable_cost_based_join_reorder=true, enable_projection=true) */
+select /*+SET_VAR(exec_mem_limit=8589934592,
parallel_fragment_exec_instance_num=16, enable_vectorized_engine=true,
batch_size=4096, disable_join_reorder=false,
enable_cost_based_join_reorder=true, enable_projection=true) */
cntrycode,
count(*) as numcust,
sum(c_acctbal) as totacctbal
diff --git a/tools/tpch-tools/queries/q4.sql b/tools/tpch-tools/queries/q4.sql
index 82dc4f5699..0fc4e80885 100644
--- a/tools/tpch-tools/queries/q4.sql
+++ b/tools/tpch-tools/queries/q4.sql
@@ -17,7 +17,7 @@
-- Modified
-select /*+SET_VAR(exec_mem_limit=8589934592,
parallel_fragment_exec_instance_num=1, enable_vectorized_engine=true,
batch_size=4096, disable_join_reorder=true,
enable_cost_based_join_reorder=false, enable_projection=true) */
+select /*+SET_VAR(exec_mem_limit=8589934592,
parallel_fragment_exec_instance_num=4, enable_vectorized_engine=true,
batch_size=4096, disable_join_reorder=true,
enable_cost_based_join_reorder=false, enable_projection=true) */
o_orderpriority,
count(*) as order_count
from
diff --git a/tools/tpch-tools/queries/q7.sql b/tools/tpch-tools/queries/q7.sql
index 9fff94149a..f1044a31c2 100644
--- a/tools/tpch-tools/queries/q7.sql
+++ b/tools/tpch-tools/queries/q7.sql
@@ -15,7 +15,7 @@
-- specific language governing permissions and limitations
-- under the License.
-select /*+SET_VAR(exec_mem_limit=8589934592,
parallel_fragment_exec_instance_num=4, enable_vectorized_engine=true,
batch_size=4096, disable_join_reorder=false,
enable_cost_based_join_reorder=false, enable_projection=true) */
+select /*+SET_VAR(exec_mem_limit=8589934592,
parallel_fragment_exec_instance_num=8, enable_vectorized_engine=true,
batch_size=4096, disable_join_reorder=false,
enable_cost_based_join_reorder=false, enable_projection=true) */
supp_nation,
cust_nation,
l_year,
diff --git a/tools/tpch-tools/queries/q8.sql b/tools/tpch-tools/queries/q8.sql
index 3df7ef8906..3ebdf57272 100644
--- a/tools/tpch-tools/queries/q8.sql
+++ b/tools/tpch-tools/queries/q8.sql
@@ -17,7 +17,7 @@
-- Modified
-select /*+SET_VAR(exec_mem_limit=8589934592,
parallel_fragment_exec_instance_num=1, enable_vectorized_engine=true,
batch_size=4096, disable_join_reorder=true,
enable_cost_based_join_reorder=false, enable_projection=true) */
+select /*+SET_VAR(exec_mem_limit=8589934592,
parallel_fragment_exec_instance_num=8, enable_vectorized_engine=true,
batch_size=4096, disable_join_reorder=true,
enable_cost_based_join_reorder=false, enable_projection=true) */
o_year,
sum(case
when nation = 'BRAZIL' then volume
diff --git a/tools/tpch-tools/queries/q9.sql b/tools/tpch-tools/queries/q9.sql
index e85f09833f..4960e8b67e 100644
--- a/tools/tpch-tools/queries/q9.sql
+++ b/tools/tpch-tools/queries/q9.sql
@@ -17,7 +17,7 @@
-- Modified
-select /*+SET_VAR(exec_mem_limit=8589934592,
parallel_fragment_exec_instance_num=4, enable_vectorized_engine=true,
batch_size=4096, disable_join_reorder=false,
enable_cost_based_join_reorder=false, enable_projection=true,
enable_remove_no_conjuncts_runtime_filter_policy=true,
runtime_filter_wait_time_ms=10000) */
+select/*+SET_VAR(exec_mem_limit=17179869184,
parallel_fragment_exec_instance_num=4, enable_vectorized_engine=true,
batch_size=4096, disable_join_reorder=false,
enable_cost_based_join_reorder=false, enable_projection=true,
enable_remove_no_conjuncts_runtime_filter_policy=true,
runtime_filter_wait_time_ms=10000) */
nation,
o_year,
sum(amount) as sum_profit
@@ -28,24 +28,18 @@ from
extract(year from o_orderdate) as o_year,
l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as
amount
from
- part,
- supplier,
- lineitem,
- partsupp,
- orders,
- nation
+ lineitem join orders on o_orderkey = l_orderkey
+ join[shuffle] part on p_partkey = l_partkey
+ join[shuffle] partsupp on ps_partkey = l_partkey
+ join[shuffle] supplier on s_suppkey = l_suppkey
+ join[broadcast] nation on s_nationkey = n_nationkey
where
- s_suppkey = l_suppkey
- and ps_suppkey = l_suppkey
- and ps_partkey = l_partkey
- and p_partkey = l_partkey
- and o_orderkey = l_orderkey
- and s_nationkey = n_nationkey
- and p_name like '%green%'
+ ps_suppkey = l_suppkey and
+ p_name like '%green%'
) as profit
group by
nation,
o_year
order by
nation,
- o_year desc;
+ o_year desc;
\ No newline at end of file
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
