TPC-H 性能测试

TPC-H 性能测试

TPC-H 通常用于测试系统的OLAP性能。

Build TPC-H Generator

git clone git@github.com:electrum/tpch-dbgen.git
cd tpch-dbgen/
make

Generate TPC-H Data

SCALE_FACTOR=1
export DSS_QUERY="${PWD}/queries"
export OUT_QUERY="/tmp/queries"
mkdir -p ${OUT_QUERY}
./dbgen -vf -s ${SCALE_FACTOR}
for i in {1..22}
do
    diff $i.sql ../queries/$i.sql  
done

for i in {1..22} do ./qgen -s 100 -d ${i} > ${OUT_QUERY}/${i}.sql
done

Prepare Database

psql postgres -c 'DROP DATABASE tpch;';
psql postgres  -c 'CREATE DATABASE tpch';
psql postgres  -c 'TRUNCATE TABLE REGION,PART CASCADE;';
# psql tpc -f dss.ddl ; psql tpc -c '\d+' ; ls -alh *.tbl

CREATE TABLE REGION
(
    R_REGIONKEY INTEGER  NOT NULL PRIMARY KEY,
    R_NAME      CHAR(25) NOT NULL,
    R_COMMENT   VARCHAR(152)
);
CREATE TABLE NATION
(
    N_NATIONKEY INTEGER  NOT NULL PRIMARY KEY,
    N_NAME      CHAR(25) NOT NULL,
    N_REGIONKEY INTEGER  NOT NULL,
    N_COMMENT   VARCHAR(152),
    FOREIGN KEY (N_REGIONKEY) REFERENCES REGION (R_REGIONKEY)
);
CREATE TABLE PART
(
    P_PARTKEY     INTEGER        NOT NULL PRIMARY KEY,
    P_NAME        VARCHAR(55)    NOT NULL,
    P_MFGR        CHAR(25)       NOT NULL,
    P_BRAND       CHAR(10)       NOT NULL,
    P_TYPE        VARCHAR(25)    NOT NULL,
    P_SIZE        INTEGER        NOT NULL,
    P_CONTAINER   CHAR(10)       NOT NULL,
    P_RETAILPRICE DECIMAL(15, 2) NOT NULL,
    P_COMMENT     VARCHAR(23)    NOT NULL
);
CREATE TABLE SUPPLIER
(
    S_SUPPKEY   INTEGER        NOT NULL PRIMARY KEY,
    S_NAME      CHAR(25)       NOT NULL,
    S_ADDRESS   VARCHAR(40)    NOT NULL,
    S_NATIONKEY INTEGER        NOT NULL,
    S_PHONE     CHAR(15)       NOT NULL,
    S_ACCTBAL   DECIMAL(15, 2) NOT NULL,
    S_COMMENT   VARCHAR(101)   NOT NULL,
    FOREIGN KEY (S_NATIONKEY) REFERENCES NATION (N_NATIONKEY)
);
CREATE TABLE PARTSUPP
(
    PS_PARTKEY    INTEGER        NOT NULL,
    PS_SUPPKEY    INTEGER        NOT NULL,
    PS_AVAILQTY   INTEGER        NOT NULL,
    PS_SUPPLYCOST DECIMAL(15, 2) NOT NULL,
    PS_COMMENT    VARCHAR(199)   NOT NULL,
    PRIMARY KEY (PS_PARTKEY, PS_SUPPKEY),
    FOREIGN KEY (PS_SUPPKEY) REFERENCES SUPPLIER (S_SUPPKEY),
    FOREIGN KEY (PS_PARTKEY) REFERENCES PART (P_PARTKEY)
);
CREATE TABLE CUSTOMER
(
    C_CUSTKEY    INTEGER        NOT NULL PRIMARY KEY,
    C_NAME       VARCHAR(25)    NOT NULL,
    C_ADDRESS    VARCHAR(40)    NOT NULL,
    C_NATIONKEY  INTEGER        NOT NULL,
    C_PHONE      CHAR(15)       NOT NULL,
    C_ACCTBAL    DECIMAL(15, 2) NOT NULL,
    C_MKTSEGMENT CHAR(10)       NOT NULL,
    C_COMMENT    VARCHAR(117)   NOT NULL,
    FOREIGN KEY (C_NATIONKEY) REFERENCES NATION (N_NATIONKEY)
);
CREATE TABLE ORDERS
(
    O_ORDERKEY      INTEGER        NOT NULL PRIMARY KEY,
    O_CUSTKEY       INTEGER        NOT NULL,
    O_ORDERSTATUS   CHAR(1)        NOT NULL,
    O_TOTALPRICE    DECIMAL(15, 2) NOT NULL,
    O_ORDERDATE     DATE           NOT NULL,
    O_ORDERPRIORITY CHAR(15)       NOT NULL,
    O_CLERK         CHAR(15)       NOT NULL,
    O_SHIPPRIORITY  INTEGER        NOT NULL,
    O_COMMENT       VARCHAR(79)    NOT NULL,
    FOREIGN KEY (O_CUSTKEY) REFERENCES CUSTOMER (C_CUSTKEY)
);
CREATE TABLE LINEITEM
(
    L_ORDERKEY      INTEGER        NOT NULL,
    L_PARTKEY       INTEGER        NOT NULL,
    L_SUPPKEY       INTEGER        NOT NULL,
    L_LINENUMBER    INTEGER        NOT NULL,
    L_QUANTITY      DECIMAL(15, 2) NOT NULL,
    L_EXTENDEDPRICE DECIMAL(15, 2) NOT NULL,
    L_DISCOUNT      DECIMAL(15, 2) NOT NULL,
    L_TAX           DECIMAL(15, 2) NOT NULL,
    L_RETURNFLAG    CHAR(1)        NOT NULL,
    L_LINESTATUS    CHAR(1)        NOT NULL,
    L_SHIPDATE      DATE           NOT NULL,
    L_COMMITDATE    DATE           NOT NULL,
    L_RECEIPTDATE   DATE           NOT NULL,
    L_SHIPINSTRUCT  CHAR(25)       NOT NULL,
    L_SHIPMODE      CHAR(10)       NOT NULL,
    L_COMMENT       VARCHAR(44)    NOT NULL,
    PRIMARY KEY (L_ORDERKEY, L_LINENUMBER),
    FOREIGN KEY (L_ORDERKEY) REFERENCES ORDERS (O_ORDERKEY),
    FOREIGN KEY (L_PARTKEY, L_SUPPKEY) REFERENCES PARTSUPP (PS_PARTKEY, PS_SUPPKEY)
);
CREATE INDEX ON ORDERS (O_ORDERDATE);
CREATE INDEX ON LINEITEM (L_SHIPDATE);
CREATE INDEX ON LINEITEM (L_PARTKEY, L_SUPPKEY);
-- ALTER TABLE lineitem ADD FOREIGN KEY(l_partkey, l_suppkey) REFERENCES partsupp(ps_partkey, ps_suppkey);

