TPC-H performance test for AnalyticDB for PostgreSQL V7.0

TPC-H

TPC-H is a decision support benchmark published by the Transaction Processing Performance Council (TPC). It consists of a suite of business-oriented ad-hoc queries and concurrent data modifications. The benchmark evaluates a decision support system's ability to execute complex queries against large data volumes. For more information, see TPC Benchmark H (TPC-H).

Test

• This test uses a 100 GB dataset and 8 segment nodes (2 cores and 16 GB of memory each).

• For a 1 TB dataset, we recommend 48 segment nodes (2 cores and 16 GB of memory each). This higher node count fully leverages the MPP architecture.

Test results

We ran the 22 standard TPC-H SQL queries on a 100 GB dataset in two scenarios:

• Using the proprietary Laser compute engine.

• Using the Greenplum native compute engine.

The Laser compute engine significantly outperforms the Greenplum native compute engine, reducing the total execution time by a factor of approximately 12.

The table below shows the detailed test results.

Procedure

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

Appendix: TPC-H test 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;