Hologres:Get started with Hologres indexes

TPC-H Q1 query

The TPC-H Q1 query performs aggregations and filtering on specific columns of the lineitem table. It includes the following condition:

l_shipdate <=: This is a filter condition. To support efficient range filtering and quickly retrieve the required data, you must define an appropriate index.

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

The TPC-H Q4 query primarily joins the lineitem and orders tables. It includes the following conditions:

• o_orderdate >= DATE '1996-07-01': This is a filter condition. To support efficient range filtering and quickly retrieve the required data, you must define an appropriate index.

• l_orderkey = o_orderkey: This is a join condition between the two tables. For best performance, use the same index on both tables to enable a local join, which reduces data shuffling during the operation.

  --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 the Q1 and Q4 queries involve the lineitem table, but they use different columns and conditions.

• For the Q1 query: The query primarily uses l_shipdate for range filtering. The clustering key accelerates range scans by leveraging the sorted order of data within files. Therefore, set l_shipdate as the clustering key. The segment key (event time column) maintains order between files. For a monotonically increasing or decreasing date column, setting it as the segment key enables effective segment key pruning. Therefore, you can also set l_shipdate as the segment key.

• For the Q4 query: The query joins the lineitem table with the orders table on the l_orderkey and o_orderkey columns. The distribution key specifies the data distribution strategy. The system places data with the same key value on the same shard. If two tables are in the same table group and are joined on their distribution key columns, the system automatically distributes matching records to the same shard during data writes. When these tables are joined, the system performs a local join on each node without shuffling data across the network. This avoids runtime data shuffling and redistribution, significantly improving execution efficiency. Therefore, set l_orderkey as the distribution key.

• The final table structure for lineitem 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.
      clustering_key = 'L_SHIPDATE',--Accelerates range filtering.
      event_time_column = 'L_SHIPDATE'--Accelerates segment key pruning.
    );
  COMMIT;

hologres_dataset_tpch_100g.orders

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

• Set the o_orderkey column of the orders table as the distribution key to leverage local join capabilities and improve join query efficiency.

• The o_orderdate column is primarily used for date filtering. Set it as the segment key to accelerate segment key pruning.

• The final table structure for orders 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.
      event_time_column = 'O_ORDERDATE'--Accelerates segment key pruning.
    );
  COMMIT;