Hologres:Performance comparison testing between Hologres and ClickHouse

Test environments

To eliminate the impact of the network bandwidth, this performance testing uses the same Elastic Compute Service (ECS) instance to send query requests to Hologres and ClickHouse in a virtual private cloud (VPC). The result caching feature is disabled in Hologres. The following tables describe the specific test environments.

• Test environment in ClickHouse

  Item	Description
  Server	An ECS instance
  CPU	Intel Xeon (Ice Lake) Platinum 8369B, 64 vCPU cores
  Memory	256 GiB
  Internal bandwidth	32 Gbit/s
  Disk	Enhanced SSD (ESSD), 200 GB, PL1, the maximum IOPS of 50,000 per disk
  Operating system	CentOS 8.4 64-bit
  Hardware fee (excluding the Internet bandwidth)	USD 1370.04 per month
  Internet bandwidth over IPv4	200 Mbit/s
  Network traffic fee	USD 2323.85 per month
  ClickHouse version	V21.8.3.44

• Test environment in Hologres

  Item	Description
  Computing resources	64 CPU cores and 256 GB of memory
  Storage resources	Logical storage: 200 GB
  Internet bandwidth	Greater than 5 Gbit/s
  Total fee	USD 1653.73 per month
  Hologres version	V0.10.33

Test data

SQL statements

• SQL statements in ClickHouse

  The DDL statements and query statements used are the same as those provided on the ClickHouse official website. For more information, see Star Schema Benchmark on the ClickHouse official website.

