Hologres:Pengenalan indeks

Kueri TPC-H Q1

TPC-H Q1 melakukan agregasi dan penyaringan data pada kolom-kolom tertentu dari tabel `lineitem`.

l_shipdate <=: Menyaring kueri. Indeks harus diatur untuk mendukung penyaringan rentang dan mempercepat penyaringan data.

--TPC-H 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 '120' DAY
GROUP BY
        l_returnflag,
        l_linestatus
ORDER BY
        l_returnflag,
        l_linestatus;

Kueri TPC-H Q4

TPC-H Q4 terutama melakukan kueri penggabungan (join) antara tabel `lineitem` dan `orders`.

• o_orderdate >= DATE '1996-07-01': Ini adalah filter. Anda dapat mengatur indeks untuk mendukung penyaringan rentang dan dengan cepat menemukan data yang diperlukan.

• l_orderkey = o_orderkey: Ini adalah penggabungan antara dua tabel. Anda dapat mengatur indeks yang sama untuk kedua tabel. Disarankan untuk melakukan Local JOIN guna mengurangi operasi pengacakan (shuffle) data selama interaksi antara kedua tabel.

  --TPC-H Q4 Query
  SELECT
          o_orderpriority,
          COUNT(*) AS order_count
  FROM
          orders
  WHERE
          o_orderdate >= DATE '1996-07-01'
          AND o_orderdate < DATE '1996-07-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;

hologres_dataset_tpch_100g.lineitem

Kedua kueri Q1 dan Q4 melibatkan tabel `lineitem`, tetapi menggunakan bidang dan kondisi kueri yang berbeda.

• Untuk kueri Q1: Kueri terutama menggunakan `l_shipdate` untuk penyaringan rentang. `clustering_key` memanfaatkan data terurut dalam satu file untuk mempercepat penyaringan rentang. Oleh karena itu, Anda dapat mengatur `l_shipdate` sebagai Clustering Key. `segment_key` (`event_time_column`) digunakan untuk mempertahankan urutan antar file. Untuk bidang tanggal yang bersifat monoton naik atau turun, kami merekomendasikan agar Anda juga mengaturnya sebagai Segment Key. Hal ini memungkinkan penyaringan tingkat file yang efektif. Oleh karena itu, Anda juga dapat mengatur `l_shipdate` sebagai Segment Key.

• Untuk kueri Q4: Kueri terutama menggunakan bidang `l_orderkey` dari tabel `lineitem` untuk digabungkan dengan bidang `o_orderkey` dari tabel `orders`. `distribution_key` menentukan kebijakan distribusi data. Sistem menyimpan data dengan nilai `distribution_key` yang sama pada shard yang sama. Jika dua tabel berada dalam kelompok tabel yang sama dan bidang join-nya adalah Distribution Key, catatan dengan nilai kunci yang sama dari kedua tabel secara otomatis didistribusikan ke shard yang sama saat data ditulis. Saat tabel-tabel ini digabungkan, Local JOIN dilakukan pada node saat ini, yang menghindari pengacakan (shuffling) dan redistribusi data berdasarkan kunci join pada waktu proses (runtime), sehingga secara signifikan meningkatkan efisiensi eksekusi. Oleh karena itu, atur `l_orderkey` sebagai Distribution Key.

• Skema tabel akhir untuk tabel `lineitem` adalah sebagai berikut:

  BEGIN;
  CREATE TABLE hologres_dataset_tpch_100g.lineitem
  (
      l_ORDERKEY      BIGINT      NOT NULL,
      L_PARTKEY       INT         NOT NULL,
      L_SUPPKEY       INT         NOT NULL,
      L_LINENUMBER    INT         NOT NULL,
      L_QUANTITY      DECIMAL(15,2) NOT NULL,
      L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL,
      L_DISCOUNT      DECIMAL(15,2) NOT NULL,
      L_TAX           DECIMAL(15,2) NOT NULL,
      L_RETURNFLAG    TEXT        NOT NULL,
      L_LINESTATUS    TEXT        NOT NULL,
      L_SHIPDATE      TIMESTAMPTZ NOT NULL,
      L_COMMITDATE    TIMESTAMPTZ NOT NULL,
      L_RECEIPTDATE   TIMESTAMPTZ NOT NULL,
      L_SHIPINSTRUCT  TEXT        NOT NULL,
      L_SHIPMODE      TEXT        NOT NULL,
      L_COMMENT       TEXT        NOT NULL,
      PRIMARY KEY (L_ORDERKEY,L_LINENUMBER)
  )
  WITH (
      distribution_key = 'L_ORDERKEY',--Mengaktifkan Local JOIN untuk penggabungan tabel.
      clustering_key = 'L_SHIPDATE',--Mempercepat penyaringan rentang.
      event_time_column = 'L_SHIPDATE'--Mempercepat pemangkasan file.
    );
  COMMIT;

hologres_dataset_tpch_100g.orders

Dalam contoh ini, tabel `orders` digunakan dalam kueri Q4.

• Atur bidang `o_orderkey` dari tabel `orders` sebagai Distribution Key untuk memanfaatkan kemampuan Local JOIN dan meningkatkan efisiensi kueri join.

• Bidang `o_orderdate` terutama digunakan untuk menyaring kueri berdasarkan bidang tanggal. Anda dapat mengaturnya sebagai Segment Key untuk mempercepat pemangkasan file.

• Skema tabel akhir untuk tabel `orders` adalah sebagai berikut:

  BEGIN;
  CREATE TABLE hologres_dataset_tpch_100g.orders
  (
      O_ORDERKEY      BIGINT      NOT NULL PRIMARY KEY,
      O_CUSTKEY       INT         NOT NULL,
      O_ORDERSTATUS   TEXT        NOT NULL,
      O_TOTALPRICE    DECIMAL(15,2) NOT NULL,
      O_ORDERDATE     timestamptz NOT NULL,
      O_ORDERPRIORITY TEXT        NOT NULL,
      O_CLERK         TEXT        NOT NULL,
      O_SHIPPRIORITY  INT         NOT NULL,
      O_COMMENT       TEXT        NOT NULL
  )
  WITH (
      distribution_key = 'O_ORDERKEY',--Mengaktifkan Local JOIN untuk penggabungan tabel.
      event_time_column = 'O_ORDERDATE'--Mempercepat pemangkasan file.
    );
  COMMIT;