Aurora PostgreSQL で相関サブクエリを最適化する

相関サブクエリは、外部クエリのテーブルの列を参照します。外部クエリが返す 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);

サブクエリ変換を使用した 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 の Memoize ノードは、サブクエリキャッシュの重要な部分です。Memoize ノードは、入力パラメータ値からクエリ結果の行にマッピングするために、ローカルキャッシュにハッシュテーブルを維持します。ハッシュテーブルのメモリ制限は、work_mem と hash_mem_multiplier の積です。詳細については、「Resource Consumption」を参照してください。

クエリの実行中、サブクエリキャッシュはキャッシュヒットレート (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 の下に 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 評価が成功せず、キャッシュがクリーンアップされ、ある時点で中止されたことを意味します。その後、サブクエリ実行は元のキャッシュされていない再実行に戻されます。

制限

サブクエリキャッシュは、相関サブクエリの特定のパターンをサポートしていません。サブクエリキャッシュが有効になっている場合でも、これらのタイプのクエリはキャッシュなしで実行されます。