This is an automated email from the ASF dual-hosted git repository.
jiafengzheng pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris-website.git
The following commit(s) were added to refs/heads/master by this push:
new 5c83cec637c Performance test documentation update
5c83cec637c is described below
commit 5c83cec637cc2f38cf514f9e9a21de3737bf8610
Author: jiafeng.zhang <zhang...@gmail.com>
AuthorDate: Thu Nov 10 11:30:47 2022 +0800
Performance test documentation update
Performance test documentation update
---
docs/benchmark/ssb.md | 378 ++++++++---
docs/benchmark/tpch.md | 718 +++++++++++++++++++-
.../current/benchmark/ssb.md | 381 ++++++++---
.../current/benchmark/tpch.md | 721 +++++++++++++++++++--
static/images/image-20220614114351241.png | Bin 92508 -> 81771 bytes
static/images/ssb_12_11_015.png | Bin 0 -> 75967 bytes
static/images/ssb_v11_v015_compare.png | Bin 86762 -> 81539 bytes
7 files changed, 1966 insertions(+), 232 deletions(-)
diff --git a/docs/benchmark/ssb.md b/docs/benchmark/ssb.md
index e73ba27e9e7..39aa606ba4e 100644
--- a/docs/benchmark/ssb.md
+++ b/docs/benchmark/ssb.md
@@ -28,16 +28,22 @@ under the License.
[Star Schema Benchmark(SSB)](https://www.cs.umb.edu/~poneil/StarSchemaB.PDF)
is a performance test set in a lightweight data warehouse scenario. Based on
[TPC-H](http://www.tpc.org/tpch/), SSB provides a simplified version of the
star schema dataset, which is mainly used to test the performance of
multi-table association queries under the star schema. . In addition, the
industry usually flattens SSB as a wide table model (hereinafter referred to
as: SSB flat) to test the performance of t [...]
-This document mainly introduces the performance of Doris on the SSB test set.
+This document mainly introduces the performance of Doris on the SSB 100G test
set.
> Note 1: The standard test set including SSB is usually far from the actual
> business scenario, and some tests will perform parameter tuning for the test
> set. Therefore, the test results of the standard test set can only reflect
> the performance of the database in specific scenarios. Users are advised to
> conduct further testing with actual business data.
>
> Note 2: The operations involved in this document are all performed in the
> Ubuntu Server 20.04 environment, and CentOS 7 can also be tested.
-On the 13 queries on the SSB standard test dataset, we performed peer-to-peer
tests based on Doris 1.1 version and Doris 0.15.0 RC04 version, and the overall
performance was improved by 2-3 times.
+We conducted pairwise testing on 13 queries on the SSB standard test dataset
based on Apache Doris 1.2.0-rc01, Apache Doris 1.1.3 and Apache Doris 0.15.0
RC04 versions.
+
+The overall performance improvement on SSB FlAT wide tables was nearly 4x on
Apache Doris 1.2.0-rc01 compared to Apache Doris 1.1.3, and nearly 10x on
Apache Doris 0.15.0 RC04.
+On the standard SSB test SQL, Apache Doris 1.2.0-rc01 delivers an overall
performance improvement of nearly 2X over Apache Doris 1.1.3 and nearly 31X
over Apache Doris 0.15.0 RC04.
+
+
+
## 1. Hardware Environment
| Number of machines | 4 Tencent Cloud hosts (1 FE, 3 BE) |
@@ -52,7 +58,7 @@ On the 13 queries on the SSB standard test dataset, we
performed peer-to-peer te
- Doris deploys 3BE 1FE;
- Kernel version: Linux version 5.4.0-96-generic (buildd@lgw01-amd64-051)
- OS version: Ubuntu Server 20.04 LTS 64 bit
-- Doris software version: Apache Doris 1.1, Apache Doris 0.15.0 RC04
+- Doris software version: Apache Doris 1.2.0-rc01, Apache Doris 1.1.3 ,
Apache Doris 0.15.0 RC04
- JDK: openjdk version "11.0.14" 2022-01-18
## 3. Test data volume
@@ -68,51 +74,68 @@ On the 13 queries on the SSB standard test dataset, we
performed peer-to-peer te
## 4. Test Results
-Here we use the upcoming Doris-1.1 version and Doris-0.15.0 RC04 version for
comparative testing. The test results are as follows:
-
-| Query | Doris-1.1(ms) | Doris-0.15.0 RC04(ms) |
-| ----- | ------------- | --------------------- |
-| Q1.1 | 90 | 250 |
-| Q1.2 | 10 | 30 |
-| Q1.3 | 70 | 120 |
-| Q2.1 | 360 | 900 |
-| Q2.2 | 340 | 1020 |
-| Q2.3 | 260 | 770 |
-| Q3.1 | 550 | 1710 |
-| Q3.2 | 290 | 670 |
-| Q3.3 | 240 | 550 |
-| Q3.4 | 20 | 30 |
-| Q4.1 | 480 | 1250 |
-| Q4.2 | 240 | 400 |
-| Q4.3 | 200 | 330 |
+Here we use Apache Doris 1.2.0-rc01, Apache Doris 1.1.3 and Apache Doris
0.15.0 RC04 versions for comparative testing, with the following results.
+
+| Query | Apache Doris 1.2.0-rc01(ms) | Apache Doris 1.1.3 (ms) | Doris
0.15.0 RC04 (ms) |
+| ----- | ------------- | ------------- | ----------------- |
+| Q1.1 | 20 | 90 | 250 |
+| Q1.2 | 10 | 10 | 30 |
+| Q1.3 | 30 | 70 | 120 |
+| Q2.1 | 90 | 360 | 900 |
+| Q2.2 | 90 | 340 | 1020 |
+| Q2.3 | 60 | 260 | 770 |
+| Q3.1 | 160 | 550 | 1710 |
+| Q3.2 | 80 | 290 | 670 |
+| Q3.3 | 90 | 240 | 550 |
+| Q3.4 | 20 | 20 | 30 |
+| Q4.1 | 140 | 480 | 1250 |
+| Q4.2 | 50 | 240 | 400 |
+| Q4.3 | 30 | 200 | 330 |
+| Total | 880 | 3150 | 8030 |
**Interpretation of results**
- The data set corresponding to the test results is scale 100, about 600
million.
- The test environment is configured to be commonly used by users, including 4
cloud servers, 16-core 64G SSD, and 1 FE and 3 BE deployment.
- Use common user configuration tests to reduce user selection and evaluation
costs, but will not consume so many hardware resources during the entire test
process.
-- The test results are averaged over 3 executions. And the data has been fully
compacted (if the data is tested immediately after the data is imported, the
query delay may be higher than the test result, and the speed of compaction is
being continuously optimized and will be significantly reduced in the future).
-## 5. Environment Preparation
-Please refer to the [official document](../install/install-deploy.md) to
install and deploy Doris to obtain a normal running Doris cluster (at least 1
FE 1 BE, 1 FE 3 BE is recommended).
+## 5. Standard SSB test results
-You can modify BE's configuration file be.conf to add the following
configuration items and restart BE for better query performance.
+Here we use Apache Doris 1.2.0-rc01, Apache Doris 1.1.3 and Apache Doris
0.15.0 RC04 versions for comparative testing, with the following results.
-```shell
-enable_storage_vectorization=true
-enable_low_cardinality_optimize=true
-```
+| Query | Apache Doris 1.2.0-rc01 (ms) | Apache Doris 1.1.3 (ms) | Doris
0.15.0 RC04 (ms) |
+| ----- | ------- | ---------------------- | ------------------------------- |
+| Q1.1 | 40 | 18 | 350 |
+| Q1.2 | 30 | 100 | 80 |
+| Q1.3 | 20 | 70 | 80 |
+| Q2.1 | 350 | 940 | 20680 |
+| Q2.2 | 320 | 750 | 18250 |
+| Q2.3 | 300 | 720 | 14760 |
+| Q3.1 | 650 | 2150 | 22190 |
+| Q3.2 | 260 | 510 | 8360 |
+| Q3.3 | 220 | 450 | 6200 |
+| Q3.4 | 60 | 70 | 160 |
+| Q4.1 | 840 | 1480 | 24320 |
+| Q4.2 | 460 | 560 | 6310 |
+| Q4.3 | 610 | 660 | 10170 |
+| Total | 4160 | 8478 | 131910 |
-The scripts covered in the following documents are stored in
`tools/ssb-tools/` in the Doris codebase.
+**Interpretation of results**
-> **Notice:**
->
-> The above two parameters do not have these two parameters in version 0.15.0
RC04 and do not need to be configured.
+- The data set corresponding to the test results is scale 100, about 600
million.
+- The test environment is configured to be commonly used by users, including 4
cloud servers, 16-core 64G SSD, and 1 FE and 3 BE deployment.
+- Use common user configuration tests to reduce user selection and evaluation
costs, but will not consume so many hardware resources during the entire test
process.
+
+## 6. Environment Preparation
+
+Please first refer to the [official documentation](.
/install/install-deploy.md) for Apache Doris installation and deployment to get
a working Doris cluster (at least 1 FE 1 BE, 1 FE 3 BE recommended).
-## 6. Data Preparation
+The scripts covered in the following documentation are stored in the Apache
Doris codebase:
[ssb-tools](https://github.com/apache/doris/tree/master/tools/ssb-tools)
-### 6.1 Download and install the SSB data generation tool.
+## 7. Data Preparation
+
+### 7.1 Download and install the SSB data generation tool.
Execute the following script to download and compile the
[ssb-dbgen](https://github.com/electrum/ssb-dbgen.git) tool.
@@ -122,7 +145,7 @@ sh build-ssb-dbgen.sh
After successful installation, the `dbgen` binary will be generated in the
`ssb-dbgen/` directory.
-### 6.2 Generate SSB test set
+### 7.2 Generate SSB test set
Execute the following script to generate the SSB dataset:
@@ -146,9 +169,9 @@ With the `-s 100` parameter, the resulting dataset size is:
| supplier | 20万（200000） | 17M | 1 |
| date | 2556 | 228K | 1 |
-### 6.3 Create table
+### 7.3 Create table
-#### 6.3.1 Prepare the `doris-cluster.conf` file.
+#### 7.3.1 Prepare the `doris-cluster.conf` file.
Before calling the import script, you need to write the FE's ip port and other
information in the `doris-cluster.conf` file.
@@ -165,23 +188,15 @@ export PASSWORD='xxx'
export DB="ssb"
````
-#### 6.3.2 Execute the following script to generate and create the SSB table:
+#### 7.3.2 Execute the following script to generate and create the SSB table:
```shell
sh create-ssb-tables.sh
````
-Or copy the build table in
[create-ssb-tables.sql](https://github.com/apache/incubator-doris/tree/master/tools/ssb-tools/ddl/create-ssb-tables.sql)
Statement, executed in Doris.
-
-#### 6.3.3 Execute the following script to generate and create an SSB flat
table:
-
-```shell
-sh create-ssb-flat-table.sh
-````
-
-Or copy
[create-ssb-flat-table.sql](https://github.com/apache/incubator-doris/tree/master/tools/ssb-tools/ddl/create-ssb-flat-table.sql)
The table building statement in , executed in Doris.
+Or copy
[create-ssb-tables.sql](https://github.com/apache/incubator-doris/tree/master/tools/ssb-tools/ddl/create-ssb-tables.sql)
and [
create-ssb-flat-table.sql](https://github.com/apache/incubator-doris/tree/master/tools/ssb-tools/ddl/create-ssb-flat-table.sql)
of the The create table statements are executed in the MySQL client.
-Below is the `lineorder_flat` table building statement. The "lineorder_flat"
table is created in the above `create-ssb-flat-table.sh` script with the
default number of buckets (48 buckets). You can delete this table and adjust
the number of buckets according to your cluster size node configuration, so as
to obtain a better test effect.
+The following is the `lineorder_flat` table build statement. The
`lineorder_flat` table is created in the `create-ssb-flat-table.sh` script
above with the default number of buckets (48 buckets). You can delete this
table and tune this bucketing number according to your cluster size node
configuration to get better one test results.
```sql
CREATE TABLE `lineorder_flat` (
@@ -243,39 +258,23 @@ PROPERTIES (
);
```
-> ### 6.4 Import data
->
-> #### 6.4.1 Import 4 dimension table data
->
-> Because the data volume of these four dimension tables (customer, part,
supplier and date) is small, the import is relatively simple. We use the
following command to import the data of these four tables first:
->
-> ```shell
-> sh load-ssb-dimension-data.sh
-> ````
->
-> #### 6.4.2 Import fact table lineorder.
->
-> Import the lineorder table data by the following command
->
-> ````shell
-> sh load-ssb-fact-data.sh -c 5
-> ````
->
-> `-c 5` means start 5 concurrent thread imports (default is 3). In the case
of a single BE node, the import time of the lineorder data generated by `sh
gen-ssb-data.sh -s 100 -c 100` using `sh load-ssb-fact-data.sh -c 3` is about
10min. Memory overhead is about 5-6GB. If you start more threads, you can speed
up the import, but it will add additional memory overhead.
->
-> > Note: For faster import speed, you can restart BE after adding
`flush_thread_num_per_store=5` in be.conf. This configuration indicates the
number of disk write threads for each data directory, and the default is 2.
Larger data can improve write data throughput, but may increase IO Util.
(Reference value: 1 mechanical disk, when the default is 2, the IO Util during
the import process is about 12%, and when it is set to 5, the IO Util is about
26%. If it is an SSD disk, it is almost 0) .
->
-> #### 6.4.3 Import flat table
->
-> Import the lineorder_flat table data with the following command:
->
-> ```shell
-> sh load-ssb-flat-data.sh
-> ````
+### 7.4 Import data
+
+We use the following command to complete the import of all data from SSB test
set and SSB FLAT wide table data synthesis and import into the table.
+
+```shell
+ sh bin/load-ssb-data.sh -c 10
+```
+
+`-c 5` means start 10 concurrent threads for import (default is 5). In the
single BE node case, the lineorder data generated by `sh gen-ssb-data.sh -s 100
-c 100` will also generate the data of the ssb-flat table at the end, if more
threads are started, it can speed up the import, but it will add extra memory
overhead.
+
+> Notes.
>
-> > Note: Flat table data is imported in the way of 'INSERT INTO ... SELECT
... '.
+> 1. This configuration indicates the number of write threads per data
directory, and the default is 2. Larger data can improve write data throughput,
but may increase IO Util. (Reference value: 1 mechanical disk, at default is 2,
the IO Util during import is about 12%, and when set to 5, the IO Util is about
26%. (In case of SSD disks, it is almost 0).
+>
+> 2. flat table data using 'INSERT INTO ... SELECT ... ' method to import.
-### 6.5 Check imported data
+### 7.5 Check imported data
```sql
select count(*) from part;
@@ -297,9 +296,9 @@ The amount of data should be the same as the number of rows
that generate the da
| supplier | 20万（200000） | 17 MB | 9.143 MB |
| date | 2556 | 228 KB | 34.276 KB |
-### 6.6 Query test
+### 7.6 Query test
-#### 6.6.1 Test SQL
+#### 7.6.1 SSB FLAT Test SQL
```sql
--Q1.1
@@ -386,3 +385,220 @@ GROUP BY YEAR, S_CITY, P_BRAND
ORDER BY YEAR ASC, S_CITY ASC, P_BRAND ASC;
```
+#### 7.6.2 SSB Standard Test SQL
+
+```SQL
+--Q1.1
+SELECT SUM(lo_extendedprice * lo_discount) AS REVENUE
+FROM lineorder, dates
+WHERE
+ lo_orderdate = d_datekey
+ AND d_year = 1993
+ AND lo_discount BETWEEN 1 AND 3
+ AND lo_quantity < 25;
+--Q1.2
+SELECT SUM(lo_extendedprice * lo_discount) AS REVENUE
+FROM lineorder, dates
+WHERE
+ lo_orderdate = d_datekey
+ AND d_yearmonth = 'Jan1994'
+ AND lo_discount BETWEEN 4 AND 6
+ AND lo_quantity BETWEEN 26 AND 35;
+
+--Q1.3
+SELECT
+ SUM(lo_extendedprice * lo_discount) AS REVENUE
+FROM lineorder, dates
+WHERE
+ lo_orderdate = d_datekey
+ AND d_weeknuminyear = 6
+ AND d_year = 1994
+ AND lo_discount BETWEEN 5 AND 7
+ AND lo_quantity BETWEEN 26 AND 35;
+
+--Q2.1
+SELECT SUM(lo_revenue), d_year, p_brand
+FROM lineorder, dates, part, supplier
+WHERE
+ lo_orderdate = d_datekey
+ AND lo_partkey = p_partkey
+ AND lo_suppkey = s_suppkey
+ AND p_category = 'MFGR#12'
+ AND s_region = 'AMERICA'
+GROUP BY d_year, p_brand
+ORDER BY p_brand;
+
+--Q2.2
+SELECT SUM(lo_revenue), d_year, p_brand
+FROM lineorder, dates, part, supplier
+WHERE
+ lo_orderdate = d_datekey
+ AND lo_partkey = p_partkey
+ AND lo_suppkey = s_suppkey
+ AND p_brand BETWEEN 'MFGR#2221' AND 'MFGR#2228'
+ AND s_region = 'ASIA'
+GROUP BY d_year, p_brand
+ORDER BY d_year, p_brand;
+
+--Q2.3
+SELECT SUM(lo_revenue), d_year, p_brand
+FROM lineorder, dates, part, supplier
+WHERE
+ lo_orderdate = d_datekey
+ AND lo_partkey = p_partkey
+ AND lo_suppkey = s_suppkey
+ AND p_brand = 'MFGR#2239'
+ AND s_region = 'EUROPE'
+GROUP BY d_year, p_brand
+ORDER BY d_year, p_brand;
+
+--Q3.1
+SELECT
+ c_nation,
+ s_nation,
+ d_year,
+ SUM(lo_revenue) AS REVENUE
+FROM customer, lineorder, supplier, dates
+WHERE
+ lo_custkey = c_custkey
+ AND lo_suppkey = s_suppkey
+ AND lo_orderdate = d_datekey
+ AND c_region = 'ASIA'
+ AND s_region = 'ASIA'
+ AND d_year >= 1992
+ AND d_year <= 1997
+GROUP BY c_nation, s_nation, d_year
+ORDER BY d_year ASC, REVENUE DESC;
+
+--Q3.2
+SELECT
+ c_city,
+ s_city,
+ d_year,
+ SUM(lo_revenue) AS REVENUE
+FROM customer, lineorder, supplier, dates
+WHERE
+ lo_custkey = c_custkey
+ AND lo_suppkey = s_suppkey
+ AND lo_orderdate = d_datekey
+ AND c_nation = 'UNITED STATES'
+ AND s_nation = 'UNITED STATES'
+ AND d_year >= 1992
+ AND d_year <= 1997
+GROUP BY c_city, s_city, d_year
+ORDER BY d_year ASC, REVENUE DESC;
+
+--Q3.3
+SELECT
+ c_city,
+ s_city,
+ d_year,
+ SUM(lo_revenue) AS REVENUE
+FROM customer, lineorder, supplier, dates
+WHERE
+ lo_custkey = c_custkey
+ AND lo_suppkey = s_suppkey
+ AND lo_orderdate = d_datekey
+ AND (
+ c_city = 'UNITED KI1'
+ OR c_city = 'UNITED KI5'
+ )
+ AND (
+ s_city = 'UNITED KI1'
+ OR s_city = 'UNITED KI5'
+ )
+ AND d_year >= 1992
+ AND d_year <= 1997
+GROUP BY c_city, s_city, d_year
+ORDER BY d_year ASC, REVENUE DESC;
+
+--Q3.4
+SELECT
+ c_city,
+ s_city,
+ d_year,
+ SUM(lo_revenue) AS REVENUE
+FROM customer, lineorder, supplier, dates
+WHERE
+ lo_custkey = c_custkey
+ AND lo_suppkey = s_suppkey
+ AND lo_orderdate = d_datekey
+ AND (
+ c_city = 'UNITED KI1'
+ OR c_city = 'UNITED KI5'
+ )
+ AND (
+ s_city = 'UNITED KI1'
+ OR s_city = 'UNITED KI5'
+ )
+ AND d_yearmonth = 'Dec1997'
+GROUP BY c_city, s_city, d_year
+ORDER BY d_year ASC, REVENUE DESC;
+
+--Q4.1
+SELECT /*+SET_VAR(parallel_fragment_exec_instance_num=4,
enable_vectorized_engine=true, batch_size=4096,
enable_cost_based_join_reorder=true, enable_projection=true) */
+ d_year,
+ c_nation,
+ SUM(lo_revenue - lo_supplycost) AS PROFIT
+FROM dates, customer, supplier, part, lineorder
+WHERE
+ lo_custkey = c_custkey
+ AND lo_suppkey = s_suppkey
+ AND lo_partkey = p_partkey
+ AND lo_orderdate = d_datekey
+ AND c_region = 'AMERICA'
+ AND s_region = 'AMERICA'
+ AND (
+ p_mfgr = 'MFGR#1'
+ OR p_mfgr = 'MFGR#2'
+ )
+GROUP BY d_year, c_nation
+ORDER BY d_year, c_nation;
+
+--Q4.2
+SELECT /*+SET_VAR(parallel_fragment_exec_instance_num=2,
enable_vectorized_engine=true, batch_size=4096,
enable_cost_based_join_reorder=true, enable_projection=true) */
+ d_year,
+ s_nation,
+ p_category,
+ SUM(lo_revenue - lo_supplycost) AS PROFIT
+FROM dates, customer, supplier, part, lineorder
+WHERE
+ lo_custkey = c_custkey
+ AND lo_suppkey = s_suppkey
+ AND lo_partkey = p_partkey
+ AND lo_orderdate = d_datekey
+ AND c_region = 'AMERICA'
+ AND s_region = 'AMERICA'
+ AND (
+ d_year = 1997
+ OR d_year = 1998
+ )
+ AND (
+ p_mfgr = 'MFGR#1'
+ OR p_mfgr = 'MFGR#2'
+ )
+GROUP BY d_year, s_nation, p_category
+ORDER BY d_year, s_nation, p_category;
+
+--Q4.3
+SELECT /*+SET_VAR(parallel_fragment_exec_instance_num=2,
enable_vectorized_engine=true, batch_size=4096,
enable_cost_based_join_reorder=true, enable_projection=true) */
+ d_year,
+ s_city,
+ p_brand,
+ SUM(lo_revenue - lo_supplycost) AS PROFIT
+FROM dates, customer, supplier, part, lineorder
+WHERE
+ lo_custkey = c_custkey
+ AND lo_suppkey = s_suppkey
+ AND lo_partkey = p_partkey
+ AND lo_orderdate = d_datekey
+ AND s_nation = 'UNITED STATES'
+ AND (
+ d_year = 1997
+ OR d_year = 1998
+ )
+ AND p_category = 'MFGR#14'
+GROUP BY d_year, s_city, p_brand
+ORDER BY d_year, s_city, p_brand;
+```
+
diff --git a/docs/benchmark/tpch.md b/docs/benchmark/tpch.md
index f6cebadb6b2..117a4610862 100644
--- a/docs/benchmark/tpch.md
+++ b/docs/benchmark/tpch.md
@@ -34,7 +34,7 @@ This document mainly introduces the performance of Doris on
the TPC-H test set.
>
> Note 2: The operations covered in this document are tested on CentOS 7.x.
-On the 22 queries on the TPC-H standard test dataset, we conducted a
comparison test based on Doris version 1.1 and Doris 0.15.0 RC04 version, and
the overall performance was improved by 3-4 times. In individual scenarios, a
ten-fold improvement has been achieved.
+On 22 queries on the TPC-H standard test dataset, we conducted pairwise tests
based on Apache Doris 1.2.0-rc01, Apache Doris 1.1.3 and Apache Doris 0.15.0
RC04 versions. The overall performance of Apache Doris 1.2.0-rc01 is nearly 3
times better than that of Apache Doris 1.1.3 and nearly 11 times better than
that of Apache Doris 0.15.0 RC04.
@@ -53,12 +53,12 @@ On the 22 queries on the TPC-H standard test dataset, we
conducted a comparison
- Doris deploys 3BE 1FE;
- Kernel version: Linux version 5.4.0-96-generic (buildd@lgw01-amd64-051)
- OS version: CentOS 7.8
-- Doris software version: Apache Doris 1.1, Apache Doris 0.15.0 RC04
+- Doris software version: Apache Doris 1.2.0-rc01、 Apache Doris 1.1.3 、 Apache
Doris 0.15.0 RC04
- JDK: openjdk version "11.0.14" 2022-01-18
## 3. Test Data Volume
-The entire test simulation generates 100G of data and is imported into Doris
0.15.0 RC04 and Doris 1.1 versions for testing. The following is the relevant
description of the table and the amount of data.
+The entire test simulation generates 100G of data and is imported into Apache
Doris 1.2.0-rc01, Apache Doris 1.1.3 and Apache Doris 0.15.0 RC04 versions
for testing. The following is the relevant description of the table and the
amount of data.
| TPC-H Table Name | Rows | data size | remark |
| :--------------- | :---------- | ---------- | :----- |
@@ -75,9 +75,9 @@ The entire test simulation generates 100G of data and is
imported into Doris 0.1
TPCH 22 test query statements ：
[TPCH-Query-SQL](https://github.com/apache/incubator-doris/tree/master/tools/tpch-tools/queries)
-Notice:
+**Notice:**
-The following four parameters in the above SQL do not exist in 0.15.0 RC04.
When executed in 0.15.0 RC04, remove them:
+The following four parameters in the above SQL are not present in Apache Doris
0.15.0 RC04 and are removed during execution.
```
1. enable_vectorized_engine=true,
@@ -88,40 +88,39 @@ The following four parameters in the above SQL do not exist
in 0.15.0 RC04. When
## 5. Test Result
-Here we use the upcoming Doris-1.1 version and Doris-0.15.0 RC04 version for
comparative testing. The test results are as follows:
-
-| Query | Doris-1.1(s) | 0.15.0 RC04(s) |
-| --------- | ------------ | -------------- |
-| Q1 | 3.75 | 28.63 |
-| Q2 | 4.22 | 7.88 |
-| Q3 | 2.64 | 9.39 |
-| Q4 | 1.5 | 9.3 |
-| Q5 | 2.15 | 4.11 |
-| Q6 | 0.19 | 0.43 |
-| Q7 | 1.04 | 1.61 |
-| Q8 | 1.75 | 50.35 |
-| Q9 | 7.94 | 16.34 |
-| Q10 | 1.41 | 5.21 |
-| Q11 | 0.35 | 1.72 |
-| Q12 | 0.57 | 5.39 |
-| Q13 | 8.15 | 20.88 |
-| Q14 | 0.3 | |
-| Q15 | 0.66 | 1.86 |
-| Q16 | 0.79 | 1.32 |
-| Q17 | 1.51 | 26.67 |
-| Q18 | 3.364 | 11.77 |
-| Q19 | 0.829 | 1.71 |
-| Q20 | 2.77 | 5.2 |
-| Q21 | 4.47 | 10.34 |
-| Q22 | 0.9 | 3.22 |
-| **total** | **51.253** | **223.33** |
+Here we use Apache Doris 1.2.0-rc01, Apache Doris 1.1.3 and Apache Doris
0.15.0 RC04 versions for comparison tests with the following results.
+
+| Query | Apache Doris 1.2.0-rc01 (s) | Apache Doris 1.1.3 (s) | Apache
Doris 0.15.0 RC04 (s) |
+| -------- | --------------------------- | ---------------------- |
---------------------------- |
+| Q1 | 2.12 | 3.75 | 28.63
|
+| Q2 | 0.20 | 4.22 | 7.88
|
+| Q3 | 0.62 | 2.64 | 9.39
|
+| Q4 | 0.61 | 1.5 | 9.3
|
+| Q5 | 1.05 | 2.15 | 4.11
|
+| Q6 | 0.08 | 0.19 | 0.43
|
+| Q7 | 0.58 | 1.04 | 1.61
|
+| Q8 | 0.72 | 1.75 | 50.35
|
+| Q9 | 3.61 | 7.94 | 16.34
|
+| Q10 | 1.26 | 1.41 | 5.21
|
+| Q11 | 0.15 | 0.35 | 1.72
|
+| Q12 | 0.21 | 0.57 | 5.39
|
+| Q13 | 2.62 | 8.15 | 20.88
|
+| Q14 | 0.16 | 0.3 |
|
+| Q15 | 0.30 | 0.66 | 1.86
|
+| Q16 | 0.38 | 0.79 | 1.32
|
+| Q17 | 0.65 | 1.51 | 26.67
|
+| Q18 | 2.28 | 3.364 | 11.77
|
+| Q19 | 0.20 | 0.829 | 1.71
|
+| Q20 | 0.21 | 2.77 | 5.2
|
+| Q21 | 1.17 | 4.47 | 10.34
|
+| Q22 | 0.46 | 0.9 | 3.22
|
+| **Total** | **19.64** | **51.253** |
**223.33** |
- **Result description**
- The data set corresponding to the test results is scale 100, about 600
million.
- The test environment is configured to be commonly used by users, including
4 cloud servers, 16-core 64G SSD, and 1 FE and 3 BE deployment.
- Use common user configuration tests to reduce user selection and
evaluation costs, but will not consume so many hardware resources during the
entire test process.
- - The test results are averaged over 3 executions. And the data has been
fully compacted (if the data is tested immediately after the data is imported,
the query delay may be higher than the test result, and the speed of compaction
is being continuously optimized, and will be significantly reduced in the
future).
- - 0.15 RC04 Q14 execution failed in TPC-H test, unable to complete query.
+ - Apache Doris 0.15 RC04 Q14 execution failed in TPC-H test, unable to
complete query.
## 6. Environmental Preparation
@@ -212,6 +211,8 @@ select count(*) from revenue0;
### 7.6 Query Test
+#### 7.6.1 Executing Query Scripts
+
Execute the above test SQL or execute the following command
```
@@ -225,5 +226,654 @@ Execute the above test SQL or execute the following
command
>2. Doris' new query optimizer will be released in subsequent versions
>3. Set `set mem_exec_limit=8G` before executing the query
+#### 7.6.2 Single SQL Execution
+
+```SQL
+--Q1
+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) */
+ l_returnflag,
+ l_linestatus,
+ sum(l_quantity) as sum_qty,
+ sum(l_extendedprice) as sum_base_price,
+ sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
+ sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
+ avg(l_quantity) as avg_qty,
+ avg(l_extendedprice) as avg_price,
+ avg(l_discount) as avg_disc,
+ count(*) as count_order
+from
+ lineitem
+where
+ l_shipdate <= date '1998-12-01' - interval '90' day
+group by
+ l_returnflag,
+ l_linestatus
+order by
+ l_returnflag,
+ l_linestatus;
+
+--Q2
+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) */
+ s_acctbal,
+ s_name,
+ n_name,
+ p_partkey,
+ p_mfgr,
+ s_address,
+ s_phone,
+ s_comment
+from
+ 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'
+
+order by
+ s_acctbal desc,
+ n_name,
+ s_name,
+ p_partkey
+limit 100;
+
+--Q3
+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,
runtime_filter_wait_time_ms=10000) */
+ l_orderkey,
+ sum(l_extendedprice * (1 - l_discount)) as revenue,
+ o_orderdate,
+ o_shippriority
+from
+ (
+ select l_orderkey, l_extendedprice, l_discount, o_orderdate,
o_shippriority, o_custkey from
+ lineitem join orders
+ where l_orderkey = o_orderkey
+ and o_orderdate < date '1995-03-15'
+ and l_shipdate > date '1995-03-15'
+ ) t1 join customer c
+ on c.c_custkey = t1.o_custkey
+ where c_mktsegment = 'BUILDING'
+group by
+ l_orderkey,
+ o_orderdate,
+ o_shippriority
+order by
+ revenue desc,
+ o_orderdate
+limit 10;
+
+--Q4
+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
+ (
+ select
+ *
+ from
+ lineitem
+ where l_commitdate < l_receiptdate
+ ) t1
+ right semi join orders
+ on t1.l_orderkey = o_orderkey
+where
+ o_orderdate >= date '1993-07-01'
+ and o_orderdate < date '1993-07-01' + interval '3' month
+group by
+ o_orderpriority
+order by
+ o_orderpriority;
+
+--Q5
+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) */
+ n_name,
+ sum(l_extendedprice * (1 - l_discount)) as revenue
+from
+ customer,
+ orders,
+ lineitem,
+ supplier,
+ nation,
+ region
+where
+ c_custkey = o_custkey
+ and l_orderkey = o_orderkey
+ and l_suppkey = s_suppkey
+ and c_nationkey = s_nationkey
+ and s_nationkey = n_nationkey
+ and n_regionkey = r_regionkey
+ and r_name = 'ASIA'
+ and o_orderdate >= date '1994-01-01'
+ and o_orderdate < date '1994-01-01' + interval '1' year
+group by
+ n_name
+order by
+ revenue desc;
+
+--Q6
+select /*+SET_VAR(exec_mem_limit=8589934592,
parallel_fragment_exec_instance_num=1, enable_vectorized_engine=true,
batch_size=4096, disable_join_reorder=false,
enable_cost_based_join_reorder=false, enable_projection=true) */
+ sum(l_extendedprice * l_discount) as revenue
+from
+ lineitem
+where
+ l_shipdate >= date '1994-01-01'
+ and l_shipdate < date '1994-01-01' + interval '1' year
+ and l_discount between .06 - 0.01 and .06 + 0.01
+ and l_quantity < 24;
+
+--Q7
+select /*+SET_VAR(exec_mem_limit=458589934592,
parallel_fragment_exec_instance_num=2, 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,
+ sum(volume) as revenue
+from
+ (
+ select
+ n1.n_name as supp_nation,
+ n2.n_name as cust_nation,
+ extract(year from l_shipdate) as l_year,
+ l_extendedprice * (1 - l_discount) as volume
+ from
+ supplier,
+ lineitem,
+ orders,
+ customer,
+ nation n1,
+ nation n2
+ where
+ s_suppkey = l_suppkey
+ and o_orderkey = l_orderkey
+ and c_custkey = o_custkey
+ and s_nationkey = n1.n_nationkey
+ and c_nationkey = n2.n_nationkey
+ and (
+ (n1.n_name = 'FRANCE' and n2.n_name = 'GERMANY')
+ or (n1.n_name = 'GERMANY' and n2.n_name = 'FRANCE')
+ )
+ and l_shipdate between date '1995-01-01' and date '1996-12-31'
+ ) as shipping
+group by
+ supp_nation,
+ cust_nation,
+ l_year
+order by
+ supp_nation,
+ cust_nation,
+ l_year;
+
+--Q8
+
+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
+ else 0
+ end) / sum(volume) as mkt_share
+from
+ (
+ select
+ extract(year from o_orderdate) as o_year,
+ l_extendedprice * (1 - l_discount) as volume,
+ n2.n_name as nation
+ from
+ lineitem,
+ orders,
+ customer,
+ supplier,
+ part,
+ nation n1,
+ nation n2,
+ region
+ where
+ p_partkey = l_partkey
+ and s_suppkey = l_suppkey
+ and l_orderkey = o_orderkey
+ and o_custkey = c_custkey
+ and c_nationkey = n1.n_nationkey
+ and n1.n_regionkey = r_regionkey
+ and r_name = 'AMERICA'
+ and s_nationkey = n2.n_nationkey
+ and o_orderdate between date '1995-01-01' and date '1996-12-31'
+ and p_type = 'ECONOMY ANODIZED STEEL'
+ ) as all_nations
+group by
+ o_year
+order by
+ o_year;
+
+--Q9
+select/*+SET_VAR(exec_mem_limit=37179869184,
parallel_fragment_exec_instance_num=2, 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=100000) */
+ nation,
+ o_year,
+ sum(amount) as sum_profit
+from
+ (
+ select
+ n_name as nation,
+ extract(year from o_orderdate) as o_year,
+ l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as
amount
+ from
+ 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
+ ps_suppkey = l_suppkey and
+ p_name like '%green%'
+ ) as profit
+group by
+ nation,
+ o_year
+order by
+ nation,
+ o_year desc;
+
+--Q10
+
+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) */
+ c_custkey,
+ c_name,
+ sum(t1.l_extendedprice * (1 - t1.l_discount)) as revenue,
+ c_acctbal,
+ n_name,
+ c_address,
+ c_phone,
+ c_comment
+from
+ customer,
+ (
+ select o_custkey,l_extendedprice,l_discount from lineitem, orders
+ where l_orderkey = o_orderkey
+ and o_orderdate >= date '1993-10-01'
+ and o_orderdate < date '1993-10-01' + interval '3' month
+ and l_returnflag = 'R'
+ ) t1,
+ nation
+where
+ c_custkey = t1.o_custkey
+ 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;
+
+--Q11
+select /*+SET_VAR(exec_mem_limit=8589934592,
parallel_fragment_exec_instance_num=2, enable_vectorized_engine=true,
batch_size=4096, disable_join_reorder=false,
enable_cost_based_join_reorder=true, enable_projection=true) */
+ ps_partkey,
+ sum(ps_supplycost * ps_availqty) as value
+from
+ partsupp,
+ (
+ select s_suppkey
+ from supplier, nation
+ where s_nationkey = n_nationkey and n_name = 'GERMANY'
+ ) B
+where
+ ps_suppkey = B.s_suppkey
+group by
+ ps_partkey having
+ sum(ps_supplycost * ps_availqty) > (
+ select
+ sum(ps_supplycost * ps_availqty) * 0.000002
+ from
+ partsupp,
+ (select s_suppkey
+ from supplier, nation
+ where s_nationkey = n_nationkey and n_name = 'GERMANY'
+ ) A
+ where
+ ps_suppkey = A.s_suppkey
+ )
+order by
+ value desc;
+
+--Q12
+
+select /*+SET_VAR(exec_mem_limit=8589934592,
parallel_fragment_exec_instance_num=2, enable_vectorized_engine=true,
batch_size=4096, disable_join_reorder=false,
enable_cost_based_join_reorder=true, enable_projection=true) */
+ l_shipmode,
+ sum(case
+ when o_orderpriority = '1-URGENT'
+ or o_orderpriority = '2-HIGH'
+ then 1
+ else 0
+ end) as high_line_count,
+ sum(case
+ when o_orderpriority <> '1-URGENT'
+ and o_orderpriority <> '2-HIGH'
+ then 1
+ else 0
+ end) as low_line_count
+from
+ orders,
+ lineitem
+where
+ o_orderkey = l_orderkey
+ and l_shipmode in ('MAIL', 'SHIP')
+ and l_commitdate < l_receiptdate
+ and l_shipdate < l_commitdate
+ and l_receiptdate >= date '1994-01-01'
+ and l_receiptdate < date '1994-01-01' + interval '1' year
+group by
+ l_shipmode
+order by
+ l_shipmode;
+
+--Q13
+select /*+SET_VAR(exec_mem_limit=45899345920,
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
+ (
+ select
+ c_custkey,
+ count(o_orderkey) as c_count
+ from
+ orders right outer join customer on
+ c_custkey = o_custkey
+ and o_comment not like '%special%requests%'
+ group by
+ c_custkey
+ ) as c_orders
+group by
+ c_count
+order by
+ custdist desc,
+ c_count desc;
+
+--Q14
+
+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,
runtime_filter_mode=OFF) */
+ 100.00 * sum(case
+ when p_type like 'PROMO%'
+ then l_extendedprice * (1 - l_discount)
+ else 0
+ end) / sum(l_extendedprice * (1 - l_discount)) as promo_revenue
+from
+ part,
+ lineitem
+where
+ l_partkey = p_partkey
+ and l_shipdate >= date '1995-09-01'
+ and l_shipdate < date '1995-09-01' + interval '1' month;
+
+--Q15
+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,
+ s_phone,
+ total_revenue
+from
+ supplier,
+ revenue0
+where
+ s_suppkey = supplier_no
+ and total_revenue = (
+ select
+ max(total_revenue)
+ from
+ revenue0
+ )
+order by
+ s_suppkey;
+
+--Q16
+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) */
+ p_brand,
+ p_type,
+ p_size,
+ count(distinct ps_suppkey) as supplier_cnt
+from
+ partsupp,
+ part
+where
+ p_partkey = ps_partkey
+ and p_brand <> 'Brand#45'
+ and p_type not like 'MEDIUM POLISHED%'
+ and p_size in (49, 14, 23, 45, 19, 3, 36, 9)
+ and ps_suppkey not in (
+ select
+ s_suppkey
+ from
+ supplier
+ where
+ s_comment like '%Customer%Complaints%'
+ )
+group by
+ p_brand,
+ p_type,
+ p_size
+order by
+ supplier_cnt desc,
+ p_brand,
+ p_type,
+ p_size;
+
+--Q17
+select /*+SET_VAR(exec_mem_limit=8589934592,
parallel_fragment_exec_instance_num=1, enable_vectorized_engine=true,
batch_size=4096, disable_join_reorder=false,
enable_cost_based_join_reorder=true, enable_projection=true) */
+ sum(l_extendedprice) / 7.0 as avg_yearly
+from
+ lineitem join [broadcast]
+ part p1 on p1.p_partkey = l_partkey
+where
+ p1.p_brand = 'Brand#23'
+ and p1.p_container = 'MED BOX'
+ and l_quantity < (
+ select
+ 0.2 * avg(l_quantity)
+ from
+ lineitem join [broadcast]
+ part p2 on p2.p_partkey = l_partkey
+ where
+ l_partkey = p1.p_partkey
+ and p2.p_brand = 'Brand#23'
+ and p2.p_container = 'MED BOX'
+ );
+
+--Q18
+
+select /*+SET_VAR(exec_mem_limit=45899345920,
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) */
+ c_name,
+ c_custkey,
+ t3.o_orderkey,
+ t3.o_orderdate,
+ t3.o_totalprice,
+ sum(t3.l_quantity)
+from
+customer join
+(
+ select * from
+ lineitem join
+ (
+ select * from
+ orders left semi join
+ (
+ select
+ l_orderkey
+ from
+ lineitem
+ group by
+ l_orderkey having sum(l_quantity) > 300
+ ) t1
+ on o_orderkey = t1.l_orderkey
+ ) t2
+ on t2.o_orderkey = l_orderkey
+) t3
+on c_custkey = t3.o_custkey
+group by
+ c_name,
+ c_custkey,
+ t3.o_orderkey,
+ t3.o_orderdate,
+ t3.o_totalprice
+order by
+ t3.o_totalprice desc,
+ t3.o_orderdate
+limit 100;
+
+--Q19
+
+select /*+SET_VAR(exec_mem_limit=8589934592,
parallel_fragment_exec_instance_num=2, enable_vectorized_engine=true,
batch_size=4096, disable_join_reorder=false,
enable_cost_based_join_reorder=false, enable_projection=true) */
+ sum(l_extendedprice* (1 - l_discount)) as revenue
+from
+ lineitem,
+ part
+where
+ (
+ p_partkey = l_partkey
+ and p_brand = 'Brand#12'
+ and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
+ and l_quantity >= 1 and l_quantity <= 1 + 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#23'
+ and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
+ and l_quantity >= 10 and l_quantity <= 10 + 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#34'
+ and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
+ and l_quantity >= 20 and l_quantity <= 20 + 10
+ and p_size between 1 and 15
+ and l_shipmode in ('AIR', 'AIR REG')
+ and l_shipinstruct = 'DELIVER IN PERSON'
+ );
+
+--Q20
+select /*+SET_VAR(exec_mem_limit=8589934592,
parallel_fragment_exec_instance_num=2, enable_vectorized_engine=true,
batch_size=4096, disable_join_reorder=true,
enable_cost_based_join_reorder=true, enable_projection=true,
runtime_bloom_filter_size=551943) */
+s_name, s_address from
+supplier left semi join
+(
+ select * from
+ (
+ select l_partkey,l_suppkey, 0.5 * sum(l_quantity) as l_q
+ from lineitem
+ where l_shipdate >= date '1994-01-01'
+ and l_shipdate < date '1994-01-01' + interval '1' year
+ group by l_partkey,l_suppkey
+ ) t2 join
+ (
+ select ps_partkey, ps_suppkey, ps_availqty
+ from partsupp left semi join part
+ on ps_partkey = p_partkey and p_name like 'forest%'
+ ) t1
+ on t2.l_partkey = t1.ps_partkey and t2.l_suppkey = t1.ps_suppkey
+ and t1.ps_availqty > t2.l_q
+) t3
+on s_suppkey = t3.ps_suppkey
+join nation
+where s_nationkey = n_nationkey
+ and n_name = 'CANADA'
+order by s_name;
+
+--Q21
+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) */
+s_name, count(*) as numwait
+from
+ lineitem l2 right semi join
+ (
+ select * from
+ lineitem l3 right anti join
+ (
+ select * from
+ orders join lineitem l1 on l1.l_orderkey = o_orderkey and o_orderstatus
= 'F'
+ join
+ (
+ select * from
+ supplier join nation
+ where s_nationkey = n_nationkey
+ and n_name = 'SAUDI ARABIA'
+ ) t1
+ where t1.s_suppkey = l1.l_suppkey and l1.l_receiptdate > l1.l_commitdate
+ ) t2
+ on l3.l_orderkey = t2.l_orderkey and l3.l_suppkey <> t2.l_suppkey and
l3.l_receiptdate > l3.l_commitdate
+ ) t3
+ on l2.l_orderkey = t3.l_orderkey and l2.l_suppkey <> t3.l_suppkey
+
+group by
+ t3.s_name
+order by
+ numwait desc,
+ t3.s_name
+limit 100;
+
+--Q21
+
+with tmp as (select
+ avg(c_acctbal) as av
+ from
+ customer
+ where
+ c_acctbal > 0.00
+ and substring(c_phone, 1, 2) in
+ ('13', '31', '23', '29', '30', '18', '17'))
+
+select /*+SET_VAR(exec_mem_limit=8589934592,
parallel_fragment_exec_instance_num=4,runtime_bloom_filter_size=4194304) */
+ cntrycode,
+ count(*) as numcust,
+ sum(c_acctbal) as totacctbal
+from
+ (
+ select
+ substring(c_phone, 1, 2) as cntrycode,
+ c_acctbal
+ from
+ orders right anti join customer c on o_custkey = c.c_custkey
join tmp on c.c_acctbal > tmp.av
+ where
+ substring(c_phone, 1, 2) in
+ ('13', '31', '23', '29', '30', '18', '17')
+ ) as custsale
+group by
+ cntrycode
+order by
+ cntrycode;
+```
+
diff --git a/i18n/zh-CN/docusaurus-plugin-content-docs/current/benchmark/ssb.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/benchmark/ssb.md
index 5cbc2055667..403feb6c35e 100644
--- a/i18n/zh-CN/docusaurus-plugin-content-docs/current/benchmark/ssb.md
+++ b/i18n/zh-CN/docusaurus-plugin-content-docs/current/benchmark/ssb.md
@@ -1,6 +1,6 @@
---
{
- "title": "Star-Schema-Benchmark 测试",
+ "title": "Star-Schema-Benchmark",
"language": "zh-CN"
}
---
@@ -28,16 +28,22 @@ under the License.
[Star Schema Benchmark(SSB)](https://www.cs.umb.edu/~poneil/StarSchemaB.PDF)
是一个轻量级的数仓场景下的性能测试集。SSB基于 [TPC-H](http://www.tpc.org/tpch/)
提供了一个简化版的星型模型数据集，主要用于测试在星型模型下，多表关联查询的性能表现。另外，业界内通常也会将SSB打平为宽表模型（以下简称：SSB
flat），来测试查询引擎的性能，参考[Clickhouse](https://clickhouse.com/docs/zh/getting-started/example-datasets/star-schema)。
-本文档主要介绍 Doris 在 SSB 测试集上的性能表现。
+本文档主要介绍Apache Doris 在 SSB 100G 测试集上的性能表现。
> 注1：包括 SSB
> 在内的标准测试集通常和实际业务场景差距较大，并且部分测试会针对测试集进行参数调优。所以标准测试集的测试结果仅能反映数据库在特定场景下的性能表现。建议用户使用实际业务数据进行进一步的测试。
>
> 注2：本文档涉及的操作都在 Ubuntu Server 20.04 环境进行，CentOS 7 也可测试。
-在 SSB 标准测试数据集上的 13 个查询上，我们基于 Doris 1.1 版本和 Doris 0.15.0 RC04 版本进行了对别测试，整体性能提升了
2-3 倍。
+在 SSB 标准测试数据集上的 13 个查询上，我们基于 Apache Doris 1.2.0-rc01， Apache Doris 1.1.3 及
Apache Doris 0.15.0 RC04 版本进行了对别测试。
+
+在 SSB FlAT 宽表上， Apache Doris 1.2.0-rc01上相对 Apache Doris 1.1.3 整体性能提升了将近4倍，相对于
Apache Doris 0.15.0 RC04 ,性能提升了将近10倍 。
+在标准的 SSB 测试SQL上， Apache Doris 1.2.0-rc01 上相对 Apache Doris 1.1.3
整体性能提升了将近2倍，相对于 Apache Doris 0.15.0 RC04 ,性能提升了将近 31 倍 。
+
+
+
## 1. 硬件环境
| 机器数量 | 4 台腾讯云主机（1个FE，3个BE） |
@@ -52,7 +58,7 @@ under the License.
- Doris部署 3BE 1FE；
- 内核版本：Linux version 5.4.0-96-generic (buildd@lgw01-amd64-051)
- 操作系统版本：Ubuntu Server 20.04 LTS 64位
-- Doris 软件版本：Apache Doris 1.1 、Apache Doris 0.15.0 RC04
+- Doris 软件版本： Apache Doris 1.2.0-rc01、Apache Doris 1.1.3 及 Apache Doris 0.15.0
RC04
- JDK：openjdk version "11.0.14" 2022-01-18
## 3. 测试数据量
@@ -66,53 +72,70 @@ under the License.
| date | 2,556 | 日期表 |
| lineorder_flat | 600,037,902 | 数据展平后的宽表 |
-## 4. 测试结果
-
-这里我们使用即将发布的 Doris-1.1版本和 Doris-0.15.0 RC04 版本进行对比测试，测试结果如下：
-
-| Query | Doris-1.1(ms) | Doris-0.15.0 RC04(ms) |
-| ----- | ------------- | --------------------- |
-| Q1.1 | 90 | 250 |
-| Q1.2 | 10 | 30 |
-| Q1.3 | 70 | 120 |
-| Q2.1 | 360 | 900 |
-| Q2.2 | 340 | 1020 |
-| Q2.3 | 260 | 770 |
-| Q3.1 | 550 | 1710 |
-| Q3.2 | 290 | 670 |
-| Q3.3 | 240 | 550 |
-| Q3.4 | 20 | 30 |
-| Q4.1 | 480 | 1250 |
-| Q4.2 | 240 | 400 |
-| Q4.3 | 200 | 330 |
+## 4. SSB 宽表测试结果
+
+这里我们使用 Apache Doris 1.2.0-rc01、 Apache Doris 1.1.3 及 Apache Doris 0.15.0 RC04
版本进行对比测试，测试结果如下：
+
+
+| Query | Apache Doris 1.2.0-rc01(ms) | Apache Doris 1.1.3(ms) | Apache Doris
0.15.0 RC04(ms) |
+| ----- | ------------- | ------------- | ----------------- |
+| Q1.1 | 20 | 90 | 250 |
+| Q1.2 | 10 | 10 | 30 |
+| Q1.3 | 30 | 70 | 120 |
+| Q2.1 | 90 | 360 | 900 |
+| Q2.2 | 90 | 340 | 1020 |
+| Q2.3 | 60 | 260 | 770 |
+| Q3.1 | 160 | 550 | 1710 |
+| Q3.2 | 80 | 290 | 670 |
+| Q3.3 | 90 | 240 | 550 |
+| Q3.4 | 20 | 20 | 30 |
+| Q4.1 | 140 | 480 | 1250 |
+| Q4.2 | 50 | 240 | 400 |
+| Q4.3 | 30 | 200 | 330 |
+| 合计 | 880 | 3150 | 8030 |
**结果说明**
- 测试结果对应的数据集为scale 100, 约6亿条。
- 测试环境配置为用户常用配置，云服务器4台，16核 64G SSD，1 FE 3 BE 部署。
- 选用用户常见配置测试以降低用户选型评估成本，但整个测试过程中不会消耗如此多的硬件资源。
-- 测试结果为3次执行取平均值。并且数据经过充分的
compaction（如果在刚导入数据后立刻测试，则查询延迟可能高于本测试结果，compaction的速度正在持续优化中，未来会显著降低）。
-## 5. 环境准备
+## 5. 标准 SSB 测试结果
+
+这里我们使用 Apache Doris 1.2.0-rc01、Apache Doris 1.1.3 及 Apache Doris 0.15.0 RC04
版本进行对比测试，测试结果如下：
+
+| Query | Apache Doris 1.2.0-rc01(ms) | Apache Doris 1.1.3 (ms) | Apache
Doris 0.15.0 RC04(ms) |
+| ----- | ------- | ---------------------- | ------------------------------- |
+| Q1.1 | 40 | 18 | 350 |
+| Q1.2 | 30 | 100 | 80 |
+| Q1.3 | 20 | 70 | 80 |
+| Q2.1 | 350 | 940 | 20680 |
+| Q2.2 | 320 | 750 | 18250 |
+| Q2.3 | 300 | 720 | 14760 |
+| Q3.1 | 650 | 2150 | 22190 |
+| Q3.2 | 260 | 510 | 8360 |
+| Q3.3 | 220 | 450 | 6200 |
+| Q3.4 | 60 | 70 | 160 |
+| Q4.1 | 840 | 1480 | 24320 |
+| Q4.2 | 460 | 560 | 6310 |
+| Q4.3 | 610 | 660 | 10170 |
+| 合计 | 4160 | 8478 | 131910 |
-请先参照 [官方文档](../install/install-deploy.md) 进行 Doris 的安装部署，以获得一个正常运行中的 Doris
集群（至少包含 1 FE 1 BE，推荐 1 FE 3 BE）。
+**结果说明**
-可修改 BE 的配置文件 be.conf 添加以下配置项，重启BE，以获得更好的查询性能。
+- 测试结果对应的数据集为scale 100, 约6亿条。
+- 测试环境配置为用户常用配置，云服务器4台，16核 64G SSD，1 FE 3 BE 部署。
+- 选用用户常见配置测试以降低用户选型评估成本，但整个测试过程中不会消耗如此多的硬件资源。
-```shell
-enable_storage_vectorization=true
-enable_low_cardinality_optimize=true
-```
+## 6. 环境准备
-以下文档中涉及的脚本都存放在 Doris 代码库的 `tools/ssb-tools/` 下。
+请先参照 [官方文档](../install/install-deploy.md) 进行 Apache Doris 的安装部署，以获得一个正常运行中的
Doris 集群（至少包含 1 FE 1 BE，推荐 1 FE 3 BE）。
-> **注意：**
->
-> 上面这两个参数在 0.15.0 RC04 版本里没有这两个参数，不需要配置。
+以下文档中涉及的脚本都存放在 Apache Doris
代码库：[ssb-tools](https://github.com/apache/doris/tree/master/tools/ssb-tools)
-## 6. 数据准备
+## 7. 数据准备
-### 6.1 下载安装 SSB 数据生成工具。
+### 7.1 下载安装 SSB 数据生成工具。
执行以下脚本下载并编译 [ssb-dbgen](https://github.com/electrum/ssb-dbgen.git) 工具。
@@ -122,7 +145,7 @@ sh build-ssb-dbgen.sh
安装成功后，将在 `ssb-dbgen/` 目录下生成 `dbgen` 二进制文件。
-### 6.2 生成 SSB 测试集
+### 7.2 生成 SSB 测试集
执行以下脚本生成 SSB 数据集：
@@ -146,9 +169,9 @@ sh gen-ssb-data.sh -s 100 -c 100
| supplier | 20万（200000） | 17M | 1 |
| date | 2556 | 228K | 1 |
-### 6.3 建表
+### 7.3 建表
-#### 6.3.1 准备 `doris-cluster.conf` 文件。
+#### 7.3.1 准备 `doris-cluster.conf` 文件。
在调用导入脚本前，需要将 FE 的 ip 端口等信息写在 `doris-cluster.conf` 文件中。
@@ -165,21 +188,14 @@ export PASSWORD='xxx'
export DB="ssb"
```
-#### 6.3.2 执行以下脚本生成创建 SSB 表：
+#### 7.3.2 执行以下脚本生成创建 SSB 表：
```shell
sh create-ssb-tables.sh
```
-或者复制
[create-ssb-tables.sql](https://github.com/apache/incubator-doris/tree/master/tools/ssb-tools/ddl/create-ssb-tables.sql)
中的建表语句，在 Doris 中执行。
-
-#### 6.3.3 执行以下脚本生成创建 SSB flat 表：
+或者复制
[create-ssb-tables.sql](https://github.com/apache/incubator-doris/tree/master/tools/ssb-tools/ddl/create-ssb-tables.sql)
和
[create-ssb-flat-table.sql](https://github.com/apache/incubator-doris/tree/master/tools/ssb-tools/ddl/create-ssb-flat-table.sql)
中的建表语句，在 MySQL 客户端中执行。
-```shell
-sh create-ssb-flat-table.sh
-```
-或者复制
[create-ssb-flat-table.sql](https://github.com/apache/incubator-doris/tree/master/tools/ssb-tools/ddl/create-ssb-flat-table.sql)
中的建表语句，在 Doris 中执行。
-
-下面是 `lineorder_flat` 表建表语句。在上面的 `create-ssb-flat-table.sh`
脚本中创建"lineorder_flat"表，并进行了默认分桶数（48个桶)。您可以删除该表，根据您的集群规模节点配置对这个分桶数进行调整，这样可以获取到更好的一个测试效果。
+下面是 `lineorder_flat` 表建表语句。在上面的 `create-ssb-flat-table.sh` 脚本中创建
`lineorder_flat`
表，并进行了默认分桶数（48个桶)。您可以删除该表，根据您的集群规模节点配置对这个分桶数进行调整，这样可以获取到更好的一个测试效果。
```sql
CREATE TABLE `lineorder_flat` (
@@ -242,41 +258,23 @@ PROPERTIES (
```
+### 7.4 导入数据
-
-### 6.4 导入数据
-
-#### 6.4.1 导入 4 张维度表数据
-
-因为这 4 张维表（customer, part, supplier and date）数据量较小，导入较简单，我们使用以下命令先导入这4表的数据：
+我们使用以下命令完成 SSB 测试集所有数据导入及 SSB FLAT 宽表数据合成并导入到表里。
```shell
-sh load-ssb-dimension-data.sh
+sh bin/load-ssb-data.sh -c 10
```
-#### 6.4.2 导入事实表 lineorder。
-
-通过以下命令导入 lineorder 表数据
-
-````shell
-sh load-ssb-fact-data.sh -c 5
-````
-
-`-c 5` 表示启动 5 个并发线程导入（默认为3）。在单 BE 节点情况下，由 `sh gen-ssb-data.sh -s 100 -c 100`
生成的 lineorder 数据，使用 `sh load-ssb-fact-data.sh -c 3` 的导入时间约为 10min。内存开销约为
5-6GB。如果开启更多线程，可以加快导入速度，但会增加额外的内存开销。
-
-> 注：为获得更快的导入速度，你可以在 be.conf 中添加 `flush_thread_num_per_store=5`
后重启BE。该配置表示每个数据目录的写盘线程数，默认为2。较大的数据可以提升写数据吞吐，但可能会增加 IO
Util。（参考值：1块机械磁盘，在默认为2的情况下，导入过程中的 IO Util 约为12%，设置为5时，IO Util 约为26%。如果是 SSD
盘，则几乎为 0）。
+`-c 5` 表示启动 10 个并发线程导入（默认为 5）。在单 BE 节点情况下，由 `sh gen-ssb-data.sh -s 100 -c 100`
生成的 lineorder 数据，同时会在最后生成ssb-flat表的数据，如果开启更多线程，可以加快导入速度，但会增加额外的内存开销。
-#### 6.4.3 导入flat表
-
-通过以下命令导入 lineorder_flat 表数据：
-
-```shell
-sh load-ssb-flat-data.sh
-```
-
-> 注：flat 表数据采用 'INSERT INTO ... SELECT ... ' 的方式导入。
+> 注：
+>
+> 1. 为获得更快的导入速度，你可以在 be.conf 中添加 `flush_thread_num_per_store=5`
后重启BE。该配置表示每个数据目录的写盘线程数，默认为2。较大的数据可以提升写数据吞吐，但可能会增加 IO
Util。（参考值：1块机械磁盘，在默认为2的情况下，导入过程中的 IO Util 约为12%，设置为5时，IO Util 约为26%。如果是 SSD
盘，则几乎为 0）。
+>
+> 2. flat 表数据采用 'INSERT INTO ... SELECT ... ' 的方式导入。
-### 6.5 检查导入数据
+### 7.5 检查导入数据
```sql
select count(*) from part;
@@ -298,9 +296,13 @@ select count(*) from lineorder_flat;
| supplier | 20万（200000） | 17 MB | 9.143 MB |
| date | 2556 | 228 KB | 34.276 KB |
-### 6.6 查询测试
+### 7.6 查询测试
-#### 6.6.1 测试SQL
+SSB-FlAT 查询语句
：[ssb-flat-queries](https://github.com/apache/doris/tree/master/tools/ssb-tools/ssb-flat-queries)
+
+标准 SSB 查询语句
：[ssb-queries](https://github.com/apache/doris/tree/master/tools/ssb-tools/ssb-queries)
+
+#### 7.6.1 SSB FLAT 测试 SQL
```sql
--Q1.1
@@ -387,3 +389,222 @@ GROUP BY YEAR, S_CITY, P_BRAND
ORDER BY YEAR ASC, S_CITY ASC, P_BRAND ASC;
```
+
+
+#### **7.6.2 SSB 标准测试 SQL**
+
+```sql
+--Q1.1
+SELECT SUM(lo_extendedprice * lo_discount) AS REVENUE
+FROM lineorder, dates
+WHERE
+ lo_orderdate = d_datekey
+ AND d_year = 1993
+ AND lo_discount BETWEEN 1 AND 3
+ AND lo_quantity < 25;
+--Q1.2
+SELECT SUM(lo_extendedprice * lo_discount) AS REVENUE
+FROM lineorder, dates
+WHERE
+ lo_orderdate = d_datekey
+ AND d_yearmonth = 'Jan1994'
+ AND lo_discount BETWEEN 4 AND 6
+ AND lo_quantity BETWEEN 26 AND 35;
+
+--Q1.3
+SELECT
+ SUM(lo_extendedprice * lo_discount) AS REVENUE
+FROM lineorder, dates
+WHERE
+ lo_orderdate = d_datekey
+ AND d_weeknuminyear = 6
+ AND d_year = 1994
+ AND lo_discount BETWEEN 5 AND 7
+ AND lo_quantity BETWEEN 26 AND 35;
+
+--Q2.1
+SELECT SUM(lo_revenue), d_year, p_brand
+FROM lineorder, dates, part, supplier
+WHERE
+ lo_orderdate = d_datekey
+ AND lo_partkey = p_partkey
+ AND lo_suppkey = s_suppkey
+ AND p_category = 'MFGR#12'
+ AND s_region = 'AMERICA'
+GROUP BY d_year, p_brand
+ORDER BY p_brand;
+
+--Q2.2
+SELECT SUM(lo_revenue), d_year, p_brand
+FROM lineorder, dates, part, supplier
+WHERE
+ lo_orderdate = d_datekey
+ AND lo_partkey = p_partkey
+ AND lo_suppkey = s_suppkey
+ AND p_brand BETWEEN 'MFGR#2221' AND 'MFGR#2228'
+ AND s_region = 'ASIA'
+GROUP BY d_year, p_brand
+ORDER BY d_year, p_brand;
+
+--Q2.3
+SELECT SUM(lo_revenue), d_year, p_brand
+FROM lineorder, dates, part, supplier
+WHERE
+ lo_orderdate = d_datekey
+ AND lo_partkey = p_partkey
+ AND lo_suppkey = s_suppkey
+ AND p_brand = 'MFGR#2239'
+ AND s_region = 'EUROPE'
+GROUP BY d_year, p_brand
+ORDER BY d_year, p_brand;
+
+--Q3.1
+SELECT
+ c_nation,
+ s_nation,
+ d_year,
+ SUM(lo_revenue) AS REVENUE
+FROM customer, lineorder, supplier, dates
+WHERE
+ lo_custkey = c_custkey
+ AND lo_suppkey = s_suppkey
+ AND lo_orderdate = d_datekey
+ AND c_region = 'ASIA'
+ AND s_region = 'ASIA'
+ AND d_year >= 1992
+ AND d_year <= 1997
+GROUP BY c_nation, s_nation, d_year
+ORDER BY d_year ASC, REVENUE DESC;
+
+--Q3.2
+SELECT
+ c_city,
+ s_city,
+ d_year,
+ SUM(lo_revenue) AS REVENUE
+FROM customer, lineorder, supplier, dates
+WHERE
+ lo_custkey = c_custkey
+ AND lo_suppkey = s_suppkey
+ AND lo_orderdate = d_datekey
+ AND c_nation = 'UNITED STATES'
+ AND s_nation = 'UNITED STATES'
+ AND d_year >= 1992
+ AND d_year <= 1997
+GROUP BY c_city, s_city, d_year
+ORDER BY d_year ASC, REVENUE DESC;
+
+--Q3.3
+SELECT
+ c_city,
+ s_city,
+ d_year,
+ SUM(lo_revenue) AS REVENUE
+FROM customer, lineorder, supplier, dates
+WHERE
+ lo_custkey = c_custkey
+ AND lo_suppkey = s_suppkey
+ AND lo_orderdate = d_datekey
+ AND (
+ c_city = 'UNITED KI1'
+ OR c_city = 'UNITED KI5'
+ )
+ AND (
+ s_city = 'UNITED KI1'
+ OR s_city = 'UNITED KI5'
+ )
+ AND d_year >= 1992
+ AND d_year <= 1997
+GROUP BY c_city, s_city, d_year
+ORDER BY d_year ASC, REVENUE DESC;
+
+--Q3.4
+SELECT
+ c_city,
+ s_city,
+ d_year,
+ SUM(lo_revenue) AS REVENUE
+FROM customer, lineorder, supplier, dates
+WHERE
+ lo_custkey = c_custkey
+ AND lo_suppkey = s_suppkey
+ AND lo_orderdate = d_datekey
+ AND (
+ c_city = 'UNITED KI1'
+ OR c_city = 'UNITED KI5'
+ )
+ AND (
+ s_city = 'UNITED KI1'
+ OR s_city = 'UNITED KI5'
+ )
+ AND d_yearmonth = 'Dec1997'
+GROUP BY c_city, s_city, d_year
+ORDER BY d_year ASC, REVENUE DESC;
+
+--Q4.1
+SELECT /*+SET_VAR(parallel_fragment_exec_instance_num=4,
enable_vectorized_engine=true, batch_size=4096,
enable_cost_based_join_reorder=true, enable_projection=true) */
+ d_year,
+ c_nation,
+ SUM(lo_revenue - lo_supplycost) AS PROFIT
+FROM dates, customer, supplier, part, lineorder
+WHERE
+ lo_custkey = c_custkey
+ AND lo_suppkey = s_suppkey
+ AND lo_partkey = p_partkey
+ AND lo_orderdate = d_datekey
+ AND c_region = 'AMERICA'
+ AND s_region = 'AMERICA'
+ AND (
+ p_mfgr = 'MFGR#1'
+ OR p_mfgr = 'MFGR#2'
+ )
+GROUP BY d_year, c_nation
+ORDER BY d_year, c_nation;
+
+--Q4.2
+SELECT /*+SET_VAR(parallel_fragment_exec_instance_num=2,
enable_vectorized_engine=true, batch_size=4096,
enable_cost_based_join_reorder=true, enable_projection=true) */
+ d_year,
+ s_nation,
+ p_category,
+ SUM(lo_revenue - lo_supplycost) AS PROFIT
+FROM dates, customer, supplier, part, lineorder
+WHERE
+ lo_custkey = c_custkey
+ AND lo_suppkey = s_suppkey
+ AND lo_partkey = p_partkey
+ AND lo_orderdate = d_datekey
+ AND c_region = 'AMERICA'
+ AND s_region = 'AMERICA'
+ AND (
+ d_year = 1997
+ OR d_year = 1998
+ )
+ AND (
+ p_mfgr = 'MFGR#1'
+ OR p_mfgr = 'MFGR#2'
+ )
+GROUP BY d_year, s_nation, p_category
+ORDER BY d_year, s_nation, p_category;
+
+--Q4.3
+SELECT /*+SET_VAR(parallel_fragment_exec_instance_num=2,
enable_vectorized_engine=true, batch_size=4096,
enable_cost_based_join_reorder=true, enable_projection=true) */
+ d_year,
+ s_city,
+ p_brand,
+ SUM(lo_revenue - lo_supplycost) AS PROFIT
+FROM dates, customer, supplier, part, lineorder
+WHERE
+ lo_custkey = c_custkey
+ AND lo_suppkey = s_suppkey
+ AND lo_partkey = p_partkey
+ AND lo_orderdate = d_datekey
+ AND s_nation = 'UNITED STATES'
+ AND (
+ d_year = 1997
+ OR d_year = 1998
+ )
+ AND p_category = 'MFGR#14'
+GROUP BY d_year, s_city, p_brand
+ORDER BY d_year, s_city, p_brand;
+```
+
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/benchmark/tpch.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/benchmark/tpch.md
index e63c0625bae..2ece066ab60 100644
--- a/i18n/zh-CN/docusaurus-plugin-content-docs/current/benchmark/tpch.md
+++ b/i18n/zh-CN/docusaurus-plugin-content-docs/current/benchmark/tpch.md
@@ -28,13 +28,13 @@ under the License.
TPC-H是一个决策支持基准（Decision Support
Benchmark），它由一套面向业务的特别查询和并发数据修改组成。查询和填充数据库的数据具有广泛的行业相关性。这个基准测试演示了检查大量数据、执行高度复杂的查询并回答关键业务问题的决策支持系统。TPC-H报告的性能指标称为TPC-H每小时复合查询性能指标(QphH@Size)，反映了系统处理查询能力的多个方面。这些方面包括执行查询时所选择的数据库大小，由单个流提交查询时的查询处理能力，以及由多个并发用户提交查询时的查询吞吐量。
-本文档主要介绍 Doris 在 TPC-H 测试集上的性能表现。
+本文档主要介绍 Doris 在 TPC-H 100G 测试集上的性能表现。
> 注1：包括 TPC-H
> 在内的标准测试集通常和实际业务场景差距较大，并且部分测试会针对测试集进行参数调优。所以标准测试集的测试结果仅能反映数据库在特定场景下的性能表现。建议用户使用实际业务数据进行进一步的测试。
>
> 注2：本文档涉及的操作都在 CentOS 7.x 上进行测试。
-在 TPC-H 标准测试数据集上的 22 个查询上，我们基于 Doris 1.1 版本和 Doris 0.15.0 RC04
版本进行了对别测试，整体性能提升了 3-4 倍。个别场景下达到十几倍的提升。
+在 TPC-H 标准测试数据集上的 22 个查询上，我们基于 Apache Doris 1.2.0-rc01， Apache Doris 1.1.3 及
Apache Doris 0.15.0 RC04 版本进行了对别测试， Apache Doris 1.2.0-rc01上相对 Apache Doris
1.1.3 整体性能提升了将近 3 倍，相对于 Apache Doris 0.15.0 RC04 ,性能提升了将近 11 倍 。
@@ -53,12 +53,12 @@ TPC-H是一个决策支持基准（Decision Support Benchmark），它由一套
- Doris部署 3BE 1FE；
- 内核版本：Linux version 5.4.0-96-generic (buildd@lgw01-amd64-051)
- 操作系统版本：CentOS 7.8
-- Doris 软件版本：Apache Doris 1.1 、Apache Doris 0.15.0 RC04
+- Doris 软件版本： Apache Doris 1.2.0-rc01、 Apache Doris 1.1.3 、 Apache Doris
0.15.0 RC04
- JDK：openjdk version "11.0.14" 2022-01-18
## 3. 测试数据量
-整个测试模拟生成100G的数据分别导入到 Doris 0.15.0 RC04 和 Doris 1.1 版本进行测试，下面是表的相关说明及数据量。
+整个测试模拟生成 TPCH 100G 的数据分别导入到 Apache Doris 1.2.0-rc01， Apache Doris 1.1.3 及
Apache Doris 0.15.0 RC04 版本进行测试，下面是表的相关说明及数据量。
| TPC-H表名 | 行数 | 导入后大小 | 备注 |
| :-------- | :----- | ---------- | :----------- |
@@ -73,11 +73,11 @@ TPC-H是一个决策支持基准（Decision Support Benchmark），它由一套
## 4. 测试SQL
-TPCH 22个测试查询语句 ：
[TPCH-Query-SQL](https://github.com/apache/incubator-doris/tree/master/tools/tpch-tools/queries)
+TPCH 22 个测试查询语句 ：
[TPCH-Query-SQL](https://github.com/apache/incubator-doris/tree/master/tools/tpch-tools/queries)
**注意：**
-以上SQL中的以下四个参数在0.15.0 RC04中不存在，在0.15.0 RC04中执行的时候，去掉：
+以上 SQL 中的以下四个参数在 Apache Doris 0.15.0 RC04 中不存在，在执行的时候，去掉：
```
1. enable_vectorized_engine=true,
@@ -86,45 +86,42 @@ TPCH 22个测试查询语句 ：
[TPCH-Query-SQL](https://github.com/apache/incu
4. enable_projection=true
```
-
-
## 5. 测试结果
-这里我们使用即将发布的 Doris-1.1版本和 Doris-0.15.0 RC04 版本进行对比测试，测试结果如下：
-
-| Query | Doris-1.1(s) | 0.15.0 RC04(s) |
-| --------- | ------------ | -------------- |
-| Q1 | 3.75 | 28.63 |
-| Q2 | 4.22 | 7.88 |
-| Q3 | 2.64 | 9.39 |
-| Q4 | 1.5 | 9.3 |
-| Q5 | 2.15 | 4.11 |
-| Q6 | 0.19 | 0.43 |
-| Q7 | 1.04 | 1.61 |
-| Q8 | 1.75 | 50.35 |
-| Q9 | 7.94 | 16.34 |
-| Q10 | 1.41 | 5.21 |
-| Q11 | 0.35 | 1.72 |
-| Q12 | 0.57 | 5.39 |
-| Q13 | 8.15 | 20.88 |
-| Q14 | 0.3 | |
-| Q15 | 0.66 | 1.86 |
-| Q16 | 0.79 | 1.32 |
-| Q17 | 1.51 | 26.67 |
-| Q18 | 3.364 | 11.77 |
-| Q19 | 0.829 | 1.71 |
-| Q20 | 2.77 | 5.2 |
-| Q21 | 4.47 | 10.34 |
-| Q22 | 0.9 | 3.22 |
-| **total** | **51.253** | **223.33** |
+这里我们使用 Apache Doris 1.2.0-rc01， Apache Doris 1.1.3 及 Apache Doris 0.15.0 RC04
版本进行对比测试，测试结果如下：
+
+| Query | Apache Doris 1.2.0-rc01 (s) | Apache Doris 1.1.3 (s) | Apache
Doris 0.15.0 RC04 (s) |
+| -------- | --------------------------- | ---------------------- |
---------------------------- |
+| Q1 | 2.12 | 3.75 | 28.63
|
+| Q2 | 0.20 | 4.22 | 7.88
|
+| Q3 | 0.62 | 2.64 | 9.39
|
+| Q4 | 0.61 | 1.5 | 9.3
|
+| Q5 | 1.05 | 2.15 | 4.11
|
+| Q6 | 0.08 | 0.19 | 0.43
|
+| Q7 | 0.58 | 1.04 | 1.61
|
+| Q8 | 0.72 | 1.75 | 50.35
|
+| Q9 | 3.61 | 7.94 | 16.34
|
+| Q10 | 1.26 | 1.41 | 5.21
|
+| Q11 | 0.15 | 0.35 | 1.72
|
+| Q12 | 0.21 | 0.57 | 5.39
|
+| Q13 | 2.62 | 8.15 | 20.88
|
+| Q14 | 0.16 | 0.3 |
|
+| Q15 | 0.30 | 0.66 | 1.86
|
+| Q16 | 0.38 | 0.79 | 1.32
|
+| Q17 | 0.65 | 1.51 | 26.67
|
+| Q18 | 2.28 | 3.364 | 11.77
|
+| Q19 | 0.20 | 0.829 | 1.71
|
+| Q20 | 0.21 | 2.77 | 5.2
|
+| Q21 | 1.17 | 4.47 | 10.34
|
+| Q22 | 0.46 | 0.9 | 3.22
|
+| **合计** | **19.64** | **51.253** | **223.33**
|
**结果说明**
- 测试结果对应的数据集为scale 100, 约6亿条。
- 测试环境配置为用户常用配置，云服务器4台，16核 64G SSD，1 FE 3 BE 部署。
- 选用用户常见配置测试以降低用户选型评估成本，但整个测试过程中不会消耗如此多的硬件资源。
-- 测试结果为3次执行取平均值。并且数据经过充分的
compaction（如果在刚导入数据后立刻测试，则查询延迟可能高于本测试结果，compaction的速度正在持续优化中，未来会显著降低）。
-- 0.15 RC04 在 TPC-H 测试中 Q14 执行失败，无法完成查询。
+- Apache Doris 0.15 RC04 在 TPC-H 测试中 Q14 执行失败，无法完成查询。
## 6. 环境准备
@@ -215,6 +212,8 @@ select count(*) from revenue0;
### 7.6 查询测试
+## 7.6.1 执行查询脚本
+
执行上面的测试 SQL 或者 执行下面的命令
```
@@ -228,5 +227,653 @@ select count(*) from revenue0;
>2. Doris 新的查询优化器将在后续的版本中发布
>3. 执行查询之前设置 `set mem_exec_limit=8G`
+## 7.6.2 单个 SQL 执行
+```sql
+--Q1
+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) */
+ l_returnflag,
+ l_linestatus,
+ sum(l_quantity) as sum_qty,
+ sum(l_extendedprice) as sum_base_price,
+ sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
+ sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
+ avg(l_quantity) as avg_qty,
+ avg(l_extendedprice) as avg_price,
+ avg(l_discount) as avg_disc,
+ count(*) as count_order
+from
+ lineitem
+where
+ l_shipdate <= date '1998-12-01' - interval '90' day
+group by
+ l_returnflag,
+ l_linestatus
+order by
+ l_returnflag,
+ l_linestatus;
+
+--Q2
+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) */
+ s_acctbal,
+ s_name,
+ n_name,
+ p_partkey,
+ p_mfgr,
+ s_address,
+ s_phone,
+ s_comment
+from
+ 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'
+
+order by
+ s_acctbal desc,
+ n_name,
+ s_name,
+ p_partkey
+limit 100;
+
+--Q3
+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,
runtime_filter_wait_time_ms=10000) */
+ l_orderkey,
+ sum(l_extendedprice * (1 - l_discount)) as revenue,
+ o_orderdate,
+ o_shippriority
+from
+ (
+ select l_orderkey, l_extendedprice, l_discount, o_orderdate,
o_shippriority, o_custkey from
+ lineitem join orders
+ where l_orderkey = o_orderkey
+ and o_orderdate < date '1995-03-15'
+ and l_shipdate > date '1995-03-15'
+ ) t1 join customer c
+ on c.c_custkey = t1.o_custkey
+ where c_mktsegment = 'BUILDING'
+group by
+ l_orderkey,
+ o_orderdate,
+ o_shippriority
+order by
+ revenue desc,
+ o_orderdate
+limit 10;
+
+--Q4
+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
+ (
+ select
+ *
+ from
+ lineitem
+ where l_commitdate < l_receiptdate
+ ) t1
+ right semi join orders
+ on t1.l_orderkey = o_orderkey
+where
+ o_orderdate >= date '1993-07-01'
+ and o_orderdate < date '1993-07-01' + interval '3' month
+group by
+ o_orderpriority
+order by
+ o_orderpriority;
+
+--Q5
+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) */
+ n_name,
+ sum(l_extendedprice * (1 - l_discount)) as revenue
+from
+ customer,
+ orders,
+ lineitem,
+ supplier,
+ nation,
+ region
+where
+ c_custkey = o_custkey
+ and l_orderkey = o_orderkey
+ and l_suppkey = s_suppkey
+ and c_nationkey = s_nationkey
+ and s_nationkey = n_nationkey
+ and n_regionkey = r_regionkey
+ and r_name = 'ASIA'
+ and o_orderdate >= date '1994-01-01'
+ and o_orderdate < date '1994-01-01' + interval '1' year
+group by
+ n_name
+order by
+ revenue desc;
+
+--Q6
+select /*+SET_VAR(exec_mem_limit=8589934592,
parallel_fragment_exec_instance_num=1, enable_vectorized_engine=true,
batch_size=4096, disable_join_reorder=false,
enable_cost_based_join_reorder=false, enable_projection=true) */
+ sum(l_extendedprice * l_discount) as revenue
+from
+ lineitem
+where
+ l_shipdate >= date '1994-01-01'
+ and l_shipdate < date '1994-01-01' + interval '1' year
+ and l_discount between .06 - 0.01 and .06 + 0.01
+ and l_quantity < 24;
+
+--Q7
+select /*+SET_VAR(exec_mem_limit=458589934592,
parallel_fragment_exec_instance_num=2, 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,
+ sum(volume) as revenue
+from
+ (
+ select
+ n1.n_name as supp_nation,
+ n2.n_name as cust_nation,
+ extract(year from l_shipdate) as l_year,
+ l_extendedprice * (1 - l_discount) as volume
+ from
+ supplier,
+ lineitem,
+ orders,
+ customer,
+ nation n1,
+ nation n2
+ where
+ s_suppkey = l_suppkey
+ and o_orderkey = l_orderkey
+ and c_custkey = o_custkey
+ and s_nationkey = n1.n_nationkey
+ and c_nationkey = n2.n_nationkey
+ and (
+ (n1.n_name = 'FRANCE' and n2.n_name = 'GERMANY')
+ or (n1.n_name = 'GERMANY' and n2.n_name = 'FRANCE')
+ )
+ and l_shipdate between date '1995-01-01' and date '1996-12-31'
+ ) as shipping
+group by
+ supp_nation,
+ cust_nation,
+ l_year
+order by
+ supp_nation,
+ cust_nation,
+ l_year;
+
+--Q8
+
+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
+ else 0
+ end) / sum(volume) as mkt_share
+from
+ (
+ select
+ extract(year from o_orderdate) as o_year,
+ l_extendedprice * (1 - l_discount) as volume,
+ n2.n_name as nation
+ from
+ lineitem,
+ orders,
+ customer,
+ supplier,
+ part,
+ nation n1,
+ nation n2,
+ region
+ where
+ p_partkey = l_partkey
+ and s_suppkey = l_suppkey
+ and l_orderkey = o_orderkey
+ and o_custkey = c_custkey
+ and c_nationkey = n1.n_nationkey
+ and n1.n_regionkey = r_regionkey
+ and r_name = 'AMERICA'
+ and s_nationkey = n2.n_nationkey
+ and o_orderdate between date '1995-01-01' and date '1996-12-31'
+ and p_type = 'ECONOMY ANODIZED STEEL'
+ ) as all_nations
+group by
+ o_year
+order by
+ o_year;
+
+--Q9
+select/*+SET_VAR(exec_mem_limit=37179869184,
parallel_fragment_exec_instance_num=2, 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=100000) */
+ nation,
+ o_year,
+ sum(amount) as sum_profit
+from
+ (
+ select
+ n_name as nation,
+ extract(year from o_orderdate) as o_year,
+ l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as
amount
+ from
+ 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
+ ps_suppkey = l_suppkey and
+ p_name like '%green%'
+ ) as profit
+group by
+ nation,
+ o_year
+order by
+ nation,
+ o_year desc;
+
+--Q10
+
+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) */
+ c_custkey,
+ c_name,
+ sum(t1.l_extendedprice * (1 - t1.l_discount)) as revenue,
+ c_acctbal,
+ n_name,
+ c_address,
+ c_phone,
+ c_comment
+from
+ customer,
+ (
+ select o_custkey,l_extendedprice,l_discount from lineitem, orders
+ where l_orderkey = o_orderkey
+ and o_orderdate >= date '1993-10-01'
+ and o_orderdate < date '1993-10-01' + interval '3' month
+ and l_returnflag = 'R'
+ ) t1,
+ nation
+where
+ c_custkey = t1.o_custkey
+ 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;
+
+--Q11
+select /*+SET_VAR(exec_mem_limit=8589934592,
parallel_fragment_exec_instance_num=2, enable_vectorized_engine=true,
batch_size=4096, disable_join_reorder=false,
enable_cost_based_join_reorder=true, enable_projection=true) */
+ ps_partkey,
+ sum(ps_supplycost * ps_availqty) as value
+from
+ partsupp,
+ (
+ select s_suppkey
+ from supplier, nation
+ where s_nationkey = n_nationkey and n_name = 'GERMANY'
+ ) B
+where
+ ps_suppkey = B.s_suppkey
+group by
+ ps_partkey having
+ sum(ps_supplycost * ps_availqty) > (
+ select
+ sum(ps_supplycost * ps_availqty) * 0.000002
+ from
+ partsupp,
+ (select s_suppkey
+ from supplier, nation
+ where s_nationkey = n_nationkey and n_name = 'GERMANY'
+ ) A
+ where
+ ps_suppkey = A.s_suppkey
+ )
+order by
+ value desc;
+
+--Q12
+
+select /*+SET_VAR(exec_mem_limit=8589934592,
parallel_fragment_exec_instance_num=2, enable_vectorized_engine=true,
batch_size=4096, disable_join_reorder=false,
enable_cost_based_join_reorder=true, enable_projection=true) */
+ l_shipmode,
+ sum(case
+ when o_orderpriority = '1-URGENT'
+ or o_orderpriority = '2-HIGH'
+ then 1
+ else 0
+ end) as high_line_count,
+ sum(case
+ when o_orderpriority <> '1-URGENT'
+ and o_orderpriority <> '2-HIGH'
+ then 1
+ else 0
+ end) as low_line_count
+from
+ orders,
+ lineitem
+where
+ o_orderkey = l_orderkey
+ and l_shipmode in ('MAIL', 'SHIP')
+ and l_commitdate < l_receiptdate
+ and l_shipdate < l_commitdate
+ and l_receiptdate >= date '1994-01-01'
+ and l_receiptdate < date '1994-01-01' + interval '1' year
+group by
+ l_shipmode
+order by
+ l_shipmode;
+
+--Q13
+select /*+SET_VAR(exec_mem_limit=45899345920,
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
+ (
+ select
+ c_custkey,
+ count(o_orderkey) as c_count
+ from
+ orders right outer join customer on
+ c_custkey = o_custkey
+ and o_comment not like '%special%requests%'
+ group by
+ c_custkey
+ ) as c_orders
+group by
+ c_count
+order by
+ custdist desc,
+ c_count desc;
+
+--Q14
+
+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,
runtime_filter_mode=OFF) */
+ 100.00 * sum(case
+ when p_type like 'PROMO%'
+ then l_extendedprice * (1 - l_discount)
+ else 0
+ end) / sum(l_extendedprice * (1 - l_discount)) as promo_revenue
+from
+ part,
+ lineitem
+where
+ l_partkey = p_partkey
+ and l_shipdate >= date '1995-09-01'
+ and l_shipdate < date '1995-09-01' + interval '1' month;
+
+--Q15
+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,
+ s_phone,
+ total_revenue
+from
+ supplier,
+ revenue0
+where
+ s_suppkey = supplier_no
+ and total_revenue = (
+ select
+ max(total_revenue)
+ from
+ revenue0
+ )
+order by
+ s_suppkey;
+
+--Q16
+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) */
+ p_brand,
+ p_type,
+ p_size,
+ count(distinct ps_suppkey) as supplier_cnt
+from
+ partsupp,
+ part
+where
+ p_partkey = ps_partkey
+ and p_brand <> 'Brand#45'
+ and p_type not like 'MEDIUM POLISHED%'
+ and p_size in (49, 14, 23, 45, 19, 3, 36, 9)
+ and ps_suppkey not in (
+ select
+ s_suppkey
+ from
+ supplier
+ where
+ s_comment like '%Customer%Complaints%'
+ )
+group by
+ p_brand,
+ p_type,
+ p_size
+order by
+ supplier_cnt desc,
+ p_brand,
+ p_type,
+ p_size;
+
+--Q17
+select /*+SET_VAR(exec_mem_limit=8589934592,
parallel_fragment_exec_instance_num=1, enable_vectorized_engine=true,
batch_size=4096, disable_join_reorder=false,
enable_cost_based_join_reorder=true, enable_projection=true) */
+ sum(l_extendedprice) / 7.0 as avg_yearly
+from
+ lineitem join [broadcast]
+ part p1 on p1.p_partkey = l_partkey
+where
+ p1.p_brand = 'Brand#23'
+ and p1.p_container = 'MED BOX'
+ and l_quantity < (
+ select
+ 0.2 * avg(l_quantity)
+ from
+ lineitem join [broadcast]
+ part p2 on p2.p_partkey = l_partkey
+ where
+ l_partkey = p1.p_partkey
+ and p2.p_brand = 'Brand#23'
+ and p2.p_container = 'MED BOX'
+ );
+
+--Q18
+
+select /*+SET_VAR(exec_mem_limit=45899345920,
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) */
+ c_name,
+ c_custkey,
+ t3.o_orderkey,
+ t3.o_orderdate,
+ t3.o_totalprice,
+ sum(t3.l_quantity)
+from
+customer join
+(
+ select * from
+ lineitem join
+ (
+ select * from
+ orders left semi join
+ (
+ select
+ l_orderkey
+ from
+ lineitem
+ group by
+ l_orderkey having sum(l_quantity) > 300
+ ) t1
+ on o_orderkey = t1.l_orderkey
+ ) t2
+ on t2.o_orderkey = l_orderkey
+) t3
+on c_custkey = t3.o_custkey
+group by
+ c_name,
+ c_custkey,
+ t3.o_orderkey,
+ t3.o_orderdate,
+ t3.o_totalprice
+order by
+ t3.o_totalprice desc,
+ t3.o_orderdate
+limit 100;
+
+--Q19
+
+select /*+SET_VAR(exec_mem_limit=8589934592,
parallel_fragment_exec_instance_num=2, enable_vectorized_engine=true,
batch_size=4096, disable_join_reorder=false,
enable_cost_based_join_reorder=false, enable_projection=true) */
+ sum(l_extendedprice* (1 - l_discount)) as revenue
+from
+ lineitem,
+ part
+where
+ (
+ p_partkey = l_partkey
+ and p_brand = 'Brand#12'
+ and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
+ and l_quantity >= 1 and l_quantity <= 1 + 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#23'
+ and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
+ and l_quantity >= 10 and l_quantity <= 10 + 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#34'
+ and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
+ and l_quantity >= 20 and l_quantity <= 20 + 10
+ and p_size between 1 and 15
+ and l_shipmode in ('AIR', 'AIR REG')
+ and l_shipinstruct = 'DELIVER IN PERSON'
+ );
+
+--Q20
+select /*+SET_VAR(exec_mem_limit=8589934592,
parallel_fragment_exec_instance_num=2, enable_vectorized_engine=true,
batch_size=4096, disable_join_reorder=true,
enable_cost_based_join_reorder=true, enable_projection=true,
runtime_bloom_filter_size=551943) */
+s_name, s_address from
+supplier left semi join
+(
+ select * from
+ (
+ select l_partkey,l_suppkey, 0.5 * sum(l_quantity) as l_q
+ from lineitem
+ where l_shipdate >= date '1994-01-01'
+ and l_shipdate < date '1994-01-01' + interval '1' year
+ group by l_partkey,l_suppkey
+ ) t2 join
+ (
+ select ps_partkey, ps_suppkey, ps_availqty
+ from partsupp left semi join part
+ on ps_partkey = p_partkey and p_name like 'forest%'
+ ) t1
+ on t2.l_partkey = t1.ps_partkey and t2.l_suppkey = t1.ps_suppkey
+ and t1.ps_availqty > t2.l_q
+) t3
+on s_suppkey = t3.ps_suppkey
+join nation
+where s_nationkey = n_nationkey
+ and n_name = 'CANADA'
+order by s_name;
+
+--Q21
+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) */
+s_name, count(*) as numwait
+from
+ lineitem l2 right semi join
+ (
+ select * from
+ lineitem l3 right anti join
+ (
+ select * from
+ orders join lineitem l1 on l1.l_orderkey = o_orderkey and o_orderstatus
= 'F'
+ join
+ (
+ select * from
+ supplier join nation
+ where s_nationkey = n_nationkey
+ and n_name = 'SAUDI ARABIA'
+ ) t1
+ where t1.s_suppkey = l1.l_suppkey and l1.l_receiptdate > l1.l_commitdate
+ ) t2
+ on l3.l_orderkey = t2.l_orderkey and l3.l_suppkey <> t2.l_suppkey and
l3.l_receiptdate > l3.l_commitdate
+ ) t3
+ on l2.l_orderkey = t3.l_orderkey and l2.l_suppkey <> t3.l_suppkey
+
+group by
+ t3.s_name
+order by
+ numwait desc,
+ t3.s_name
+limit 100;
+
+--Q21
+
+with tmp as (select
+ avg(c_acctbal) as av
+ from
+ customer
+ where
+ c_acctbal > 0.00
+ and substring(c_phone, 1, 2) in
+ ('13', '31', '23', '29', '30', '18', '17'))
+
+select /*+SET_VAR(exec_mem_limit=8589934592,
parallel_fragment_exec_instance_num=4,runtime_bloom_filter_size=4194304) */
+ cntrycode,
+ count(*) as numcust,
+ sum(c_acctbal) as totacctbal
+from
+ (
+ select
+ substring(c_phone, 1, 2) as cntrycode,
+ c_acctbal
+ from
+ orders right anti join customer c on o_custkey = c.c_custkey
join tmp on c.c_acctbal > tmp.av
+ where
+ substring(c_phone, 1, 2) in
+ ('13', '31', '23', '29', '30', '18', '17')
+ ) as custsale
+group by
+ cntrycode
+order by
+ cntrycode;
+
+```
diff --git a/static/images/image-20220614114351241.png
b/static/images/image-20220614114351241.png
index fe64d53ef3e..1c0db4136db 100644
Binary files a/static/images/image-20220614114351241.png and
b/static/images/image-20220614114351241.png differ
diff --git a/static/images/ssb_12_11_015.png b/static/images/ssb_12_11_015.png
new file mode 100644
index 00000000000..a835fcae984
Binary files /dev/null and b/static/images/ssb_12_11_015.png differ
diff --git a/static/images/ssb_v11_v015_compare.png
b/static/images/ssb_v11_v015_compare.png
index d2e2b5ba998..1414df824ac 100644
Binary files a/static/images/ssb_v11_v015_compare.png and
b/static/images/ssb_v11_v015_compare.png differ
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
