Hologres:Mulai menggunakan indeks Hologres

Kueri TPC-H Q1

Kueri TPC-H Q1 melakukan agregasi dan pemfilteran pada kolom-kolom tertentu dari tabel lineitem. Kueri ini mencakup kondisi berikut:

l_shipdate <=: Ini adalah kondisi filter. Untuk mendukung pemfilteran rentang yang efisien dan mengambil data yang diperlukan secara cepat, Anda harus menentukan indeks yang sesuai.

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

Kueri TPC-H Q4 terutama menggabungkan tabel lineitem dan orders. Kueri ini mencakup kondisi berikut:

• o_orderdate >= DATE '1996-07-01': Ini adalah kondisi filter. Untuk mendukung pemfilteran rentang yang efisien dan mengambil data yang diperlukan secara cepat, Anda harus menentukan indeks yang sesuai.

• l_orderkey = o_orderkey: Ini adalah kondisi penggabungan antara kedua tabel. Untuk performa terbaik, gunakan indeks yang sama pada kedua tabel agar memungkinkan penggabungan lokal (local join), yang mengurangi pengacakan data selama operasi.

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

Baik kueri Q1 maupun Q4 melibatkan tabel lineitem, tetapi menggunakan kolom dan kondisi yang berbeda.

• Untuk kueri Q1: Kueri terutama menggunakan l_shipdate untuk pemfilteran rentang. Clustering key mempercepat pemindaian rentang dengan memanfaatkan urutan data yang diurutkan dalam file. Oleh karena itu, tetapkan l_shipdate sebagai clustering key. Segment key (event time column) mempertahankan urutan antar file. Untuk kolom tanggal yang nilainya meningkat atau menurun secara monoton, menetapkannya sebagai segment key memungkinkan pemangkasan kunci segmen yang efektif. Oleh karena itu, Anda juga dapat menetapkan l_shipdate sebagai segment key.

• Untuk kueri Q4: Kueri menggabungkan tabel lineitem dengan tabel orders pada kolom l_orderkey dan o_orderkey. Distribution key menentukan strategi distribusi data. Sistem menempatkan data dengan nilai kunci yang sama pada shard yang sama. Jika dua tabel berada dalam kelompok tabel yang sama dan digabungkan berdasarkan kolom distribution key-nya, sistem secara otomatis mendistribusikan catatan yang cocok ke shard yang sama saat data ditulis. Ketika tabel-tabel ini digabungkan, sistem melakukan penggabungan lokal pada setiap node tanpa mengacak data melalui jaringan. Hal ini menghindari pengacakan dan redistribusi data saat runtime, sehingga secara signifikan meningkatkan efisiensi eksekusi. Oleh karena itu, tetapkan l_orderkey sebagai distribution key.

• Struktur tabel akhir untuk 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',--Enables local join.
      clustering_key = 'L_SHIPDATE',--Accelerates range filtering.
      event_time_column = 'L_SHIPDATE'--Accelerates segment key pruning.
    );
  COMMIT;

hologres_dataset_tpch_100g.orders

Dalam contoh ini, tabel orders digunakan dalam kueri Q4.

• Tetapkan kolom o_orderkey pada tabel orders sebagai distribution key untuk memanfaatkan kemampuan penggabungan lokal dan meningkatkan efisiensi kueri penggabungan.

• Kolom o_orderdate terutama digunakan untuk pemfilteran berdasarkan tanggal. Tetapkan sebagai segment key untuk mempercepat pemangkasan kunci segmen.

• Struktur tabel akhir untuk 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',--Enables local join.
      event_time_column = 'O_ORDERDATE'--Accelerates segment key pruning.
    );
  COMMIT;