PostgreSQL数据库OLAP测试TPCH安装部署详解

开源软件联盟PostgreSQL分会 2020-04-07

3031

转载自公众号DB印象

前言

TPCH是OLAP系统的测试基准，主要目的是评价特定查询的在统计分析、数据挖掘、分析处理等决策支持方面的能力。

TPCH默认支持Oracle、SQL SERVER、DB2等数据库，我们可以通过修改相关的代码，将工具包顺利应用到PostgreSQL的单机、分布式环境中。

TPCH创建的数仓中包含 8 张表，模拟商品零售业决策支持系统的 22 个查询，SQL涵盖了统计分组、排序、聚集操作、子查询、多表关联等复杂操作，我们可以在测试中观测到各个查询的响应时间，即从提交查询到结果返回所需时间。

更多介绍可参考TPC官方网站：

http://www.tpc.org/tpc_documents_current_versions/current_specifications.asp

文章目录

一、测试环境介绍

二、TPCH安装部署

三、TPCH测试数据的准备及执行

四、部署相关报错处理

一、测试部署环境介绍

1.操作系统版本

[root@Aken-DB ~]# cat etc/redhat-release 
CentOS Linux release 7.2 (Final)

2.PostgreSQL和tpch版本

tpch 2.18
PostgreSQL 11.5

二、TPCH安装部署

1.下载tpch工具包并安装

1）下载地址

http://www.tpc.org/tpc_documents_current_versions/current_specifications.asp

2）上传工具包并解压

[tbase@VM_0_37_centos tpch]$ pwd
/data/tbase/tpch
[tbase@VM_0_37_centos tpch]$ unzip e30e0296-f77a-48da-a257-a87dd8f8baf2-tpc-h-tool.zip
[tbase@VM_0_37_centos tpch]$ cd 2.18.0_rc2/dbgen

3）安装依赖环境gcc

安装gcc编译工具，已经安装gcc的这一步跳过

[root@VM_0_37_centos dbgen]$ yum install gcc -y

4) 配置tpch相关配置文件使之支持Postgresql

[tbase@Aken-DB ~/tpch/2.18.0_rc2/dbgen]$ vi makefile.suite  修改以下参数

CC      =gcc
# Current values for DATABASE are: INFORMIX, DB2, TDAT (Teradata)
#                                  SQLSERVER, SYBASE, ORACLE, VECTORWISE
# Current values for MACHINE are:  ATT, DOS, HP, IBM, ICL, MVS,
#                                  SGI, SUN, U2200, VMS, LINUX, WIN32
# Current values for WORKLOAD are:  TPCH
DATABASE= POSTGRESQL   <<<<<填写数据库类型
MACHINE = LINUX
WORKLOAD = TPCH

vi data/tbase/tpch/2.18.0_rc2/dbgen/tpcd.h，添加以下内容

[tbase@Tencent-SNG ~/tpch/2.18.0_rc2/dbgen]$ vi tpcd.h 添加以下内容
#ifdef POSTGRESQL <<<<<和makefile中的保持一致
#define GEN_QUERY_PLAN  "EXPLAIN PLAN"
#define START_TRAN      "SET TRANSACTION"
#define END_TRAN        "COMMIT;"
#define SET_OUTPUT      ""
#define SET_ROWCOUNT    "LIMIT %d\n"
#define SET_DBASE      ""
#endif

5）执行编译

