7.0版TPC-H性能测试

TPC-H简介

TPC-H是由TPC（Transaction Processing Performance Council）组织发布的决策支持基准测试，由一套面向业务的临时查询和并发数据修改组成。该基准测试用于评估决策支持系统在大数据量下执行复杂查询的能力。更多信息，请参见TPC Benchmark H（TPC-H）。

测试说明

• 本文以100 GB数据规模为例进行测试，规格选型为8个segment节点（每节点2C16G）。

• 如需测试1 TB数据规模，推荐使用48个segment节点（每节点2C16G），通过更多的计算节点发挥MPP架构优势。

测试结果

基于TPC-H 100 GB数据集，运行22条标准测试SQL，在以下两种场景下进行测试：

• 开启自研计算引擎Laser。

• 采用Greenplum原生计算引擎。

Laser计算引擎对比原生计算引擎带来了显著的性能提升，总执行时间约提升12倍。

详细的测试数据如下表所示。

测试步骤

ALTER DATABASE your_db SET laser.enable_vectorized_engine TO on;
ALTER DATABASE your_db SET statement_mem = '16GB';

附录：TPC-H测试SQL

• Q1

  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 '93 day'
  group by
      l_returnflag,
      l_linestatus
  order by
      l_returnflag,
      l_linestatus;

• Q2

  select
      s_acctbal,
      s_name,
      n_name,
      p_partkey,
      p_mfgr,
      s_address,
      s_phone,
      s_comment
  from
      part,
      supplier,
      partsupp,
      nation,
      region
  where
      p_partkey = ps_partkey
      and s_suppkey = ps_suppkey
      and p_size = 23
      and p_type like '%STEEL'
      and s_nationkey = n_nationkey
      and n_regionkey = r_regionkey
      and r_name = 'EUROPE'
      and ps_supplycost = (
          select
              min(ps_supplycost)
          from
              partsupp,
              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'
      )
  order by
      s_acctbal desc,
      n_name,
      s_name,
      p_partkey
  limit 100;

• Q3

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

• Q4

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

• Q5

  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 = 'MIDDLE EAST'
      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
      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 0.06 - 0.01 and 0.06 + 0.01
      and l_quantity < 24;

• Q7

  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 = 'JORDAN' and n2.n_name = 'INDONESIA')
                  or (n1.n_name = 'INDONESIA' and n2.n_name = 'JORDAN')
              )
              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
      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 = 'STANDARD BRUSHED BRASS'
      ) as all_nations
  group by
      o_year
  order by
      o_year;

• Q9

  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 '%chartreuse%'
      ) as profit
  group by
      nation,
      o_year
  order by
      nation,
      o_year desc;

• Q10

  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-08-01'
      and o_orderdate < date '1994-08-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;

• Q11

  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 = 'INDONESIA'
  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 = 'INDONESIA'
          )
  order by
      value desc;

• Q12

  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 ('REG AIR', 'TRUCK')
      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
      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 '%pending%requests%'
          group by
              c_custkey
      ) as c_orders (c_custkey, c_count)
  group by
      c_count
  order by
      custdist desc,
      c_count desc;

• Q14

  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-11-01'
      and l_shipdate < date '1994-11-01' + interval '1' month;

• Q15

  create view revenue0 (supplier_no, total_revenue) as
      select
          l_suppkey,
          sum(l_extendedprice * (1 - l_discount))
      from
          lineitem
      where
          l_shipdate >= date '1997-10-01'
          and l_shipdate < date '1997-10-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;
  drop view revenue0;

• Q16

  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#44'
      and p_type not like 'SMALL BURNISHED%'
      and p_size in (36, 27, 34, 45, 11, 6, 25, 16)
      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
      sum(l_extendedprice) / 7.0 as avg_yearly
  from
      lineitem,
      part
  where
      p_partkey = l_partkey
      and p_brand = 'Brand#42'
      and p_container = 'JUMBO PACK'
      and l_quantity < (
          select
              0.2 * avg(l_quantity)
          from
              lineitem
          where
              l_partkey = p_partkey
      );

• Q18

  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) > 312
      )
      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;

• Q19

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

• Q20

  select
      s_name,
      s_address
  from
      supplier,
      nation
  where
      s_suppkey in (
          select
              ps_suppkey
          from
              partsupp
          where
              ps_partkey in (
                  select
                      p_partkey
                  from
                      part
                  where
                      p_name like 'magenta%'
              )
              and ps_availqty > (
                  select
                      0.5 * sum(l_quantity)
                  from
                      lineitem
                  where
                      l_partkey = ps_partkey
                      and l_suppkey = ps_suppkey
                      and l_shipdate >= date '1996-01-01'
                      and l_shipdate < date '1996-01-01' + interval '1' year
              )
      )
      and s_nationkey = n_nationkey
      and n_name = 'RUSSIA'
  order by
      s_name;

• Q21

  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 = 'MOZAMBIQUE'
  group by
      s_name
  order by
      numwait desc,
      s_name
  limit 100;

• Q22

  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 ('13', '31', '23', '29', '30', '18', '17')
              and c_acctbal > (
                  select
                      avg(c_acctbal)
                  from
                      customer
                  where
                      c_acctbal > 0.00
                      and substring(c_phone from 1 for 2) in ('13', '31', '23', '29', '30', '18', '17')
              )
              and not exists (
                  select
                      *
                  from
                      orders
                  where
                      o_custkey = c_custkey
              )
      ) as custsale
  group by
      cntrycode
  order by
      cntrycode;