PolarDB:Benchmark kinerja IMCI (TPC-H 100 GB)

Langkah 1: Buat database

Pada kluster PolarDB for PostgreSQL, buat akun istimewa dan buat database uji tpchdb.

Langkah 2: Hasilkan data TPC-H

Masuk ke instance ECS dan siapkan data uji.

1. Unduh tool TPC-H dbgen.tar.gz, unggah ke instance ECS, lalu ekstrak dan kompilasi.

   --- Ekstrak paket
   tar -zxvf dbgen.tar.gz
   --- Kompilasi kode
   cd ./dbgen
   make -f makefile.suite

2. Gunakan tool TPC-H untuk menghasilkan data uji sebesar 100 GB. Operasi ini membutuhkan waktu sekitar 30 menit.

   --- Pindah ke direktori dbgen
   ./dbgen -s 100 -f

Langkah 3: Impor data ke PolarDB

Pada instance ECS, gunakan tool koneksi PolarDB (PolarDB-Tools) untuk menghubungkan ke kluster Anda dan impor data uji ke database tpchdb.

1. Pastikan Anda berada di direktori dbgen. Output yang diharapkan adalah /<installation_path>/dbgen.

   --- Konfirmasi direktori saat ini
   pwd

2. Gunakan PolarDB-Tools untuk menghubungkan ke kluster dari command line.

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

3. Impor data uji.

   \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 '';

Langkah 1: Aktifkan IMCI

Berdasarkan versi kluster PolarDB for PostgreSQL Anda, aktifkan fitur indeks penyimpanan kolom.

Langkah 2: Buat indeks pada semua tabel uji

Buat IMCI pada masing-masing 8 tabel uji. SQL berikut menambahkan constraint primary key (diperlukan oleh IMCI) dan membuat indeks penyimpanan kolom menggunakan tipe indeks csi.

--tabel partsupp
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);

--tabel part
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);

--tabel supplier
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);

--tabel customer
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);

--tabel orders
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);

--tabel lineitem
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);

--tabel nation
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);

--tabel region
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);

Langkah 3: Konfigurasi parameter kueri IMCI

Atur parameter berikut sebelum menjalankan kueri benchmark. Setiap parameter dijelaskan dalam Parameter.

SET polar_csi.enable_pk TO ON;
SET polar_csi.enable_query TO ON;
SET polar_csi.cost_threshold TO 0;
SET polar_csi.memory_limit TO 65536;
// Versi kernel tinggi
SET polar_csi.max_parallel_workers TO 32;
// Versi kernel rendah
SET polar_csi.exec_parallel TO 32;

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;
--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'
        );
--LIMIT -1

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

Kinerja kueri

IMCI memberikan percepatan kueri lebih dari 50x dibandingkan mesin penyimpanan baris. Dalam benchmark ini, ke-22 kueri TPC-H selesai dalam 23,92 detik dengan IMCI dibandingkan 2.363,7 detik pada penyimpanan baris — percepatan keseluruhan sebesar 98,82x.

Rincian per kueri (Q15 timeout pada penyimpanan baris; dihitung sebagai 500 detik):

Efisiensi penyimpanan

Kompresi kolom IMCI secara signifikan mengurangi penggunaan penyimpanan dibandingkan tabel heap penyimpanan baris.

• Penyimpanan baris (tabel heap): 126 GB

• IMCI (PK dinonaktifkan): 25 GB — 20% dari ukuran penyimpanan baris. Terbaik untuk data statis.

• IMCI (PK diaktifkan): 53 GB — 42% dari ukuran penyimpanan baris. Terbaik untuk data dinamis.

Parameter polar_csi.enable_pk mengontrol apakah primary key disertakan dalam indeks penyimpanan kolom. Rincian per tabel:

Waktu pembuatan indeks

Pembuatan IMCI single-threaded pada ke-8 tabel membutuhkan waktu total 39 menit.