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

最佳化 Aurora PostgreSQL 中的相關子查詢

關聯的子查詢參考外部查詢中的資料表資料欄。它會針對外部查詢傳回的每個資料列評估一次。在下列範例中，子查詢參考資料表 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);

使用子查詢轉換來改善 Aurora PostgreSQL 查詢效能

在子查詢中啟用轉換

若要啟用相關子查詢轉換為同等外部聯結，請將 apg_enable_correlated_scalar_transform 參數設定為 ON。此參數的預設值為 OFF。

您可以修改叢集或執行個體參數群組來設定參數。如需詳細資訊，請參閱 Amazon Aurora 的參數群組。

或者，您也可以使用下列命令，為目前的工作階段設定 設定 ：

SET apg_enable_correlated_scalar_transform TO ON;

驗證轉換

使用 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)  

限制

使用子查詢快取來改善 Aurora PostgreSQL 查詢效能

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

了解子查詢快取

PostgreSQL 的記憶節點是子查詢快取的關鍵部分。記憶節點會在本機快取中維護雜湊資料表，以從輸入參數值映射至查詢結果列。雜湊資料表的記憶體限制是 work_mem 和 hash_mem_multiplier 的乘積。若要進一步了解，請參閱資源使用。

在查詢執行期間，子查詢快取會使用快取命中率 (CHR) 來估計快取是否改善查詢效能，並在查詢執行時間決定是否繼續使用快取。CHR 是快取命中次數與請求總數的比率。例如，如果相關子查詢需要執行 100 次，而且可以從快取擷取其中 70 個執行結果，則 CHR 為 0.7。

對於每個快取遺漏的 apg_subquery_cache_check_interval 數量，檢查 CHR 是否大於 apg_subquery_cache_hit_rate_threshold 來評估子查詢快取的優點。如果沒有，快取將從記憶體中刪除，查詢執行將返回原始、未快取的子查詢重新執行。

控制子查詢快取行為的參數

下表列出控制子查詢快取行為的參數。

您可以修改叢集或執行個體參數群組來設定參數。如需詳細資訊，請參閱 Amazon Aurora 的參數群組。

或者，您也可以使用下列命令，為目前的工作階段設定 設定 ：

SET apg_enable_subquery_cache TO ON;

在 Aurora PostgreSQL 中開啟子查詢快取

啟用子查詢快取時，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 評估未通過，並且快取已在某個時間點清除和捨棄。子查詢執行接著會傳回原始未快取的重新執行。

限制

子查詢快取不支援關聯子查詢的特定模式。即使子查詢快取已開啟，仍會在沒有快取的情況下執行這些類型的查詢：