Hologres:Columnar JSONB

Columnar JSONB'yi etkinleştirme

Bir tablodaki belirli bir JSONB sütunu için sütun yönelimli depolama optimizasyonunu etkinleştirmek üzere aşağıdaki deyimi kullanın.

-- Enable column-oriented storage optimization for a specific column in a specific table.
ALTER TABLE <table_name> ALTER COLUMN <column_name> SET (enable_columnar_type = ON);

table_name tablo adıdır. column_name sütun adıdır.

DECIMAL tür çıkarımını etkinleştirme

Hologres V2.0.11 ve sonraki sürümler DECIMAL verileri için sütun yönelimli depolama optimizasyonunu destekler. Örnek olarak aşağıdaki JSON verilerini ele alalım:

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

DECIMAL tür çıkarımını etkinleştirdiğinizde balance değeri de sütun yönelimli optimizasyonu destekler. Bu özelliği etkinleştirmek için aşağıdaki deyimi kullanın:

-- Enable column-oriented optimization for DECIMAL values in a specific column in a specific table.
ALTER TABLE <table_name> ALTER COLUMN <column_name> SET (enable_decimal = ON);

table_name tablo adıdır. column_name sütun adıdır.

Columnar JSONB durumunu kontrol etme

Bir tablonun Columnar JSONB durumunu kontrol etmek için aşağıdaki deyimleri kullanın.

• Aşağıdaki komut Hologres V1.3.37 ve sonraki sürümlerde desteklenir.

  Not

  Hologres V2.0.17 ve önceki sürümlerde bu komut yalnızca public şemasındaki tabloları gösterebilir. V2.0.18'den itibaren diğer şemalardaki tabloların durumunu da gösterebilir.

  -- In V2.0.17 and earlier, you can query only tables in the public schema. In V2.0.18 and later, you can query tables in other schemas.
  SELECT * FROM hologres.hg_column_options WHERE schema_name='<schema_name>' AND table_name = '<table_name>';

  schema_name şema adı table_name ise tablo adıdır.

• Hologres V1.3.10 ile V1.3.36 arasında aşağıdaki komutu kullanın.

  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 tablo adıdır.

• Örnek sonuç:

  Sonuçta bir sütunun attoptions veya option özelliği enable_columnar_type = ON ise yapılandırmanın başarılı olduğu anlamına gelir.

   num | name |           type           | notnull | comment | primary_key | default |          attoptions
  ------+------+--------------------------+---------+---------+-------------+---------+------------------------------
     1 | ds   | timestamp with time zone | f       |         | f           |         |
     2 | tags | jsonb                    | f       |         | f           |         | {enable_columnar_type=on}
  (2 rows)

Columnar JSONB'yi devre dışı bırakma

Bir tablodaki belirli bir JSONB sütunu için sütun yönelimli depolama optimizasyonunu devre dışı bırakmak üzere aşağıdaki komutu kullanın.

-- Disable column-oriented storage optimization for a specific column in a specific table.
ALTER TABLE <table_name> ALTER COLUMN <column_name> SET (enable_columnar_type = OFF);

table_name tablo adıdır. column_name sütun adıdır.

DECIMAL tür çıkarımını devre dışı bırakma

Belirli bir sütun için DECIMAL tür çıkarımını devre dışı bırakmak üzere aşağıdaki komutu kullanın.

-- Disable column-oriented optimization for DECIMAL values in a specific column in a specific table.
ALTER TABLE <table_name> ALTER COLUMN <column_name> SET (enable_decimal = OFF);

table_name tablo adıdır. column_name sütun adıdır.

Bitmap dizini ayarlama

Hologres'te bitmap_columns özelliği bir bitmap dizinini belirtir. Bu dizin veri depolamadan bağımsız ayrı bir dizin yapısıdır. Eşitlik karşılaştırmalarını hızlandırmak için bitmap vektör yapısını kullanarak dosya blokları içindeki verilerin hızlı eşitlik filtrelemesine olanak tanır. Hologres V2.0'dan itibaren sütun yönelimli depolama etkinleştirilmiş JSONB sütunları için bitmap dizini ayarlamayı destekler. Columnar JSONB etkinleştirildiğinde sistem verileri int int[] bigint bigint[] text text[] ve jsonb olmak üzere yedi veri türüne ayrıştırır. Bitmap dizini etkinleştirildiğinde sistem int int[] bigint bigint[] text ve text[] türlerine çıkarılan veriler için bir bitmap dizini oluşturur.