sed 's/|$//' region.tbl    | psql tpch -c "COPY region    FROM STDIN DELIMITER '|';"
sed 's/|$//' nation.tbl    | psql tpch -c "COPY nation    FROM STDIN DELIMITER '|';"       
sed 's/|$//' supplier.tbl  | psql tpch -c "COPY supplier  FROM STDIN DELIMITER '|';"
sed 's/|$//' part.tbl      | psql tpch -c "COPY part      FROM STDIN DELIMITER '|';"
sed 's/|$//' customer.tbl  | psql tpch -c "COPY customer  FROM STDIN DELIMITER '|';"       
sed 's/|$//' partsupp.tbl  | psql tpch -c "COPY partsupp  FROM STDIN DELIMITER '|';"
sed 's/|$//' orders.tbl    | psql tpch -c "COPY orders    FROM STDIN DELIMITER '|';"
sed 's/|$//' lineitem.tbl  | psql tpch -c "COPY lineitem  FROM STDIN DELIMITER '|';"

Prepare Queries

git clone https://github.com/2ndQuadrant/pg-tpch.git
cd pg-tpch/queries
psql tpc
\i q01.analyze.sql
\i q02.analyze.sql
\i q03.analyze.sql
\i q04.analyze.sql
\i q05.analyze.sql
\i q06.analyze.sql
\i q07.analyze.sql
\i q08.analyze.sql
\i q09.analyze.sql
\i q10.analyze.sql
\i q11.analyze.sql
\i q12.analyze.sql
\i q13.analyze.sql
\i q14.analyze.sql
\i q15.analyze.sql
\i q16.analyze.sql
\i q17.analyze.sql
\i q18.analyze.sql
\i q19.analyze.sql
\i q20.analyze.sql
\i q21.analyze.sql
\i q22.analyze.sql

