Hologres:Introduction to indexes

TPC-H Q1 query

TPC-H Q1 aggregates and filters data in specific columns of the `lineitem` table.

l_shipdate <=: Filters the query. An index must be set to support range filtering and accelerate data filtering.

--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 mainly performs a join query on the `lineitem` and `orders` tables.

• o_orderdate >= DATE '1996-07-01': This is a filter. You can set an index to support range filtering and quickly find the required data.

• l_orderkey = o_orderkey: This is a join between two tables. You can set the same index for both tables. Performing a Local JOIN is recommended to reduce data shuffle operations during the interaction between the two tables.

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

Both Q1 and Q4 queries involve the `lineitem` table, but they use different fields and query conditions.

• For the Q1 query: The query mainly uses `l_shipdate` for range filtering. The `clustering_key` uses the ordered data within a file to accelerate range filtering. Therefore, you can set `l_shipdate` as the Clustering Key. The `segment_key` (`event_time_column`) is used to maintain order between files. For date fields that are monotonically increasing or decreasing, we recommend that you also set them as the Segment Key. This allows for effective file-level filtering. Therefore, you can also set `l_shipdate` as the Segment Key.

• For the Q4 query: The query mainly uses the `l_orderkey` field of the `lineitem` table to join with the `o_orderkey` field of the `orders` table. The `distribution_key` specifies the data distribution policy. The system stores data with the same `distribution_key` value on the same shard. If two tables are in the same table group and their join field is the Distribution Key, records with the same key value from both tables are automatically distributed to the same shard when data is written. When these tables are joined, a Local JOIN is performed on the current node. This avoids data shuffling and redistribution based on the join key at runtime, which can significantly improve execution efficiency. Therefore, set `l_orderkey` as the Distribution Key.

• The final table schema for the `lineitem` table is as follows:

  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 for table joins.
      clustering_key = 'L_SHIPDATE',--Accelerates range filtering.
      event_time_column = 'L_SHIPDATE'--Accelerates file pruning.
    );
  COMMIT;

hologres_dataset_tpch_100g.orders

In this example, the `orders` table is used in the Q4 query.

• Set the `o_orderkey` field of the `orders` table as the Distribution Key to use the Local JOIN capability and improve the efficiency of join queries.

• The `o_orderdate` field is mainly used for filtering queries on date fields. You can set it as the Segment Key to accelerate file pruning.

• The final table schema for the `orders` table is as follows:

  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 for table joins.
      event_time_column = 'O_ORDERDATE'--Accelerates file pruning.
    );
  COMMIT;