# Aurora PostgreSQL で相関サブクエリを最適化する
<a name="apg-correlated-subquery"></a>

 相関サブクエリは、外部クエリのテーブルの列を参照します。外部クエリが返す 1 行ごとに 1 回評価されます。次の例では、サブクエリはテーブル ot の列を参照します。このテーブルはサブクエリの FROM 句に含まれませんが、外部クエリの FROM 句で参照されます。テーブル ot の行数が 100 万行の場合、サブクエリは 100 万回評価される必要があります。

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

**注記**  
サブクエリ変換とサブクエリキャッシュは、バージョン 16.8 以降の Aurora PostgreSQL で使用できますが、Babelfish for Aurora PostgreSQL は 4.2.0 からのこれらの機能をサポートしています。
Babelfish for Aurora PostgreSQL バージョン 4.6.0 および 5.2.0 以降では、以下のパラメータがこれらの機能を制御します。  
 babelfishpg\$1tsql.apg\$1enable\$1correlated\$1scalar\$1transform 
 babelfishpg\$1tsql.apg\$1enable\$1subquery\$1cache 
デフォルトでは、どちらのパラメータも有効になっています。

## サブクエリ変換を使用した Aurora PostgreSQL クエリのパフォーマンスの向上
<a name="apg-corsubquery-transformation"></a>

Aurora PostgreSQL は、相関サブクエリを同等の外部結合に変換して高速化できます。この最適化は、次の 2 種類の相関サブクエリに適用されます。
+ 単一の集計値を返し、SELECT リストに表示されるサブクエリ。

  ```
  SELECT ot.a, ot.b, (SELECT AVG(it.b) FROM it WHERE it.a = ot.a) FROM ot;
  ```
+ 単一の集計値を返し、WHERE 句に表示されるサブクエリ。

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

### サブクエリでの変換の有効化
<a name="apg-corsub-transform"></a>

 相関サブクエリを同等の外部結合に変換できるようにするには、`apg_enable_correlated_scalar_transform` パラメータを `ON` に設定します。このパラメータのデフォルト値は `OFF` です。

パラメータ設定は、クラスターまたはインスタンスのパラメータグループで変更できます。詳細については[Amazon Aurora のパラメータグループ](USER_WorkingWithParamGroups.md)を参照してください。

または、次のコマンドを呼び出すことで、現在のセッションのみの設定を構成できます。

```
SET apg_enable_correlated_scalar_transform TO ON;
```

### 変換の検証
<a name="apg-corsub-transform-confirm"></a>

EXPLAIN コマンドを使用して、相関サブクエリがクエリプラン内の外部結合に変換されているかどうかを確認します。

 変換を有効にすると、該当する相関サブクエリ部分が外部結合に変換されます。例えば、次のようになります。

```
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
```

GUC パラメータが `OFF` に設定されている場合、同じクエリは変換されません。プランには外部結合はなく、代わりにサブプランがあります。

```
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)
```

### 制限事項
<a name="apg-corsub-transform-limitations"></a>
+ サブクエリは SELECT リストまたは where 句の中の条件の 1 つに存在する必要があります。そうでない場合、変換されません。
+ サブクエリは集計関数を返す必要があります。ユーザー定義の集計関数は変換ではサポートされていません。
+ 戻り式が単純な集計関数ではないサブクエリは変換されません。
+ サブクエリ WHERE 句の相関条件は、単純な列参照である必要があります。そうでない場合、変換されません。
+ サブクエリ where 句の相関条件は、単純な等価述語である必要があります。
+ サブクエリに HAVING 句または GROUP BY 句を含めることはできません。
+ サブクエリの where 句には、AND と組み合わせた 1 つ以上の述語を含めることができます。

**注記**  
変換のパフォーマンスへの影響は、スキーマ、データ、ワークロードによって異なります。変換を伴う相関サブクエリの実行は、外部クエリによって生成される行数が増えるにつれてパフォーマンスが大幅に向上する可能性があります。この機能を本番環境で有効にする前に、実際のスキーマ、データ、ワークロードを使用して本番環境でテストすることを強くお勧めします。

## サブクエリキャッシュを使用して Aurora PostgreSQL クエリのパフォーマンスを向上させる
<a name="apg-subquery-cache"></a>

 Aurora PostgreSQL は、相関サブクエリの結果を保存するためのサブクエリキャッシュをサポートしています。この機能は、サブクエリの結果が既にキャッシュにある場合に、相関サブクエリの繰り返しの実行をスキップします。

