Mengoptimalkan subkueri berkorelasi di Aurora PostgreSQL - Amazon Aurora
Meningkatkan kinerja kueri PostgreSQL Aurora menggunakan transformasi subqueryMenggunakan cache subquery untuk meningkatkan kinerja kueri PostgreSQL Aurora

Terjemahan disediakan oleh mesin penerjemah. Jika konten terjemahan yang diberikan bertentangan dengan versi bahasa Inggris aslinya, utamakan versi bahasa Inggris.

Mengoptimalkan subkueri berkorelasi di Aurora PostgreSQL

Sebuah kolom tabel referensi subquery berkorelasi dari kueri luar. Ini dievaluasi sekali untuk setiap baris yang dikembalikan oleh kueri luar. Dalam contoh berikut, subquery referensi kolom dari tabel ot. Tabel ini tidak termasuk dalam klausa FROM subquery, tetapi direferensikan dalam klausa FROM kueri luar. Jika tabel ot memiliki 1 juta baris, subquery perlu dievaluasi 1 juta kali. 

SELECT ot.a, ot.b FROM ot WHERE ot.b < (SELECT AVG(it.b) FROM it WHERE it.a = ot.a);
catatan

  • Transformasi subquery dan cache subquery tersedia di Aurora PostgreSQL dimulai dengan versi 16.8, sedangkan Babelfish untuk Aurora PostgreSQL mendukung fitur ini dari 4.2.0.

  • Dimulai dengan Babelfish untuk Aurora PostgreSQL versi 4.6.0 dan 5.2.0, parameter berikut mengontrol fitur-fitur ini:

    • babelfishpg_tsql.apg_enable_correlated_scalar_transform

    • babelfishpg_tsql.apg_enable_subquery_cache

    Secara default, kedua parameter dihidupkan.

Meningkatkan kinerja kueri PostgreSQL Aurora menggunakan transformasi subquery

Aurora PostgreSQL dapat mempercepat subquery yang berkorelasi dengan mengubahnya menjadi gabungan luar yang setara. Optimalisasi ini berlaku untuk dua jenis subkueri berkorelasi berikut:

  • Subquery yang mengembalikan nilai agregat tunggal dan muncul dalam daftar SELECT.

    SELECT ot.a, ot.b, (SELECT AVG(it.b) FROM it WHERE it.a = ot.a) FROM ot;

  • Subquery yang mengembalikan nilai agregat tunggal dan muncul dalam klausa WHERE.

    SELECT ot.a, ot.b FROM ot WHERE ot.b < (SELECT AVG(it.b) FROM it WHERE it.a = ot.a);

Mengaktifkan transformasi dalam subquery

Untuk mengaktifkan transformasi subkueri yang berkorelasi menjadi gabungan luar yang setara, atur parameternya ke. apg_enable_correlated_scalar_transform ON Nilai default dari parameter ini adalahOFF.

Anda dapat memodifikasi kelompok parameter cluster atau instance untuk mengatur parameter. Untuk mempelajari selengkapnya, lihat .

Atau, Anda dapat mengonfigurasi pengaturan hanya untuk sesi saat ini dengan perintah berikut:

SET apg_enable_correlated_scalar_transform TO ON;

Memverifikasi transformasi

Gunakan perintah EXPLORE untuk memverifikasi apakah subquery yang berkorelasi telah diubah menjadi gabungan luar dalam rencana kueri.

Ketika transformasi diaktifkan, bagian subquery berkorelasi yang berlaku akan diubah menjadi gabungan luar. Misalnya: 

postgres=> CREATE TABLE ot (a INT, b INT);
CREATE TABLE
postgres=> CREATE TABLE it (a INT, b INT);
CREATE TABLE

postgres=> SET apg_enable_correlated_scalar_transform TO ON;
SET
postgres=> EXPLAIN (COSTS FALSE) SELECT ot.a, ot.b FROM ot WHERE ot.b < (SELECT AVG(it.b) FROM it WHERE it.a = ot.a);

                         QUERY PLAN
--------------------------------------------------------------
 Hash Join
   Hash Cond: (ot.a = apg_scalar_subquery.scalar_output)
   Join Filter: ((ot.b)::numeric < apg_scalar_subquery.avg)
   ->  Seq Scan on ot
   ->  Hash
         ->  Subquery Scan on apg_scalar_subquery
               ->  HashAggregate
                     Group Key: it.a
                     ->  Seq Scan on it

