TPC-H 性能测试

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

Build TPC-H Generator

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

Generate TPC-H Data

1. SCALE_FACTOR=1
2. export DSS_QUERY="${PWD}/queries"
3. export OUT_QUERY="/tmp/queries"
4. mkdir -p ${OUT_QUERY}

6. ./dbgen -vf -s ${SCALE_FACTOR}

10. for i in {1..22}
11. do

13. diff $i.sql ../queries/$i.sql
14. done

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

Prepare Database

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

1. CREATE TABLE REGION
2. (
3. R_REGIONKEY INTEGER  NOT NULL PRIMARY KEY,
4. R_NAME      CHAR(25) NOT NULL,
5. R_COMMENT   VARCHAR(152)
6. );

8. CREATE TABLE NATION
9. (
10. N_NATIONKEY INTEGER  NOT NULL PRIMARY KEY,
11. N_NAME      CHAR(25) NOT NULL,
12. N_REGIONKEY INTEGER  NOT NULL,
13. N_COMMENT   VARCHAR(152),
14. FOREIGN KEY (N_REGIONKEY) REFERENCES REGION (R_REGIONKEY)
15. );

17. CREATE TABLE PART
18. (
19. P_PARTKEY     INTEGER        NOT NULL PRIMARY KEY,
20. P_NAME        VARCHAR(55)    NOT NULL,
21. P_MFGR        CHAR(25)       NOT NULL,
22. P_BRAND       CHAR(10)       NOT NULL,
23. P_TYPE        VARCHAR(25)    NOT NULL,
24. P_SIZE        INTEGER        NOT NULL,
25. P_CONTAINER   CHAR(10)       NOT NULL,
26. P_RETAILPRICE DECIMAL(15, 2) NOT NULL,
27. P_COMMENT     VARCHAR(23)    NOT NULL
28. );

30. CREATE TABLE SUPPLIER
31. (
32. S_SUPPKEY   INTEGER        NOT NULL PRIMARY KEY,
33. S_NAME      CHAR(25)       NOT NULL,
34. S_ADDRESS   VARCHAR(40)    NOT NULL,
35. S_NATIONKEY INTEGER        NOT NULL,
36. S_PHONE     CHAR(15)       NOT NULL,
37. S_ACCTBAL   DECIMAL(15, 2) NOT NULL,
38. S_COMMENT   VARCHAR(101)   NOT NULL,
39. FOREIGN KEY (S_NATIONKEY) REFERENCES NATION (N_NATIONKEY)
40. );

42. CREATE TABLE PARTSUPP
43. (
44. PS_PARTKEY    INTEGER        NOT NULL,
45. PS_SUPPKEY    INTEGER        NOT NULL,
46. PS_AVAILQTY   INTEGER        NOT NULL,
47. PS_SUPPLYCOST DECIMAL(15, 2) NOT NULL,
48. PS_COMMENT    VARCHAR(199)   NOT NULL,
49. PRIMARY KEY (PS_PARTKEY, PS_SUPPKEY),
50. FOREIGN KEY (PS_SUPPKEY) REFERENCES SUPPLIER (S_SUPPKEY),
51. FOREIGN KEY (PS_PARTKEY) REFERENCES PART (P_PARTKEY)
52. );

54. CREATE TABLE CUSTOMER
55. (
56. C_CUSTKEY    INTEGER        NOT NULL PRIMARY KEY,
57. C_NAME       VARCHAR(25)    NOT NULL,
58. C_ADDRESS    VARCHAR(40)    NOT NULL,
59. C_NATIONKEY  INTEGER        NOT NULL,
60. C_PHONE      CHAR(15)       NOT NULL,
61. C_ACCTBAL    DECIMAL(15, 2) NOT NULL,
62. C_MKTSEGMENT CHAR(10)       NOT NULL,
63. C_COMMENT    VARCHAR(117)   NOT NULL,
64. FOREIGN KEY (C_NATIONKEY) REFERENCES NATION (N_NATIONKEY)
65. );

67. CREATE TABLE ORDERS
68. (
69. O_ORDERKEY      INTEGER        NOT NULL PRIMARY KEY,
70. O_CUSTKEY       INTEGER        NOT NULL,
71. O_ORDERSTATUS   CHAR(1)        NOT NULL,
72. O_TOTALPRICE    DECIMAL(15, 2) NOT NULL,
73. O_ORDERDATE     DATE           NOT NULL,
74. O_ORDERPRIORITY CHAR(15)       NOT NULL,
75. O_CLERK         CHAR(15)       NOT NULL,
76. O_SHIPPRIORITY  INTEGER        NOT NULL,
77. O_COMMENT       VARCHAR(79)    NOT NULL,
78. FOREIGN KEY (O_CUSTKEY) REFERENCES CUSTOMER (C_CUSTKEY)
79. );

