Hologres:Columnar JSONB

Aktifkan columnar JSONB

Gunakan pernyataan berikut untuk mengaktifkan optimasi penyimpanan berorientasi kolom pada kolom JSONB tertentu dalam sebuah tabel.

-- Aktifkan optimasi penyimpanan berorientasi kolom untuk kolom tertentu dalam tabel tertentu.
ALTER TABLE <table_name> ALTER COLUMN <column_name> SET (enable_columnar_type = ON);

table_name adalah nama tabel. column_name adalah nama kolom.

Aktifkan inferensi tipe DECIMAL

Hologres V2.0.11 dan versi yang lebih baru mendukung optimasi penyimpanan berorientasi kolom untuk data DECIMAL. Pertimbangkan data JSON berikut sebagai contoh:

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

Setelah Anda mengaktifkan inferensi tipe DECIMAL, nilai balance juga mendukung optimasi berorientasi kolom. Gunakan pernyataan berikut untuk mengaktifkan fitur ini:

-- Aktifkan optimasi berorientasi kolom untuk nilai DECIMAL dalam kolom tertentu di tabel tertentu.
ALTER TABLE <table_name> ALTER COLUMN <column_name> SET (enable_decimal = ON);

table_name adalah nama tabel. column_name adalah nama kolom.

Memeriksa status columnar JSONB

Gunakan pernyataan berikut untuk memeriksa status columnar JSONB suatu tabel.

• Perintah berikut didukung di Hologres V1.3.37 dan versi yang lebih baru.

  Catatan

  Di Hologres V2.0.17 dan versi sebelumnya, perintah ini hanya dapat menampilkan tabel dalam skema public. Mulai dari V2.0.18, perintah ini dapat menampilkan status tabel di skema lain.

  -- Di V2.0.17 dan versi sebelumnya, Anda hanya dapat mengkueri tabel dalam skema public. Di V2.0.18 dan versi yang lebih baru, Anda dapat mengkueri tabel di skema lain.
  SELECT * FROM hologres.hg_column_options WHERE schema_name='<schema_name>' AND table_name = '<table_name>';

  schema_name adalah nama skema, dan table_name adalah nama tabel.

• Untuk Hologres V1.3.10 hingga V1.3.36, gunakan perintah berikut.

  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 adalah nama tabel.

• Contoh hasil:

  Dalam hasil tersebut, jika properti attoptions atau option untuk suatu kolom bernilai enable_columnar_type = ON, hal ini menunjukkan bahwa konfigurasi berhasil.

   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)

Nonaktifkan columnar JSONB

Gunakan perintah berikut untuk menonaktifkan optimasi penyimpanan berorientasi kolom pada kolom JSONB tertentu dalam sebuah tabel.

-- Nonaktifkan optimasi penyimpanan berorientasi kolom untuk kolom tertentu dalam tabel tertentu.
ALTER TABLE <table_name> ALTER COLUMN <column_name> SET (enable_columnar_type = OFF);

table_name adalah nama tabel. column_name adalah nama kolom.

Nonaktifkan inferensi tipe DECIMAL

Untuk menonaktifkan inferensi tipe DECIMAL pada kolom tertentu, gunakan perintah berikut.

-- Nonaktifkan optimasi berorientasi kolom untuk nilai DECIMAL dalam kolom tertentu di tabel tertentu.
ALTER TABLE <table_name> ALTER COLUMN <column_name> SET (enable_decimal = OFF);

table_name adalah nama tabel. column_name adalah nama kolom.

Menetapkan indeks bitmap

Di Hologres, properti bitmap_columns menentukan indeks bitmap, yaitu struktur indeks independen yang terpisah dari penyimpanan data. Indeks ini menggunakan struktur vektor bitmap untuk mempercepat perbandingan kesamaan, sehingga memungkinkan pemfilteran kesamaan data yang cepat dalam blok file. Mulai dari V2.0, Hologres mendukung penyetelan indeks bitmap untuk kolom JSONB yang telah diaktifkan penyimpanan berorientasi kolomnya. Setelah columnar JSONB diaktifkan, sistem mengurai data menjadi tujuh tipe data: int, int[], bigint, bigint[], text, text[], dan jsonb. Saat indeks bitmap diaktifkan, sistem membangun indeks bitmap untuk data yang diinferensikan sebagai tipe int, int[], bigint, bigint[], text, dan text[].

Sintaksnya adalah sebagai berikut:

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

Parameter:

Contoh

1. Buat tabel.

   DROP TABLE IF EXISTS user_tags;
   -- Buat tabel data
   BEGIN;
   CREATE TABLE IF NOT EXISTS user_tags (
       ds timestamptz,
       tags jsonb
   );
   COMMIT;

2. Aktifkan optimasi penyimpanan berorientasi kolom untuk kolom tags.

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

3. Periksa status penyimpanan columnar JSONB.

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

   Pada hasil berikut, properti options pada baris tags bernilai {enable_columnar_type=on}, yang menunjukkan bahwa konfigurasi berhasil.

    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. Impor data.

   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. (Opsional) Paksa flush data.

   Setelah data ditulis, sistem melakukan optimasi columnar JSONB selama proses flush data. Untuk melihat efeknya segera, jalankan perintah berikut untuk memaksa flush data.

   VACUUM user_tags;