[tbase@Aken-DB ~/tpch/2.18.0_rc2/dbgen]$ make -f makefile.suite
gcc  -g -DDBNAME=\"dss\" -DLINUX  -DPOSTGRESQL  -DTPCH  -DRNG_TEST -D_FILE_OFFSET_BITS=64    -c -o build.o build.c
gcc  -g -DDBNAME=\"dss\" -DLINUX  -DPOSTGRESQL  -DTPCH  -DRNG_TEST -D_FILE_OFFSET_BITS=64    -c -o driver.o driver.c
gcc  -g -DDBNAME=\"dss\" -DLINUX  -DPOSTGRESQL  -DTPCH  -DRNG_TEST -D_FILE_OFFSET_BITS=64    -c -o bm_utils.o bm_utils.c
gcc  -g -DDBNAME=\"dss\" -DLINUX  -DPOSTGRESQL  -DTPCH  -DRNG_TEST -D_FILE_OFFSET_BITS=64    -c -o rnd.o rnd.c
gcc  -g -DDBNAME=\"dss\" -DLINUX  -DPOSTGRESQL  -DTPCH  -DRNG_TEST -D_FILE_OFFSET_BITS=64    -c -o print.o print.c
gcc  -g -DDBNAME=\"dss\" -DLINUX  -DPOSTGRESQL  -DTPCH  -DRNG_TEST -D_FILE_OFFSET_BITS=64    -c -o load_stub.o load_stub.c
gcc  -g -DDBNAME=\"dss\" -DLINUX  -DPOSTGRESQL  -DTPCH  -DRNG_TEST -D_FILE_OFFSET_BITS=64    -c -o bcd2.o bcd2.c
gcc  -g -DDBNAME=\"dss\" -DLINUX  -DPOSTGRESQL  -DTPCH  -DRNG_TEST -D_FILE_OFFSET_BITS=64    -c -o speed_seed.o speed_seed.c
gcc  -g -DDBNAME=\"dss\" -DLINUX  -DPOSTGRESQL  -DTPCH  -DRNG_TEST -D_FILE_OFFSET_BITS=64    -c -o text.o text.c
gcc  -g -DDBNAME=\"dss\" -DLINUX  -DPOSTGRESQL  -DTPCH  -DRNG_TEST -D_FILE_OFFSET_BITS=64    -c -o permute.o permute.c
gcc  -g -DDBNAME=\"dss\" -DLINUX  -DPOSTGRESQL  -DTPCH  -DRNG_TEST -D_FILE_OFFSET_BITS=64    -c -o rng64.o rng64.c
gcc  -g -DDBNAME=\"dss\" -DLINUX  -DPOSTGRESQL  -DTPCH  -DRNG_TEST -D_FILE_OFFSET_BITS=64  -O -o dbgen build.o driver.o bm_utils.o rnd.o print.o load_stub.o bcd2.o speed_seed.o text.o permute.o rng64.o -lm
gcc  -g -DDBNAME=\"dss\" -DLINUX  -DPOSTGRESQL  -DTPCH  -DRNG_TEST -D_FILE_OFFSET_BITS=64    -c -o qgen.o qgen.c
gcc  -g -DDBNAME=\"dss\" -DLINUX  -DPOSTGRESQL  -DTPCH  -DRNG_TEST -D_FILE_OFFSET_BITS=64    -c -o varsub.o varsub.c
gcc  -g -DDBNAME=\"dss\" -DLINUX  -DPOSTGRESQL  -DTPCH  -DRNG_TEST -D_FILE_OFFSET_BITS=64  -O -o qgen build.o bm_utils.o qgen.o rnd.o varsub.otext.o bcd2.o permute.o speed_seed.o rng64.o -lm
[tbase@Aken-DB ~/tpch/2.18.0_rc2/dbgen]$

至此，postgresql测试使用的tpch已经安装部署完毕。

三、测试数据准备及执行

1.生成测试数据包

[tbase@Aken-DB~/tpch/2.18.0_rc2/dbgen]$ ./dbgen -s 3
TPC-H Population Generator (Version 2.18.0)
Copyright Transaction Processing Performance Council 1994 - 2010

这里的-s 参数指定生成测试数据的仓库数，建议设置100以上，这里为了方便，选取较小的值，真实业务场景测试可设置1000以上。

生成的数据文件存放在/data/tbase/tpch/2.18.0_rc2/dbgen目录下，以tbl结尾，一共8个文件，对应的是8个表

[tbase@Aken-DB ~/tpch/2.18.0_rc2/dbgen]$ ls -rlt *.tbl
-rw-r--r-- 1 tbase tbase    4248964 Mar 24 12:03 supplier.tbl
-rw-r--r-- 1 tbase tbase        389 Mar 24 12:03 region.tbl
-rw-r--r-- 1 tbase tbase  359420241 Mar 24 12:03 partsupp.tbl
-rw-r--r-- 1 tbase tbase   72627115 Mar 24 12:03 part.tbl
-rw-r--r-- 1 tbase tbase  520574264 Mar 24 12:03 orders.tbl
-rw-r--r-- 1 tbase tbase       2224 Mar 24 12:03 nation.tbl
-rw-r--r-- 1 tbase tbase 2309057846 Mar 24 12:03 lineitem.tbl
-rw-r--r-- 1 tbase tbase   73234309 Mar 24 12:03 customer.tbl
[tbase@Aken-DB~/tpch/2.18.0_rc2/dbgen]$

