Hologres：列式JSONB

開啟列式JSONB

通過以下語句對某張表的某個JSONB列開啟JSONB列存最佳化。

-- 開啟xx表的xx列的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推導後，也支援按照列存最佳化。開啟方法如下：

-- 開啟xx表的xx列的Decimal列存最佳化
ALTER TABLE <table_name> ALTER COLUMN <column_name> SET (enable_decimal = ON);

table_name為表名稱；column_name為列名稱。

查看某張表的列式JSONB開啟情況

通過以下語句查看某張表的列式JSONB開啟情況。

• Hologres V1.3.37及以上版本支援如下命令。

  說明

  該命令在Hologres V2.0.17及以下版本只支援看publicSchema下的表，從2.0.18版本開始支援查看其他Schema下的表開啟情況。

  --2.0.17及以下版本僅支援查看public schema的表，2.0.18版本可以查詢其他schema的表
  SELECT * FROM hologres.hg_column_options WHERE schema_name='<schema_name>' AND table_name = '<table_name>';

  其中schema_name為Schema名稱，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列存最佳化。

-- 關閉xx表的xx列的JSONB列式儲存最佳化
ALTER TABLE <table_name> ALTER COLUMN <column_name> SET (enable_columnar_type = OFF);

table_name為表名稱；column_name為列名稱。

關閉Decimal類型推導

通過以下命令關閉某張表的某個列的Decimal類型列存最佳化推導。

-- 關閉xx表的xx列的Decimal列存最佳化
ALTER TABLE <table_name> ALTER COLUMN <column_name> SET (enable_decimal = OFF);

table_name為表名稱；column_name為列名稱。

設定Bitmap索引

在Hologres中，bitmap_columns屬性指定位元影像索引，是資料存放區之外的獨立索引結構，以位元影像向量結構加速等值比較情境，能夠對檔案塊內的資料進行快速的等值過濾，適用於等值過濾查詢的情境。Hologres從V2.0版本開始支援對開啟了列存的JSONB設定Bitmap索引。開啟列存JSONB後，系統會解析出int、int[]、bigint、bigint[]、text、text[]、jsonb這7種資料類型。開啟Bitmap索引後，系統會對推導成int、int[]、bigint、bigint[]、text、text[]類型的資料建立Bitmap索引。

使用文法如下：

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. 開啟tags列的JSONB列存最佳化。

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

3. 查看JSONB列式儲存開啟情況。

   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. （可選）強制觸發資料落盤。

   寫入資料後，系統會在資料落盤時進行JSONB的列存最佳化，為了儘快看到效果，此處使用如下後台命令，強制觸發資料落盤。

   VACUUM user_tags;

6. 範例查詢。

   使用如下SQL查詢id為10的first_name。

   SELECT
       (tags -> 'first_name')::text AS first_name
   FROM
       user_tags
   WHERE (tags -> 'id')::int = 10;

7. 通過執行計畫檢查列存最佳化是否使用。

   -- 顯示詳細的統計資訊
   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設定Bitmap索引，以提升針對某個key等值查詢的效率，設定方法如下。

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

9. 通過執行計畫檢查Bitmap索引是否生效。

   -- 查看執行計畫
   EXPLAIN ANALYZE
   SELECT
       (tags -> 'first_name')::text AS first_name
   FROM
       user_tags
   WHERE (tags -> 'id')::int = 10;

   返回結果如下：

   結果中有bitmap_used，表示使用了Bitmap索引。

查詢會帶出完整JSONB列

Hologres的列式JSONB方案對於大部分使用情境都有比較好的最佳化效果，需要注意的是：對於查詢結果需要帶出完整JSONB列的情境，效能相較於直接儲存原始格式的JSONB會有降低，比如以下SQL：

--建表DDL
CREATE TABLE TBL(key int, json_data jsonb); 
SELECT json_data FROM TBL WHERE key = 123;
SELECT * FROM TBL limit 10;

原因在於底層已經將JSONB資料轉成了列式儲存，所以當需要查詢出完整JSON資料的時候，就需要將那些已經列式儲存的資料再重新拼裝成原來的JSONB格式：

這個步驟就會產生大量的IO以及轉換開銷，如果涉及到的資料量很大，列數又很多，甚至可能成為效能瓶頸，所以此情境下建議不要開啟列式最佳化。

極稀疏的JSONB資料

當Hologres列式化JSONB資料遇到稀疏的欄位時，Hologres會將這部分欄位合并至一個叫做holo.remaining的特殊列中，以此來避免列數膨脹的問題。所以如果JSONB資料包含的都是稀疏欄位，比如極端情況下每個欄位都只會出現一次，那麼列式化將不會起效，因為所有欄位都是稀疏的，那麼所有欄位都會合并至holo.remaining欄位，等於沒有進行列式化，這種情況下不會有查詢效能的提升。

包含複雜嵌套結構的JSONB資料

如下JSONB資料的根節點就是一個數組，且該數組中存放的是非同構的JSONB資料，當前Hologres在列式化JSONB資料的時候，遇到類似複雜的嵌套結構，會將這部分資料退化成一列，所以此JSONB資料開啟列式JSONB最佳化，將不會帶來明顯的查詢效能收益。

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

慢查詢診斷

如果開啟列式JSONB後，發現查詢效能反而比不開啟效能還要差很多，首先排查查詢是否帶出了完整JSONB列，如果SQL過於複雜，可以使用Explain Analyze方式來診斷，SQL命令樣本如下：

CREATE TABLE TBL(key int, json_data json); --建表DDL
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;

• 如果JSON的某個欄位中儲存的是TEXT數組，需要判斷數組中是否包含特定值，建議使用以下寫法：

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