Hologres:Optimalkan kinerja kueri tabel internal

Periksa apakah statistik terkini

Jalankan EXPLAIN pada kueri Anda dan periksa estimasi rows untuk setiap tabel.

Jika tabel besar menunjukkan rows=1000 (nilai default), statistik tersebut sudah usang.

Perbarui statistik

analyze <tablename>;

Identifikasi kapan harus memperbarui statistik

Jalankan analyze <tablename> saat:

• Setelah mengimpor data.

• Setelah beberapa operasi INSERT, UPDATE, atau DELETE.

• Untuk tabel internal maupun tabel eksternal.

• Pada tabel induk untuk tabel partisi.

Jika Anda mengalami error OOM selama join atau kueri lambat, jalankan analyze <tablename> sebelum mengimpor data.

Pahami jumlah shard default

Hologres menetapkan jumlah shard default berdasarkan spesifikasi instans, kira-kira sama dengan jumlah CU kueri yang tersedia. Setelah penskalaan, database yang sudah ada mempertahankan jumlah shard aslinya—hanya database baru yang menggunakan nilai default yang diperbarui.

Perbarui statistik untuk kueri join

Seperti disebutkan dalam Memelihara statistik, statistik yang usang dapat menyebabkan tabel yang lebih besar membuat hash table, sehingga mengurangi efisiensi join. Jalankan ANALYZE untuk memperbarui statistik tabel.

Pilih distribution key untuk join lokal

Distribution key menentukan bagaimana data didistribusikan ke seluruh shard. Pemilihan yang tepat memungkinkan join lokal dan mengurangi pengacakan data (data shuffling).

-- Buat tabel dengan distribution key yang cocok
BEGIN;
CREATE TABLE orders (order_id INT, customer_id INT, amount DECIMAL);
CALL set_table_property('orders', 'distribution_key', 'customer_id');
COMMIT;

BEGIN;
CREATE TABLE customers (id INT, name TEXT);
CALL set_table_property('customers', 'distribution_key', 'id');
COMMIT;

Dengan distribution key yang benar, rencana eksekusi tidak menampilkan operator Redistribute Motion, yang mengonfirmasi join lokal.

Gunakan Runtime Filter dalam join

Sejak V2.0, Hologres secara otomatis menerapkan Percepat join multi-tabel dengan runtime filter untuk join tabel besar-kecil, sehingga mengurangi data yang dipindai tanpa konfigurasi manual.

Menyetel algoritma urutan join

Untuk kueri yang menggabungkan banyak tabel, pengoptimal mungkin memerlukan waktu terlalu lama untuk menemukan urutan join optimal. Sesuaikan algoritma sesuai kebutuhan:

set optimizer_join_order = '<value>'; 

Mengoptimalkan operator Motion dalam join

Hologres menggunakan operator Motion untuk mendistribusikan ulang data antar shard:

Periksa rencana eksekusi untuk operator Motion yang mahal dan sesuaikan desain tabel:

• Operator Motion yang memakan waktu: Rancang ulang distribusi.

• Karakteristik Motion tidak efisien akibat statistik usang: Segarkan statistik dengan analyze.

• Membroadcast tabel kecil: Kurangi jumlah shard untuk mengoptimalkan efisiensi Broadcast Motion.

Mengoptimalkan COUNT DISTINCT

• Ganti COUNT DISTINCT eksak (intensif sumber daya) dengan APPROX_COUNT_DISTINCT (lebih cepat, tingkat error 0,1% hingga 1%) bila variasi kecil dapat diterima.

• Ganti COUNT DISTINCT dengan UNIQ (V1.3+).

• Tetapkan distribution key yang sesuai

  Gunakan kunci COUNT DISTINCT sebagai distribution key untuk menghindari pengacakan data antar shard.

• Upgrade ke V2.1+ untuk optimasi bawaan

  V2.1+ mencakup optimasi bawaan untuk skenario COUNT DISTINCT termasuk satu atau lebih COUNT DISTINCT, data skew, dan kueri tanpa GROUP BY.

Pilih format penyimpanan

Hologres mendukung penyimpanan row, column, dan hybrid. Pilih berdasarkan workload Anda:

Pilih tipe data

• Gunakan tipe lebih kecil bila memungkinkan (INT alih-alih BIGINT)

• Tentukan presisi untuk tipe DECIMAL/NUMERIC.

• Hindari FLOAT atau DOUBLE untuk kolom GROUP BY.

• Gunakan TEXT untuk fleksibilitas. Minimalkan N saat menggunakan VARCHAR(N) atau CHAR(N).

• Gunakan TIMESTAMPTZ dan DATE alih-alih TEXT untuk tanggal.

• Gunakan tipe data konsisten dalam kondisi join untuk menghindari konversi implisit

Rancang primary key

Primary key yang tepat meningkatkan rencana eksekusi, terutama untuk kueri GROUP BY.

Dalam mode columnar store, primary key memperlambat penulisan—throughput biasanya 3x lebih tinggi tanpa primary key.

Gunakan tabel partisi

Pilih indeks yang tepat

Contoh:

BEGIN;
CREATE TABLE events (
    event_id INT NOT NULL,
    user_id INT NOT NULL,
    event_time TIMESTAMPTZ NOT NULL,
    event_type TEXT
);
CALL set_table_property('events', 'clustering_key', 'event_time');
CALL set_table_property('events', 'segment_key', 'event_time');
CALL set_table_property('events', 'bitmap_columns', 'user_id,event_type');
COMMIT;

Verifikasi penggunaan indeks dalam kueri dengan menjalankan EXPLAIN:

EXPLAIN SELECT * FROM events WHERE event_time > '2026-01-01';

Nonaktifkan dictionary encoding untuk kolom karakter

