Optimalkan performa kueri untuk tabel Beam dengan kunci sortir - AnalyticDB

Jika Anda sering menjalankan kueri rentang atau penyaringan kesetaraan pada satu atau lebih kolom tabel Beam, Anda dapat menentukan kunci pengurutan majemuk atau kunci pengurutan terpadu untuk meningkatkan kinerja kueri.

Saat membuat tabel, Beam memungkinkan Anda menentukan satu atau lebih kolom sebagai kunci pengurutan. Setelah data ditulis ke tabel, sistem akan mengurutkan data berdasarkan kunci pengurutan tersebut. Saat Beam memindai data yang diurutkan dalam tabel, nilai maksimum dan minimum dari kolom digunakan untuk melewati blok data yang tidak memenuhi kondisi filter. Hal ini secara signifikan mengurangi overhead I/O. Dalam banyak kasus, kunci pengurutan juga membantu mencapai rasio kompresi data yang lebih baik. Beam mendukung kunci pengurutan majemuk dan kunci pengurutan terpadu.

Catatan Penggunaan

Hanya AnalyticDB for PostgreSQL V7.0 versi 7.0.1.x atau lebih baru yang mendukung fitur optimalisasi pengurutan Beam. Untuk informasi tentang cara melihat versi mesin minor suatu instans, lihat Lihat Versi Mesin Minor.

Pengurutan Majemuk

Tentukan Kunci Pengurutan Majemuk

Kunci pengurutan majemuk terdiri dari semua kolom yang terdaftar dalam definisi kunci pengurutan. Data diurutkan berdasarkan urutan kolom yang terdaftar. Kunci pengurutan majemuk efisien ketika kondisi filter kueri mencakup awalan kolom kunci pengurutan.

Eksekusi pernyataan berikut untuk membuat tabel Beam dengan kunci pengurutan majemuk:

CREATE TABLE beam_example (
    id integer,
    name text,
    ftime timestamp
) 
USING beam 
DISTRIBUTED BY (id) 
ORDER BY(id);

Pelihara Kunci Pengurutan Majemuk

Setelah membuat tabel dengan kunci pengurutan majemuk, data yang ditulis tidak langsung diurutkan berdasarkan kolom kunci pengurutan. Proses latar belakang secara otomatis menghasilkan urutan pengurutan optimal berdasarkan jumlah data dan jumlah file. Jika Anda ingin segera mengurutkan data yang ditulis, Anda dapat mengeksekusi pernyataan OPTIMIZE.

Eksekusi pernyataan berikut untuk segera mengurutkan data dalam tabel beam_example:

OPTIMIZE beam_example;

Penting

Pernyataan OPTIMIZE memblokir perubahan DDL. Setelah mengeksekusi pernyataan tersebut, perubahan DDL akan dilanjutkan secara otomatis.

Pengurutan Terpadu

Catatan Penggunaan

Hanya AnalyticDB for PostgreSQL V7.0 versi 7.0.4.0 atau lebih baru yang mendukung pengurutan terpadu Beam. Untuk informasi tentang cara melihat versi mesin minor suatu instans, lihat Lihat Versi Mesin Minor.
Anda dapat membuat tabel Beam yang hanya berisi dua hingga delapan kolom kunci pengurutan terpadu.
Kami merekomendasikan agar Anda tidak menentukan kolom bertambah, seperti kolom tanggal dan timestamp, sebagai kunci pengurutan terpadu.

Tentukan Kunci Pengurutan Terpadu

Kunci pengurutan terpadu memberikan bobot yang sama untuk setiap kolom dalam kunci pengurutan. Dengan cara ini, semua kolom kunci pengurutan digunakan secara merata. Kunci pengurutan terpadu efisien untuk kueri yang kondisi filternya mencakup kolom kunci pengurutan.

Eksekusi pernyataan berikut untuk membuat tabel Beam dengan kunci pengurutan terpadu:

CREATE TABLE beam_example_interleaved (
    id integer,
    name text,
    ftime timestamp,
    region varchar,
    age integer
) 
USING beam 
DISTRIBUTED BY (id) 
ZORDER BY(name, region, age);

Pelihara Kunci Pengurutan Terpadu

