Hologres：Hologres索引入門

TPC-H Q1 Query

TPC-H Q1主要是對lineitem表的部分欄位做彙總查詢和過濾篩選。其中：

l_shipdate <= ：過濾查詢。需要設定索引以支援範圍過濾，可快速過濾出所需資料。

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

TPC-H Q4 Query

TPC-H Q4主要是對lineitem和orders表進行關聯查詢。其中：

• o_orderdate >= DATE '1996-07-01'：過濾查詢。需要設定索引以支援範圍過濾，可快速過濾出所需資料。

• l_orderkey = o_orderkey：兩表JOIN。需要為兩個表設定同一個索引，最好能進行Local JOIN，以減少資料在兩表互動時的Shuffle操作。

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

Q1和Q4中均涉及對lineitem表的查詢，其中使用的欄位和查詢條件不同。

• 對於Q1 Query：主要用l_shipdate做範圍過濾篩選。clustering_key可以利用檔案內的有序性加速範圍過濾，因此我們可將l_shipdate設定為Clustering Key。segment_key（event_time_column）用於保持檔案間的有序性，對於單調遞增/遞減的日期欄位，也建議設定為Segment Key，可以有效進行檔案層過濾，因此也可將l_shipdate設定為Segment Key。

• 對於Q4 Query ：主要用lineitem表的l_orderkey欄位與orders表的o_orderkey欄位進行關聯查詢（JOIN）。distribution_key用於指定資料的分布策略，系統會根據distribution_key，將相同的資料存放在同一個Shard上，當兩張表在同一個Table Group內，並且JOIN的欄位是Distribution Key時，在進行資料寫入時，會自動將兩張表重相同Key的記錄分發到同一個Shard上，當表進行JOIN時，只需要在當前節點進行Local JOIN，無需按照JOIN Key進行資料Shuffle，避免了運行時資料打散和重分發，可以顯著提高執行效率。因此，我們將l_orderkey設定為Distribution Key。

• 最終lineitem表的表結構如下：

  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',--在join時可以實現local join
      clustering_key = 'L_SHIPDATE',--加速範圍過濾
      event_time_column = 'L_SHIPDATE'--加速檔案裁剪
    );
  COMMIT;

hologres_dataset_tpch_100g.orders

本樣本中，orders表參與Q4 Query的計算。

• 將orders表的o_orderkey欄位設定為Distribution Key，可有效利用Local JOIN的能力，提升關聯（JOIN）查詢的效率。

• o_orderdate欄位主要用於日期欄位的過濾查詢，所以將其設定為Segment Key，加速檔案的裁剪。

• 最終orders表的表結構如下：

  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',--在join時可以實現local join
      event_time_column = 'O_ORDERDATE'--加速檔案裁剪
    );
  COMMIT;