Optimisasi pengurutan AnalyticDB for PostgreSQL - AnalyticDB

AnalyticDB for PostgreSQL mendukung dua metode pengurutan—compound sorting dan interleaved sorting—untuk mempercepat kinerja kueri. Untuk sebagian besar beban kerja, gunakan compound sorting. Beralihlah ke interleaved sorting hanya jika kueri Anda melakukan filter pada kolom non-leading dari sort key dengan frekuensi yang kurang lebih sama.

Cara kerja pengurutan

Saat membuat tabel, tentukan satu atau beberapa kolom sebagai sort key. Setelah data dimuat, urutkan tabel berdasarkan sort key tersebut. AnalyticDB for PostgreSQL mencatat nilai minimum dan maksimum untuk setiap kolom di setiap blok disk, membentuk indeks rough set. Selama pemindaian tabel, mesin kueri membandingkan nilai filter terhadap rentang min/maks dan melewatkan blok disk apa pun yang berada di luar rentang tersebut.

Contoh: Sebuah tabel menyimpan data selama tujuh tahun yang diurutkan berdasarkan tanggal. Kueri untuk satu bulan hanya perlu memindai 1/(7 × 12) data—98,8% blok disk dilewati. Tanpa pengurutan, kueri tersebut akan memindai setiap blok disk.

Pilih kunci pengurutan

Untuk filter equality dan range pada sekumpulan kolom tetap, gunakan compound sorting. Compound sorting memperlakukan sort key sebagai awalan terurut: mesin kueri paling efektif ketika kondisi filter sesuai dengan kolom leading dari sort key. Ini merupakan pilihan tepat untuk sebagian besar kueri.

Untuk filter yang mencakup kolom berbeda yang bersifat non-leading, gunakan interleaved sorting. Interleaved sorting memberikan bobot yang sama pada setiap kolom dalam sort key, sehingga mesin kueri dapat melewatkan blok tanpa memedulikan kolom mana yang muncul dalam filter. Konsekuensinya, interleaved sorting memerlukan analisis tambahan pada data dan umumnya lebih lambat dibandingkan compound sorting.

Catatan

Sort key interleaved dapat berisi hingga delapan kolom.

Untuk kolom JOIN, atur kolom JOIN sebagai distribution key sekaligus sort key. Hal ini memungkinkan query optimizer memilih merge join alih-alih hash join. Karena data sudah diurutkan berdasarkan join key, pengoptimal sepenuhnya melewatkan fase pengurutan dalam merge join.

Perbandingan kinerja antara compound sorting dan interleaved sorting

Tolok ukur berikut menggunakan dua tabel kolom append-only dengan data identik: test (diurutkan dengan SORT) dan test_multi (diurutkan dengan MULTISORT). Kedua tabel memiliki sort key (id, num1, num2).

Persiapan

Buat kedua tabel:

CREATE TABLE test(id int, num1 int, num2 int, value varchar)
WITH (APPENDONLY=TRUE, ORIENTATION=column)
DISTRIBUTED BY (id)
ORDER BY (id, num1, num2);

CREATE TABLE test_multi(id int, num1 int, num2 int, value varchar)
WITH (APPENDONLY=TRUE, ORIENTATION=column)
DISTRIBUTED BY (id)
ORDER BY (id, num1, num2);

Masukkan 10 juta baris ke masing-masing tabel:

INSERT INTO test(id, num1, num2, value)
SELECT g,
  (random() * 10000000)::int,
  (random() * 10000000)::int,
  (ARRAY['foo', 'bar', 'baz', 'quux', 'boy', 'girl', 'mouse', 'child', 'phone'])[floor(random() * 10 + 1)]
FROM generate_series(1, 10000000) AS g;

INSERT INTO test_multi SELECT * FROM test;

Urutkan masing-masing tabel menggunakan metode yang sesuai:

SORT test;            -- compound sorting
MULTISORT test_multi; -- interleaved sorting

Hasil kueri equality

Tiga kueri melakukan filter pada kombinasi kolom sort key yang berbeda:

