PolarDB:Performance metrics

Test content and data volume

TPC-H is a widely used industry-standard performance benchmark developed and published by the TPC to evaluate the analytical capabilities of databases. The columnstore index query test uses 8 data tables and executes 22 complex SQL queries, covering single-table statistics, multi-table joins, subqueries, aggregations, and sorting. The test data volume is TPC-H SF100 (100 GB of data).

Test environment

The test environment is configured as follows:

• PolarDB for PostgreSQL cluster:

  • Database engine: PostgreSQL 14.

  • Product edition: Enterprise Edition, dedicated specifications.

  • Node specification: 32 cores, 256 GB.

Environment preparation

Before you start the test, make sure that the following requirements are met. Generating the test data takes approximately 30 minutes, and creating the columnstore indexes in a single-threaded serial manner takes approximately 39 minutes. Reserve the test time accordingly.

• The ECS instance and the PolarDB cluster reside in the same VPC.

• On the PolarDB for PostgreSQL cluster, a privileged account (create a privileged account) and the test database (create the test database) tpchdb have been created.

• You have logged on to the ECS instance.

• The TPCH tool dbgen.tar.gz has been downloaded and uploaded to the ECS instance.

Data preparation

Data preparation includes compiling the TPCH tool, generating the test data, and importing the data into the test database tpchdb. Perform the following steps in order:

1. Decompress and compile the dbgen tool.

   # Decompress
   tar -zxvf dbgen.tar.gz
   # Compile
   cd./dbgen
   make -f makefile.suite

2. Use the TPCH tool to generate 100 GB of test data. The expected execution time is approximately 30 minutes.

   # Change to the dbgen directory
   ./dbgen -s 100 -f

3. Make sure that you are in the dbgen directory. pwd should return /<installation_path>/dbgen.

   # Confirm the current directory
   pwd

4. On the ECS instance, use the database connection tool provided by PolarDB (PolarDB-Tools) to connect to your cluster from the command line. The target database is tpchdb.

   /u01/polardb_pg_tools/bin/psql  -h <cluster_endpoint> -p <port_for_cluster_endpoint> -U <privileged_account> -d tpchdb

5. Import the test data.

   \i./dss.ddl
   \copy part from./part.tbl with delimiter as '|' NULL '';
   \copy region from./region.tbl with delimiter as '|' NULL '';
   \copy nation from./nation.tbl with delimiter as '|' NULL '';
   \copy orders from./orders.tbl with delimiter as '|' NULL '';
   \copy customer from./customer.tbl with delimiter as '|' NULL '';
   \copy lineitem from./lineitem.tbl with delimiter as '|' NULL '';
   \copy partsupp from./partsupp.tbl with delimiter as '|' NULL '';
   \copy supplier from./supplier.tbl with delimiter as '|' NULL '';

6. Verify the import results of the eight tables. The row count of each table must match the row count listed in the Columnstore index disk space section (866,037,932 rows in total). If a table fails to be imported, re-import the data of that table before you perform the subsequent steps.

Enable and create columnstore indexes

1. Based on your PolarDB for PostgreSQL cluster version, enable the columnstore index feature.