Kueri yang sama tidak diubah ketika parameter GUC diputarOFF. Rencana tidak akan memiliki gabungan luar tetapi subplan sebagai gantinya.

postgres=> SET apg_enable_correlated_scalar_transform TO OFF;
SET
postgres=> EXPLAIN (COSTS FALSE) SELECT ot.a, ot.b FROM ot WHERE ot.b < (SELECT AVG(it.b) FROM it WHERE it.a = ot.a);
                QUERY PLAN
----------------------------------------
 Seq Scan on ot
   Filter: ((b)::numeric < (SubPlan 1))
   SubPlan 1
     ->  Aggregate
           ->  Seq Scan on it
                 Filter: (a = ot.a)

Batasan

  • Subquery harus dalam daftar SELECT atau dalam salah satu kondisi dalam klausa where. Jika tidak, itu tidak akan diubah.

  • Subquery harus mengembalikan fungsi agregat. Fungsi agregat yang ditentukan pengguna tidak didukung untuk transformasi.

  • Subquery yang ekspresi pengembaliannya bukan fungsi agregat sederhana tidak akan diubah.

  • Kondisi berkorelasi dalam klausa WHERE subquery harus menjadi referensi kolom sederhana. Jika tidak, itu tidak akan diubah.

  • Kondisi berkorelasi dalam subquery di mana klausa harus berupa predikat kesetaraan biasa.

  • Subquery tidak dapat berisi klausa HAVING atau GROUP BY.

  • Klausa where dalam subquery mungkin berisi satu atau lebih predikat yang dikombinasikan dengan AND.

catatan

Dampak kinerja transformasi bervariasi tergantung pada skema, data, dan beban kerja Anda. Eksekusi subquery yang berkorelasi dengan transformasi dapat secara signifikan meningkatkan kinerja karena jumlah baris yang dihasilkan oleh kueri luar meningkat. Kami sangat menyarankan Anda menguji fitur ini di lingkungan non-produksi dengan skema, data, dan beban kerja Anda yang sebenarnya sebelum mengaktifkannya di lingkungan produksi.

Menggunakan cache subquery untuk meningkatkan kinerja kueri PostgreSQL Aurora

Aurora PostgreSQL mendukung cache subquery untuk menyimpan hasil subquery berkorelasi. Fitur ini melewatkan eksekusi subquery berkorelasi berulang saat hasil subquery sudah ada di cache.

Memahami cache subquery

Node Memoize PostgreSQL adalah bagian penting dari cache subquery. Node Memoize mempertahankan tabel hash di cache lokal untuk memetakan dari nilai parameter input ke baris hasil kueri. Batas memori untuk tabel hash adalah produk dari work_mem dan hash_mem_multiplier. Untuk mempelajari lebih lanjut, lihat Konsumsi Sumber Daya.

Selama eksekusi kueri, cache subquery menggunakan Cache Hit Rate (CHR) untuk memperkirakan apakah cache meningkatkan kinerja kueri dan untuk memutuskan pada runtime kueri apakah akan terus menggunakan cache. CHR adalah rasio jumlah klik cache dengan jumlah total permintaan. Misalnya, jika subquery yang berkorelasi perlu dieksekusi 100 kali, dan 70 dari hasil eksekusi tersebut dapat diambil dari cache, CHR adalah 0,7.

Untuk setiap jumlah cache apg_subquery_cache_check_interval yang hilang, manfaat cache subquery dievaluasi dengan memeriksa apakah CHR lebih besar dari apg_subquery_cache_hit_rate_threshold. Jika tidak, cache akan dihapus dari memori, dan eksekusi kueri akan kembali ke eksekusi ulang subquery asli yang tidak di-cache.

Parameter yang mengontrol perilaku cache subquery

Tabel berikut mencantumkan parameter yang mengontrol perilaku cache subquery.

Parameter

Deskripsi

Default

Diizinkan

apg_enable_subquery_cache

Mengaktifkan penggunaan cache untuk subquery skalar berkorelasi.

MATI

 ON, OFF

apg_subquery_cache_check_interval

Menetapkan frekuensi, dalam jumlah kesalahan cache, untuk mengevaluasi tingkat hit cache subquery.

500

 0–2147483647

apg_subquery_cache_hit_rate_threshold

Menetapkan ambang batas untuk hit rate cache subquery.

0,3

 0,0—1,0