\i q01.explain.sql
\i q02.explain.sql
\i q03.explain.sql
\i q04.explain.sql
\i q05.explain.sql
\i q06.explain.sql
\i q07.explain.sql
\i q08.explain.sql
\i q09.explain.sql
\i q10.explain.sql
\i q11.explain.sql
\i q12.explain.sql
\i q13.explain.sql
\i q14.explain.sql
\i q15.explain.sql
\i q16.explain.sql
\i q17.explain.sql
\i q18.explain.sql
\i q19.explain.sql
\i q20.explain.sql
\i q21.explain.sql
\i q22.explain.sql

-- enhance with index
ALTER TABLE region ADD PRIMARY KEY (r_regionkey);
ALTER TABLE nation ADD PRIMARY KEY (n_nationkey);
ALTER TABLE customer ADD PRIMARY KEY (c_custkey);
ALTER TABLE supplier ADD PRIMARY KEY (s_suppkey);
ALTER TABLE part ADD PRIMARY KEY (p_partkey);
ALTER TABLE orders ADD PRIMARY KEY (o_orderkey);
ALTER TABLE partsupp ADD PRIMARY KEY (ps_partkey, ps_suppkey);
ALTER TABLE lineitem ADD PRIMARY KEY (l_orderkey, l_linenumber);
ALTER TABLE nation ADD FOREIGN KEY(n_regionkey) REFERENCES region(r_regionkey);
ALTER TABLE customer ADD FOREIGN KEY(c_nationkey) REFERENCES nation(n_nationkey);
ALTER TABLE supplier ADD FOREIGN KEY(s_nationkey) REFERENCES nation(n_nationkey);
ALTER TABLE orders ADD FOREIGN KEY(o_custkey) REFERENCES customer(c_custkey);
ALTER TABLE customer ADD FOREIGN KEY(c_suppkey) REFERENCES supplier(s_suppkey);
ALTER TABLE partsupp ADD FOREIGN KEY(ps_partkey) REFERENCES part(p_partkey);
ALTER TABLE lineitem ADD FOREIGN KEY(l_orderkey) REFERENCES orders(o_orderkey);
ALTER TABLE lineitem ADD FOREIGN KEY(l_partkey, l_suppkey) REFERENCES partsupp(ps_partkey, ps_suppkey);

TPC-H

Apple M1 Max (10C 64G 8TB)

s = 10 , s = 100

查询	10仓耗时	100仓耗时
1	3.9	94
2	0.9	21
3	1.4	94
4	5.0	247
5	1.3	123
6	1.8	79
7	1.2	109
8	0.9	288
9	4.6	1346
10	1.7	209
11	0.7	32
12	1.1	115
13	11.8	214
14	0.7	111
15	3.7	189
16	1.5	140
17	7.5	605
18	37.4	844
19	0.3	36
20	1.7	653
21	2.2	215
22	1.0	68

最后修改 2023-02-27: update zh docs (a5a4cc0)