2. Create columnstore indexes for the eight test tables.

   -- partsupp table
   ALTER TABLE PARTSUPP ADD CONSTRAINT partsupp_pkey PRIMARY KEY (PS_PARTKEY, PS_SUPPKEY);
   CREATE INDEX imps ON partsupp USING csi(ps_partkey, ps_suppkey, ps_availqty, ps_supplycost, ps_comment);
   
   -- part table
   ALTER TABLE PART ADD CONSTRAINT part_kpey PRIMARY KEY (P_PARTKEY);
   CREATE INDEX im_p ON part USING csi(p_partkey, p_name, p_mfgr, p_brand, p_type, p_size, p_container, p_retailprice, p_comment);
   
   -- supplier table
   ALTER TABLE SUPPLIER ADD CONSTRAINT supplier_pkey PRIMARY KEY (S_SUPPKEY);
   CREATE INDEX im_s ON supplier USING csi(s_suppkey, s_name, s_address, s_nationkey, s_phone, s_acctbal, s_comment);
   
   -- customer table
   ALTER TABLE CUSTOMER ADD CONSTRAINT customer_pkey PRIMARY KEY (C_CUSTKEY);
   CREATE INDEX im_c ON customer USING csi(c_custkey, c_name, c_address, c_nationkey, c_phone, c_acctbal, c_mktsegment, c_comment);
   
   -- orders table
   ALTER TABLE ORDERS ADD CONSTRAINT orders_pkey PRIMARY KEY (O_ORDERKEY);
   CREATE INDEX im_o ON orders USING csi(o_orderkey, o_custkey, o_orderstatus, o_totalprice, o_orderdate, o_orderpriority, o_clerk, o_shippriority, o_comment);
   
   -- lineitem table
   ALTER TABLE LINEITEM ADD CONSTRAINT lineitem_pkey PRIMARY KEY (L_ORDERKEY, L_LINENUMBER);
   CREATE INDEX im_l ON lineitem USING csi(l_orderkey, l_partkey, l_suppkey, l_linenumber, l_quantity, l_extendedprice, l_discount, l_tax, l_returnflag, l_linestatus, l_shipdate, l_commitdate, l_receiptdate, l_shipinstruct, l_shipmode, l_comment);
   
   -- nation table
   ALTER TABLE NATION ADD CONSTRAINT nation_pkey PRIMARY KEY (N_NATIONKEY);
   CREATE INDEX im_n ON nation USING csi(n_nationkey, n_name, n_regionkey, n_comment);
   
   -- region table
   ALTER TABLE REGION ADD CONSTRAINT region_pkey PRIMARY KEY (R_REGIONKEY);
   CREATE INDEX im_r ON region USING csi(r_regionkey, r_name, r_comment);

3. Make sure that the columnstore indexes of all the eight test tables are created before you execute the subsequent queries.

4. Configure the columnstore index parameters for the subsequent queries. First, in the cluster parameter settings of the console, set polar_csi.max_parallel_workers to 32 and make the configuration take effect as prompted by the console. Then run the following commands in the session. For the values and take-effect methods of the parameters, see Columnstore index parameters.

   SET polar_csi.enable_query TO ON;
   SET polar_csi.cost_threshold TO 0;

   Note

   • polar_csi.memory_limit is a SIGHUP parameter. After it is modified, you must reload the configuration for the modification to take effect. You cannot modify it by using SET in the current session. To adjust the memory limit for the test, configure it in the console before you create the columnstore indexes and start the test, and complete the parameter reload or the restart of the related workers as prompted by the console.

   • polar_csi.max_parallel_workers was named polar_csi.exec_parallel in early engine versions. For older engine versions that do not support polar_csi.max_parallel_workers, use polar_csi.exec_parallel.

     • PostgreSQL 14:

       • For version 2.0.14.20.42.0 and earlier, use polar_csi.exec_parallel.

       • For version 2.0.14.20.43.0 and later, use polar_csi.max_parallel_workers.

     • PostgreSQL 16:

       • For version 2.0.16.11.15.0 and earlier, use polar_csi.exec_parallel.

       • For version 2.0.16.13.16.0 and later, use polar_csi.max_parallel_workers.

Execute SQL queries

In the PolarDB for PostgreSQL cluster, execute the 22 complex SQL queries in sequence and collect the execution time.

1. Enable timing statistics before you execute the queries. \timing collects the total execution time of each SQL statement. You must enable it to collect execution time data.

   \timing