2、数据入库

2.1 创建测试库database=tpch

[tbase@VM_0_37_centos dbgen]$ psql -h 127.0.0.1 -p 11000 -d postgres -U tbase
psql (PostgreSQL 11.5 )
Type "help" for help.
postgres=# create database tpch;
CREATE DATABASE
postgres=# \c tpch 
You are now connected to database "tpch" as user "tbase".
tpch=#

2.2 创建数据表

CREATE TABLE NATION  ( N_NATIONKEY  INTEGER NOT NULL,
                            N_NAME       CHAR(25) NOT NULL,
                            N_REGIONKEY  INTEGER NOT NULL,
                            N_COMMENT    VARCHAR(152),
                            N_NULL    VARCHAR(10)
                            );




CREATE TABLE REGION  ( R_REGIONKEY  INTEGER NOT NULL,
                            R_NAME       CHAR(25) NOT NULL,
                            R_COMMENT    VARCHAR(152),
                            R_NULL VARCHAR(10)  
                            );




CREATE TABLE PART  ( P_PARTKEY     INTEGER NOT NULL,
                          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,
                          P_NULL VARCHAR(10) 
                          );


CREATE TABLE SUPPLIER ( S_SUPPKEY     INTEGER NOT NULL,
                             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,
                             S_NULL VARCHAR(10)         
                             );




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,
                             PS_NULL  VARCHAR(10)   
                             );




CREATE TABLE CUSTOMER ( C_CUSTKEY     INTEGER NOT NULL,
                             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,
                             C_NULL  VARCHAR(10)        
                             );




CREATE TABLE ORDERS  ( O_ORDERKEY       INTEGER NOT NULL,
                           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,
                           O_NULL   VARCHAR(10)       
                           );




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,
                             L_NULL  VARCHAR(10)  
                             );

注意：

这里每个数据表都增中了最后一条 xxx varchar(10)，目的是为了生成的数据不需要处理就能入库。

如果是Postgresql分布式环境，可能还需要显式指定分布键或主键：

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),
                            N_NULL    VARCHAR(10)
                            ) distribute by shard(N_NATIONKEY  );

2.3 数据入库

tpch=# \timing on
Timing is on.
tpch=#  \copy customer from '/data/tbase/tpch/2.18.0_rc2/dbgen/customer.tbl'with CSV DELIMITER '|';
COPY 450000
Time: 2191.737 ms (00:02.192)
tpch=#  \copy supplier from '/data/tbase/tpch/2.18.0_rc2/dbgen/supplier.tbl'with CSV DELIMITER '|';
COPY 30000
Time: 169.858 ms
tpch=# \copy nation from '/data/tbase/tpch/2.18.0_rc2/dbgen/nation.tbl'with CSV DELIMITER '|'; 
COPY 25
Time: 9.681 ms
tpch=# \copy region from '/data/tbase/tpch/2.18.0_rc2/dbgen/region.tbl'with CSV DELIMITER '|';
COPY 5
Time: 9.535 ms
tpch=# \copy part from '/data/tbase/tpch/2.18.0_rc2/dbgen/part.tbl'with CSV DELIMITER '|';
COPY 600000
Time: 2472.496 ms (00:02.472)
tpch=# \copy partsupp from '/data/tbase/tpch/2.18.0_rc2/dbgen/partsupp.tbl'with CSV DELIMITER '|';
COPY 2400000
Time: 9015.238 ms (00:09.015)
tpch=# \copy orders from '/data/tbase/tpch/2.18.0_rc2/dbgen/orders.tbl'with CSV DELIMITER '|';
COPY 4500000
Time: 19050.252 ms (00:19.050)
tpch=# \copy lineitem from '/data/tbase/tpch/2.18.0_rc2/dbgen/lineitem.tbl'with CSV DELIMITER '|';
COPY 17996609
Time: 114296.076 ms (01:54.296)
tpch=#

2.4 创建主键，外键，索引。

注意：如果是分布式环境，建议将主键在建表ddl语句中显式指定。

