Hologres:JSONB 形式データの列指向ストレージ

JSONB データ型に列指向ストレージを有効にする

テーブル内の JSONB データ型の列に対して列指向ストレージを有効にするには、次のステートメントを実行します。

-- テーブル内の JSONB データ型の列に対して列指向ストレージを有効にします。
ALTER TABLE <table_name> ALTER COLUMN <column_name> SET (enable_columnar_type = ON);

table_name パラメーターはテーブルの名前を指定し、column_name パラメーターは列の名前を指定します。

DECIMAL 型推論を有効にする

Hologres V2.0.11 以降では、DECIMAL 型のデータの列指向ストレージがサポートされています。JSON データの例:

{
  "name":"Mike",
  "statistical_period":"2023-01-01 00:00:00+08",
  "balance":123.45
}

balance のデータに対して DECIMAL 型推論を有効にすると、データに対して列指向ストレージがサポートされます。ステートメントの例:

-- テーブル内の DECIMAL データ型の列に対して列指向ストレージを有効にします。
ALTER TABLE <table_name> ALTER COLUMN <column_name> SET (enable_decimal = ON);

table_name パラメーターはテーブルの名前を指定し、column_name パラメーターは列の名前を指定します。

JSONB データ型のストレージモードを確認する

JSONB データ型のストレージモードを確認するために使用されるステートメントは、Hologres のバージョンによって異なります。

• Hologres V1.3.37 以降の場合は、次のステートメントを実行します。

  説明

  Hologres V2.0.17 以前では、このステートメントを使用して public スキーマ内のテーブルのストレージモードのみを確認できます。Hologres V2.0.18 以降では、このステートメントを使用してすべてのスキーマ内のテーブルのストレージモードを確認できます。

  -- Hologres V2.0.17 以前では、このステートメントを使用して public スキーマ内のテーブルのストレージモードのみを確認できます。Hologres V2.0.18 以降では、このステートメントを使用してすべてのスキーマ内のテーブルのストレージモードを確認できます。
  SELECT * FROM hologres.hg_column_options WHERE schema_name='<schema_name>' AND table_name = '<table_name>';

  schema_name パラメーターはスキーマ名を指定し、table_name パラメーターはテーブル名を指定します。

• Hologres V1.3.10 から V1.1.36 の場合は、次のステートメントを実行します。

  SELECT DISTINCT
      a.attnum as num,
      a.attname as name,
      format_type(a.atttypid, a.atttypmod) as type,
      a.attnotnull as notnull, 
      com.description as comment,
      coalesce(i.indisprimary,false) as primary_key,
      def.adsrc as default,
      a.attoptions
  FROM pg_attribute a 
  JOIN pg_class pgc ON pgc.oid = a.attrelid
  LEFT JOIN pg_index i ON 
      (pgc.oid = i.indrelid AND i.indkey[0] = a.attnum)
  LEFT JOIN pg_description com on 
      (pgc.oid = com.objoid AND a.attnum = com.objsubid)
  LEFT JOIN pg_attrdef def ON 
      (a.attrelid = def.adrelid AND a.attnum = def.adnum)
  WHERE a.attnum > 0 AND pgc.oid = a.attrelid
  AND pg_table_is_visible(pgc.oid)
  AND NOT a.attisdropped
  AND pgc.relname = '<table_name>' 
  ORDER BY a.attnum;

  table_name パラメーターはテーブル名を指定します。

• 結果例

  次の図では、列の attoptions または option プロパティは enable_columnar_type = ON であり、この列に対して列指向ストレージが有効になっていることを示しています。

  

JSONB データ型に列指向ストレージを無効にする

テーブル内の JSONB データ型の列に対して列指向ストレージを無効にするには、次のステートメントを実行します。

-- テーブル内の JSONB データ型の列に対して列指向ストレージを無効にします。
ALTER TABLE <table_name> ALTER COLUMN <column_name> SET (enable_columnar_type = OFF);

table_name パラメーターはテーブルの名前を指定し、column_name パラメーターは列の名前を指定します。

DECIMAL 型推論を無効にする

テーブルの列に対して DECIMAL 型推論を無効にするには、次のステートメントを実行します。

-- テーブル内の DECIMAL 型の列に対して列指向ストレージを無効にします。
ALTER TABLE <table_name> ALTER COLUMN <column_name> SET (enable_decimal = OFF);