2. Execute the 22 queries in sequence. Each query starts with EXPLAIN ANALYZE, which outputs the execution plan and the Execution Time. The total execution time collected by \timing includes the client round-trip overhead and differs from the Execution Time. Use a consistent metric when you collect and compare execution times, and do not mix these two metrics.

   Click to view the details of the 22 SQL queries

   Q1

   EXPLAIN ANALYZE
   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' - '60 day'::interval
   GROUP BY
       l_returnflag,
       l_linestatus
   ORDER BY
       l_returnflag,
       l_linestatus;

   Q2

   EXPLAIN ANALYZE
   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 = 43
       and p_type like '%NICKEL'
       and s_nationkey = n_nationkey
       and n_regionkey = r_regionkey
       and r_name = 'MIDDLE EAST'
       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 = 'MIDDLE EAST'
       )
   ORDER BY
       s_acctbal desc,
       n_name,
       s_name,
       p_partkey limit 100;

   Q3

   EXPLAIN ANALYZE
   SELECT
       l_orderkey,
       sum(l_extendedprice * (1 - l_discount)) as revenue,
       o_orderdate,
       o_shippriority
   FROM
       customer,
       orders,
       lineitem
   WHERE
       c_mktsegment = 'FURNITURE'
       and c_custkey = o_custkey
       and l_orderkey = o_orderkey
       and o_orderdate < date '1995-03-05'
       and l_shipdate > date '1995-03-05'
   GROUP BY
       l_orderkey,
       o_orderdate,
       o_shippriority
   ORDER BY
       revenue desc,
       o_orderdate limit 100;

   Q4

   EXPLAIN ANALYZE
   SELECT
       o_orderpriority,
       count(*) as order_count
   FROM
       orders
   WHERE
       o_orderdate >= date '1993-05-01'
       and o_orderdate < date '1993-05-01' + interval '3 month'::interval
       and exists (
       SELECT
       *
       FROM
       lineitem
       WHERE
       l_orderkey = o_orderkey
       and l_commitdate < l_receiptdate
       )
   GROUP BY
       o_orderpriority
   ORDER BY
       o_orderpriority;

   Q5

   EXPLAIN ANALYZE
   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 '1995-01-01'
           and o_orderdate < date '1995-01-01' + '1 year'::interval
   GROUP BY
           n_name
   ORDER BY
           revenue desc;

   Q6

   EXPLAIN ANALYZE
   SELECT
           sum(l_extendedprice * l_discount) as revenue
   FROM
           lineitem
   WHERE
           l_shipdate >= date '1993-01-01'
           and l_shipdate < date '1993-01-01' + '1 year'::interval
           and l_discount between 0.03 - 0.01 and 0.03 + 0.01
           and l_quantity < 24;

   Q7

   EXPLAIN ANALYZE
   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 = 'FRANCE'
                   AND n2.n_name = 'GERMANY')
               OR (n1.n_name = 'GERMANY'
                   AND n2.n_name = 'FRANCE'))
           AND l_shipdate BETWEEN CAST('1995-01-01' AS date)
           AND CAST('1996-12-31' AS date)) AS shipping
   GROUP BY
       supp_nation,
       cust_nation,
       l_year
   ORDER BY
       supp_nation,
       cust_nation,
       l_year;

   Q8

   EXPLAIN ANALYZE
   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 '1995-01-01'::date and '1996-12-31'::date
                           and p_type = 'PROMO POLISHED NICKEL'
           ) as all_nations
   GROUP BY
           o_year
   ORDER BY
           o_year;

   Q9

   EXPLAIN ANALYZE
   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 '%navajo%'
           ) as profit
   GROUP BY
           nation,
           o_year
   ORDER BY
           nation,
           o_year desc limit 100;

   Q10

   EXPLAIN ANALYZE
   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 '1993-08-01'
           and o_orderdate < date '1993-08-01' + '3 month'::interval
           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

   EXPLAIN ANALYZE
   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;

   Q12

   EXPLAIN ANALYZE
   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 ('AIR', 'FOB')
           and l_commitdate < l_receiptdate
           and l_shipdate < l_commitdate
           and l_receiptdate >= date '1996-01-01'
           and l_receiptdate < date '1996-01-01' + '1 year'::interval
   GROUP BY
           l_shipmode
   ORDER BY
           l_shipmode;

   Q13

   EXPLAIN ANALYZE
   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

   EXPLAIN ANALYZE
   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 '1995-02-01'
           and l_shipdate < date '1995-02-01' + interval '1 month'::interval;

   Q15

   EXPLAIN ANALYZE
   WITH revenue0 as (
       SELECT
       l_suppkey as supplier_no,
       sum(l_extendedprice * (1 - l_discount)) as total_revenue
       FROM
       lineitem
       WHERE
       l_shipdate >= date '1995-08-01'
       and l_shipdate < date '1995-08-01' + '3 month'::interval
       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;

   Q16

   EXPLAIN ANALYZE
   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#13'
           and p_type not like 'ECONOMY BRUSHED%'
           and p_size in (11, 8, 10, 31, 21, 13, 32, 28)
           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 100;

   Q17

   EXPLAIN ANALYZE
   SELECT
           sum(l_extendedprice) / 7.0 as avg_yearly
   FROM
           lineitem,
           part
   where
           p_partkey = l_partkey
           and p_brand = 'Brand#44'
           and p_container = 'MED PKG'
           and l_quantity < (
                   SELECT
                           0.2 * avg(l_quantity)
                   FROM
                           lineitem
                   WHERE
                           l_partkey = p_partkey
           );

   Q18

   EXPLAIN ANALYZE
   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;

   Q19

   EXPLAIN ANALYZE
   SELECT
           sum(l_extendedprice* (1 - l_discount)) as revenue
   FROM
           lineitem,
           part
   WHERE
           (
                   p_partkey = l_partkey
                   and p_brand = 'Brand#15'
                   and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
                   and l_quantity >= 10 and l_quantity <= 10 + 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 >= 18 and l_quantity <= 18 + 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#21'
                   and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
                   and l_quantity >= 30 and l_quantity <= 30 + 10
                   and p_size between 1 and 15
                   and l_shipmode in ('AIR', 'AIR REG')
                   and l_shipinstruct = 'DELIVER IN PERSON'
           );

   Q20

   EXPLAIN ANALYZE
   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 'lemon%'
                           )
                           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 '1997-01-01'
                                           and l_shipdate < date '1997-01-01' + '1 year'::interval
                           )
           )
           and s_nationkey = n_nationkey
           and n_name = 'INDONESIA'
   ORDER BY
           s_name limit 100;

   Q21

   EXPLAIN ANALYZE
   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 = 'INDIA'
   GROUP BY
           s_name
   ORDER BY
           numwait desc,
           s_name limit 100;

   Q22

   EXPLAIN ANALYZE
   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
                                   ('16', '17', '24', '21', '19', '22', '15')
                           and c_acctbal > (
                                   SELECT
                                           avg(c_acctbal)
                                   FROM
                                           customer
                                   WHERE
                                           c_acctbal > 0.00
                                           and substring(c_phone from 1 for 2) in
                                                   ('16', '17', '24', '21', '19', '22', '15')
                           )
                           and not exists (
                                   SELECT
                                           *
                                   FROM
                                           orders
                                   WHERE
                                           o_custkey = c_custkey
                           )
           ) as custsale
   GROUP BY
           cntrycode
   ORDER BY
           cntrycode;

Test results

Follow-up steps

After the test, the cluster retains 100 GB of TPC-H test data (the PostgreSQL row store occupies 126 GB) and up to 53 GB of columnstore indexes, and the ECS instance retains the .tbl data files generated by dbgen. These data continuously occupy storage space and incur fees. Clean up the data as needed:

• Delete the TPC-H data tables and columnstore indexes from the test database tpchdb to release the storage space occupied by the row store data and the columnstore indexes.

• In the dbgen directory on the ECS instance, delete the .tbl files to release disk space.