Q1 — hanya kolom pertama: WHERE id = 100000
Q2 — hanya kolom kedua: WHERE num1 = 8766963
Q3 — kolom kedua dan ketiga: WHERE num1 = 100000 AND num2 = 2904114

Metode pengurutan	Q1	Q2	Q3
Compound sorting	0,026 dtk	3,95 dtk	4,21 dtk
Interleaved sorting	0,55 dtk	0,42 dtk	0,071 dtk

Hasil kueri range

Kombinasi kolom yang sama, menggunakan predikat range:

Q1 — kolom pertama: WHERE id > 5000 AND id < 100000
Q2 — kolom kedua: WHERE num1 > 5000 AND num1 < 100000
Q3 — kolom kedua dan ketiga: WHERE num1 > 5000 AND num1 < 100000 AND num2 < 100000

Metode pengurutan	Q1	Q2	Q3
Compound sorting	0,07 dtk	3,35 dtk	3,64 dtk
Interleaved sorting	0,44 dtk	0,28 dtk	0,047 dtk

Kesimpulan

Q1 (filter kolom leading): Compound sorting lebih cepat. Ketika filter sesuai dengan kolom leading dari sort key, mesin kueri melewatkan blok secara efisien tanpa analisis tambahan yang diperlukan oleh interleaved sorting.
Q2 (filter kolom non-leading): Interleaved sorting mengungguli compound sorting karena memberikan rentang min/maks independen untuk setiap kolom, bukan hanya untuk awalan.
Q3 (multiple kolom non-leading): Interleaved sorting jauh lebih cepat. Semakin banyak kolom non-leading yang disertakan dalam filter, semakin besar keunggulannya—setiap kolom tambahan berkontribusi dalam mempersempit pemindaian blok.

Akselerasi pengurutan

Setelah menjalankan SORT <tablename>, AnalyticDB for PostgreSQL mendorong operator yang sadar pengurutan—SORT, AGG, dan JOIN—ke lapisan penyimpanan. Kueri yang sesuai dengan urutan fisik data dieksekusi terhadap blok yang sudah terurut, sehingga menghilangkan kebutuhan pengurutan saat eksekusi.

Catatan

Akselerasi pengurutan memerlukan agar semua data dalam tabel telah diurutkan. Setelah menulis data baru, jalankan kembali SORT <tablename>.
Akselerasi pengurutan diaktifkan secara default.

Contoh berikut membandingkan waktu kueri sebelum dan sesudah akselerasi pengurutan pada tabel uji bernama far.

Persiapan

Buat tabel far:

CREATE TABLE far(a int, b int)
WITH (APPENDONLY=TRUE, COMPRESSTYPE=ZSTD, COMPRESSLEVEL=5)
DISTRIBUTED BY (a)  -- distribution key
ORDER BY (a);       -- sort key

Masukkan satu juta baris:

INSERT INTO far VALUES (generate_series(0, 1000000), 1);

Urutkan tabel:
```
SORT far;
```

Perbandingan kinerja kueri

Catatan

Waktu kueri di bawah ini hanya sebagai referensi. Waktu aktual bervariasi tergantung pada volume data, sumber daya komputasi, dan kondisi jaringan.

ORDER BY

Sebelum akselerasi pengurutan:
Setelah akselerasi pengurutan:

GROUP BY

Sebelum akselerasi pengurutan:
Setelah akselerasi pengurutan:

JOIN

Sebelum akselerasi pengurutan:
Setelah akselerasi pengurutan:
Catatan
Untuk menggunakan akselerasi pengurutan pada operator JOIN, nonaktifkan pengoptimal ORCA dan aktifkan algoritma merge join:
```
SET enable_mergejoin TO on;
SET optimizer TO off;
```

Rangkuman

	ORDER BY	GROUP BY	JOIN
Sebelum akselerasi	323,980 ms	779,368 ms	289,075 ms
Setelah akselerasi	6,971 ms	6,859 ms	12,315 ms