table_name パラメーターはテーブルの名前を指定し、column_name パラメーターは列の名前を指定します。

ビットマップインデックスを作成する

Hologres では、bitmap_columns プロパティはビットマップインデックスを指定します。このプロパティは、データストレージとは独立したインデックススキーマを使用します。bitmap_columns プロパティを使用して、ビットマップベクトル構造に基づく等価比較を高速化できます。ビットマップインデックスは、保存ファイル内の指定された値と等しいデータをフィルタリングするのに役立ちます。したがって、bitmap_columns プロパティはポイントクエリに適用できます。Hologres V2.0 以降では、列指向ストレージモードで保存されている JSONB 形式のデータを持つ列にビットマップインデックスを作成できます。JSONB データ型に対して列指向ストレージが有効になると、システムは INT、INT[]、BIGINT、BIGINT[]、TEXT、TEXT[]、および JSONB のデータ型のデータを解析できます。ビットマップインデックス機能が有効になると、システムは解析された INT、INT[]、BIGINT、BIGINT[]、TEXT、および TEXT[] データ型のデータを持つ列にビットマップインデックスを作成します。

構文:

call set_table_property('<table_name>', 'bitmap_columns', '[<columnName>{:[on|off]}[,...]]');

次の表に、上記の構文のパラメーターを示します。

例

1. テーブルを作成します。

   DROP TABLE IF EXISTS user_tags;
   
   -- テーブルを作成します。
   BEGIN;
   CREATE TABLE IF NOT EXISTS user_tags (
       ds timestamptz,
       tags jsonb
   );
   COMMIT;

2. JSONB データ型の tags 列に対して列指向ストレージを有効にします。

   ALTER TABLE user_tags ALTER COLUMN tags SET (enable_columnar_type = ON);

3. tags 列のストレージモードを表示します。

   select * from hologres.hg_column_options where table_name = 'user_tags';

   次の戻り値では、tags 列の options プロパティは enable_columnar_type = on であり、列指向ストレージが有効になっていることを示しています。

    schema_name | table_name | column_id | column_name |       column_type        | notnull | comment | default |          options
   -------------+------------+-----------+-------------+--------------------------+---------+---------+---------+---------------------------
    public      | user_tags  |         1 | ds          | timestamp with time zone | f       |         |         |
    public      | user_tags  |         2 | tags        | jsonb                    | f       |         |         | {enable_columnar_type=on}
   (2 rows)

4. データをインポートします。

   INSERT INTO user_tags (ds, tags)
   SELECT
       '2022-01-01 00:00:00+08'
       , ('{"id":' || i || ',"first_name" :"Sig",  "gender" :"Male"}')::jsonb
   FROM
       generate_series(1, 10001) i;

5. オプション。データをディスクに強制的に書き込みます。

   データがディスクに書き込まれると、Hologres は JSONB 形式のデータに対して列指向ストレージを有効にします。できるだけ早くストレージ効果を確認するには、次のステートメントを実行してデータをディスクに強制的に書き込みます。

   VACUUM user_tags;

6. テーブルからデータをクエリします。

   id 列の値が 10 である first_name 列のデータを照会するには、次のステートメントを実行します。

   SELECT
       (tags -> 'first_name')::text AS first_name  -- first_name をテキストとして選択
   FROM
       user_tags  -- user_tags テーブルから
   WHERE (tags -> 'id')::int = 10; -- ID が 10 の場合

7. 実行プランに基づいて、JSONB データ型に対して列指向ストレージが有効になっているかどうかを確認します。

   -- 詳細な統計情報をクエリします。
   SET hg_experimental_show_execution_statistics_in_explain = ON;
   -- 実行プランをクエリします。
   EXPLAIN ANALYZE
   SELECT
       (tags -> 'first_name')::text AS first_name
   FROM
       user_tags
   WHERE (tags -> 'id')::int = 10;

   次の図では、columnar_access_used が返されます。これは、JSONB データ型に対して列指向ストレージが有効になっていることを示します。

   

8. 手順 6 のクエリでは、tags 列にビットマップインデックスを作成して、キーベースのポイントクエリの効率を向上させることができます。サンプルステートメント：

   call set_table_property('user_tags', 'bitmap_columns', 'tags');