### サブクエリキャッシュについて
<a name="apg-subquery-cache-understand"></a>

 PostgreSQL の Memoize ノードは、サブクエリキャッシュの重要な部分です。Memoize ノードは、入力パラメータ値からクエリ結果の行にマッピングするために、ローカルキャッシュにハッシュテーブルを維持します。ハッシュテーブルのメモリ制限は、work\$1mem と hash\$1mem\$1multiplier の積です。詳細については、「[Resource Consumption](https://www.postgresql.org/docs/16/runtime-config-resource.html)」を参照してください。

 クエリの実行中、サブクエリキャッシュはキャッシュヒットレート (CHR) を使用して、キャッシュがクエリのパフォーマンスを向上させているかどうかを推定し、クエリの実行時にキャッシュを引き続き使用するかどうかを決定します。CHR は、キャッシュヒット数とリクエストの合計数の比率です。例えば、相関サブクエリを 100 回実行する必要があり、それらの実行結果のうち 70 個をキャッシュから取得できる場合、CHR は 0.7 です。

キャッシュミスの apg\$1subquery\$1cache\$1check\$1interval 数ごとに、CHR が apg\$1subquery\$1cache\$1hit\$1rate\$1threshold より大きいかどうかをチェックすることで、サブクエリキャッシュの利点が評価されます。そうでない場合、キャッシュはメモリから削除され、クエリの実行はキャッシュされていない元のサブクエリの再実行に戻ります。

### サブクエリのキャッシュ動作を制御するパラメータ
<a name="apg-subquery-cache-parameters"></a>

次の表に、サブクエリキャッシュの動作を制御するパラメータを示します。


|  パラメータ  | 説明  | デフォルト | 許可  | 
| --- | --- | --- | --- | 
| apg\$1enable\$1subquery\$1cache  | 相関スカラーサブクエリのキャッシュの使用を有効にします。  | VOFF  | ON、OFF | 
| apg\$1subquery\$1cache\$1check\$1interval  | サブクエリのキャッシュヒットレートを評価する頻度をキャッシュミスの数で設定します。  | 500  | 0-2147483647 | 
| apg\$1subquery\$1cache\$1hit\$1rate\$1threshold  | サブクエリのキャッシュヒットレートのしきい値を設定します。  | 0.3  | 0.0–1.0 | 

**注記**  
`apg_subquery_cache_check_interval` の値を大きくすると、CHR ベースのキャッシュメリットの推定の精度は向上しますが、キャッシュテーブルに `apg_subquery_cache_check_interval` 行が含まれるまで CHR は評価されないため、キャッシュオーバーヘッドが増加します。
`apg_subquery_cache_hit_rate_threshold` の値が大きいほど、サブクエリキャッシュの放棄と、キャッシュされていない元のサブクエリの再実行に戻るように偏ります。

パラメータ設定は、クラスターまたはインスタンスのパラメータグループで変更できます。詳細については[Amazon Aurora のパラメータグループ](USER_WorkingWithParamGroups.md)を参照してください。

または、次のコマンドを呼び出すことで、現在のセッションのみの設定を構成できます。

```
SET apg_enable_subquery_cache TO ON;
```

### Aurora PostgreSQL でサブクエリキャッシュを有効にする
<a name="apg-subquery-cache-turningon"></a>

サブクエリキャッシュを有効にすると、Aurora PostgreSQL はキャッシュを適用してサブクエリの結果を保存します。クエリプランの SubPlan の下に Memoize ノードが含まれるようになります。

 例えば、次のコマンドシーケンスは、サブクエリキャッシュのない単純な相関サブクエリの推定クエリ実行プランを示しています。

```
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)
```

`apg_enable_subquery_cache` を有効にすると、クエリプランの SubPlan ノードの下に Memoize ノードが含まれ、サブクエリがキャッシュを使用する予定であることを示します。

```
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)
```

 実際のクエリ実行プランには、キャッシュヒットやキャッシュミスなど、サブクエリキャッシュの詳細が含まれています。次の出力は、上記のクエリ例でテーブルにいくつかの値を挿入した後の、実際のクエリ実行プランを示しています。

```
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
```

キャッシュヒット数の合計は 4 で、キャッシュミス数の合計は 6 です。ヒットとミスの合計数が Memoize ノードのループ数よりも少ない場合、CHR 評価が成功せず、キャッシュがクリーンアップされ、ある時点で中止されたことを意味します。その後、サブクエリ実行は元のキャッシュされていない再実行に戻されます。

### 制限事項
<a name="apg-subquery-cache-limitations"></a>

サブクエリキャッシュは、相関サブクエリの特定のパターンをサポートしていません。サブクエリキャッシュが有効になっている場合でも、これらのタイプのクエリはキャッシュなしで実行されます。
+ IN/EXISTS/ANY/ALL の相関サブクエリ
+ 非決定的関数を含む相関サブクエリ 
+ ハッシュまたは等価演算をサポートしていないデータ型で外部テーブル列を参照する相関サブクエリ。