tpch-# ALTER TABLE REGION ADD PRIMARY KEY (R_REGIONKEY);      ^
Time: 0.361 ms
tpch=# ALTER TABLE NATION ADD PRIMARY KEY (N_NATIONKEY);
ALTER TABLE
Time: 39.303 ms
tpch=#  ALTER TABLE REGION ADD PRIMARY KEY (R_REGIONKEY);
ALTER TABLE
Time: 20.124 ms
tpch=# ALTER TABLE PART ADD PRIMARY KEY (P_PARTKEY);
ALTER TABLE
Time: 578.483 ms
tpch=# ALTER TABLE SUPPLIER ADD PRIMARY KEY (S_SUPPKEY);
ALTER TABLE
Time: 57.278 ms
tpch=# ALTER TABLE PARTSUPP ADD PRIMARY KEY (PS_PARTKEY,PS_SUPPKEY);
ALTER TABLE
Time: 2897.372 ms (00:02.897)
tpch=# ALTER TABLE CUSTOMER ADD PRIMARY KEY (C_CUSTKEY);
ALTER TABLE
Time: 530.964 ms
tpch=# ALTER TABLE LINEITEM ADD PRIMARY KEY (L_ORDERKEY,L_LINENUMBER);
ALTER TABLE
Time: 19937.997 ms (00:19.938)
tpch=# ALTER TABLE ORDERS ADD PRIMARY KEY (O_ORDERKEY);
ALTER TABLE
Time: 2942.569 ms (00:02.943)
tpch=# ALTER TABLE PARTSUPP ADD FOREIGN KEY (PS_PARTKEY) REFERENCES PART(P_PARTKEY);
ALTER TABLE
Time: 1675.523 ms (00:01.676)
tpch=# ALTER TABLE LINEITEM ADD FOREIGN KEY (L_ORDERKEY) REFERENCES ORDERS(O_ORDERKEY);
ALTER TABLE
Time: 12478.864 ms (00:12.479)
tpch=# CREATE INDEX l_shipdate_idx ON LINEITEM(L_SHIPDATE);
CREATE INDEX
Time: 16593.676 ms (00:16.594)
tpch=#

2.5 更新统计信息

tpch=#  vacuum ANALYZE ;
VACUUM
Time: 9269.969 ms (00:09.270)

2.6 表名、记录数

2.7 执行22条查询语句进行测试

内容比较多，有点长哈~~~

--1
select
    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 '103' day
group by
    l_returnflag,
    l_linestatus
order by
    l_returnflag,
    l_linestatus
LIMIT 1;




--2 
select
    s_acctbal,
    s_name,
    n_name,
    p_partkey,
    p_mfgr,
    s_address,
    s_phone,
    s_comment
from
    part,
    supplier,
    partsupp,
    nation,
    region,
    (
        select
            ps_partkey,
            min(ps_supplycost) as min
        from
            partsupp,
            supplier,
            nation,
            region
        where
            s_suppkey = ps_suppkey
            and s_nationkey = n_nationkey
            and n_regionkey = r_regionkey
            and r_name = 'EUROPE'
        group by
            ps_partkey
    ) temp
where
    p_partkey = partsupp.ps_partkey
    and s_suppkey = ps_suppkey
    and p_size = 47
    and p_type like '%BRASS'
    and s_nationkey = n_nationkey
    and n_regionkey = r_regionkey
    and r_name = 'EUROPE'
    and ps_supplycost = temp.min
    and p_partkey = temp.ps_partkey
order by
    s_acctbal desc,
    n_name,
    s_name,
    p_partkey
LIMIT 100;




--3
select
    l_orderkey,
    sum(l_extendedprice * (1 - l_discount)) as revenue,
    o_orderdate,
    o_shippriority
from
    customer,
    orders,
    lineitem
where
    c_mktsegment = 'BUILDING'
    and c_custkey = o_custkey
    and l_orderkey = o_orderkey
    and o_orderdate < date '1995-03-20'
    and l_shipdate > date '1995-03-20'
group by
    l_orderkey,
    o_orderdate,
    o_shippriority
order by
    revenue desc,
    o_orderdate
LIMIT 10;




--4
select
    o_orderpriority,
    count(*) as order_count
from
    orders
where
    o_orderdate >= date '1995-12-01'
    and o_orderdate < date '1995-12-01' + interval '3' month
    and exists 
    (
        select
            *
        from
            lineitem
        where
            l_orderkey = o_orderkey
            and l_commitdate < l_receiptdate 
        limit 1
    )
group by
    o_orderpriority
