AnalyticDB:Optimalisasi Pengurutan

Informasi latar belakang

Saat membuat tabel, Anda dapat menentukan satu atau lebih kolom sebagai kunci pengurutan. Setelah data ditulis ke tabel, data tersebut diurutkan berdasarkan kunci pengurutan.

Pengurutan mempercepat kueri rentang. Setelah diurutkan, nilai maksimum dan minimum untuk setiap kolom dicatat untuk mempercepat kueri rentang. Jika kondisi rentang digunakan dalam kueri, mesin kueri AnalyticDB for PostgreSQL dapat menggunakan nilai maksimum dan minimum untuk melewati blok disk di luar rentang tertentu selama pemindaian tabel.

Sebagai contoh, jika sebuah tabel menyimpan tujuh tahun data yang diurutkan berdasarkan tanggal dan Anda ingin mengambil data bulan tertentu, hanya 1/(7 × 12) dari data tabel yang perlu dipindai, sehingga 98,8% blok disk dapat dilewati selama pemindaian. Jika data tidak diurutkan berdasarkan tanggal, semua blok disk mungkin dipindai.

AnalyticDB for PostgreSQL mendukung dua metode pengurutan:

• Pengurutan majemuk: Berlaku untuk skenario di mana kondisi filter merupakan subset awalan dari kunci pengurutan. Sebagai contoh, metode ini dapat digunakan jika kondisi filter dari kueri SQL mencakup kolom pertama dari kunci pengurutan.

• Pengurutan berselang-seling: Memberikan bobot yang sama pada setiap kolom dari kunci pengurutan. Metode ini lebih berguna untuk skenario di mana kondisi kueri mencakup subset kondisi filter.

Pilih kunci pengurutan

• Jika kolom tertentu sering digunakan dalam kondisi kesetaraan atau rentang pada kueri SQL Anda, Anda dapat menggunakan kolom-kolom ini sebagai kunci pengurutan. Ini mempercepat kueri SQL dengan memanfaatkan pengurutan data dan indeks himpunan kasar. Dalam kebanyakan kasus, disarankan untuk menggunakan pengurutan majemuk.

• Jika kolom berbeda digunakan dalam kondisi filter kueri SQL Anda, Anda dapat menggunakan pengurutan berselang-seling untuk mempercepat kueri. Namun, pengurutan berselang-seling umumnya lebih memakan waktu dibandingkan pengurutan majemuk karena memerlukan analisis tambahan terhadap data.

  Catatan

  Kunci pengurutan berselang-seling dapat mencakup hingga delapan kolom.

• Jika Anda sering menggunakan kolom tertentu sebagai kondisi JOIN, Anda dapat menentukan kolom JOIN tersebut sebagai kunci distribusi dan kunci pengurutan. Hal ini memungkinkan penggunaan merge join alih-alih hash join. Fase pengurutan dari merge join dapat dilewati karena data sudah diurutkan berdasarkan kunci join.

Perbandingan performa antara pengurutan majemuk dan pengurutan berselang-seling

Dalam bagian ini, dua tabel yang berisi data yang sama diurutkan secara terpisah menggunakan pengurutan majemuk dan pengurutan berselang-seling. Hasil kueri pada kedua tabel menunjukkan bahwa kedua metode pengurutan memberikan performa yang berbeda dalam skenario yang berbeda.

1. Eksekusi pernyataan berikut untuk membuat dua tabel dengan kolom id, num1, num2, dan value, serta tentukan kolom id, num1, dan num2 sebagai kunci pengurutan dari 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);

2. Eksekusi pernyataan berikut untuk menyisipkan 10 juta baris data ke dalam kedua 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;

   Eksekusi pernyataan berikut untuk meminta jumlah total baris dalam kedua tabel:

   • SELECT count(*) FROM test;

     Informasi berikut dikembalikan:

       count
     ----------
      10000000
     (1 row)

   • SELECT count(*) FROM test_multi;

     Informasi berikut dikembalikan:

       count
     ----------
      10000000
     (1 row)

3. Urutkan kedua tabel secara terpisah menggunakan pengurutan majemuk dan pengurutan berselang-seling.

   Eksekusi pernyataan berikut untuk mengurutkan tabel test menggunakan pengurutan majemuk:

   SORT test;

   Eksekusi pernyataan berikut untuk mengurutkan tabel test_multi menggunakan pengurutan berselang-seling:

   MULTISORT test_multi;

