Babelfish での相関サブクエリの最適化
相関サブクエリは、外部クエリのテーブルの列を参照します。外部クエリが返す 1 行ごとに 1 回評価されます。次の例では、サブクエリはテーブル t1 の列を参照します。このテーブルはサブクエリの FROM 句に含まれませんが、外部クエリの FROM 句で参照されます。テーブル t1 の行数が 100 万行の場合、サブクエリは 100 万回評価される必要があります。
SELECT col1, col2 FROM t1 WHERE col1 < (SELECT sum(col1) FROM t2 WHERE t1.col2 = t2.col2);
サブクエリ変換を使用した Babelfish クエリのパフォーマンスの向上
Babelfish は、相関サブクエリを同等の外部結合に変換して高速化できます。この最適化は、次の 2 種類の相関サブクエリに適用されます。
-
単一の集計値を返し、SELECT リストに表示されるサブクエリ。詳細については、Microsoft Transact-SQL のドキュメントの「SELECT clause
」を参照してください。 SELECT ( SELECT avg(a) FROM inner_sb_t2 WHERE inner_sb_t2.a = outer_sb_t1.a) FROM outer_sb_t1; -
単一の集計値を返し、WHERE 句に表示されるサブクエリ。
SELECT * FROM outer_sb_t1 WHERE ( SELECT avg(a) FROM inner_sb_t2 WHERE inner_sb_t2.a = outer_sb_t1.a) > 1.0;
サブクエリでの変換の有効化
相関サブクエリを同等の外部結合に変換できるようにするには、apg_enable_correlated_scalar_transform パラメータを ON に設定します。このパラメータは、Babelfish 4.2.0 以降のバージョンで利用できます。このパラメータのデフォルト値は OFF です。
パラメータ設定は、クラスターまたはインスタンスのパラメータグループで変更できます。詳細については、「Amazon Aurora のパラメータグループ」を参照してください。
または、関数 set_config を呼び出すことで、現在のセッションのみの設定を行うこともできます。例えば、次のコマンドを実行して、Babelfish でサブクエリキャッシュを有効にします。詳細については、「Configuration Settings Functions
1> SELECT set_config('apg_enable_subquery_cache', 'on', false) 2> GO
変換の検証
EXPLAIN コマンドを使用して、相関サブクエリがクエリプラン内の外部結合に変換されているかどうかを確認します。詳細については、「説明プランを使用して Babelfish クエリのパフォーマンスを向上させる」を参照してください。
変換を有効にすると、該当する相関サブクエリ部分が外部結合に変換されます。例:
1>select set_config('apg_enable_correlated_scalar_transform', 'true', false);2>GO1>set BABELFISH_STATISTICS PROFILE on2>GO1>select customer_name, ( select max(o.cost) from correlated_orders o2>where o.customer_id = c.customer_id and o.total_amount > 10 and o.customer_id != 10) AS max_order_amount3>from correlated_customers c order by customer_name ;4>GOQUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Query Text: select customer_name, ( select max(o.cost) from correlated_orders o where o.customer_id = c.customer_id and o.total_amount > 10 and o.customer_id != 10 ) AS max_order_amount from correlated_customers c order by customer_name Sort (cost=88.23..90.18 rows=780 width=40) Sort Key: c.customer_name NULLS FIRST -> Hash Left Join (cost=30.90..50.76 rows=780 width=40) Hash Cond: (c.customer_id = o.customer_id) -> Seq Scan on correlated_customers c (cost=0.00..17.80 rows=780 width=36) -> Hash (cost=28.73..28.73 rows=174 width=12) -> HashAggregate (cost=26.99..28.73 rows=174 width=12) Group Key: o.customer_id -> Seq Scan on correlated_orders o (cost=0.00..25.30 rows=338 width=12) Filter: (((total_amount)::numeric > 10) AND (customer_id <> 10)) ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GUC パラメータが OFF に設定されている場合、同じクエリは変換されません。プランには外部結合はなく、代わりにサブプランがあります。
1>select set_config('apg_enable_correlated_scalar_transform', 'false', false);2>GO1>select customer_name, ( select max(o.cost)2>from correlated_orders o3>where o.customer_id = c.customer_id and o.total_amount > 10 and o.customer_id != 10) AS max_order_amount4>from correlated_customers c order by customer_name ;5>GOQUERY PLAN ------------------------------------------------------------------------------------------------------------------------- Sort (cost=21789.97..21791.92 rows=780 width=40) Sort Key: c.customer_name NULLS FIRST -> Seq Scan on correlated_customers c (cost=0.00..21752.50 rows=780 width=40) SubPlan 1 -> Aggregate (cost=27.86..27.87 rows=1 width=8) -> Seq Scan on correlated_orders o (cost=0.00..27.85 rows=2 width=8) Filter: (((total_amount)::numeric > 10) AND (customer_id <> 10) AND (customer_id = c.customer_id))
制限事項
-
サブクエリは select_list または where 句の中の条件の 1 つに存在する必要があります。それ以外の場合、変換されません。
-
サブクエリは集計関数を返す必要があります。ユーザー定義の集計関数は変換ではサポートされていません。
-
戻り式が単純な集計関数ではないサブクエリは変換されません。
-
サブクエリ WHERE 句の相関条件は、単純な列参照である必要があります。そうでない場合、変換されません。
サブクエリ where 句の相関条件は、単純な等価述語である必要があります。
TOP 句を含む相関サブクエリは変換できません。
-
サブクエリに HAVING 句または GROUP BY 句を含めることはできません。
-
サブクエリの where 句には、AND と組み合わせた 1 つ以上の述語を含めることができます。WHERE 句に OR 句が含まれている場合、変換することはできません。
サブクエリキャッシュを使用して Babelfish クエリのパフォーマンスを向上させる
バージョン 4.2.0 以降、Babelfish は相関サブクエリの結果を保存するためのサブクエリキャッシュをサポートしています。この機能は、サブクエリの結果が既にキャッシュにある場合に、相関サブクエリの繰り返しの実行をスキップします。
サブクエリキャッシュについて
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 より大きいかどうかをチェックすることで、サブクエリキャッシュの利点が評価されます。そうでない場合、キャッシュはメモリから削除され、クエリの実行はキャッシュされていない元のサブクエリの再実行に戻ります。
サブクエリのキャッシュ動作を制御するパラメータ
次の表に、サブクエリキャッシュの動作を制御するパラメータを示します。
|
パラメータ |
説明 |
デフォルト |
許可 |
|---|---|---|---|
apg_enable_subquery_cache |
相関スカラーサブクエリのキャッシュの使用を有効にします。 |
VOFF |
ON、OFF |
apg_subquery_cache_check_interval |
サブクエリのキャッシュヒットレートを評価する頻度をキャッシュミスの数で設定します。 |
500 |
0-2147483647 |
apg_subquery_cache_hit_rate_threshold |
サブクエリのキャッシュヒットレートのしきい値を設定します。 |
0.3 |
0.0–1.0 |
注記
apg_subquery_cache_check_intervalの値を大きくすると、CHR ベースのキャッシュメリットの推定の精度は向上しますが、キャッシュテーブルにapg_subquery_cache_check_interval行が含まれるまで CHR は評価されないため、キャッシュオーバーヘッドが増加します。apg_subquery_cache_hit_rate_thresholdの値が大きいほど、サブクエリキャッシュの放棄と、キャッシュされていない元のサブクエリの再実行に戻るように偏ります。
パラメータ設定は、クラスターまたはインスタンスのパラメータグループで変更できます。詳細については、「パラメータグループを使用する」を参照してください。
または、関数 set_config を呼び出すことで、現在のセッションのみの設定を構成できます。例えば、次のコマンドを実行して、Babelfish でサブクエリキャッシュを有効にします。詳細については、「Configuration Settings Functions
1> SELECT set_config('apg_enable_subquery_cache', 'on', false) 2> GO
Babelfish でサブクエリキャッシュを有効にする
現在、サブクエリキャッシュはデフォルトで OFF です。前述のように、パラメータグループを変更することで有効にできます。apg_enable_subquery_cache が ON の場合、Babelfish はサブクエリキャッシュを適用してサブクエリの結果を保存します。クエリプランの SubPlan の下に Memoize ノードが含まれるようになります。
例えば、次のコマンドシーケンスは、サブクエリキャッシュのない単純な相関サブクエリの推定クエリ実行プランを示しています。詳細については、「説明プランを使用して Babelfish クエリのパフォーマンスを向上させる」を参照してください。
1>CREATE TABLE outer_table (outer_col1 INT, outer_col2 INT)2>CREATE TABLE inner_table (inner_col1 INT, inner_col2 INT)3>GO1>EXEC sp_babelfish_configure 'babelfishpg_tsql.explain_costs', 'off'2>GO1>SET BABELFISH_SHOWPLAN_ALL ON2>GO1>SELECT outer_col1, (2>SELECT inner_col13>FROM inner_table4>WHERE inner_col2 = outer_col25>) FROM outer_table6>GOQUERY PLAN ------------------------------------------------------------ Query Text: SELECT outer_col1, ( SELECT inner_col1 FROM inner_table WHERE inner_col2 = outer_col2 ) FROM outer_table Seq Scan on outer_table SubPlan 1 -> Seq Scan on inner_table Filter: (inner_col2 = outer_table.outer_col2)
1>SET BABELFISH_SHOWPLAN_ALL OFF2>GO1>EXEC sp_babelfish_configure 'babelfishpg_tsql.explain_costs', 'on'2>GO
apg_enable_subquery_cache を有効にすると、クエリプランの SubPlan ノードの下に Memoize ノードが含まれ、サブクエリがキャッシュを使用する予定であることを示します。
Seq Scan on outer_table SubPlan 1 -> Memoize Cache Key: outer_table.outer_col2 Cache Mode: logical -> Seq Scan on inner_table Filter: (inner_col2 = outer_table.outer_col2)
実際のクエリ実行プランには、キャッシュヒットやキャッシュミスなど、サブクエリキャッシュの詳細が含まれています。次の出力は、上記のクエリ例でテーブルにいくつかの値を挿入した後の、実際のクエリ実行プランを示しています。
Seq Scan on outer_table (actual rows=10 loops=1) SubPlan 1 -> Memoize (actual rows=1 loops=10) Cache Key: outer_table.outer_col2 Cache Mode: logical Hits: 4 Misses: 6 Evictions: 0 Overflows: 0 Memory Usage: 1kB -> Seq Scan on inner_table (actual rows=1 loops=6) Filter: (inner_col2 = outer_table.outer_col2) Rows Removed by Filter: 4
キャッシュヒット数の合計は 4 で、キャッシュミス数の合計は 6 です。ヒットとミスの合計数が Memoize ノードのループ数よりも少ない場合、CHR 評価が成功せず、キャッシュがクリーンアップされ、ある時点で中止されたことを意味します。その後、サブクエリ実行は元のキャッシュされていない再実行に戻されます。
制限事項
サブクエリキャッシュは、相関サブクエリの特定のパターンをサポートしていません。サブクエリキャッシュが有効になっている場合でも、これらのタイプのクエリはキャッシュなしで実行されます。
-
IN/EXISTS/ANY/ALL の相関サブクエリ
-
非決定的関数を含む相関サブクエリ
-
BIT、VARBINARY、または BINARY データ型の外部テーブル列を参照する相関サブクエリ
