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
<a name="apg-correlated-subquery"></a>

 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\$1tsql.apg\$1enable\$1correlated\$1scalar\$1transform 
 babelfishpg\$1tsql.apg\$1enable\$1subquery\$1cache 
Secara default, kedua parameter dihidupkan.

## Meningkatkan kinerja kueri PostgreSQL Aurora menggunakan transformasi subquery
<a name="apg-corsubquery-transformation"></a>

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
<a name="apg-corsub-transform"></a>

 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 adalah`OFF`. 

Anda dapat memodifikasi kelompok parameter cluster atau instance untuk mengatur parameter. Untuk mempelajari selengkapnya, lihat [](USER_WorkingWithParamGroups.md).

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

```
SET apg_enable_correlated_scalar_transform TO ON;
```

### Memverifikasi transformasi
<a name="apg-corsub-transform-confirm"></a>

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. Contoh: 

```
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 diputar`OFF`. 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
<a name="apg-corsub-transform-limitations"></a>
+ 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
<a name="apg-subquery-cache"></a>

 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
<a name="apg-subquery-cache-understand"></a>

 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\$1mem dan hash\$1mem\$1multiplier. Untuk mempelajari lebih lanjut, lihat [Konsumsi Sumber Daya](https://www.postgresql.org/docs/16/runtime-config-resource.html). 

 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\$1subquery\$1cache\$1check\$1interval yang hilang, manfaat cache subquery dievaluasi dengan memeriksa apakah CHR lebih besar dari apg\$1subquery\$1cache\$1hit\$1rate\$1threshold. 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
<a name="apg-subquery-cache-parameters"></a>

Tabel berikut mencantumkan parameter yang mengontrol perilaku cache subquery.


|  Parameter  | Deskripsi  | Default | Diizinkan  | 
| --- | --- | --- | --- | 
| apg\$1enable\$1subquery\$1cache  | Mengaktifkan penggunaan cache untuk subquery skalar berkorelasi.  | MATI  | ON, OFF | 
| apg\$1subquery\$1cache\$1check\$1interval  | Menetapkan frekuensi, dalam jumlah kesalahan cache, untuk mengevaluasi tingkat hit cache subquery.   | 500  | 0–2147483647 | 
| apg\$1subquery\$1cache\$1hit\$1rate\$1threshold  | 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 [](USER_WorkingWithParamGroups.md).

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
<a name="apg-subquery-cache-turningon"></a>

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 dihidupkan`apg_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
<a name="apg-subquery-cache-limitations"></a>

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.