本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。

# 最佳化 Aurora PostgreSQL 中的相互關聯子查詢
<a name="apg-correlated-subquery"></a>

 關聯的子查詢會參考外部查詢中的資料表資料欄。它會針對外部查詢傳回的每個資料列評估一次。在下列範例中，子查詢參考資料表 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 可以透過將相互關聯子查詢轉換為同等外部聯結來加速相互關聯子查詢。此最佳化適用於下列兩種相互關聯的子查詢類型：
+ 傳回單一彙總值並出現在 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 子句中的其中一個條件。否則，將不會進行轉換。
+ 子查詢必須傳回彙總函數。不支援使用者定義的彙總函數進行轉換。
+ 傳回運算式不是簡單彙總函數的子查詢將不會轉換。
+ 子查詢 WHERE 子句中的相互關聯條件應該是簡單的資料欄參考。否則，將不會進行轉換。
+ 子查詢中的相互關聯條件，其中子句必須是純等式述詞。
+ 子查詢不能包含 HAVING 或 GROUP BY 子句。
+ 子查詢中的 WHERE 子句可能包含一或多個與 AND 結合的述詞。

**注意**  
轉換的效能影響取決於您的結構描述、資料和工作負載。隨著外部查詢產生的資料列數增加，轉換的相互關聯子查詢執行可以大幅改善效能。強烈建議您在非生產環境中使用實際結構描述、資料和工作負載測試此功能，然後再在生產環境中啟用此功能。

## 使用子查詢快取來改善 Aurora PostgreSQL 查詢效能
<a name="apg-subquery-cache"></a>

 Aurora PostgreSQL 支援子查詢快取，以存放相互關聯子查詢的結果。當子查詢結果已在快取中時，此功能會略過重複的相會關聯子查詢執行。

### 了解子查詢快取
<a name="apg-subquery-cache-understand"></a>

 PostgreSQL 的記憶節點是子查詢快取的關鍵部分。記憶節點會在本機快取中維護雜湊資料表，以從輸入參數值映射至查詢結果列。雜湊資料表的記憶體限制是 work\$1mem 和 hash\$1mem\$1multiplier 的乘積。若要進一步了解，請參閱[資源耗用](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  | 啟用相互關聯純量子查詢的快取使用。  | OFF  | 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 下會有記憶節點。

 例如，下列命令序列顯示沒有子查詢快取之簡單相互關聯子查詢的預估查詢執行計劃。

```
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 節點下包含記憶節點，表示子查詢打算使用快取。

```
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。如果命中和未命中總數少於記憶節點中的迴圈數目，表示 CHR 評估未通過，且快取已在某個時間點清除和捨棄。子查詢執行接著會返回原始未快取的重新執行。

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

子查詢快取不支援相互關聯子查詢的特定模式。即使子查詢快取已開啟，仍會在沒有快取的情況下執行這些類型的查詢：
+ IN/EXISTS/ANY/ALL 相互關聯子查詢
+ 包含非確定性函數的相互關聯子查詢。
+ 參考資料類型不支援雜湊或相等操作之外部資料表資料欄的相互關聯子查詢。