实时数仓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'];