グローバルセカンダリインデックス - Hologres - Alibaba Cloud ドキュメントセンター

Hologres V4.0 以降では、グローバルセカンダリインデックスがサポートされています。この機能は、プライマリキー以外の列に対して効率的なキー値クエリが必要なシナリオに最適です。主キーインデックスとは異なり、セカンダリインデックスはデータの一意性を要求しませんが、特定の列に対するクエリパフォーマンスを大幅に向上させることができます。

前提条件

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

制限事項

グローバルセカンダリインデックス列は、TEXT および INTEGER データ型のみをサポートします。
グローバルセカンダリインデックスは変更できません。
インデックスキー列と INCLUDE 列に重複する列を含めることはできません。
プライマリテーブルにはプライマリキーが必要です。
グローバルセカンダリインデックス内のインデックスキー列と INCLUDE 列の合計数は 256 を超えることはできません。
標準内部テーブルのみがグローバルセカンダリインデックスをサポートします。物理パーティションテーブルおよび論理パーティションテーブルはこの機能をサポートしません。
プライマリテーブルの列がグローバルセカンダリインデックスの一部である場合、その列を削除または変更することはできません。
グローバルセカンダリインデックスを持つプライマリテーブルのテーブルグループを変更したり、リシャーディングしたりすることはできません。
デフォルトでは、グローバルセカンダリインデックスは標準ストレージ (ホットストレージ) のみをサポートします。
グローバルセカンダリインデックスのストレージモード (ローストア、列ストア、または行列ハイブリッドストア) は、そのプライマリテーブルと同じです。詳細は次のとおりです：
- プライマリテーブルがローストアを使用する場合、そのグローバルセカンダリインデックスもデフォルトでローストアを使用します。
- プライマリテーブルが列ストアを使用する場合、そのグローバルセカンダリインデックスもデフォルトで列ストアを使用します。
- プライマリテーブルが行列ハイブリッドストアを使用する場合、そのグローバルセカンダリインデックスもデフォルトで行列ハイブリッドストアを使用します。

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

構文

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	いいえ	グローバルセカンダリインデックスに含める列。

使用上の注意
- CREATE 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	注文番号 (プライマリキー)。
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	注文コメント。

次の SQL 文は、サンプルの orders テーブルを作成します。

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 '注文番号 (プライマリキー)';
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 '注文コメント';

次の SQL 文は、特定の注文優先度のデータを頻繁にクエリするために使用されます：

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 列にインデックスを追加して、クエリ効率を向上させます。
上記の実行計画は、クエリがビットマップインデックスを使用していることを示しています。ビットマップインデックスは、クエリパフォーマンスを限定的にしか向上させません。より高いクエリ/秒 (QPS) が必要な場合は、orders テーブルの O_ORDERPRIORITY 列にインデックスを追加できます。
```
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;
```
実行計画を表示して、固定プランを使用したポイントクエリの最適化を確認できます。