order by
    o_orderpriority
LIMIT 1;




--5
select
    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 = 'AMERICA'
    and o_orderdate >= date '1997-01-01'
    and o_orderdate < date '1997-01-01' + interval '1' year
group by
    n_name
order by
    revenue desc
LIMIT 1;




--6
select
    sum(l_extendedprice * l_discount) as revenue
from
    lineitem
where
    l_shipdate >= date '1997-01-01'
    and l_shipdate < date '1997-01-01' + interval '1' year
    and l_discount between 0.07 - 0.01 and 0.07 + 0.01
    and l_quantity < 24
LIMIT 1;




--7
select
    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 = 'CHINA' and n2.n_name = 'INDONESIA')
            or (n1.n_name = 'INDONESIA' and n2.n_name = 'CHINA')
        )
        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
LIMIT 1;








--8
select
    o_year,
    sum(case
    when nation = 'INDONESIA' 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
        part,
        supplier,
        lineitem,
        orders,
        customer,
        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 = 'ASIA'
        and s_nationkey = n2.n_nationkey
        and o_orderdate between date '1995-01-01' and date '1996-12-31'
        and p_type = 'PROMO POLISHED TIN'
) as all_nations
group by
    o_year
order by
    o_year
LIMIT 1;








--9
select
    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
        part,
        supplier,
        lineitem,
        partsupp,
        orders,
        nation
    where
        s_suppkey = l_suppkey
        and ps_suppkey = l_suppkey
        and ps_partkey = l_partkey
        and p_partkey = l_partkey
        and o_orderkey = l_orderkey
        and s_nationkey = n_nationkey
        and p_name like '%thistle%'
) as profit
group by
    nation,
    o_year
order by
    nation,
    o_year desc
LIMIT 1;








--10
select
    c_custkey,
    c_name,
    sum(l_extendedprice * (1 - l_discount)) as revenue,
    c_acctbal,
    n_name,
    c_address,
    c_phone,
    c_comment
from
    customer,
    orders,
    lineitem,
    nation
where
    c_custkey = o_custkey
    and l_orderkey = o_orderkey
    and o_orderdate >= date '1994-06-01'
    and o_orderdate < date '1994-06-01' + interval '3' month
    and l_returnflag = 'R'
    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;








--11
select
    ps_partkey,
    sum(ps_supplycost * ps_availqty) as value
from
    partsupp,
    supplier,
    nation
where
    ps_suppkey = s_suppkey
    and s_nationkey = n_nationkey
    and n_name = 'ALGERIA'
group by
    ps_partkey having
    sum(ps_supplycost * ps_availqty) > 
    (
        select
            sum(ps_supplycost * ps_availqty) * 0.0001000000
        from
            partsupp,
            supplier,
            nation
        where
            ps_suppkey = s_suppkey
            and s_nationkey = n_nationkey
            and n_name = 'ALGERIA'
    )
order by
    value desc
LIMIT 1;




