Hologres:Getting started with Hologres indexes

TPC-H Q1

TPC-H Q1 is used to aggregate and filter data in specific columns of the lineitem table.

l_shipdate <=: Filters data. You must configure an index to allow data filtering based on a time range.

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

TPC-H Q4 is mainly performed to join the lineitem and orders tables.

• o_orderdate >= DATE '1996-07-01': Filters data. You must configure an index to allow data filtering based on a time range.

• l_orderkey = o_orderkey: Joins the two tables. If possible, configure the same index for the two tables to allow local joins. This helps reduce data shuffling between the two tables.

  --TPC-H Q4
  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

The index configurations and query conditions for the lineitem table vary in TPC-H Q1 and TPC-H Q4.

• TPC-H Q1: The l_shipdate column specifies the time sequence of data in a file and is used for range filtering. You can configure the l_shipdate column as the clustering key column to accelerate range filtering. The event time column is used to sort files. We recommend that you configure date columns in which dates monotonically increase or decrease as event time columns to facilitate file filtering. In TPC-H Q1, you can configure the l_shipdate column as the event time column.

• TPC-H Q4: Join queries are performed based on the l_orderkey column of the lineitem table and the o_orderkey column of the orders table. Data is distributed based on the distribution key. The system distributes data records with the same distribution key value to the same shard. If two tables are stored in the same table group and are joined based on the distribution key, the system automatically distributes data records that have the same distribution key value to the same shard. During data writes, a local join is performed on the current node, and data shuffling based on the join key is not required. This prevents data scattering and redistribution during runtime and significantly improves execution efficiency. Therefore, we recommend that you configure the l_orderkey column as the distribution key.

• The following sample code is used to create the lineitem table:

  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',-- Specify the distribution key to allow local joins.
      clustering_key = 'L_SHIPDATE',-- Specify the clustering key to accelerate range filtering.
      event_time_column = 'L_SHIPDATE'-- Specify the event time column to accelerate file pruning.
    );
  COMMIT;

hologres_dataset_tpch_100g.orders

In this example, the orders table is used in TPC-H Q4.

• Configure the o_orderkey column in the orders table as the distribution key to allow local joins. This helps improve the efficiency of join queries.

• Configure the o_orderdate column in the orders table as the event time column to accelerate file pruning. The o_orderdate column contains date information and can be used for filtering.

• The following sample code is used to create the orders table:

  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',-- Specify the distribution key to allow local joins.
      event_time_column = 'O_ORDERDATE'-- Specify the event time column to accelerate file pruning.
    );
  COMMIT;