Setelah membuat tabel dengan kunci pengurutan terpadu, data yang ditulis tidak langsung diurutkan secara terpadu. Proses latar belakang secara otomatis menghasilkan urutan pengurutan optimal berdasarkan jumlah data dan jumlah file. Seiring dengan peningkatan jumlah data yang ditulis, pengurutan terpadu dapat menyebabkan kemiringan data terjadi karena perubahan rentang data. Dalam hal ini, Anda harus secara manual mengurutkan ulang data tabel.

Sebagai contoh, tabel beam_example_interleaved diurutkan berdasarkan kolom name dan region secara terpadu. Eksekusi pernyataan berikut untuk menanyakan kemiringan data pada kolom kunci pengurutan terpadu tabel:

SELECT * FROM adbpg_toolkit.pg_get_interleaved_skew('beam_example_interleaved'::regclass)
 relid | colname  | skew |  suggestion   
-------+----------+------+---------------
 17139 | name     | 0.46 | 
 17139 | region   | 0.54 | NEED OPTIMIZE
 17139 | OVER ALL | 0.54 | NEED OPTIMIZE
(3 rows)

Eksekusi pernyataan berikut untuk menanyakan kemiringan data pada setiap kolom dari semua tabel pengurutan terpadu basis data saat ini:

SELECT * FROM adbpg_toolkit.pg_stat_interleaved_skew;
 relid |         relname          | colname  | skew |  suggestion   
-------+--------------------------+----------+------+---------------
 17139 | beam_example_interleaved | name     | 0.46 | 
 17139 | beam_example_interleaved | region   | 0.54 | NEED OPTIMIZE
 17139 | beam_example_interleaved | OVER ALL | 0.54 | NEED OPTIMIZE
(3 rows)

Kolom dalam hasil sampel:

skew: Rasio kemiringan kolom kunci pengurutan terpadu.
suggestion: Saran pada kolom kunci pengurutan terpadu. NEED OPTIMIZE menunjukkan bahwa Anda harus mengurutkan ulang kolom.
OVER ALL dalam kolom colname menunjukkan kemiringan data keseluruhan dari semua kolom kunci pengurutan terpadu. Jika Anda mengetahui kemiringan data dari kolom kunci pengurutan terpadu tertentu saja, Anda dapat mengurutkan seluruh tabel berdasarkan kemiringan data dari kolom tersebut.

Jika Anda ingin segera mengurutkan data tabel, Anda dapat mengeksekusi pernyataan OPTIMIZE.

Eksekusi pernyataan berikut untuk segera mengurutkan data dalam tabel beam_example_interleaved:

OPTIMIZE beam_example_interleaved;

Penting

Pernyataan OPTIMIZE memblokir perubahan DDL. Setelah mengeksekusi pernyataan tersebut, perubahan DDL akan dilanjutkan secara otomatis.

Tambah atau Modifikasi Kunci Pengurutan

Setelah membuat tabel, Anda dapat menambahkan, memodifikasi, atau menghapus kunci pengurutan.

Catatan Penggunaan

Saat menambahkan kunci pengurutan ke tabel atau memodifikasi kunci pengurutan dalam tabel, tabel dikunci dan data tidak dapat dibaca atau ditulis dalam tabel.
Jika Anda memodifikasi kunci pengurutan dalam tabel, semua data tabel ditulis ulang. Jika sebuah tabel berisi sejumlah besar data, waktu yang diperpanjang diperlukan untuk menulis ulang data. Lanjutkan dengan hati-hati.
Jika Anda memodifikasi kunci pengurutan majemuk dalam tabel, data tabel segera diurutkan ulang. Jika Anda memodifikasi kunci pengurutan terpadu dalam tabel, data tabel tidak langsung diurutkan ulang. Setelah memodifikasi kunci pengurutan terpadu dalam tabel, kami merekomendasikan agar Anda mengeksekusi pernyataan OPTIMIZE.

Contoh

Tambah atau modifikasi kunci pengurutan majemuk.

ALTER TABLE beam_example SET ORDER BY(id, name);

Tambah atau modifikasi kunci pengurutan terpadu.

ALTER TABLE beam_example_interleaved SET ZORDER BY(name, region);

Hapus kunci pengurutan.

ALTER TABLE beam_example SET ORDER NONE;

Pilih Kunci Pengurutan

Saran untuk memilih kunci pengurutan untuk tabel Beam:

