Hologres グローバルセカンダリインデックスでクエリ性能を強化 - Hologres - Alibaba Cloud - Hologres

前提条件

お使いの Hologres インスタンスは V4.0 以降である必要があります。お使いのインスタンスのバージョンが V4.0 より前の場合は、インスタンスのアップグレードをご参照ください。

制限事項

グローバルセカンダリインデックスを持つテーブルは、Fixed FE または Fixed Copy を使用したデータ書き込みをサポートしていません。
グローバルセカンダリインデックスのインデックス列は、TEXT、INTEGER、BIGINT、VARCHAR のデータ型のみをサポートします。
グローバルセカンダリインデックスは変更できません。
1 つの列をインデックス列とインクルード列の両方として使用することはできません。
ソーステーブルにはプライマリキーが必要です。
ソーステーブルに time_to_live_in_seconds パラメーターを設定することはできません。
グローバルセカンダリインデックス内のインデックス列とインクルード列の合計数は 512 を超えることはできません。
グローバルセカンダリインデックスは、内部テーブルにのみ作成できます。物理パーティションテーブルおよび論理パーティションテーブルはサポートされていません。
ソーステーブルの列がグローバルセカンダリインデックスの一部である場合、その列を削除または変更することはできません。
グローバルセカンダリインデックスを持つソーステーブルに対して、Table Group の変更やリシャーディングを実行することはできません。
デフォルトでは、グローバルセカンダリインデックスは標準ストレージのみを使用します。
グローバルセカンダリインデックスは、ソーステーブルと同じストレージ形式 (行指向ストレージ、列指向ストレージ、または行列混在ストレージ) を使用します。詳細は以下の通りです：
- ソーステーブルが行指向ストレージを使用している場合、そのグローバルセカンダリインデックスもデフォルトで行指向ストレージを使用します。
- ソーステーブルが列指向ストレージを使用している場合、そのグローバルセカンダリインデックスもデフォルトで列指向ストレージを使用します。
- ソーステーブルが行列混在ストレージを使用している場合、そのグローバルセカンダリインデックスもデフォルトで行列混在ストレージを使用します。

グローバルセカンダリインデックスの作成

構文

CREATE GLOBAL INDEX [ IF NOT EXISTS ] index_name
  ON [schema_name.]table_name (index_column_name [, ...])
  [ INCLUDE (include_column_name[, ...]) ]

パラメーター

パラメーター	必須	説明
index_name	はい	グローバルセカンダリインデックスの名前。
schema_name	いいえ	ソーステーブルのスキーマ名。このパラメーターを指定しない場合、デフォルトのスキーマが使用されます。
table_name	はい	ソーステーブルの名前。
index_column_name	はい	インデックスを作成する列。最良の結果を得るには、非プライマリキーに対するポイントクエリのフィルター条件として使用する列を指定します。
include_column_name	いいえ	グローバルセカンダリインデックスに含める列。

注意事項
- SQL 文を送信すると、システムはインデックスの構築を開始します。CREATE GLOBAL INDEX 操作は、インデックスが構築され、利用可能になった後に完了します。
- インデックスを構築すると追加のデータが書き込まれ、書き込みパフォーマンスに影響します。この影響は、ソーステーブルのデータ量とインデックス内の列数に応じて大きくなります。
- グローバルセカンダリインデックスは、常にソーステーブルと同じスキーマに作成されます。インデックスに異なるスキーマを指定することはできません。
- クエリがグローバルセカンダリインデックスを使用できるのは、参照されるすべての列が、インデックス列またはインクルード列としてインデックスに含まれている場合のみです。

グローバルセカンダリインデックスの削除

構文
```
DROP INDEX [schema_name.]index_name
```

パラメーター

パラメーター	必須	説明
schema_name	いいえ	グローバルセカンダリインデックスのスキーマ名。このパラメーターを指定しない場合、デフォルトのスキーマが使用されます。
index_name	はい	グローバルセカンダリインデックスの名前。

グローバルセカンダリインデックスの表示

すべてのグローバルセカンダリインデックスを表示する