4. Bandingkan performa kueri kesetaraan.

   • Q1 difilter berdasarkan kolom pertama dari kunci pengurutan.

     SELECT * FROM test WHERE id = 100000;
     SELECT * FROM test_multi WHERE id = 100000;

   • Q2 difilter berdasarkan kolom kedua dari kunci pengurutan.

     SELECT * FROM test WHERE num1 = 8766963;
     SELECT * FROM test_multi WHERE num1 = 8766963;

   • Q3 difilter berdasarkan kolom kedua dan ketiga dari kunci pengurutan.

     SELECT * FROM test WHERE num1 = 100000 AND num2=2904114;
     SELECT * FROM test_multi WHERE num1 = 100000 AND num2=2904114;

   Tabel berikut menjelaskan hasil perbandingan performa.

   Metode pengurutan	Q1	Q2	Q3
   Pengurutan majemuk	0,026s	3,95s	4,21s
   Pengurutan berselang-seling	0,55s	0,42s	0,071s

5. Bandingkan performa kueri rentang.

   • Q1 difilter berdasarkan kolom pertama dari kunci pengurutan.

     SELECT count(*) FROM test WHERE id>5000 AND id < 100000;
     SELECT count(*) FROM test_multi WHERE id>5000 AND id < 100000;

   • Q2 difilter berdasarkan kolom kedua dari kunci pengurutan.

     SELECT count(*) FROM test WHERE num1 >5000 AND num1 <100000;
     SELECT count(*) FROM test_multi WHERE num1 >5000 AND num1 <100000;

   • Q3 difilter berdasarkan kolom kedua dan ketiga dari kunci pengurutan.

     SELECT count(*) FROM test WHERE num1 >5000 AND num1 <100000 AND num2 < 100000;
     SELECT count(*) FROM test_multi WHERE num1 >5000 AND num1 <100000 AND num2 < 100000;

   Tabel berikut menjelaskan hasil perbandingan performa.

   Metode pengurutan	Q1	Q2	Q3
   Pengurutan majemuk	0,07s	3,35s	3,64s
   Pengurutan berselang-seling	0,44s	0,28s	0,047s

Kesimpulan Uji:

• Q1 menggunakan kolom pertama dari kunci pengurutan untuk memfilter data. Dalam hal ini, kueri yang menggunakan pengurutan majemuk memiliki waktu respons yang lebih singkat dibandingkan kueri yang menggunakan pengurutan berselang-seling.

• Q2 menggunakan kolom non-pertama dari kunci pengurutan untuk memfilter data. Dalam hal ini, kueri yang menggunakan pengurutan berselang-seling lebih unggul dibandingkan kueri yang menggunakan pengurutan majemuk.

• Q3 menggunakan kolom non-pertama dari kunci pengurutan untuk memfilter data. Dalam hal ini, kueri yang menggunakan pengurutan berselang-seling lebih cepat dan efektif dibandingkan dengan yang menggunakan pengurutan majemuk. Semakin banyak kolom dari kunci pengurutan berselang-seling yang digunakan, semakin baik performa kueri yang menggunakan pengurutan berselang-seling.

Akselerasi pengurutan

Fitur akselerasi pengurutan didukung. Setelah Anda mengeksekusi pernyataan SORT <tablename>, sistem akan mengurutkan data dalam tabel yang ditentukan. Kemudian, AnalyticDB for PostgreSQL mendorong operator seperti SORT ke lapisan penyimpanan sehingga kueri dipercepat berdasarkan urutan fisik data. Saat data dasar Anda terurut, kueri Anda dapat dipercepat. Fitur ini dapat mempercepat kueri yang berisi operator SORT, AGG, atau JOIN berdasarkan kunci pengurutan.

Dalam contoh ini, tabel uji bernama far digunakan untuk membandingkan waktu kueri sebelum dan sesudah akselerasi pengurutan.

1. Eksekusi pernyataan berikut untuk membuat tabel uji bernama far:

   CREATE TABLE far(a int,  b int)
   WITH (APPENDONLY=TRUE, COMPRESSTYPE=ZSTD, COMPRESSLEVEL=5)
   DISTRIBUTED BY (a)  --Kunci distribusi
   ORDER BY (a);       --Kunci pengurutan

2. Eksekusi pernyataan berikut untuk menulis satu juta baris data ke tabel far:

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

3. Eksekusi pernyataan berikut untuk mengurutkan data dalam tabel far:

   SORT far;

Perbandingan Performa Kueri:

• ORDER BY

  • Sebelum akselerasi pengurutan

  • Setelah akselerasi pengurutan

• GROUP BY

  • Sebelum akselerasi pengurutan

  • Setelah akselerasi pengurutan

• JOIN

  • Sebelum akselerasi pengurutan

  • Setelah akselerasi pengurutan

    Catatan

    Jika Anda ingin menggunakan fitur akselerasi pengurutan untuk operator JOIN, Anda harus mengeksekusi pernyataan berikut untuk menonaktifkan optimizer ORCA dan mengaktifkan algoritma merge join:

    SET enable_mergejoin TO on;
    SET optimizer TO off;