81. CREATE TABLE LINEITEM
82. (
83. L_ORDERKEY      INTEGER        NOT NULL,
84. L_PARTKEY       INTEGER        NOT NULL,
85. L_SUPPKEY       INTEGER        NOT NULL,
86. L_LINENUMBER    INTEGER        NOT NULL,
87. L_QUANTITY      DECIMAL(15, 2) NOT NULL,
88. L_EXTENDEDPRICE DECIMAL(15, 2) NOT NULL,
89. L_DISCOUNT      DECIMAL(15, 2) NOT NULL,
90. L_TAX           DECIMAL(15, 2) NOT NULL,
91. L_RETURNFLAG    CHAR(1)        NOT NULL,
92. L_LINESTATUS    CHAR(1)        NOT NULL,
93. L_SHIPDATE      DATE           NOT NULL,
94. L_COMMITDATE    DATE           NOT NULL,
95. L_RECEIPTDATE   DATE           NOT NULL,
96. L_SHIPINSTRUCT  CHAR(25)       NOT NULL,
97. L_SHIPMODE      CHAR(10)       NOT NULL,
98. L_COMMENT       VARCHAR(44)    NOT NULL,
99. PRIMARY KEY (L_ORDERKEY, L_LINENUMBER),
100. FOREIGN KEY (L_ORDERKEY) REFERENCES ORDERS (O_ORDERKEY),
101. FOREIGN KEY (L_PARTKEY, L_SUPPKEY) REFERENCES PARTSUPP (PS_PARTKEY, PS_SUPPKEY)
102. );

104. CREATE INDEX ON ORDERS (O_ORDERDATE);
105. CREATE INDEX ON LINEITEM (L_SHIPDATE);
106. CREATE INDEX ON LINEITEM (L_PARTKEY, L_SUPPKEY);
107. -- ALTER TABLE lineitem ADD FOREIGN KEY(l_partkey, l_suppkey) REFERENCES partsupp(ps_partkey, ps_suppkey);

1. sed 's/|$//' region.tbl    | psql tpch -c "COPY region    FROM STDIN DELIMITER '|';"
2. sed 's/|$//' nation.tbl    | psql tpch -c "COPY nation    FROM STDIN DELIMITER '|';"
3. sed 's/|$//' supplier.tbl  | psql tpch -c "COPY supplier  FROM STDIN DELIMITER '|';"
4. sed 's/|$//' part.tbl      | psql tpch -c "COPY part      FROM STDIN DELIMITER '|';"
5. sed 's/|$//' customer.tbl  | psql tpch -c "COPY customer  FROM STDIN DELIMITER '|';"
6. sed 's/|$//' partsupp.tbl  | psql tpch -c "COPY partsupp  FROM STDIN DELIMITER '|';"
7. sed 's/|$//' orders.tbl    | psql tpch -c "COPY orders    FROM STDIN DELIMITER '|';"
8. sed 's/|$//' lineitem.tbl  | psql tpch -c "COPY lineitem  FROM STDIN DELIMITER '|';"

Prepare Queries

1. git clone https://github.com/2ndQuadrant/pg-tpch.git
2. cd pg-tpch/queries
3. psql tpc

5. \i q01.analyze.sql
6. \i q02.analyze.sql
7. \i q03.analyze.sql
8. \i q04.analyze.sql
9. \i q05.analyze.sql
10. \i q06.analyze.sql
11. \i q07.analyze.sql
12. \i q08.analyze.sql
13. \i q09.analyze.sql
14. \i q10.analyze.sql
15. \i q11.analyze.sql
16. \i q12.analyze.sql
17. \i q13.analyze.sql
18. \i q14.analyze.sql
19. \i q15.analyze.sql
20. \i q16.analyze.sql
21. \i q17.analyze.sql
22. \i q18.analyze.sql
23. \i q19.analyze.sql
24. \i q20.analyze.sql
25. \i q21.analyze.sql
26. \i q22.analyze.sql

1. \i q01.explain.sql
2. \i q02.explain.sql
3. \i q03.explain.sql
4. \i q04.explain.sql
5. \i q05.explain.sql
6. \i q06.explain.sql
7. \i q07.explain.sql
8. \i q08.explain.sql
9. \i q09.explain.sql
10. \i q10.explain.sql
11. \i q11.explain.sql
12. \i q12.explain.sql
13. \i q13.explain.sql
14. \i q14.explain.sql
15. \i q15.explain.sql
16. \i q16.explain.sql
17. \i q17.explain.sql
18. \i q18.explain.sql
19. \i q19.explain.sql
20. \i q20.explain.sql
21. \i q21.explain.sql
22. \i q22.explain.sql

1. -- enhance with index
2. ALTER TABLE region ADD PRIMARY KEY (r_regionkey);
3. ALTER TABLE nation ADD PRIMARY KEY (n_nationkey);
4. ALTER TABLE customer ADD PRIMARY KEY (c_custkey);
5. ALTER TABLE supplier ADD PRIMARY KEY (s_suppkey);
6. ALTER TABLE part ADD PRIMARY KEY (p_partkey);
7. ALTER TABLE orders ADD PRIMARY KEY (o_orderkey);
8. ALTER TABLE partsupp ADD PRIMARY KEY (ps_partkey, ps_suppkey);
9. ALTER TABLE lineitem ADD PRIMARY KEY (l_orderkey, l_linenumber);

11. ALTER TABLE nation ADD FOREIGN KEY(n_regionkey) REFERENCES region(r_regionkey);
12. ALTER TABLE customer ADD FOREIGN KEY(c_nationkey) REFERENCES nation(n_nationkey);
13. ALTER TABLE supplier ADD FOREIGN KEY(s_nationkey) REFERENCES nation(n_nationkey);
14. ALTER TABLE orders ADD FOREIGN KEY(o_custkey) REFERENCES customer(c_custkey);
15. ALTER TABLE customer ADD FOREIGN KEY(c_suppkey) REFERENCES supplier(s_suppkey);
16. ALTER TABLE partsupp ADD FOREIGN KEY(ps_partkey) REFERENCES part(p_partkey);
17. ALTER TABLE lineitem ADD FOREIGN KEY(l_orderkey) REFERENCES orders(o_orderkey);
18. 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

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