catatan

  • Nilai yang lebih besar apg_subquery_cache_check_interval dapat meningkatkan akurasi estimasi manfaat cache berbasis ChR, tetapi akan meningkatkan overhead cache, karena CHR tidak akan dievaluasi hingga tabel cache memiliki baris. apg_subquery_cache_check_interval

  • Nilai apg_subquery_cache_hit_rate_threshold bias yang lebih besar untuk meninggalkan cache subquery dan kembali ke eksekusi ulang subquery asli yang tidak di-cache.

Anda dapat memodifikasi kelompok parameter cluster atau instance untuk mengatur parameter. Untuk mempelajari selengkapnya, lihat .

Atau, Anda dapat mengonfigurasi pengaturan hanya untuk sesi saat ini dengan perintah berikut:

SET apg_enable_subquery_cache TO ON;

Mengaktifkan cache subquery di Aurora PostgreSQL

Ketika cache subquery diaktifkan, Aurora PostgreSQL menerapkan cache untuk menyimpan hasil subquery. Rencana kueri kemudian akan memiliki node Memoize di bawah SubPlan.

Misalnya, urutan perintah berikut menunjukkan perkiraan rencana eksekusi kueri dari subquery berkorelasi sederhana tanpa cache subquery. 

postgres=> SET apg_enable_subquery_cache TO OFF;
SET
postgres=> EXPLAIN (COSTS FALSE) SELECT ot.a, ot.b FROM ot WHERE ot.b < (SELECT it.b FROM it WHERE it.a = ot.a);

             QUERY PLAN
------------------------------------
 Seq Scan on ot
   Filter: (b < (SubPlan 1))
   SubPlan 1
     ->  Seq Scan on it
           Filter: (a = ot.a)

Setelah dihidupkanapg_enable_subquery_cache, rencana kueri akan berisi node Memoize di bawah SubPlan node, yang menunjukkan bahwa subquery berencana untuk menggunakan cache.

postgres=> SET apg_enable_subquery_cache TO ON;
SET
postgres=> EXPLAIN (COSTS FALSE) SELECT ot.a, ot.b FROM ot WHERE ot.b < (SELECT it.b FROM it WHERE it.a = ot.a);

             QUERY PLAN
------------------------------------
 Seq Scan on ot
   Filter: (b < (SubPlan 1))
   SubPlan 1
     ->  Memoize
           Cache Key: ot.a
           Cache Mode: binary
           ->  Seq Scan on it
                 Filter: (a = ot.a)

Rencana eksekusi kueri yang sebenarnya berisi rincian lebih lanjut dari cache subquery, termasuk klik cache dan kesalahan cache. Output berikut menunjukkan rencana eksekusi query aktual dari contoh query di atas setelah memasukkan beberapa nilai ke tabel. 

postgres=> EXPLAIN (COSTS FALSE, TIMING FALSE, ANALYZE TRUE) SELECT ot.a, ot.b FROM ot WHERE ot.b < (SELECT it.b FROM it WHERE it.a = ot.a);
            QUERY PLAN
-----------------------------------------------------------------------------
 Seq Scan on ot (actual rows=2 loops=1)
   Filter: (b < (SubPlan 1))
   Rows Removed by Filter: 8
   SubPlan 1
     ->  Memoize (actual rows=0 loops=10)
           Cache Key: ot.a
           Cache Mode: binary
           Hits: 4  Misses: 6  Evictions: 0  Overflows: 0  Memory Usage: 1kB
           ->  Seq Scan on it (actual rows=0 loops=6)
                 Filter: (a = ot.a)
                 Rows Removed by Filter: 4

Total nomor hit cache adalah 4, dan total nomor cache miss adalah 6. Jika jumlah total hit dan misses kurang dari jumlah loop di node Memoize, itu berarti evaluasi CHR tidak lulus dan cache dibersihkan dan ditinggalkan di beberapa titik. Eksekusi subquery kemudian kembali ke eksekusi ulang asli yang tidak di-cache.

Batasan

Cache subquery tidak mendukung pola tertentu dari subquery yang berkorelasi. Jenis kueri tersebut akan dijalankan tanpa cache, bahkan jika cache subquery diaktifkan:

  • IN/EXISTS/ANY/ALLsubquery berkorelasi

  • Subquery berkorelasi yang berisi fungsi nondeterministik.

  • Subkueri berkorelasi yang mereferensikan kolom tabel luar dengan tipe data yang tidak mendukung operasi hashing atau kesetaraan.