9. 実行プランを表示して、ビットマップインデックスが使用されているかどうかを確認します。

   -- 実行プランを表示します。
   EXPLAIN ANALYZE
   SELECT
       (tags -> 'first_name')::text AS first_name
   FROM
       user_tags
   WHERE (tags -> 'id')::int = 10;

   次の図は、返された結果を示しています。

   返された結果には、ビットマップインデックスが使用されていることを示す bitmap_used が含まれています。

JSONB データ型の完全な列を必要とするクエリ

Hologres が提供する JSONB データ型の列指向ストレージは、ほとんどのシナリオで優れたパフォーマンスを発揮します。ただし、JSONB データ型の完全な列を必要とするクエリの場合、JSONB 形式のデータの元のストレージモードの方がパフォーマンスが優れています。SQL ステートメントの例:

-- Execute the following data definition language (DDL) statement to create a table:  -- 次のデータ定義言語 (DDL) ステートメントを実行して、テーブルを作成します。
CREATE TABLE TBL(key int, json_data jsonb); 
SELECT json_data FROM TBL WHERE key = 123;
SELECT * FROM TBL limit 10;

JSONB データ型の列指向ストレージは、基盤となるレイヤーで実装されています。JSONB データ型の完全な列を取得する場合、システムは列指向ストレージのデータを元の JSONB 形式のデータに変換する必要があります。

この変換プロセスでは、大量の I/O 操作が発生し、オーバーヘッドが高くなります。多数の列と大量のデータが関係する場合、変換によってパフォーマンスが低下する可能性があります。このシナリオでは、JSONB データ型の列指向ストレージを有効にしないことをお勧めします。

JSONB データ型の非常にスパースなデータのストレージ

列指向ストレージに格納する JSONB 形式のデータにスパースフィールドが含まれている場合、Hologres は、過剰な数の列が生成されないように、holo.remaining という名前の列にスパースフィールドを配置します。すべての JSONB 形式のフィールドがスパースである場合、たとえば、各フィールドが 1 回だけ出現する場合、列指向ストレージは有効になりません。これは、すべてのフィールドが holo.remaining 列に配置されるためです。この場合、JSONB データ型の列指向ストレージでは、クエリのパフォーマンスは向上しません。

複雑なネスト構造を持つ JSONB 形式のデータのストレージ

この例では、JSONB 形式のデータのルートノードは配列であり、異なる構造の JSONB 形式のデータが含まれています。Hologres は、複雑なネスト構造を持つデータを 1 つの列に格納します。この場合、JSONB データ型の列指向ストレージでは、クエリのパフォーマンスは向上しません。

'[
  {"key1": "value1"}, 
  {"key2": 123},
  {"key3": 123.01}
]'

低速クエリの診断

JSONB データ型に対して列指向ストレージを有効にした後にクエリのパフォーマンスが低下した場合は、クエリで JSONB データ型の完全な列が必要かどうかを確認します。 複雑な SQL ステートメントの場合は、Explain Analyze ステートメントを実行して、クエリで JSONB データ型の完全な列が必要かどうかを確認できます。 サンプルステートメント:

CREATE TABLE TBL(key int, json_data json); -- テーブルを作成します。
ALTER TABLE TBL ALTER COLUMN json_data SET (enable_columnar_type = on);
Explain Analyze SELECT json_data FROM TBL WHERE key = 123;

Explain Analyze ステートメントの戻り値には、Hint キーワードが含まれています。 Hint キーワードに次の情報が含まれている場合、クエリでは JSONB データ型の完全な列が必要です。 その結果、クエリのパフォーマンスが低下します。

Column 'json_data' has enabled columnar jsonb, but the query scanned the entire Jsonb value

パフォーマンスの高い SQL 構文

• JSONB 形式のデータは、さまざまな構文を使用して TEXT 形式に変換できます。この例では、json_data 列の name 属性を取得する場合、->> 演算子を使用するとパフォーマンスが向上します。

  -- パフォーマンスが高い
  SELECT json_data->>'name' FROM tbl; 
  -- 通常のパフォーマンス
  SELECT (json_data->'name')::text FROM tbl;

• JSONB 形式のフィールドに TEXT 配列が含まれていて、TEXT 配列に指定された値が含まれているかどうかを確認する場合は、次の構文を使用することをお勧めします。

  SELECT key FROM tbl WHERE jsonb_to_textarray(json_data->'phones') && ARRAY['123456'];