6. Jalankan kueri contoh.

   Jalankan pernyataan SQL berikut untuk mengkueri first_name di mana id bernilai 10.

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

7. Periksa rencana eksekusi untuk memverifikasi bahwa kueri menggunakan optimasi columnar.

   -- Tampilkan statistik detail.
   SET hg_experimental_show_execution_statistics_in_explain = ON;
   -- Lihat rencana eksekusi.
   EXPLAIN ANALYZE
   SELECT
       (tags -> 'first_name')::text AS first_name
   FROM
       user_tags
   WHERE (tags -> 'id')::int = 10;

   Jika columnar_access_used muncul dalam hasil, optimasi columnar JSONB telah digunakan.

8. Untuk kueri pada Langkah 6, Anda juga dapat menetapkan indeks bitmap pada kolom tags untuk meningkatkan efisiensi kueri kesamaan untuk kunci tertentu dengan menggunakan perintah berikut:

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

9. Periksa rencana eksekusi untuk memverifikasi bahwa indeks bitmap efektif.

   -- Lihat rencana eksekusi.
   EXPLAIN ANALYZE
   SELECT
       (tags -> 'first_name')::text AS first_name
   FROM
       user_tags
   WHERE (tags -> 'id')::int = 10;

   Hasilnya adalah sebagai berikut:

   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.

   Jika bitmap_used muncul dalam hasil, indeks bitmap telah digunakan.

Mengembalikan seluruh kolom JSONB

Columnar JSONB Hologres memberikan optimasi yang baik untuk sebagian besar kasus penggunaan. Namun, untuk skenario di mana hasil kueri perlu menyertakan seluruh kolom JSONB, kinerjanya mungkin lebih rendah dibandingkan dengan menyimpan data dalam format JSONB asli. Sebagai contoh, pertimbangkan pernyataan SQL berikut:

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

Penurunan kinerja ini terjadi karena lapisan dasar telah mengonversi data JSONB menjadi penyimpanan berorientasi kolom. Oleh karena itu, saat Anda perlu mengkueri data JSON lengkap, sistem harus menyusun ulang data berorientasi kolom kembali ke format JSONB asli:

Langkah ini menghasilkan overhead I/O dan konversi yang signifikan. Jika volume data besar dan jumlah kolom tinggi, proses ini dapat menjadi bottleneck kinerja. Oleh karena itu, kami menyarankan untuk tidak mengaktifkan optimasi berorientasi kolom dalam skenario ini.

Data JSONB yang sangat sparse

Saat Hologres menemui bidang sparse selama mengonversi data JSONB ke format berorientasi kolom, sistem menggabungkan bidang-bidang tersebut ke dalam kolom khusus bernama holo.remaining untuk mencegah ledakan jumlah kolom. Oleh karena itu, jika data JSONB seluruhnya terdiri dari bidang sparse—misalnya, dalam kasus ekstrem di mana setiap bidang hanya muncul sekali—konversi berorientasi kolom tidak akan efektif. Karena semua bidang bersifat sparse, semuanya digabung ke dalam kolom holo.remaining, sehingga tidak terjadi konversi berorientasi kolom yang sebenarnya. Dalam kasus ini, tidak akan ada peningkatan kinerja kueri.

Data JSONB dengan struktur bersarang kompleks

Pada data JSONB berikut, node akar berupa array yang berisi data JSONB non-homogen. Saat ini, ketika Hologres mengonversi data JSONB ke format berorientasi kolom, sistem menurunkan spesifikasi struktur bersarang kompleks seperti ini menjadi satu kolom tunggal. Oleh karena itu, mengaktifkan optimasi columnar JSONB untuk jenis data JSONB ini tidak akan memberikan manfaat signifikan terhadap kinerja kueri.

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

Diagnosis kueri lambat

Jika Anda menemukan bahwa kinerja kueri memburuk setelah mengaktifkan columnar JSONB, pertama-tama periksa apakah kueri mengembalikan seluruh kolom JSONB. Jika pernyataan SQL terlalu kompleks, Anda dapat menggunakan perintah EXPLAIN ANALYZE untuk diagnosis. Contoh perintahnya adalah sebagai berikut:

CREATE TABLE TBL(key int, json_data json); -- DDL untuk membuat tabel
ALTER TABLE TBL ALTER COLUMN json_data SET (enable_columnar_type = on);
Explain Analyze SELECT json_data FROM TBL WHERE key = 123;

Hasil EXPLAIN ANALYZE berisi informasi petunjuk. Jika petunjuk tersebut berisi pesan berikut, artinya kueri mengembalikan seluruh kolom JSONB, yang menyebabkan penurunan kinerja:

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

Sintaks SQL yang lebih efisien

• Ada beberapa cara untuk mengonversi data bidang JSONB ke format TEXT, tetapi operator ->> memberikan kinerja lebih baik. Sebagai contoh, untuk mendapatkan atribut name dari kolom json_data:

  -- Kinerja lebih baik
  SELECT json_data->>'name' FROM tbl; 
  -- Kinerja rata-rata
  SELECT (json_data->'name')::text FROM tbl;

• Jika bidang JSON menyimpan array TEXT dan Anda perlu memeriksa apakah array tersebut berisi nilai tertentu, kami menyarankan menggunakan sintaks berikut:

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