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 985ae1a0ddd doris 1.2 benchmark
985ae1a0ddd is described below
commit 985ae1a0dddb05458121343b0d0b8243f6738a64
Author: jiafeng.zhang <zhang...@gmail.com>
AuthorDate: Tue Nov 22 17:32:25 2022 +0800
doris 1.2 benchmark
---
blog/ssb.md | 610 +++++++++++++++
blog/tpch.md | 881 +++++++++++++++++++++
i18n/zh-CN/docusaurus-plugin-content-blog/ssb.md | 616 +++++++++++++++
i18n/zh-CN/docusaurus-plugin-content-blog/tpch.md | 884 ++++++++++++++++++++++
4 files changed, 2991 insertions(+)
diff --git a/blog/ssb.md b/blog/ssb.md
new file mode 100644
index 00000000000..79b171a1b2a
--- /dev/null
+++ b/blog/ssb.md
@@ -0,0 +1,610 @@
+---
+{
+ 'title': "Apache Doris 1.2 Star-Schema-Benchmark Performance Test Report",
+ 'summary': "On the SSB flat wide table, the overall performance of Apache
Doris 1.2.0-rc01 has been improved by nearly 4 times compared with Apache Doris
1.1.3, and nearly 10 times compared with Apache Doris 0.15.0 RC04. On the SQL
test with standard SSB, the overall performance of Apache Doris 1.2.0-rc01 has
been improved by nearly 2 times compared with Apache Doris 1.1.3, and nearly 31
times compared with Apache Doris 0.15.0 RC04.",
+ 'date': '2022-11-22',
+ 'author': 'Apache Doris',
+ 'tags': ['Tech Sharing'],
+}
+---
+
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+
+# Star Schema Benchmark
+
+[Star Schema Benchmark(SSB)](https://www.cs.umb.edu/~poneil/StarSchemaB.PDF)
is a lightweight performance test set in the data warehouse scenario. SSB
provides a simplified star schema data based on
[TPC-H](http://www.tpc.org/tpch/), which is mainly used to test the performance
of multi-table JOIN query under star schema. In addition, the industry usually
flattens SSB into a wide table model (Referred as: SSB flat) to test the
performance of the query engine, refer to [Clickhouse](https [...]
+
+This document mainly introduces the performance of Doris on the SSB 100G test
set.
+
+> Note 1: The standard test set including SSB usually has a large gap with 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 a specific scenario. It is recommended that
users use actual business data for further testing.
+>
+> Note 2: The operations involved in this document are all performed in the
Ubuntu Server 20.04 environment, and CentOS 7 as well.
+
+With 13 queries on the SSB standard test data set, we conducted a comparison
test based on Apache Doris 1.2.0-rc01, Apache Doris 1.1.3 and Apache Doris
0.15.0 RC04 versions.
+
+On the SSB flat wide table, the overall performance of Apache Doris 1.2.0-rc01
has been improved by nearly 4 times compared with Apache Doris 1.1.3, and
nearly 10 times compared with Apache Doris 0.15.0 RC04.
+
+On the SQL test with standard SSB, the overall performance of Apache Doris
1.2.0-rc01 has been improved by nearly 2 times compared with Apache Doris
1.1.3, and nearly 31 times compared with Apache Doris 0.15.0 RC04.
+
+## 1. Hardware Environment
+
+| Number of machines | 4 Tencent Cloud Hosts (1 FE, 3 BEs) |
+| ------------------ | ----------------------------------------- |
+| CPU | AMD EPYC™ Milan (2.55GHz/3.5GHz) 16 Cores |
+| Memory | 64G |
+| Network Bandwidth | 7Gbps |
+| Disk | High-performance Cloud Disk |
+
+## 2. Software Environment
+
+- Doris deployed 3BEs and 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 versions: Apache Doris 1.2.0-rc01, Apache Doris 1.1.3 and
Apache Doris 0.15.0 RC04
+- JDK: openjdk version "11.0.14" 2022-01-18
+
+## 3. Test Data Volume
+
+| SSB Table Name | Rows | Annotation |
+| :------------- | :------------- | :------------------------------- |
+| lineorder | 600,037,902 | Commodity Order Details |
+| customer | 3,000,000 | Customer Information |
+| part | 1,400,000 | Parts Information |
+| supplier | 200,000 | Supplier Information |
+| date | 2,556 | Date |
+| lineorder_flat | 600,037,902 | Wide Table after Data Flattening |
+
+## 4. Test Results
+
+We use Apache Doris 1.2.0-rc01, Apache Doris 1.1.3 and Apache Doris 0.15.0
RC04 for comparative testing. The test results are as follows:
+
+| 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 | 1,020 |
+| Q2.3 | 60 | 260 | 770 |
+| Q3.1 | 160 | 550 | 1,710 |
+| Q3.2 | 80 | 290 | 670 |
+| Q3.3 | 90 | 240 | 550 |
+| Q3.4 | 20 | 20 | 30 |
+| Q4.1 | 140 | 480 | 1,250 |
+| Q4.2 | 50 | 240 | 400 |
+| Q4.3 | 30 | 200 | 330 |
+| Total | 880 | 3,150 | 8,030 |
+
+
+
+**Interpretation of Results**
+
+- The data set corresponding to the test results is scale 100, about 600
million.
+- The test environment is configured as the user's common configuration, with
4 cloud servers, 16-core 64G SSD, and 1 FE, 3 BEs deployment.
+- We select the user's common configuration test to reduce the cost of user
selection and evaluation, but the entire test process will not consume so many
hardware resources.
+
+
+## 5. Standard SSB Test Results
+
+Here we use Apache Doris 1.2.0-rc01, Apache Doris 1.1.3 and Apache Doris
0.15.0 RC04 for comparative testing. In the test, we use Query Time（ms） as the
main performance indicator. The test results are as follows:
+
+| 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 | 20,680 |
+| Q2.2 | 320 | 750 | 18,250 |
+| Q2.3 | 300 | 720 | 14,760 |
+| Q3.1 | 650 | 2,150 | 22,190 |
+| Q3.2 | 260 | 510 | 8,360 |
+| Q3.3 | 220 | 450 | 6,200 |
+| Q3.4 | 60 | 70 | 160 |
+| Q4.1 | 840 | 1,480 | 24,320 |
+| Q4.2 | 460 | 560 | 6,310 |
+| Q4.3 | 610 | 660 | 10,170 |
+| Total | 4,160 | 8,478 | 131,910 |
+
+
+
+**Interpretation of Results**
+
+- The data set corresponding to the test results is scale 100, about 600
million.
+- The test environment is configured as the user's common configuration, with
4 cloud servers, 16-core 64G SSD, and 1 FE 3 BEs deployment.
+- We select the user's common configuration test to reduce the cost of user
selection and evaluation, but the entire test process will not consume so many
hardware resources.
+
+## 6. Environment Preparation
+
+Please first refer to the [official documentation](.
/install/install-deploy.md) to install and deploy Apache Doris first to obtain
a Doris cluster which is working well(including at least 1 FE 1 BE, 1 FE 3 BEs
is recommended).
+
+The scripts mentioned in the following documents are stored in the Apache
Doris codebase:
[ssb-tools](https://github.com/apache/doris/tree/master/tools/ssb-tools)
+
+## 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.
+
+```shell
+sh build-ssb-dbgen.sh
+````
+
+After successful installation, the `dbgen` binary will be generated under the
`ssb-dbgen/` directory.
+
+### 7.2 Generate SSB Test Set
+
+Execute the following script to generate the SSB dataset:
+
+```shell
+sh gen-ssb-data.sh -s 100 -c 100
+````
+
+> Note 1: Check the script help via `sh gen-ssb-data.sh -h`.
+>
+> Note 2: The data will be generated under the `ssb-data/` directory with the
suffix `.tbl`. The total file size is about 60GB and may need a few minutes to
an hour to generate.
+>
+> Note 3: `-s 100` indicates that the test set size factor is 100, `-c 100`
indicates that 100 concurrent threads generate the data of the lineorder table.
The `-c` parameter also determines the number of files in the final lineorder
table. The larger the parameter, the larger the number of files and the smaller
each file.
+
+With the `-s 100` parameter, the resulting dataset size is:
+
+| Table | Rows | Size | File Number |
+| --------- | ---------------- | ---- | ----------- |
+| lineorder | 600,037,902 | 60GB | 100 |
+| customer | 3,000,000 | 277M | 1 |
+| part | 1,400,000 | 116M | 1 |
+| supplier | 200,000 | 17M | 1 |
+| date | 2,556 | 228K | 1 |
+
+### 7.3 Create Table
+
+#### 7.3.1 Prepare the `doris-cluster.conf` File.
+
+Before import the script, you need to write the FE’s ip port and other
information in the `doris-cluster.conf` file.
+
+The file location is at the same level as `load-ssb-dimension-data.sh`.
+
+The content of the file includes FE's ip, HTTP port, user name, password and
the DB name of the data to be imported:
+
+```shell
+export FE_HOST="xxx"
+export FE_HTTP_PORT="8030"
+export FE_QUERY_PORT="9030"
+export USER="root"
+export PASSWORD='xxx'
+export DB="ssb"
+```
+
+#### 7.3.2 Execute the Following Script to Generate and Create the SSB Table:
+
+```shell
+sh create-ssb-tables.sh
+````
+
+Or copy the table creation statements in
[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)
and then execute them in the MySQL client.
+
+The following is the `lineorder_flat` table build statement. Create the
`lineorder_flat` table in the above `create-ssb-flat-table.sh` script, and
perform the default number of buckets (48 buckets). You can delete this table
and adjust the number of buckets according to your cluster scale node
configuration, so as to obtain a better test result.
+
+```sql
+CREATE TABLE `lineorder_flat` (
+ `LO_ORDERDATE` date NOT NULL COMMENT "",
+ `LO_ORDERKEY` int(11) NOT NULL COMMENT "",
+ `LO_LINENUMBER` tinyint(4) NOT NULL COMMENT "",
+ `LO_CUSTKEY` int(11) NOT NULL COMMENT "",
+ `LO_PARTKEY` int(11) NOT NULL COMMENT "",
+ `LO_SUPPKEY` int(11) NOT NULL COMMENT "",
+ `LO_ORDERPRIORITY` varchar(100) NOT NULL COMMENT "",
+ `LO_SHIPPRIORITY` tinyint(4) NOT NULL COMMENT "",
+ `LO_QUANTITY` tinyint(4) NOT NULL COMMENT "",
+ `LO_EXTENDEDPRICE` int(11) NOT NULL COMMENT "",
+ `LO_ORDTOTALPRICE` int(11) NOT NULL COMMENT "",
+ `LO_DISCOUNT` tinyint(4) NOT NULL COMMENT "",
+ `LO_REVENUE` int(11) NOT NULL COMMENT "",
+ `LO_SUPPLYCOST` int(11) NOT NULL COMMENT "",
+ `LO_TAX` tinyint(4) NOT NULL COMMENT "",
+ `LO_COMMITDATE` date NOT NULL COMMENT "",
+ `LO_SHIPMODE` varchar(100) NOT NULL COMMENT "",
+ `C_NAME` varchar(100) NOT NULL COMMENT "",
+ `C_ADDRESS` varchar(100) NOT NULL COMMENT "",
+ `C_CITY` varchar(100) NOT NULL COMMENT "",
+ `C_NATION` varchar(100) NOT NULL COMMENT "",
+ `C_REGION` varchar(100) NOT NULL COMMENT "",
+ `C_PHONE` varchar(100) NOT NULL COMMENT "",
+ `C_MKTSEGMENT` varchar(100) NOT NULL COMMENT "",
+ `S_NAME` varchar(100) NOT NULL COMMENT "",
+ `S_ADDRESS` varchar(100) NOT NULL COMMENT "",
+ `S_CITY` varchar(100) NOT NULL COMMENT "",
+ `S_NATION` varchar(100) NOT NULL COMMENT "",
+ `S_REGION` varchar(100) NOT NULL COMMENT "",
+ `S_PHONE` varchar(100) NOT NULL COMMENT "",
+ `P_NAME` varchar(100) NOT NULL COMMENT "",
+ `P_MFGR` varchar(100) NOT NULL COMMENT "",
+ `P_CATEGORY` varchar(100) NOT NULL COMMENT "",
+ `P_BRAND` varchar(100) NOT NULL COMMENT "",
+ `P_COLOR` varchar(100) NOT NULL COMMENT "",
+ `P_TYPE` varchar(100) NOT NULL COMMENT "",
+ `P_SIZE` tinyint(4) NOT NULL COMMENT "",
+ `P_CONTAINER` varchar(100) NOT NULL COMMENT ""
+) ENGINE=OLAP
+DUPLICATE KEY(`LO_ORDERDATE`, `LO_ORDERKEY`)
+COMMENT "OLAP"
+PARTITION BY RANGE(`LO_ORDERDATE`)
+(PARTITION p1 VALUES [('0000-01-01'), ('1993-01-01')),
+PARTITION p2 VALUES [('1993-01-01'), ('1994-01-01')),
+PARTITION p3 VALUES [('1994-01-01'), ('1995-01-01')),
+PARTITION p4 VALUES [('1995-01-01'), ('1996-01-01')),
+PARTITION p5 VALUES [('1996-01-01'), ('1997-01-01')),
+PARTITION p6 VALUES [('1997-01-01'), ('1998-01-01')),
+PARTITION p7 VALUES [('1998-01-01'), ('1999-01-01')))
+DISTRIBUTED BY HASH(`LO_ORDERKEY`) BUCKETS 48
+PROPERTIES (
+"replication_num" = "1",
+"colocate_with" = "groupxx1",
+"in_memory" = "false",
+"storage_format" = "DEFAULT"
+);
+```
+
+### 7.4 Import data
+
+We use the following command to complete all data import of SSB test set and
SSB FLAT wide table data synthesis and then import into the table.
+
+```shell
+ sh bin/load-ssb-data.sh -c 10
+```
+
+`-c 5` means start 10 concurrent threads to import (5 by default). In the case
of a single BE node, the lineorder data generated by `sh gen-ssb-data.sh -s 100
-c 100` will also generate the data of the ssb-flat table in the end. If more
threads are enabled, the import speed can be accelerated. But it will cost
extra memory.
+
+> Notes.
+>
+> 1. To get faster import speed, you can add `flush_thread_num_per_store=5` in
be.conf and then restart BE. This configuration indicates the number of disk
writing threads for each data directory, 2 by default. Larger data can improve
write data throughput, but may increase IO Util. (Reference value: 1 mechanical
disk, with 2 by default, the IO Util during the import process is about 12%.
When it is set to 5, the IO Util is about 26%. If it is an SSD disk, it is
almost 0%) .
+>
+> 2. The flat table data is imported by 'INSERT INTO ... SELECT ... '.
+
+### 7.5 Checking Imported data
+
+
+```sql
+select count(*) from part;
+select count(*) from customer;
+select count(*) from supplier;
+select count(*) from date;
+select count(*) from lineorder;
+select count(*) from lineorder_flat;
+```
+
+The amount of data should be consistent with the number of rows of generated
data.
+
+| Table | Rows | Origin Size | Compacted Size(1 Replica) |
+| -------------- | ---------------- | ----------- | ------------------------- |
+| lineorder_flat | 600,037,902 | | 59.709 GB |
+| lineorder | 600,037,902 | 60 GB | 14.514 GB |
+| customer | 3,000,000 | 277 MB | 138.247 MB |
+| part | 1,400,000 | 116 MB | 12.759 MB |
+| supplier | 200,000 | 17 MB | 9.143 MB |
+| date | 2,556 | 228 KB | 34.276 KB
|
+
+### 7.6 Query Test
+
+- SSB-Flat Query Statement: [
ssb-flat-queries](https://github.com/apache/doris/tree/master/tools/ssb-tools/ssb-flat-queries)
+- Standard SSB Queries: [
ssb-queries](https://github.com/apache/doris/tree/master/tools/ssb-tools/ssb-queries)
+
+#### 7.6.1 SSB FLAT Test for SQL
+
+```sql
+--Q1.1
+SELECT SUM(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue
+FROM lineorder_flat
+WHERE LO_ORDERDATE >= 19930101 AND LO_ORDERDATE <= 19931231 AND LO_DISCOUNT
BETWEEN 1 AND 3 AND LO_QUANTITY < 25;
+--Q1.2
+SELECT SUM(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue
+FROM lineorder_flat
+WHERE LO_ORDERDATE >= 19940101 AND LO_ORDERDATE <= 19940131 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_flat
+WHERE weekofyear(LO_ORDERDATE) = 6 AND LO_ORDERDATE >= 19940101 AND
LO_ORDERDATE <= 19941231 AND LO_DISCOUNT BETWEEN 5 AND 7 AND LO_QUANTITY
BETWEEN 26 AND 35;
+
+--Q2.1
+SELECT SUM(LO_REVENUE), (LO_ORDERDATE DIV 10000) AS YEAR, P_BRAND
+FROM lineorder_flat WHERE P_CATEGORY = 'MFGR#12' AND S_REGION = 'AMERICA'
+GROUP BY YEAR, P_BRAND
+ORDER BY YEAR, P_BRAND;
+
+--Q2.2
+SELECT SUM(LO_REVENUE), (LO_ORDERDATE DIV 10000) AS YEAR, P_BRAND
+FROM lineorder_flat
+WHERE P_BRAND >= 'MFGR#2221' AND P_BRAND <= 'MFGR#2228' AND S_REGION = 'ASIA'
+GROUP BY YEAR, P_BRAND
+ORDER BY YEAR, P_BRAND;
+
+--Q2.3
+SELECT SUM(LO_REVENUE), (LO_ORDERDATE DIV 10000) AS YEAR, P_BRAND
+FROM lineorder_flat
+WHERE P_BRAND = 'MFGR#2239' AND S_REGION = 'EUROPE'
+GROUP BY YEAR, P_BRAND
+ORDER BY YEAR, P_BRAND;
+
+--Q3.1
+SELECT C_NATION, S_NATION, (LO_ORDERDATE DIV 10000) AS YEAR, SUM(LO_REVENUE)
AS revenue
+FROM lineorder_flat
+WHERE C_REGION = 'ASIA' AND S_REGION = 'ASIA' AND LO_ORDERDATE >= 19920101
AND LO_ORDERDATE <= 19971231
+GROUP BY C_NATION, S_NATION, YEAR
+ORDER BY YEAR ASC, revenue DESC;
+
+--Q3.2
+SELECT C_CITY, S_CITY, (LO_ORDERDATE DIV 10000) AS YEAR, SUM(LO_REVENUE) AS
revenue
+FROM lineorder_flat
+WHERE C_NATION = 'UNITED STATES' AND S_NATION = 'UNITED STATES' AND
LO_ORDERDATE >= 19920101 AND LO_ORDERDATE <= 19971231
+GROUP BY C_CITY, S_CITY, YEAR
+ORDER BY YEAR ASC, revenue DESC;
+
+--Q3.3
+SELECT C_CITY, S_CITY, (LO_ORDERDATE DIV 10000) AS YEAR, SUM(LO_REVENUE) AS
revenue
+FROM lineorder_flat
+WHERE C_CITY IN ('UNITED KI1', 'UNITED KI5') AND S_CITY IN ('UNITED KI1',
'UNITED KI5') AND LO_ORDERDATE >= 19920101 AND LO_ORDERDATE <= 19971231
+GROUP BY C_CITY, S_CITY, YEAR
+ORDER BY YEAR ASC, revenue DESC;
+
+--Q3.4
+SELECT C_CITY, S_CITY, (LO_ORDERDATE DIV 10000) AS YEAR, SUM(LO_REVENUE) AS
revenue
+FROM lineorder_flat
+WHERE C_CITY IN ('UNITED KI1', 'UNITED KI5') AND S_CITY IN ('UNITED KI1',
'UNITED KI5') AND LO_ORDERDATE >= 19971201 AND LO_ORDERDATE <= 19971231
+GROUP BY C_CITY, S_CITY, YEAR
+ORDER BY YEAR ASC, revenue DESC;
+
+--Q4.1
+SELECT (LO_ORDERDATE DIV 10000) AS YEAR, C_NATION, SUM(LO_REVENUE -
LO_SUPPLYCOST) AS profit
+FROM lineorder_flat
+WHERE C_REGION = 'AMERICA' AND S_REGION = 'AMERICA' AND P_MFGR IN ('MFGR#1',
'MFGR#2')
+GROUP BY YEAR, C_NATION
+ORDER BY YEAR ASC, C_NATION ASC;
+
+--Q4.2
+SELECT (LO_ORDERDATE DIV 10000) AS YEAR,S_NATION, P_CATEGORY, SUM(LO_REVENUE -
LO_SUPPLYCOST) AS profit
+FROM lineorder_flat
+WHERE C_REGION = 'AMERICA' AND S_REGION = 'AMERICA' AND LO_ORDERDATE >=
19970101 AND LO_ORDERDATE <= 19981231 AND P_MFGR IN ('MFGR#1', 'MFGR#2')
+GROUP BY YEAR, S_NATION, P_CATEGORY
+ORDER BY YEAR ASC, S_NATION ASC, P_CATEGORY ASC;
+
+--Q4.3
+SELECT (LO_ORDERDATE DIV 10000) AS YEAR, S_CITY, P_BRAND, SUM(LO_REVENUE -
LO_SUPPLYCOST) AS profit
+FROM lineorder_flat
+WHERE S_NATION = 'UNITED STATES' AND LO_ORDERDATE >= 19970101 AND LO_ORDERDATE
<= 19981231 AND P_CATEGORY = 'MFGR#14'
+GROUP BY YEAR, S_CITY, P_BRAND
+ORDER BY YEAR ASC, S_CITY ASC, P_BRAND ASC;
+```
+
+#### 7.6.2 SSB Standard Test for 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/blog/tpch.md b/blog/tpch.md
new file mode 100644
index 00000000000..d37a05647ad
--- /dev/null
+++ b/blog/tpch.md
@@ -0,0 +1,881 @@
+---
+{
+ 'title': "Apache Doris 1.2 TPC-H Performance Test Report",
+ 'summary': "On 22 queries on the TPC-H standard test data set, we
conducted a comparison test based on Apache Doris 1.2.0-rc01, Apache Doris
1.1.3 and Apache Doris 0.15.0 RC04 versions. Compared with Apache Doris 1.1.3,
the overall performance of Apache Doris 1.2.0-rc01 has been improved by nearly
3 times, and by nearly 11 times compared with Apache Doris 0.15.0 RC04.",
+ 'date': '2022-11-22',
+ 'author': 'Apache Doris',
+ 'tags': ['Tech Sharing'],
+}
+---
+
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+
+# TPC-H Benchmark
+
+TPC-H is a decision support benchmark (Decision Support Benchmark), which
consists of a set of business-oriented special query and concurrent data
modification. The data that is queried and populates the database has broad
industry relevance. This benchmark demonstrates a decision support system that
examines large amounts of data, executes highly complex queries, and answers
key business questions. The performance index reported by TPC-H is called TPC-H
composite query performance index [...]
+
+This document mainly introduces the performance of Doris on the TPC-H 100G
test set.
+
+> Note 1: The standard test set including TPC-H 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 a specific scenario. We suggest users use actual
business data for further testing.
+>
+> Note 2: The operations involved in this document are all tested on CentOS
7.x.
+
+On 22 queries on the TPC-H standard test data set, we conducted a comparison
test based on Apache Doris 1.2.0-rc01, Apache Doris 1.1.3 and Apache Doris
0.15.0 RC04 versions. Compared with Apache Doris 1.1.3, the overall performance
of Apache Doris 1.2.0-rc01 has been improved by nearly 3 times, and by nearly
11 times compared with Apache Doris 0.15.0 RC04.
+
+## 1. Hardware Environment
+
+| Hardware | Configuration Instructions
|
+| -------- | ------------------------------------ |
+| Number of mMachines | 4 Tencent Cloud Virtual Machine（1FE，3BEs） |
+| CPU | Intel Xeon(Cascade Lake) Platinum 8269CY 16C (2.5 GHz/3.2 GHz) |
+| Memory | 64G |
+| Network | 5Gbps |
+| Disk | ESSD Cloud Hard Disk |
+
+## 2. Software Environment
+
+- Doris Deployed 3BEs and 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.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 TPCH 100G data generated by the simulation of the entire test are
respectively imported into Apache Doris 1.2.0-rc01, Apache Doris 1.1.3 and
Apache Doris 0.15.0 RC04 for testing. The following is the relevant description
and data volume of the table.
+
+| TPC-H Table Name | Rows | Size after Import | Annotation |
+| :--------------- | :---------- | ---------- | :----- |
+| REGION | 5 | 400KB | Region |
+| NATION | 25 | 7.714 KB | Nation |
+| SUPPLIER | 1,000,000 | 85.528 MB | Supplier |
+| PART | 20,000,000 | 752.330 MB | Parts |
+| PARTSUPP | 20,000,000 | 4.375 GB | Parts Supply |
+| CUSTOMER | 15,000,000 | 1.317 GB | Customer |
+| ORDERS | 1,500,000,000 | 6.301 GB | Orders |
+| LINEITEM | 6,000,000,000 | 20.882 GB | Order Details |
+
+## 4. Test SQL
+
+TPCH 22 test query statements ：
[TPCH-Query-SQL](https://github.com/apache/incubator-doris/tree/master/tools/tpch-tools/queries)
+
+**Notice:**
+
+The following four parameters in the above SQL do not exist in Apache Doris
0.15.0 RC04. When executing, please remove:
+
+```
+1. enable_vectorized_engine=true,
+2. batch_size=4096,
+3. disable_join_reorder=false
+4. enable_projection=true
+```
+
+## 5. Test Results
+
+Here we use Apache Doris 1.2.0-rc01, Apache Doris 1.1.3 and Apache Doris
0.15.0 RC04 for comparative testing. In the test, we use Query Time(ms) as the
main performance indicator. The test results are as follows:
+
+| Query | Apache Doris 1.2.0-rc01 (ms) | Apache Doris 1.1.3 (ms) | Apache
Doris 0.15.0 RC04 (ms) |
+| -------- | --------------------------- | ---------------------- |
---------------------------- |
+| 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 as the user's common configuration,
with 4 cloud servers, 16-core 64G SSD, and 1 FE 3 BEs deployment.
+ - Select the user's common configuration test to reduce the cost of user
selection and evaluation, but the entire test process will not consume so many
hardware resources.
+ - Apache Doris 0.15 RC04 failed to execute Q14 in the TPC-H test, unable
to complete the query.
+
+## 6. Environmental 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).
+
+## 7. Data Preparation
+
+### 7.1 Download and Install TPC-H Data Generation Tool
+
+Execute the following script to download and compile the
[tpch-tools](https://github.com/apache/incubator-doris/tree/master/tools/tpch-tools)
tool.
+
+```shell
+sh build-tpch-dbgen.sh
+```
+
+After successful installation, the `dbgen` binary will be generated under the
`TPC-H_Tools_v3.0.0/` directory.
+
+### 7.2 Generating the TPC-H Test Set
+
+Execute the following script to generate the TPC-H dataset:
+
+```shell
+sh gen-tpch-data.sh
+```
+
+> Note 1: Check the script help via `sh gen-tpch-data.sh -h`.
+>
+> Note 2: The data will be generated under the `tpch-data/` directory with the
suffix `.tbl`. The total file size is about 100GB and may need a few minutes to
an hour to generate.
+>
+> Note 3: A standard test data set of 100G is generated by default.
+
+### 7.3 Create Table
+
+#### 7.3.1 Prepare the `doris-cluster.conf` File
+
+Before import the script, you need to write the FE’s ip port and other
information in the `doris-cluster.conf` file.
+
+The file location is at the same level as `load-tpch-data.sh`.
+
+The content of the file includes FE's ip, HTTP port, user name, password and
the DB name of the data to be imported:
+
+```shell
+# Any of FE host
+export FE_HOST='127.0.0.1'
+# http_port in fe.conf
+export FE_HTTP_PORT=8030
+# query_port in fe.conf
+export FE_QUERY_PORT=9030
+# Doris username
+export USER='root'
+# Doris password
+export PASSWORD=''
+# The database where TPC-H tables located
+export DB='tpch1'
+```
+
+#### Execute the Following Script to Generate and Create TPC-H Table
+
+```shell
+sh create-tpch-tables.sh
+```
+Or copy the table creation statement in
[create-tpch-tables.sql](https://github.com/apache/incubator-doris/blob/master/tools/tpch-tools/create-tpch-tables.sql)
and excute it in Doris.
+
+
+### 7.4 Import Data
+
+Please perform data import with the following command:
+
+```shell
+sh ./load-tpch-data.sh
+```
+
+### 7.5 Check Imported Data
+
+Execute the following SQL statement to check that the imported data is
consistent with the above data.
+
+```sql
+select count(*) from lineitem;
+select count(*) from orders;
+select count(*) from partsupp;
+select count(*) from part;
+select count(*) from customer;
+select count(*) from supplier;
+select count(*) from nation;
+select count(*) from region;
+select count(*) from revenue0;
+```
+
+### 7.6 Query Test
+
+#### 7.6.1 Executing Query Scripts
+
+Execute the above test SQL or execute the following command
+
+```
+./run-tpch-queries.sh
+```
+
+>Notice:
+>
+>1. At present, the query optimizer and statistics functions of Doris are not
so perfect, so we rewrite some queries in TPC-H to adapt to the execution
framework of Doris, but it does not affect the correctness of the results
+>
+>2. Doris' new query optimizer will be released in future versions
+>3. Set `set mem_exec_limit=8G` before executing the query
+
+#### 7.6.2 Single SQL Execution
+
+The following is the SQL statement used in the test, you can also get the
latest SQL from the code base.
+
+```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;
+
+--Q22
+
+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-blog/ssb.md
b/i18n/zh-CN/docusaurus-plugin-content-blog/ssb.md
new file mode 100644
index 00000000000..db24b4bb921
--- /dev/null
+++ b/i18n/zh-CN/docusaurus-plugin-content-blog/ssb.md
@@ -0,0 +1,616 @@
+---
+{
+ 'title': 'Apache Doris 1.2 Star-Schema-Benchmark 性能测试报告，',
+ 'summary': "Doris 1.2 版本在 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 倍 ",
+ 'date': '2022-11-22',
+ 'author': 'Apache Doris',
+ 'tags': ['技术解析'],
+}
+---
+
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+
+# Star Schema Benchmark
+
+[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)。
+
+本文档主要介绍Apache Doris 在 SSB 100G 测试集上的性能表现。
+
+> 注 1：包括 SSB
在内的标准测试集通常和实际业务场景差距较大，并且部分测试会针对测试集进行参数调优。所以标准测试集的测试结果仅能反映数据库在特定场景下的性能表现。建议用户使用实际业务数据进行进一步的测试。
+>
+> 注 2：本文档涉及的操作都在 Ubuntu Server 20.04 环境进行，CentOS 7 也可测试。
+
+在 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） |
+| -------- | ------------------------------------ |
+| CPU | AMD EPYC™ Milan(2.55GHz/3.5GHz) 16核 |
+| 内存 | 64G |
+| 网络带宽 | 7Gbps |
+| 磁盘 | 高性能云硬盘 |
+
+## 2. 软件环境
+
+- Doris 部署 3BE 1FE；
+- 内核版本：Linux version 5.4.0-96-generic (buildd@lgw01-amd64-051)
+- 操作系统版本：Ubuntu Server 20.04 LTS 64位
+- 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. 测试数据量
+
+| SSB表名 | 行数 | 备注 |
+| :------------- | :--------- | :--------------- |
+| lineorder | 600,037,902 | 商品订单明细表表 |
+| customer | 3,000,000 | 客户信息表 |
+| part | 1,400,000 | 零件信息表 |
+| supplier | 200,000 | 供应商信息表 |
+| date | 2,556 | 日期表 |
+| lineorder_flat | 600,037,902 | 数据展平后的宽表 |
+
+## 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 部署。
+- 选用用户常见配置测试以降低用户选型评估成本，但整个测试过程中不会消耗如此多的硬件资源。
+
+## 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 |
+
+**结果说明**
+
+- 测试结果对应的数据集为scale 100, 约6亿条。
+- 测试环境配置为用户常用配置，云服务器4台，16核 64G SSD，1 FE 3 BE 部署。
+- 选用用户常见配置测试以降低用户选型评估成本，但整个测试过程中不会消耗如此多的硬件资源。
+
+
+## 6. 环境准备
+
+请先参照 [官方文档](../install/install-deploy.md) 进行 Apache Doris 的安装部署，以获得一个正常运行中的
Doris 集群（至少包含 1 FE 1 BE，推荐 1 FE 3 BE）。
+
+以下文档中涉及的脚本都存放在 Apache Doris
代码库：[ssb-tools](https://github.com/apache/doris/tree/master/tools/ssb-tools)
+
+## 7. 数据准备
+
+### 7.1 下载安装 SSB 数据生成工具。
+
+执行以下脚本下载并编译 [ssb-dbgen](https://github.com/electrum/ssb-dbgen.git) 工具。
+
+```shell
+sh build-ssb-dbgen.sh
+```
+
+安装成功后，将在 `ssb-dbgen/` 目录下生成 `dbgen` 二进制文件。
+
+### 7.2 生成 SSB 测试集
+
+执行以下脚本生成 SSB 数据集：
+
+```shell
+sh gen-ssb-data.sh -s 100 -c 100
+```
+
+> 注1：通过 `sh gen-ssb-data.sh -h` 查看脚本帮助。
+>
+> 注2：数据会以 `.tbl` 为后缀生成在 `ssb-data/` 目录下。文件总大小约60GB。生成时间可能在数分钟到1小时不等。
+>
+> 注3：`-s 100` 表示测试集大小系数为 100，`-c 100` 表示并发100个线程生成 lineorder 表的数据。`-c`
参数也决定了最终 lineorder 表的文件数量。参数越大，文件数越多，每个文件越小。
+
+在 `-s 100` 参数下，生成的数据集大小为：
+
+| Table | Rows | Size | File Number |
+| --------- | ---------------- | ---- | ----------- |
+| lineorder | 6亿（600037902） | 60GB | 100 |
+| customer | 300万（3000000） | 277M | 1 |
+| part | 140万（1400000） | 116M | 1 |
+| supplier | 20万（200000） | 17M | 1 |
+| date | 2556 | 228K | 1 |
+
+### 7.3 建表
+
+#### 7.3.1 准备 `doris-cluster.conf` 文件。
+
+在调用导入脚本前，需要将 FE 的 ip 端口等信息写在 `doris-cluster.conf` 文件中。
+
+文件位置和 `load-ssb-dimension-data.sh` 平级。
+
+文件内容包括 FE 的 ip，HTTP 端口，用户名，密码以及待导入数据的 DB 名称：
+
+```shell
+export FE_HOST="xxx"
+export FE_HTTP_PORT="8030"
+export FE_QUERY_PORT="9030"
+export USER="root"
+export PASSWORD='xxx'
+export DB="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)
和
[create-ssb-flat-table.sql](https://github.com/apache/incubator-doris/tree/master/tools/ssb-tools/ddl/create-ssb-flat-table.sql)
中的建表语句，在 MySQL 客户端中执行。
+
+下面是 `lineorder_flat` 表建表语句。在上面的 `create-ssb-flat-table.sh` 脚本中创建
`lineorder_flat`
表，并进行了默认分桶数（48个桶)。您可以删除该表，根据您的集群规模节点配置对这个分桶数进行调整，这样可以获取到更好的一个测试效果。
+
+```sql
+CREATE TABLE `lineorder_flat` (
+ `LO_ORDERDATE` date NOT NULL COMMENT "",
+ `LO_ORDERKEY` int(11) NOT NULL COMMENT "",
+ `LO_LINENUMBER` tinyint(4) NOT NULL COMMENT "",
+ `LO_CUSTKEY` int(11) NOT NULL COMMENT "",
+ `LO_PARTKEY` int(11) NOT NULL COMMENT "",
+ `LO_SUPPKEY` int(11) NOT NULL COMMENT "",
+ `LO_ORDERPRIORITY` varchar(100) NOT NULL COMMENT "",
+ `LO_SHIPPRIORITY` tinyint(4) NOT NULL COMMENT "",
+ `LO_QUANTITY` tinyint(4) NOT NULL COMMENT "",
+ `LO_EXTENDEDPRICE` int(11) NOT NULL COMMENT "",
+ `LO_ORDTOTALPRICE` int(11) NOT NULL COMMENT "",
+ `LO_DISCOUNT` tinyint(4) NOT NULL COMMENT "",
+ `LO_REVENUE` int(11) NOT NULL COMMENT "",
+ `LO_SUPPLYCOST` int(11) NOT NULL COMMENT "",
+ `LO_TAX` tinyint(4) NOT NULL COMMENT "",
+ `LO_COMMITDATE` date NOT NULL COMMENT "",
+ `LO_SHIPMODE` varchar(100) NOT NULL COMMENT "",
+ `C_NAME` varchar(100) NOT NULL COMMENT "",
+ `C_ADDRESS` varchar(100) NOT NULL COMMENT "",
+ `C_CITY` varchar(100) NOT NULL COMMENT "",
+ `C_NATION` varchar(100) NOT NULL COMMENT "",
+ `C_REGION` varchar(100) NOT NULL COMMENT "",
+ `C_PHONE` varchar(100) NOT NULL COMMENT "",
+ `C_MKTSEGMENT` varchar(100) NOT NULL COMMENT "",
+ `S_NAME` varchar(100) NOT NULL COMMENT "",
+ `S_ADDRESS` varchar(100) NOT NULL COMMENT "",
+ `S_CITY` varchar(100) NOT NULL COMMENT "",
+ `S_NATION` varchar(100) NOT NULL COMMENT "",
+ `S_REGION` varchar(100) NOT NULL COMMENT "",
+ `S_PHONE` varchar(100) NOT NULL COMMENT "",
+ `P_NAME` varchar(100) NOT NULL COMMENT "",
+ `P_MFGR` varchar(100) NOT NULL COMMENT "",
+ `P_CATEGORY` varchar(100) NOT NULL COMMENT "",
+ `P_BRAND` varchar(100) NOT NULL COMMENT "",
+ `P_COLOR` varchar(100) NOT NULL COMMENT "",
+ `P_TYPE` varchar(100) NOT NULL COMMENT "",
+ `P_SIZE` tinyint(4) NOT NULL COMMENT "",
+ `P_CONTAINER` varchar(100) NOT NULL COMMENT ""
+) ENGINE=OLAP
+DUPLICATE KEY(`LO_ORDERDATE`, `LO_ORDERKEY`)
+COMMENT "OLAP"
+PARTITION BY RANGE(`LO_ORDERDATE`)
+(PARTITION p1 VALUES [('0000-01-01'), ('1993-01-01')),
+PARTITION p2 VALUES [('1993-01-01'), ('1994-01-01')),
+PARTITION p3 VALUES [('1994-01-01'), ('1995-01-01')),
+PARTITION p4 VALUES [('1995-01-01'), ('1996-01-01')),
+PARTITION p5 VALUES [('1996-01-01'), ('1997-01-01')),
+PARTITION p6 VALUES [('1997-01-01'), ('1998-01-01')),
+PARTITION p7 VALUES [('1998-01-01'), ('1999-01-01')))
+DISTRIBUTED BY HASH(`LO_ORDERKEY`) BUCKETS 48
+PROPERTIES (
+"replication_num" = "1",
+"colocate_with" = "groupxx1",
+"in_memory" = "false",
+"storage_format" = "DEFAULT"
+);
+```
+
+### 7.4 导入数据
+
+我们使用以下命令完成 SSB 测试集所有数据导入及 SSB FLAT 宽表数据合成并导入到表里。
+
+
+```shell
+sh bin/load-ssb-data.sh -c 10
+```
+
+`-c 5` 表示启动 10 个并发线程导入（默认为 5）。在单 BE 节点情况下，由 `sh gen-ssb-data.sh -s 100 -c 100`
生成的 lineorder 数据，同时会在最后生成ssb-flat表的数据，如果开启更多线程，可以加快导入速度，但会增加额外的内存开销。
+
+> 注：
+>
+> 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 ... ' 的方式导入。
+
+
+### 7.5 检查导入数据
+
+```sql
+select count(*) from part;
+select count(*) from customer;
+select count(*) from supplier;
+select count(*) from date;
+select count(*) from lineorder;
+select count(*) from lineorder_flat;
+```
+
+数据量应和生成数据的行数一致。
+
+| Table | Rows | Origin Size | Compacted Size(1 Replica) |
+| -------------- | ---------------- | ----------- | ------------------------- |
+| lineorder_flat | 6亿（600037902） | | 59.709 GB |
+| lineorder | 6亿（600037902） | 60 GB | 14.514 GB |
+| customer | 300万（3000000） | 277 MB | 138.247 MB |
+| part | 140万（1400000） | 116 MB | 12.759 MB |
+| supplier | 20万（200000） | 17 MB | 9.143 MB |
+| date | 2556 | 228 KB | 34.276 KB |
+
+### 7.6 查询测试
+
+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
+SELECT SUM(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue
+FROM lineorder_flat
+WHERE LO_ORDERDATE >= 19930101 AND LO_ORDERDATE <= 19931231 AND LO_DISCOUNT
BETWEEN 1 AND 3 AND LO_QUANTITY < 25;
+--Q1.2
+SELECT SUM(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue
+FROM lineorder_flat
+WHERE LO_ORDERDATE >= 19940101 AND LO_ORDERDATE <= 19940131 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_flat
+WHERE weekofyear(LO_ORDERDATE) = 6 AND LO_ORDERDATE >= 19940101 AND
LO_ORDERDATE <= 19941231 AND LO_DISCOUNT BETWEEN 5 AND 7 AND LO_QUANTITY
BETWEEN 26 AND 35;
+
+--Q2.1
+SELECT SUM(LO_REVENUE), (LO_ORDERDATE DIV 10000) AS YEAR, P_BRAND
+FROM lineorder_flat WHERE P_CATEGORY = 'MFGR#12' AND S_REGION = 'AMERICA'
+GROUP BY YEAR, P_BRAND
+ORDER BY YEAR, P_BRAND;
+
+--Q2.2
+SELECT SUM(LO_REVENUE), (LO_ORDERDATE DIV 10000) AS YEAR, P_BRAND
+FROM lineorder_flat
+WHERE P_BRAND >= 'MFGR#2221' AND P_BRAND <= 'MFGR#2228' AND S_REGION = 'ASIA'
+GROUP BY YEAR, P_BRAND
+ORDER BY YEAR, P_BRAND;
+
+--Q2.3
+SELECT SUM(LO_REVENUE), (LO_ORDERDATE DIV 10000) AS YEAR, P_BRAND
+FROM lineorder_flat
+WHERE P_BRAND = 'MFGR#2239' AND S_REGION = 'EUROPE'
+GROUP BY YEAR, P_BRAND
+ORDER BY YEAR, P_BRAND;
+
+--Q3.1
+SELECT C_NATION, S_NATION, (LO_ORDERDATE DIV 10000) AS YEAR, SUM(LO_REVENUE)
AS revenue
+FROM lineorder_flat
+WHERE C_REGION = 'ASIA' AND S_REGION = 'ASIA' AND LO_ORDERDATE >= 19920101
AND LO_ORDERDATE <= 19971231
+GROUP BY C_NATION, S_NATION, YEAR
+ORDER BY YEAR ASC, revenue DESC;
+
+--Q3.2
+SELECT C_CITY, S_CITY, (LO_ORDERDATE DIV 10000) AS YEAR, SUM(LO_REVENUE) AS
revenue
+FROM lineorder_flat
+WHERE C_NATION = 'UNITED STATES' AND S_NATION = 'UNITED STATES' AND
LO_ORDERDATE >= 19920101 AND LO_ORDERDATE <= 19971231
+GROUP BY C_CITY, S_CITY, YEAR
+ORDER BY YEAR ASC, revenue DESC;
+
+--Q3.3
+SELECT C_CITY, S_CITY, (LO_ORDERDATE DIV 10000) AS YEAR, SUM(LO_REVENUE) AS
revenue
+FROM lineorder_flat
+WHERE C_CITY IN ('UNITED KI1', 'UNITED KI5') AND S_CITY IN ('UNITED KI1',
'UNITED KI5') AND LO_ORDERDATE >= 19920101 AND LO_ORDERDATE <= 19971231
+GROUP BY C_CITY, S_CITY, YEAR
+ORDER BY YEAR ASC, revenue DESC;
+
+--Q3.4
+SELECT C_CITY, S_CITY, (LO_ORDERDATE DIV 10000) AS YEAR, SUM(LO_REVENUE) AS
revenue
+FROM lineorder_flat
+WHERE C_CITY IN ('UNITED KI1', 'UNITED KI5') AND S_CITY IN ('UNITED KI1',
'UNITED KI5') AND LO_ORDERDATE >= 19971201 AND LO_ORDERDATE <= 19971231
+GROUP BY C_CITY, S_CITY, YEAR
+ORDER BY YEAR ASC, revenue DESC;
+
+--Q4.1
+SELECT (LO_ORDERDATE DIV 10000) AS YEAR, C_NATION, SUM(LO_REVENUE -
LO_SUPPLYCOST) AS profit
+FROM lineorder_flat
+WHERE C_REGION = 'AMERICA' AND S_REGION = 'AMERICA' AND P_MFGR IN ('MFGR#1',
'MFGR#2')
+GROUP BY YEAR, C_NATION
+ORDER BY YEAR ASC, C_NATION ASC;
+
+--Q4.2
+SELECT (LO_ORDERDATE DIV 10000) AS YEAR,S_NATION, P_CATEGORY, SUM(LO_REVENUE -
LO_SUPPLYCOST) AS profit
+FROM lineorder_flat
+WHERE C_REGION = 'AMERICA' AND S_REGION = 'AMERICA' AND LO_ORDERDATE >=
19970101 AND LO_ORDERDATE <= 19981231 AND P_MFGR IN ('MFGR#1', 'MFGR#2')
+GROUP BY YEAR, S_NATION, P_CATEGORY
+ORDER BY YEAR ASC, S_NATION ASC, P_CATEGORY ASC;
+
+--Q4.3
+SELECT (LO_ORDERDATE DIV 10000) AS YEAR, S_CITY, P_BRAND, SUM(LO_REVENUE -
LO_SUPPLYCOST) AS profit
+FROM lineorder_flat
+WHERE S_NATION = 'UNITED STATES' AND LO_ORDERDATE >= 19970101 AND LO_ORDERDATE
<= 19981231 AND P_CATEGORY = 'MFGR#14'
+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-blog/tpch.md
b/i18n/zh-CN/docusaurus-plugin-content-blog/tpch.md
new file mode 100644
index 00000000000..e05b4a23919
--- /dev/null
+++ b/i18n/zh-CN/docusaurus-plugin-content-blog/tpch.md
@@ -0,0 +1,884 @@
+---
+{
+ 'title': 'Apache Doris 1.2 TPC-H 性能测试报告，',
+ 'summary': "在 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 倍",
+ 'date': '2022-11-22',
+ 'author': 'Apache Doris',
+ 'tags': ['技术解析'],
+}
+---
+
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+
+# TPC-H Benchmark
+
+TPC-H是一个决策支持基准（Decision Support
Benchmark），它由一套面向业务的特别查询和并发数据修改组成。查询和填充数据库的数据具有广泛的行业相关性。这个基准测试演示了检查大量数据、执行高度复杂的查询并回答关键业务问题的决策支持系统。TPC-H报告的性能指标称为TPC-H每小时复合查询性能指标(QphH@Size)，反映了系统处理查询能力的多个方面。这些方面包括执行查询时所选择的数据库大小，由单个流提交查询时的查询处理能力，以及由多个并发用户提交查询时的查询吞吐量。
+
+本文档主要介绍 Doris 在 TPC-H 100G 测试集上的性能表现。
+
+> 注1：包括 TPC-H
在内的标准测试集通常和实际业务场景差距较大，并且部分测试会针对测试集进行参数调优。所以标准测试集的测试结果仅能反映数据库在特定场景下的性能表现。建议用户使用实际业务数据进行进一步的测试。
+>
+> 注2：本文档涉及的操作都在 CentOS 7.x 上进行测试。
+
+在 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 倍 。
+
+
+
+## 1. 硬件环境
+
+| 硬件 | 配置说明 |
+| -------- | ------------------------------------ |
+| 机器数量 | 4 台腾讯云主机（1个FE，3个BE） |
+| CPU | Intel Xeon(Cascade Lake) Platinum 8269CY 16核 (2.5 GHz/3.2 GHz) |
+| 内存 | 64G |
+| 网络带宽 | 5Gbps |
+| 磁盘 | ESSD云硬盘 |
+
+## 2. 软件环境
+
+- Doris部署 3BE 1FE；
+- 内核版本：Linux version 5.4.0-96-generic (buildd@lgw01-amd64-051)
+- 操作系统版本：CentOS 7.8
+- 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. 测试数据量
+
+整个测试模拟生成 TPCH 100G 的数据分别导入到 Apache Doris 1.2.0-rc01， Apache Doris 1.1.3 及
Apache Doris 0.15.0 RC04 版本进行测试，下面是表的相关说明及数据量。
+
+| TPC-H表名 | 行数 | 导入后大小 | 备注 |
+| :-------- | :----- | ---------- | :----------- |
+| REGION | 5 | 400KB | 区域表 |
+| NATION | 25 | 7.714 KB | 国家表 |
+| SUPPLIER | 100万 | 85.528 MB | 供应商表 |
+| PART | 2000万 | 752.330 MB | 零部件表 |
+| PARTSUPP | 8000万 | 4.375 GB | 零部件供应表 |
+| CUSTOMER | 1500万 | 1.317 GB | 客户表 |
+| ORDERS | 1.5亿 | 6.301 GB | 订单表 |
+| LINEITEM | 6亿 | 20.882 GB | 订单明细表 |
+
+## 4. 测试SQL
+
+TPCH 22 个测试查询语句 ：
[TPCH-Query-SQL](https://github.com/apache/incubator-doris/tree/master/tools/tpch-tools/queries)
+
+**注意：**
+
+以上 SQL 中的以下四个参数在 Apache Doris 0.15.0 RC04 中不存在，在执行的时候，去掉：
+
+```
+1. enable_vectorized_engine=true,
+2. batch_size=4096,
+3. disable_join_reorder=false
+4. enable_projection=true
+```
+
+## 5. 测试结果
+
+这里我们使用 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 部署。
+- 选用用户常见配置测试以降低用户选型评估成本，但整个测试过程中不会消耗如此多的硬件资源。
+- Apache Doris 0.15 RC04 在 TPC-H 测试中 Q14 执行失败，无法完成查询。
+
+## 6. 环境准备
+
+请先参照 [官方文档](../install/install-deploy.md) 进行 Doris 的安装部署，以获得一个正常运行中的 Doris
集群（至少包含 1 FE 1 BE，推荐 1 FE 3 BE）。
+
+## 7. 数据准备
+
+### 7.1 下载安装 TPC-H 数据生成工具
+
+执行以下脚本下载并编译
[tpch-tools](https://github.com/apache/incubator-doris/tree/master/tools/tpch-tools)
工具。
+
+```shell
+sh build-tpch-dbgen.sh
+```
+
+安装成功后，将在 `TPC-H_Tools_v3.0.0/` 目录下生成 `dbgen` 二进制文件。
+
+### 7.2 生成 TPC-H 测试集
+
+执行以下脚本生成 TPC-H 数据集：
+
+```shell
+sh gen-tpch-data.sh
+```
+
+> 注1：通过 `sh gen-tpch-data.sh -h` 查看脚本帮助。
+>
+> 注2：数据会以 `.tbl` 为后缀生成在 `tpch-data/` 目录下。文件总大小约100GB。生成时间可能在数分钟到1小时不等。
+>
+> 注3：默认生成 100G 的标准测试数据集
+
+### 7.3 建表
+
+#### 7.3.1 准备 `doris-cluster.conf` 文件
+
+在调用导入脚本前，需要将 FE 的 ip 端口等信息写在 `doris-cluster.conf` 文件中。
+
+文件位置和 `load-tpch-data.sh` 平级。
+
+文件内容包括 FE 的 ip，HTTP 端口，用户名，密码以及待导入数据的 DB 名称：
+
+```shell
+# Any of FE host
+export FE_HOST='127.0.0.1'
+# http_port in fe.conf
+export FE_HTTP_PORT=8030
+# query_port in fe.conf
+export FE_QUERY_PORT=9030
+# Doris username
+export USER='root'
+# Doris password
+export PASSWORD=''
+# The database where TPC-H tables located
+export DB='tpch1'
+```
+
+#### 7.3.2 执行以下脚本生成创建 TPC-H 表
+
+```shell
+sh create-tpch-tables.sh
+```
+或者复制
[create-tpch-tables.sql](https://github.com/apache/incubator-doris/blob/master/tools/tpch-tools/create-tpch-tables.sql)
中的建表语句，在 Doris 中执行。
+
+
+### 7.4 导入数据
+
+通过下面的命令执行数据导入：
+
+```shell
+sh ./load-tpch-data.sh
+```
+
+### 7.5 检查导入数据
+
+执行下面的 SQL 语句检查导入的数据与上面的数据量是一致。
+
+```sql
+select count(*) from lineitem;
+select count(*) from orders;
+select count(*) from partsupp;
+select count(*) from part;
+select count(*) from customer;
+select count(*) from supplier;
+select count(*) from nation;
+select count(*) from region;
+select count(*) from revenue0;
+```
+
+### 7.6 查询测试
+
+## 7.6.1 执行查询脚本
+
+执行上面的测试 SQL 或者 执行下面的命令
+
+```
+./run-tpch-queries.sh
+```
+
+>注意：
+>
+>1. 目前Doris的查询优化器和统计信息功能还不完善，所以我们在TPC-H中重写了一些查询以适应Doris的执行框架，但不影响结果的正确性
+>
+>2. Doris 新的查询优化器将在后续的版本中发布
+>3. 执行查询之前设置 `set mem_exec_limit=8G`
+
+## 7.6.2 单个 SQL 执行
+
+下面是测试时使用的 SQL 语句，你也可以从代码库里获取最新的 SQL 。最新测试查询语句地址：[TPC-H
测试查询语句](https://github.com/apache/doris/tree/master/tools/tpch-tools/queries)
+
+```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;
+
+--Q22
+
+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;
+
+```
+
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