SELECT 
    n.nspname AS table_namespace,
    t.relname AS table_name,
    i.relname AS index_name
FROM 
    pg_class t
JOIN 
    pg_index ix ON t.oid = ix.indrelid
JOIN 
    pg_class i ON i.oid = ix.indexrelid
JOIN 
    pg_am am ON am.oid = i.relam
JOIN
    pg_namespace n ON n.oid = t.relnamespace 
WHERE 
    t.relkind = 'r'  -- 通常のテーブルのみをクエリします。
    AND am.amname = 'globalindex'

グローバルセカンダリインデックスのストレージサイズを表示する

ここで、global_index_name はグローバルセカンダリインデックスの名前です。
```
SELECT pg_relation_size('schema_name.global_index_name');
```

インクルード列を表示する

SELECT pg_catalog.pg_get_indexdef('global_index_name'::regclass, 0, true);

例

注文アプリケーションで、特定の注文優先度に基づいてデータを頻繁にクエリする機能が必要だとします。次の例では orders テーブルを使用します：

列	型	説明
O_ORDERKEY	BIGINT	注文 ID (プライマリキー)。
O_CUSTKEY	INT	顧客 ID (CUSTOMER テーブルを参照する外部キー)。
O_ORDERSTATUS	CHAR(1)	注文ステータス ('F' = 完了、'O' = オープン、'P' = 処理中)。
O_TOTALPRICE	DECIMAL(15,2)	注文の合計金額。
O_ORDERDATE	DATE	注文が作成された日付。
O_ORDERPRIORITY	TEXT	注文の優先度 ('1-URGENT'、'2-HIGH' など)。
O_CLERK	TEXT	注文を処理した従業員の ID。
O_SHIPPRIORITY	INT	配送の優先度。値が大きいほど優先度が高くなります。
O_COMMENT	TEXT	注文に関するコメント。

サンプルテーブル orders を作成するための SQL 文は次のとおりです。

CREATE TABLE ORDERS
(
    O_ORDERKEY      BIGINT      NOT NULL PRIMARY KEY,
    O_CUSTKEY       INT         NOT NULL,
    O_ORDERSTATUS   CHAR(1)         NOT NULL,
    O_TOTALPRICE    DECIMAL(15,2) NOT NULL,
    O_ORDERDATE     DATE NOT NULL,
    O_ORDERPRIORITY TEXT        NOT NULL,
    O_CLERK         TEXT        NOT NULL,
    O_SHIPPRIORITY  INT         NOT NULL,
    O_COMMENT       TEXT        NOT NULL
) WITH (
  orientation='row,column',
  segment_key='O_ORDERDATE',
  distribution_key='O_ORDERKEY',
  bitmap_columns='O_ORDERSTATUS,O_ORDERPRIORITY,O_CLERK,O_SHIPPRIORITY',
  dictionary_encoding_columns='o_comment:off,o_orderpriority,o_clerk'
);
COMMENT ON TABLE ORDERS IS '基本的な注文情報とステータスを格納するメインの注文テーブル。';
COMMENT ON COLUMN ORDERS.O_ORDERKEY IS '注文 ID (プライマリキー)。';
COMMENT ON COLUMN ORDERS.O_CUSTKEY IS '顧客 ID (CUSTOMER テーブルを参照する外部キー)。';
COMMENT ON COLUMN ORDERS.O_ORDERSTATUS IS '注文ステータス (''F'' = 完了、''O'' = オープン、''P'' = 処理中)。';
COMMENT ON COLUMN ORDERS.O_TOTALPRICE IS '注文の合計金額。';
COMMENT ON COLUMN ORDERS.O_ORDERDATE IS '注文が作成された日付。';
COMMENT ON COLUMN ORDERS.O_ORDERPRIORITY IS '注文の優先度 (''1-URGENT''、''2-HIGH'' など)。';
COMMENT ON COLUMN ORDERS.O_CLERK IS '注文を処理した従業員の ID。';
COMMENT ON COLUMN ORDERS.O_SHIPPRIORITY IS '配送の優先度。値が大きいほど優先度が高くなります。';
COMMENT ON COLUMN ORDERS.O_COMMENT IS '注文に関するコメント。';