Sözdizimi aşağıdaki gibidir:

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

Parametreler:

Örnek

1. Bir tablo oluşturun.

   DROP TABLE IF EXISTS user_tags;
   -- Create a data table
   BEGIN;
   CREATE TABLE IF NOT EXISTS user_tags (
       ds timestamptz,
       tags jsonb
   );
   COMMIT;

2. tags sütunu için sütun yönelimli depolama optimizasyonunu etkinleştirin.

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

3. Columnar JSONB depolama durumunu kontrol edin.

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

   Aşağıdaki sonuçta tags satırının options özelliği {enable_columnar_type=on} şeklindedir. Bu durum yapılandırmanın başarılı olduğunu gösterir.

    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. Verileri aktarın.

   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. (İsteğe bağlı) Veri boşaltmasını zorlayın.

   Veriler yazıldıktan sonra sistem veri boşaltması sırasında Columnar JSONB optimizasyonunu gerçekleştirir. Etkiyi hemen görmek için aşağıdaki komutu çalıştırarak veri boşaltmasını zorlayın.

   VACUUM user_tags;

6. Örnek bir sorgu çalıştırın.

   id değeri 10 olan first_name değerini sorgulamak için aşağıdaki SQL deyimini çalıştırın.

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

7. Sorgunun sütun yönelimli optimizasyonu kullandığını doğrulamak için yürütme planını kontrol edin.

   -- Show detailed statistics.
   SET hg_experimental_show_execution_statistics_in_explain = ON;
   -- View the execution plan.
   EXPLAIN ANALYZE
   SELECT
       (tags -> 'first_name')::text AS first_name
   FROM
       user_tags
   WHERE (tags -> 'id')::int = 10;

   Sonuçta columnar_access_used ifadesi görünüyorsa Columnar JSONB optimizasyonu kullanılmış demektir.

8. 6. Adımdaki sorgu için belirli bir anahtarın eşitlik sorgularının verimliliğini artırmak amacıyla aşağıdaki komutu kullanarak tags sütununda bir bitmap dizini de ayarlayabilirsiniz:

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

9. Bitmap dizininin etkili olduğunu doğrulamak için yürütme planını kontrol edin.

   -- View the execution plan.
   EXPLAIN ANALYZE
   SELECT
       (tags -> 'first_name')::text AS first_name
   FROM
       user_tags
   WHERE (tags -> 'id')::int = 10;

   Sonuç aşağıdaki gibidir:

   QUERY PLAN
   Gather  (cost=0.00..6.42 rows=3334 width=8)
   [2:1 id=100002 dop=1 time=7/7/7ms rows=1(1/1/1) mem=584/584/584B open=0/0/0ms get_next=7/7/7ms]
    -> Local Gather  (cost=0.00..6.30 rows=3334 width=8)
        [id=6 dop=2 time=6/4/3ms rows=1(1/0/0) mem=584/584/584B open=0/0/0ms get_next=6/4/3ms pull_dop=0/0/0]
        -> Decode  (cost=0.00..6.30 rows=3334 width=8)
            [id=4 dop=2 time=7/5/3ms rows=1(1/0/0) mem=0/0/0B open=7/5/3ms get_next=0/0/0ms]
            -> Project  (cost=0.00..6.20 rows=3334 width=8)
                [id=3 dop=2 time=7/5/3ms rows=1(1/0/0) mem=2/2/2KB open=7/5/3ms get_next=0/0/0ms]
                -> Seq Scan on user_tags  (cost=0.00..5.18 rows=3334 width=8)
                    Filter: (int4((tags -> 'id'::text)) = 10)
                    [id=2 dop=2 time=7/5/3ms rows=1(1/0/0) mem=260/160/60KB open=7/5/3ms get_next=0/0/0ms scan_rows=10001(8192/5000/1809) bitmap_used=1]
   ADVICE:
   [node id : 2]  Table user_tags misses bitmap index: ColRef_0012.

   Sonuçta bitmap_used ifadesi görünüyorsa bitmap dizini kullanılmış demektir.