--12
select
    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 ('RAIL', '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
LIMIT 1;
--13
select
    c_count,
    count(*) as custdist
from
(
    select
        c_custkey,
        count(o_orderkey)
    from
        customer left outer join orders on
        c_custkey = o_custkey
        and o_comment not like '%express%packages%'
    group by
        c_custkey
) as c_orders (c_custkey, c_count)
group by
    c_count
order by
    custdist desc,
    c_count desc
LIMIT 1;
--14
select
    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
    lineitem,
    part
where
    l_partkey = p_partkey
    and l_shipdate >= date '1994-02-01'
    and l_shipdate < date '1994-02-01' + interval '1' month
LIMIT 1;








--15
create or replace view revenue0 (supplier_no, total_revenue) as
select
    l_suppkey,
    sum(l_extendedprice * (1 - l_discount))
from
    lineitem
where
    l_shipdate >= date '1994-02-01'
    and l_shipdate < date '1994-02-01' + interval '3' month
group by
    l_suppkey;




select
    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
LIMIT 1;




--16
select
    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#55'
     and p_type not like 'MEDIUM PLATED%'
     and p_size in (35, 17, 43, 49, 19, 41, 30, 47)
     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
LIMIT 1;








--17
select
    sum(l_extendedprice)  7.0 as avg_yearly
from
   lineitem,
   part,
   (SELECT l_partkey AS agg_partkey, 0.2 * avg(l_quantity) AS avg_quantity FROM lineitem GROUP BY l_partkey) part_agg
where
    p_partkey = l_partkey
    and agg_partkey = l_partkey
    and p_brand = 'Brand#53'
    and p_container = 'JUMBO JAR'
    and l_quantity < avg_quantity    
LIMIT 1;




--18
select
    c_name,
    c_custkey,
    o_orderkey,
    o_orderdate,
    o_totalprice,
    sum(l_quantity)
from
    customer,
    orders,
    lineitem
where
    o_orderkey in 
    (
        select
            l_orderkey
        from
            lineitem
        group by
            l_orderkey having
            sum(l_quantity) > 313
    )
    and c_custkey = o_custkey
    and o_orderkey = l_orderkey
group by
    c_name,
    c_custkey,
    o_orderkey,
    o_orderdate,
    o_totalprice
order by
    o_totalprice desc,
    o_orderdate
LIMIT 100;








--19
select
    sum(l_extendedprice* (1 - l_discount)) as revenue
from
    lineitem,
    part
where
    (
         p_partkey = l_partkey
         and p_brand = 'Brand#42'
         and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
         and l_quantity >= 6 and l_quantity <= 6 + 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#55'
         and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
         and l_quantity >= 20 and l_quantity <= 20 + 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#24'
         and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
         and l_quantity >= 23 and l_quantity <= 23 + 10
         and p_size between 1 and 15
         and l_shipmode in ('AIR', 'AIR REG')
         and l_shipinstruct = 'DELIVER IN PERSON'
    )
LIMIT 1;




--20
select s_name,s_address
from supplier,nation
where
    s_suppkey in 
    (select ps_suppkey
        from partsupp,
            (
                select
                    l_partkey agg_partkey,
                    l_suppkey agg_suppkey,
                    0.5 * sum(l_quantity) AS agg_quantity
                from
                    lineitem
                where
                    l_shipdate >= date '1997-01-01'
                    and l_shipdate < date '1997-01-01' + interval '1' year
                group by
                    l_partkey,
                    l_suppkey
            ) agg_lineitem
        where
            agg_partkey = ps_partkey
            and agg_suppkey = ps_suppkey
            and ps_partkey in 
            (
                select
                    p_partkey
                from
                    part
                where
                    p_name like 'frosted%'
            )
            and ps_availqty > agg_quantity
    )
    and s_nationkey = n_nationkey
    and n_name = 'UNITED STATES'
order by
    s_name
LIMIT 1;




--21
select
    s_name,
    count(*) as numwait
from
    supplier,
    lineitem l1,
    orders,
    nation
where
    s_suppkey = l1.l_suppkey
    and o_orderkey = l1.l_orderkey
    and o_orderstatus = 'F'
    and l1.l_receiptdate > l1.l_commitdate
    and exists 
    (
        select
            *
        from
            lineitem l2
        where
            l2.l_orderkey = l1.l_orderkey
            and l2.l_suppkey <> l1.l_suppkey
    )
    and not exists 
    (
        select
            *
        from
            lineitem l3
        where
            l3.l_orderkey = l1.l_orderkey
            and l3.l_suppkey <> l1.l_suppkey
            and l3.l_receiptdate > l3.l_commitdate
    )
    and s_nationkey = n_nationkey
    and n_name = 'VIETNAM'
group by
    s_name
order by
    numwait desc,
    s_name
LIMIT 100;
--22
select
    cntrycode,
    count(*) as numcust,
    sum(c_acctbal) as totacctbal
from
(
    select
        substring(c_phone from 1 for 2) as cntrycode,
        c_acctbal
    from
        customer
    where
        substring(c_phone from 1 for 2) in
        ('34', '18', '30', '24', '33', '22', '29')
        and c_acctbal > 
        (
            select
                avg(c_acctbal)
            from
                customer
            where
                c_acctbal > 0.00
                and substring(c_phone from 1 for 2) in
                ('34', '18', '30', '24', '33', '22', '29')
        )
        and not exists 
        (
            select
                *
            from
                orders
            where
                o_custkey = c_custkey
        )
) as custsale
group by
    cntrycode
order by
    cntrycode
LIMIT 1;

四、部署相关报错处理

编译执行tpch makefile.suite报错

[tbase@Tencent-SNG ~/tpch/tpch-dbgen-master]$ make -f makefile.suite
gcc  -g -DDBNAME=\"dss\" -DLINUX -DPOSTGRESQL  -D -DRNG_TEST -D_FILE_OFFSET_BITS=64    -c -o build.o build.c
<command-line>:0:1: error: macro names must be identifiers
In file included from build.c:45:0:
dss.h:76:2: error: #error Benchmark version must be defined in config.h
 #error Benchmark version must be defined in config.h
  ^
make: *** [build.o] Error 1
[tbase@Tencent-SNG ~/tpch/tpch-dbgen-master]$

解决方法：添加数据库类型及相关操作

[tbase@Tencent-SNG ~/tpch/2.18.0_rc2/dbgen]$ vi tpcd.h 添加以下内容
#ifdef POSTGRESQL <<<<<和makefile中的保持一致
#define GEN_QUERY_PLAN  "EXPLAIN PLAN"
#define START_TRAN      "SET TRANSACTION"
#define END_TRAN        "COMMIT;"
#define SET_OUTPUT      ""
#define SET_ROWCOUNT    "LIMIT %d\n"
#define SET_DBASE      ""
#endif

重新编译即可

[tbase@Tencent-SNG ~/tpch/2.18.0_rc2/dbgen]$ make -f makefile.suite


I Love PG

关于我们
中国开源软件推进联盟PostgreSQL分会（简称：PG分会）于2017年成立，由国内多家PG生态企业所共同发起，业务上接受工信部产业发展研究院指导。PG分会致力于构建PG产业生态，推动PG产学研用发展，是国内一家PG行业协会组织。


欢迎投稿
做你的舞台，show出自己的才华 。
投稿邮箱：partner@postgresqlchina.com
                               
                                 ——愿能安放你不羁的灵魂

技术文章精彩回顾




PostgreSQL学习的九层宝塔
PostgreSQL职业发展与学习攻略
搞懂PostgreSQL数据库透明数据加密之加密算法介绍
一文读懂PostgreSQL-12分区表
PostgreSQL源码学习之：RegularLock
Postgresql源码学习之词法和语法分析
PostgreSQL buffer管理
最佳实践—PG数据库系统表空间重建
PostgreSQL V12中的流复制配置
2019，年度数据库舍 PostgreSQL 其谁?
PostgreSQL使用分片（sharding）实现水平可扩展性
一文搞懂PostgreSQL物化视图
PostgreSQL原理解析之：PostgreSQL备机是否做checkpoint
PostgreSQL复制技术概述

PG活动精彩回顾




见证精彩|PostgresConf.CN2019大会盛大开幕
PostgresConf.CN2019大会DAY2|三大分论坛，精彩不断
PostgresConf.CN2019培训日|爆满！Training Day现场速递！
「PCC-Training Day」培训日Day2圆满结束，PCC2019完美收官
创建PG全球生态！PostgresConf.CN2019大会盛大召开
首站起航！2019“让PG‘象’前行”上海站成功举行
走进蓉城丨2019“让PG‘象’前行”成都站成功举行
中国PG象牙塔计划发布，首批合作高校授牌仪式在天津举行
PostgreSQL实训基地落户沈阳航空航天大学和渤海大学，高校数据库课改正当时
群英论道聚北京，共话PostgreSQL
相聚巴厘岛| PG Conf.Asia 2019  DAY0、DAY1简报
相知巴厘岛| PG Conf.Asia 2019 DAY2简报
相惜巴厘岛| PG Conf.Asia 2019 DAY3简报
独家|硅谷Postgres大会简报
全球规模最大的PostgreSQL会议等你来！

PG培训认证精彩回顾




关于中国PostgreSQL培训认证，你想知道的都在这里！
首批中国PGCA培训圆满结束，首批认证考试将于10月18日和20日举行！
中国首批PGCA认证考试圆满结束，203位考生成功获得认证！
中国第二批PGCA认证考试圆满结束，115位考生喜获认证！
请查收：中国首批PGCA证书！
重要通知：三方共建，中国PostgreSQL认证权威升级！
一场考试迎新年 | 12月28日，首次PGCE中级认证考试开考！
近500人参与！首次PGCE中级、第三批次PGCA初级认证考试落幕！

postgresql

最后修改时间：2020-04-08 09:22:51

文章转载自开源软件联盟PostgreSQL分会，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

PostgreSQL数据库OLAP测试TPCH安装部署详解

评论