次のようなクエリを頻繁に実行して、特定の優先度の注文を取得する場合：

SELECT 
  O_ORDERKEY,
  O_CUSTKEY,
  O_ORDERSTATUS,
  O_TOTALPRICE,
  O_ORDERDATE,
  O_ORDERPRIORITY,
  O_CLERK,
  O_SHIPPRIORITY,
  O_COMMENT
FROM ORDERS
WHERE O_ORDERPRIORITY='1-URGENT'

EXPLAIN を使用して、SQL 文の実行計画を確認できます：

EXPLAIN
SELECT 
  O_ORDERKEY,
  O_CUSTKEY,
  O_ORDERSTATUS,
  O_TOTALPRICE,
  O_ORDERDATE,
  O_ORDERPRIORITY,
  O_CLERK,
  O_SHIPPRIORITY,
  O_COMMENT
FROM ORDERS
WHERE O_ORDERPRIORITY='1-URGENT'

QUERY PLAN
Gather  (cost=0.00..1.00 rows=1 width=53)
  ->  Local Gather  (cost=0.00..1.00 rows=1 width=53)
        ->  Index Scan using Clustering_index on orders  (cost=0.00..1.00 rows=1 width=53)
              Bitmap Filter: (o_orderpriority = '1-URGENT'::text)
Query Queue: init_warehouse.default_queue
Optimizer: HQO version 4.0.0

O_ORDERPRIORITY 列にインデックスを追加することで、より効率的なクエリを実行できます。

返された実行計画から、クエリがビットマップインデックスを使用したことがわかります。ビットマップインデックスによるクエリパフォーマンスの向上は限定的であるため、orders テーブルの O_ORDERPRIORITY 列にインデックスを追加して、より高い QPS を達成できます。
```
CREATE GLOBAL INDEX idx_orders ON orders(O_ORDERPRIORITY)
INCLUDE (
  O_CUSTKEY,
  O_ORDERSTATUS,
  O_TOTALPRICE,
  O_ORDERDATE,
  O_CLERK,
  O_SHIPPRIORITY,
  O_COMMENT
  );
```
インデックスを追加した後、再度 EXPLAIN 文を実行して実行計画を確認します：
```
QUERY PLAN
Local Gather  (cost=0.00..1.76 rows=3035601 width=99)
  ->  Index Scan using Clustering_index on idx_orders  (cost=0.00..1.54 rows=3035601 width=99)
        Shard Prune: Eagerly
        Shards selected: 1 out of 20
        Cluster Filter: (o_orderpriority = '1-URGENT'::text)
Query Queue: init_warehouse.default_queue
Optimizer: HQO version 4.0.0
```
この時点で、実行計画では Index Scan using Clustering_index on 操作の対象がグローバルセカンダリインデックス idx_orders であり、シャードプルーニングが使用されていることがわかります。その結果、QPS が効果的に向上します。

固定プランを使用して QPS をさらに向上させます。

SET hg_experimental_enable_fixed_dispatcher_for_scan = true;

実行計画にはインデックス上の FixedSelectNode が表示され、クエリが固定プランで最適化されて最高のパフォーマンスを発揮していることが確認できます。

SET hg_experimental_enable_fixed_dispatcher_for_scan = true;
EXPLAIN
SELECT
    O_ORDERKEY,
    O_CUSTKEY,
    O_ORDERSTATUS,
    O_TOTALPRICE,
    O_ORDERDATE,
    O_ORDERPRIORITY,
    O_CLERK,
    O_SHIPPRIORITY,
    O_COMMENT
FROM ORDERS
WHERE O_ORDERPRIORITY='1-URGENT'
QUERY PLAN
FixedSelectNode on idx_orders  (cost=0.00..0.00 rows=0 width=0)
  Qual: o_orderpriority =>  ranges: {['1-URGENT'::text,'1-URGENT'::text]}
Optimizer: HQO version 4.0.0