Tüm JSONB sütununun döndürülmesi

Hologres Columnar JSONB çoğu kullanım durumu için iyi bir optimizasyon sağlar. Ancak sorgu sonucunun tüm JSONB sütununu içermesi gereken senaryolarda performans verilerin orijinal JSONB biçiminde depolanmasına kıyasla daha düşük olabilir. Örneğin aşağıdaki SQL deyimlerini ele alalım:

-- DDL for creating the table
CREATE TABLE TBL(key int, json_data jsonb); 
SELECT json_data FROM TBL WHERE key = 123;
SELECT * FROM TBL limit 10;

Bu performans düşüşü alt katmanda JSONB verilerinin zaten sütun yönelimli depolamaya dönüştürülmüş olmasından kaynaklanır. Bu nedenle eksiksiz JSON verilerini sorgulamanız gerektiğinde sistem sütunlu verileri yeniden orijinal JSONB biçiminde birleştirmek zorunda kalır:

Bu adım önemli I/O ve dönüşüm yükü oluşturur. Veri hacmi büyük ve sütun sayısı yüksekse bu süreç bir performans darboğazına dönüşebilir. Bu nedenle bu senaryoda sütun yönelimli optimizasyonu etkinleştirmemenizi öneririz.

Son derece seyrek JSONB verileri

Hologres JSONB verilerini sütun yönelimli biçime dönüştürürken seyrek bir alanla karşılaştığında sütun sayısının patlamasını önlemek için bu alanları holo.remaining adlı özel bir sütunda birleştirir. Bu nedenle JSONB verileri tamamen seyrek alanlardan oluşuyorsa (örneğin her alanın yalnızca bir kez göründüğü uç bir durumda) sütunlu dönüşüm etkili olmaz. Tüm alanlar seyrek olduğu için hepsi holo.remaining sütununda birleştirilir ve gerçek bir sütunlu dönüşüm gerçekleşmez. Bu durumda sorgu performansında herhangi bir iyileşme olmaz.

Karmaşık iç içe yapıya sahip JSONB verileri

Aşağıdaki JSONB verilerinde kök düğüm homojen olmayan JSONB verileri içeren bir dizidir. Hologres şu anda JSONB verilerini sütun yönelimli biçime dönüştürürken bu tür karmaşık iç içe yapıları tek bir sütuna indirger. Bu nedenle bu tür JSONB verileri için Columnar JSONB optimizasyonunu etkinleştirmek sorgu performansında önemli bir avantaj sağlamaz.

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

Yavaş sorgu tanılama

Columnar JSONB'yi etkinleştirdikten sonra sorgu performansının kötüleştiğini fark ederseniz öncelikle sorgunun tüm JSONB sütununu döndürüp döndürmediğini kontrol edin. SQL deyimi çok karmaşıksa tanılama için EXPLAIN ANALYZE komutunu kullanabilirsiniz. Örnek bir komut aşağıdaki gibidir:

CREATE TABLE TBL(key int, json_data json); -- DDL for creating the table
ALTER TABLE TBL ALTER COLUMN json_data SET (enable_columnar_type = on);
Explain Analyze SELECT json_data FROM TBL WHERE key = 123;

EXPLAIN ANALYZE sonucu ipucu bilgisi içerir. İpucu aşağıdaki mesajı içeriyorsa sorgu tüm JSONB sütununu döndürmüş ve bu da performans düşüşüne neden olmuştur:

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

Daha verimli SQL sözdizimi

• JSONB alan verilerini TEXT biçimine dönüştürmenin birden çok yolu vardır ancak ->> operatörü daha iyi performans gösterir. Örneğin json_data sütununun name niteliğini almak için:

  -- Better performance
  SELECT json_data->>'name' FROM tbl; 
  -- Average performance
  SELECT (json_data->'name')::text FROM tbl;

• Bir JSON alanı bir TEXT dizisi depoluyorsa ve dizinin belirli bir değer içerip içermediğini kontrol etmeniz gerekiyorsa aşağıdaki sözdizimini kullanmanızı öneririz:

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