Jika Anda sering menjalankan kueri rentang atau penyaringan kesetaraan pada satu atau lebih kolom tabel Beam, Anda dapat menentukan kolom-kolom tersebut sebagai kunci pengurutan.
Jika Anda sering menjalankan kueri rentang atau penyaringan kesetaraan pada beberapa kolom tabel Beam, frekuensi kueri dan laju penyaringan kolom tersebut kira-kira sama, dan kolom bertambah tidak termasuk dalam kolom, kami merekomendasikan agar Anda memilih kunci pengurutan terpadu. Jika frekuensi kueri dan laju penyaringan suatu kolom tinggi, kami merekomendasikan agar Anda memilih kunci pengurutan majemuk.
Jika Anda ingin menentukan kunci pengurutan majemuk di mana semua kolom digunakan dengan frekuensi yang sama, kami merekomendasikan agar Anda menyusun kolom berdaya pembeda rendah di bagian depan kunci pengurutan majemuk.
Jika Anda ingin menentukan kunci pengurutan terpadu, kami merekomendasikan agar Anda memilih kolom berdaya pembeda rendah yang melibatkan sejumlah besar data untuk meningkatkan kinerja kueri.

Perbedaan Kinerja Kueri antara Kunci Pengurutan Majemuk dan Kunci Pengurutan Terpadu

Tabel berikut menjelaskan perbedaan kinerja kueri antara kunci pengurutan majemuk dan kunci pengurutan terpadu di bawah kondisi penyaringan yang berbeda. Dalam contoh ini, kunci pengurutan ditentukan untuk tabel Beam lineorder_flat yang berisi 1 TB data dan digunakan untuk pengujian kinerja Star Schema Benchmark (SSB). Kolom kunci pengurutan adalah LO_ORDERDATE dan P_BRAND.

Kondisi Filter	Kinerja Kueri Kunci Pengurutan Majemuk (s)	Kinerja Kueri Kunci Pengurutan Terpadu (s)
Kueri titik berdasarkan kolom pertama	0.297	18.329
Laju penyaringan 1% berdasarkan kolom pertama	1.268	19.224
Laju penyaringan 10% berdasarkan kolom pertama	16.83	38.30
Laju penyaringan 50% berdasarkan kolom pertama	65.62	76.99
Kueri titik berdasarkan kolom pertama + kueri titik berdasarkan kolom kedua	0.288	5.29
Laju penyaringan 1% berdasarkan kolom pertama + laju penyaringan 1% berdasarkan kolom kedua	7.36	6.46
Laju penyaringan 10% berdasarkan kolom pertama + laju penyaringan 10% berdasarkan kolom kedua	91.73	26.70
Laju penyaringan 50% berdasarkan kolom pertama + laju penyaringan 50% berdasarkan kolom kedua	376.22	87.82
Laju penyaringan 50% berdasarkan kolom pertama + kueri titik berdasarkan kolom kedua	71.83	19.16
Laju penyaringan 10% berdasarkan kolom pertama + laju penyaringan 1% berdasarkan kolom kedua	82.50	18.95
Laju penyaringan 1% berdasarkan kolom pertama + laju penyaringan 10% berdasarkan kolom kedua	7.98	6.43
Kueri titik berdasarkan kolom pertama + laju penyaringan 50% berdasarkan kolom kedua	0.50	31.48
Kueri titik berdasarkan kolom kedua	87.04	19.67
Laju penyaringan 1% berdasarkan kolom kedua	515.08	78.90
Laju penyaringan 10% berdasarkan kolom kedua	567.85	131.39
Laju penyaringan 50% berdasarkan kolom kedua	588.86	134.36

Penting

Hasil kueri di atas hanya menampilkan perbedaan kinerja relatif antara kedua jenis kunci pengurutan. Hasil tersebut tidak mewakili kinerja optimal AnalyticDB for PostgreSQL untuk dataset SSB.

Kesimpulan pengujian: Jika kueri dilakukan hanya berdasarkan kolom kunci pengurutan pertama atau kolom kunci pengurutan pertama dengan laju penyaringan tinggi, kunci pengurutan majemuk lebih efisien. Jika kueri dilakukan berdasarkan kolom kunci pengurutan kedua atau beberapa kolom kunci pengurutan, kunci pengurutan terpadu lebih efisien.

Referensi

Optimalisasi pengurutan Beam hanya cocok untuk instans AnalyticDB for PostgreSQL V7.0. Untuk informasi tentang cara menggunakan optimalisasi pengurutan untuk instans AnalyticDB for PostgreSQL V6.0, lihat Optimalisasi Pengurutan.