• SQL statements in Hologres

  • DDL statements

    DROP TABLE IF EXISTS lineorder_flat;
    
    BEGIN;
    CREATE TABLE IF NOT EXISTS lineorder_flat (
      lo_orderdate     date NOT NULL ,
      lo_orderkey      int NOT NULL ,
      lo_linenumber    int NOT NULL ,
      lo_custkey       int NOT NULL ,
      lo_partkey       int NOT NULL ,
      lo_suppkey       int NOT NULL ,
      lo_orderpriority text NOT NULL ,
      lo_shippriority  int NOT NULL ,
      lo_quantity      int NOT NULL ,
      lo_extendedprice int NOT NULL ,
      lo_ordtotalprice int NOT NULL ,
      lo_discount      int NOT NULL ,
      lo_revenue       int NOT NULL ,
      lo_supplycost    int NOT NULL ,
      lo_tax           int NOT NULL ,
      lo_commitdate    date NOT NULL ,
      lo_shipmode      text NOT NULL ,
      c_name           text NOT NULL ,
      c_address text NOT NULL ,
      c_city text NOT NULL ,
      c_nation text NOT NULL ,
      c_region text NOT NULL ,
      c_phone text NOT NULL ,
      c_mktsegment text NOT NULL ,
      s_region text NOT NULL ,
      s_nation text NOT NULL ,
      s_city text NOT NULL ,
      s_name text NOT NULL ,
      s_address text NOT NULL ,
      s_phone text NOT NULL ,
      p_name text NOT NULL ,
      p_mfgr text NOT NULL ,
      p_category text NOT NULL ,
      p_brand text NOT NULL ,
      p_color text NOT NULL ,
      p_type text NOT NULL ,
      p_size int NOT NULL ,
      p_container text NOT NULL,
      PRIMARY KEY (lo_orderkey,lo_linenumber)
    );
    CALL set_table_property('lineorder_flat', 'distribution_key', 'lo_orderkey');
    CALL set_table_property('lineorder_flat', 'segment_key', 'lo_orderdate');
    CALL set_table_property('lineorder_flat', 'clustering_key', 'lo_orderdate');
    CALL set_table_property('lineorder_flat', 'bitmap_columns', 'p_category,s_region,c_region,c_nation,s_nation,c_city,s_city,p_mfgr,p_brand');
    CALL set_table_property('lineorder_flat', 'time_to_live_in_seconds', '31536000');
    COMMIT;

  • Query statements

    • Q1.1

      SELECT SUM(LO_EXTENDEDPRICE * LO_DISCOUNT) AS REVENUE 
      FROM LINEORDER_FLAT 
      WHERE LO_ORDERDATE >= DATE '1993-01-01' 
      AND LO_ORDERDATE <= DATE '1993-12-31' 
      AND LO_DISCOUNT BETWEEN 1 AND 3 
      AND LO_QUANTITY < 25;

    • Q1.2

      SELECT SUM(LO_EXTENDEDPRICE * LO_DISCOUNT) AS REVENUE 
      FROM LINEORDER_FLAT  
      WHERE LO_ORDERDATE >= DATE '1994-01-01' 
      AND LO_ORDERDATE <= DATE '1994-01-31' 
      AND LO_DISCOUNT BETWEEN 4 AND 6 
      AND LO_QUANTITY BETWEEN 26 AND 35;

    • Q1.3

      SELECT SUM(LO_EXTENDEDPRICE * LO_DISCOUNT) AS REVENUE 
      FROM LINEORDER_FLAT 
      WHERE EXTRACT(WEEK FROM LO_ORDERDATE ) = 6 
      AND LO_ORDERDATE >= DATE '1994-01-01' 
      AND LO_ORDERDATE <= DATE '1994-12-31' 
      AND LO_DISCOUNT BETWEEN 5 AND 7 
      AND LO_QUANTITY BETWEEN 26 AND 35;

    • Q2.1

      SELECT SUM(LO_REVENUE),
      EXTRACT(YEAR FROM LO_ORDERDATE) AS YEAR,
      P_BRAND 
      FROM LINEORDER_FLAT 
      WHERE P_CATEGORY = 'MFGR#12' 
      AND S_REGION = 'AMERICA' 
      GROUP BY YEAR, P_BRAND 
      ORDER BY YEAR, P_BRAND;

    • Q2.2

      SELECT SUM(LO_REVENUE),
      EXTRACT(YEAR FROM LO_ORDERDATE) AS YEAR,
      P_BRAND 
      FROM LINEORDER_FLAT 
      WHERE P_BRAND BETWEEN 'MFGR#2221' AND 'MFGR#2228' 
      AND S_REGION = 'ASIA' 
      GROUP BY YEAR, P_BRAND 
      ORDER BY YEAR, P_BRAND;

    • Q2.3

      SELECT SUM(LO_REVENUE),
      EXTRACT(YEAR FROM LO_ORDERDATE) AS YEAR, 
      P_BRAND 
      FROM LINEORDER_FLAT 
      WHERE P_BRAND = 'MFGR#2239' 
      AND S_REGION = 'EUROPE' 
      GROUP BY YEAR, P_BRAND 
      ORDER BY YEAR, P_BRAND;

    • Q3.1

      SELECT C_NATION,
      S_NATION, 
      EXTRACT(YEAR FROM LO_ORDERDATE) AS YEAR, 
      SUM(LO_REVENUE) AS REVENUE 
      FROM LINEORDER_FLAT 
      WHERE C_REGION = 'ASIA' 
      AND S_REGION = 'ASIA' 
      AND LO_ORDERDATE  >= DATE '1992-01-01' 
      AND LO_ORDERDATE  <= DATE '1997-12-31' 
      GROUP BY C_NATION,S_NATION,YEAR 
      ORDER BY  YEAR ASC,REVENUE DESC;

    • Q3.2

      SELECT C_CITY, 
      S_CITY, 
      EXTRACT(YEAR FROM LO_ORDERDATE) AS YEAR, 
      SUM(LO_REVENUE) AS REVENUE
      FROM LINEORDER_FLAT 
      WHERE C_NATION = 'UNITED STATES' 
      AND S_NATION = 'UNITED STATES' 
      AND LO_ORDERDATE >= DATE '1992-01-01' 
      AND LO_ORDERDATE <= DATE '1997-12-31' 
      GROUP BY C_CITY, S_CITY, YEAR 
      ORDER BY YEAR ASC, REVENUE DESC;

    • Q3.3

      SELECT C_CITY,
      S_CITY, 
      EXTRACT(YEAR FROM LO_ORDERDATE) AS YEAR, 
      SUM(LO_REVENUE) AS REVENUE 
      FROM LINEORDER_FLAT 
      WHERE C_CITY IN ( 'UNITED KI1' ,'UNITED KI5') 
      AND S_CITY IN ( 'UNITED KI1' ,'UNITED KI5') 
      AND LO_ORDERDATE >= DATE '1992-01-01' 
      AND LO_ORDERDATE <= DATE '1997-12-31' 
      GROUP BY C_CITY, S_CITY, YEAR 
      ORDER BY YEAR ASC, REVENUE DESC;

    • Q3.4

      SELECT C_CITY,
      S_CITY, 
      EXTRACT(YEAR FROM LO_ORDERDATE) AS YEAR, 
      SUM(LO_REVENUE) AS REVENUE 
      FROM LINEORDER_FLAT 
      WHERE C_CITY IN ('UNITED KI1', 'UNITED KI5') 
      AND S_CITY IN ( 'UNITED KI1',  'UNITED KI5') 
      AND LO_ORDERDATE >= DATE '1997-12-01' 
      AND LO_ORDERDATE <= DATE '1997-12-31' 
      GROUP BY C_CITY, S_CITY, YEAR 
      ORDER BY YEAR ASC, REVENUE DESC;

    • Q4.1

      SELECT EXTRACT(YEAR FROM LO_ORDERDATE) AS YEAR, 
      C_NATION,  
      SUM(LO_REVENUE - LO_SUPPLYCOST) AS PROFIT 
      FROM LINEORDER_FLAT 
      WHERE C_REGION = 'AMERICA' 
      AND S_REGION = 'AMERICA' 
      AND P_MFGR IN ( 'MFGR#1' , 'MFGR#2') 
      GROUP BY YEAR, C_NATION 
      ORDER BY YEAR ASC, C_NATION ASC;

    • Q4.2

      SELECT EXTRACT(YEAR FROM LO_ORDERDATE) AS YEAR, 
      S_NATION, 
      P_CATEGORY, 
      SUM(LO_REVENUE - LO_SUPPLYCOST) AS PROFIT 
      FROM LINEORDER_FLAT 
      WHERE C_REGION = 'AMERICA' 
      AND S_REGION = 'AMERICA' 
      AND LO_ORDERDATE >= DATE '1997-01-01' 
      AND LO_ORDERDATE <= DATE '1998-12-31' 
      AND  P_MFGR IN ( 'MFGR#1' , 'MFGR#2') 
      GROUP BY YEAR, S_NATION, P_CATEGORY 
      ORDER BY  YEAR ASC, S_NATION ASC, P_CATEGORY ASC;

    • Q4.3

      SELECT EXTRACT(YEAR FROM LO_ORDERDATE) AS YEAR, 
      S_CITY, 
      P_BRAND, 
      SUM(LO_REVENUE - LO_SUPPLYCOST) AS PROFIT 
      FROM LINEORDER_FLAT 
      WHERE S_NATION = 'UNITED STATES' 
      AND LO_ORDERDATE >= DATE '1997-01-01' 
      AND LO_ORDERDATE <= DATE '1998-12-31' 
      AND P_CATEGORY = 'MFGR#14' 
      GROUP BY  YEAR,  S_CITY, P_BRAND 
      ORDER BY YEAR ASC,  S_CITY ASC,  P_BRAND ASC;