Dictionary encoding mempercepat perbandingan string tetapi menambahkan overhead encode/decode. Nonaktifkan untuk kolom di mana biaya perbandingan rendah:

BEGIN;
CREATE TABLE logs (id INT, message TEXT);
CALL set_table_property('logs', 'dictionary_encoding_columns', '');
COMMIT;

Hindari SQL eksternal (Postgres) seperti NOT IN

Hologres menggunakan HQE (Hologres Query Engine) untuk kinerja terbaik. Operator yang tidak didukung akan fallback ke PQE (Postgres Query Engine), yang lebih lambat.

EXPLAIN SELECT * FROM orders WHERE id NOT IN (SELECT id FROM cancelled_orders);

select * from tmp where not exists (select a from tmp1 where a = tmp.a);

select name,unnest(string_to_array(age,',')) from demo;

select cast(substring(c1, 13, 2) as int) AS hour from t2;

select extract(hour from to_timestamp(c1, 'YYYYMMDD HH24:MI:SS')) from t2;

select regexp_replace(c1::text,'-','0') from t2;

select replace(c1::text,'-','') from t2;

select date_trunc('day',to_timestamp(c1, 'YYYYMMDD HH24:MI:SS')  at time zone 'utc') from t2

select date_trunc('day',to_timestamp(c1, 'YYYYMMDD HH24:MI:SS') ) from t2;

select cast(c1 as timestamp) from t2;

select to_timestamp(c1, 'yyyyMMdd hh24:mi:ss') from t2;

select c1::text from t2;

select to_char(c1, 'yyyyMMdd hh24:mi:ss') from t2;

Hindari kueri LIKE fuzzy

Hindari pencarian fuzzy seperti operasi LIKE karena tidak menggunakan indeks.

Mengoptimalkan kueri ORDER BY LIMIT

Sejak V1.3, Hologres mendukung Merge Sort untuk kueri ORDER BY ... LIMIT, sehingga menghilangkan operasi sort berlebihan.

Mengoptimalkan kueri GROUP BY

Tetapkan kolom GROUP BY sebagai distribution key untuk mengurangi redistribusi data.

-- Jika data didistribusikan berdasarkan nilai pada kolom a, redistribusi data saat runtime berkurang, dan kemampuan komputasi paralel shard dimanfaatkan sepenuhnya.
select a, count(1) from t1 group by a; 

Sejak V4.0, Hologres secara otomatis menulis ulang kolom GROUP BY terkait untuk mengurangi penggabungan (kedalaman pencarian maks: 5 lapis). Klausa seperti GROUP BY COL_A, ((COL_A + 1)), ((COL_A + 2)) ditulis ulang menjadi GROUP BY COL_A. Contoh:

CREATE TABLE tbl (
    a int,
    b int,
    c int
);

-- Kueri
SELECT
    a,
    a + 1 as a1,
    a + 2 as a2,
    sum(b)
FROM tbl
GROUP BY
    a,
    a1,
    a2;

Rencana eksekusi mengonfirmasi penulisan ulang—klausa GROUP BY hanya berisi kolom a.

QUERY PLAN
Gather  (cost=0.00..5.00 rows=1 width=20)
  -> Project  (cost=0.00..5.00 rows=1 width=20)
    -> HashAggregate  (cost=0.00..5.00 rows=1 width=12)
          Group Key: a
        -> Redistribution  (cost=0.00..5.00 rows=1 width=8)
              Hash Key: a
            -> Local Gather  (cost=0.00..5.00 rows=1 width=8)
              -> Seq Scan on tbl  (cost=0.00..5.00 rows=1 width=8)
Query Queue: init_warehouse.default_queue
Optimizer: HQO version 4.0.0

Untuk menonaktifkan:

-- Nonaktifkan fitur ini pada tingkat session.
SET hg_experimental_remove_related_group_by_key = off; 

-- Nonaktifkan fitur ini pada tingkat database.
ALTER DATABASE <database_name> SET hg_experimental_remove_related_group_by_key = off; 

Aktifkan penggunaan kembali CTE

Saat CTE dirujuk beberapa kali, aktifkan reuse CTE untuk menghindari komputasi ulang (V1.3+):

SET optimizer_cte_inlining=off;

Mengoptimalkan analisis Top-N

• Dalam skenario OLAP, mengambil N record teratas dalam suatu grup merupakan kebutuhan umum. Misalnya, kueri SQL berikut mengambil dua record teratas dari tabel t dalam setiap partisi b, diurutkan berdasarkan a:

  CREATE TABLE t (
    a int,
    b int
  );
  
  INSERT INTO t VALUES (2, 1), (3, 1), (4, 1), (5, 2), (6, 2);
  
  SELECT
      *
  FROM (
    SELECT
    a,
    b,
    row_number() OVER (PARTITION BY b ORDER BY a) AS rn
    FROM
    t) t1
  WHERE
      rn <= 2;

  Hasil eksekusi adalah sebagai berikut:

  a	b	rn
  5	2	1
  6	2	2
  2	1	1
  3	1	2

• Mulai Hologres V4.1, operator Partition Sort mendorong klausa LIMIT ke dalam Partition, sehingga menyaring data lebih awal selama pengurutan. Ini mengurangi memori untuk fungsi jendela seperti row_number dan rank dalam skenario Top-N, sehingga menurunkan risiko OOM. Diaktifkan secara default. Untuk menonaktifkan:

  -- Nonaktifkan fitur ini pada tingkat session.
  SET hg_experimental_enable_hash_partitioned_sort_v2 = off; 
  
  -- Nonaktifkan fitur ini pada tingkat database.
  ALTER DATABASE <database_name> SET hg_experimental_enable_hash_partitioned_sort_v2 = off;