PolarDB for PostgreSQL は、実行コストに基づいて特定の種類の変換を実行できるコストベースのクエリ変換 (CBQT) をサポートしています。 これにより、一部の複雑なクエリの効率が大幅に向上します。
背景情報
クエリ変換とは、クエリ文を別の等価なクエリ文に変換するプロセスのことです。PostgreSQL では、一般的なクエリ変換には、サブクエリのプルアップ、外部結合の削除、式のプリプロセス、不要な結合の削除、述語のプッシュダウンなどがあります。 これらの変換は、等価ルールに基づいて実行されます。 これらの変換を実行した後、クエリプランが最適化されます。 PostgreSQL は常にこれらの変換を実行しようとします。
ただし、サブリンクのプッシュダウンや OR から UNION ALL への変換など、他の変換を実行した後では、クエリプランが最適化されない場合があります。 したがって、PolarDB for PostgreSQL は、CBQT に基づいてクエリ変換を実行します。
複雑なクエリの場合、CBQT フレームワークは、各クエリブロックで実行できるコストベースのクエリ変換を収集し、状態空間を形成します。 CBQT は、指定された戦略に基づいて状態空間を検索し、コストが最も低い状態を選択します。
次の図に示すように、入力 SQL 文の場合、CBQT はクエリブロック 1 とクエリブロック 2 でコストベースのクエリ変換 A と B を収集します。 状態空間は、次の状態をで構成されます。
なし: 変換は実行されません。
[1, A]: クエリブロック 1 に対して変換 A が実行されます。
[1, B]: クエリブロック 1 に対して変換 B が実行されます。
[2, A]: クエリブロック 2 に対して変換 A が実行されます。
CBQT は、状態空間でこれらの変換を順番に実行します。 デフォルトの検索戦略 linear では、状態 [1, B] がクエリプランを最も最適化できます。 その結果、プラン 4 では [1, B] が使用されます。
前提条件
お使いの PolarDB for PostgreSQL クラスタが、次のデータベースエンジンバージョンで実行されていること。
PolarDB for PostgreSQL 14 (リビジョンバージョン 2.0.14.13.28.0 以降)
PolarDB for PostgreSQL 11 (リビジョンバージョン 2.0.11.15.44.0 以降)
コンソールでリビジョンバージョンを表示するか、SHOW polardb_version; 文を実行してリビジョンバージョンをクエリできます。 リビジョンバージョンをアップグレードするには、「バージョン管理」をご参照ください。
使用方法
PolarDB for PostgreSQL が提供する CBQT を管理するには、次のパラメータを設定します。
リビジョンバージョンが 2.0.14.15.29.0 以降のクラスタの場合、PolarDB コンソールでパラメータを直接設定できます。 詳細については、「クラスタパラメータの設定」をご参照ください。 また、クラスタに接続してパラメータを設定することもできます。 クラスタへの接続方法については、「PolarDB for PostgreSQL クラスタへの接続」をご参照ください。
パラメータ | 説明 |
polar_enable_cbqt | CBQT 機能を有効にするかどうかを指定します。有効な値:
|
polar_cbqt_cost_threshold | CBQT を有効にするためのコストのしきい値。有効な値: |
polar_cbqt_strategy | CBQT 状態空間の検索戦略。有効な値:
|
polar_cbqt_iteration_limit | CBQT の反復回数。有効な値: 反復回数が多いほど、最適なプランが選択される可能性が高くなり、消費時間も長くなります。 反復回数が少ないほど、最適なプランが選択される可能性が低くなり、消費時間も短くなります。 |
次のコストベースのクエリ書き換え機能がサポートされています。
パラメータ | 説明 |
polar_cbqt_convert_or_to_union_all_mode | OR 句を UNION ALL に変換して、クエリの効率を向上させます。 詳細については、「OR 条件を 2 つ以上の個別のクエリの UNION ALL に変換する」をご参照ください。 |
polar_cbqt_pushdown_sublink | サブリンクをサブクエリにプッシュダウンし、サブクエリ内のインデックスを使用してパラメータ化パスを生成することで、クエリの効率を向上させます。 詳細については、「サブリンクのプッシュダウン」をご参照ください。 |
例
この例では、サブリンクのプッシュダウン機能を使用して、CBQT 機能とそのパラメータを示します。
テストテーブルを作成し、テストデータを挿入します。
CREATE TABLE t_small(a int); CREATE TABLE t_big(a int, b int, c int); CREATE INDEX ON t_big(a); INSERT INTO t_big SELECT i, i, i FROM generate_series(1, 1000000)i; INSERT INTO t_small VALUES(1), (1000000); ANALYZE t_small, t_big; /* テストテーブルを作成し、テストデータを挿入します。 */CBQT 機能を無効にし、サブリンクのプッシュダウン機能を有効にします。 この場合、サブリンクのプッシュダウン機能は有効になりません。
t_bigテーブルで全表スキャンが実行され、効率が悪くなります。-- CBQT 機能を無効にする SET polar_enable_cbqt to off; -- サブリンクのプッシュダウン機能を有効にする SET polar_cbqt_pushdown_sublink to on; EXPLAIN ANALYZE SELECT * FROM (SELECT a, sum(b) b FROM t_big GROUP BY a)v WHERE a IN (SELECT a FROM t_small); /* CBQT 機能を無効にし、サブリンクプッシュダウン機能を有効にします。 */結果例:
QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------- Merge Semi Join (cost=1.46..59511.17 rows=10000 width=12) (actual time=0.052..1274.435 rows=2 loops=1) Merge Cond: (t_big.a = t_small.a) -> GroupAggregate (cost=0.42..46910.13 rows=1000000 width=12) (actual time=0.033..1151.005 rows=1000000 loops=1) Group Key: t_big.a -> Index Scan using t_big_a_idx on t_big (cost=0.42..31910.13 rows=1000000 width=8) (actual time=0.022..433.821 rows=1000000 loops=1) -> Sort (cost=1.03..1.03 rows=2 width=4) (actual time=0.015..0.016 rows=2 loops=1) Sort Key: t_small.a Sort Method: quicksort Memory: 25kB -> Seq Scan on t_small (cost=0.00..1.02 rows=2 width=4) (actual time=0.005..0.006 rows=2 loops=1) Planning Time: 0.904 ms Execution Time: 1274.539 ms (11 rows) /* 結果例 */CBQT 機能とサブリンクのプッシュダウン機能を有効にします。 この場合、サブリンクのプッシュダウン機能が有効になります。
a in (select a from t_small)句はサブクエリにプッシュダウンされます。 結合条件に基づいてt_bigテーブルのパラメータ化パスが生成され、スキャンされるデータ量と実行時間が大幅に短縮されます。-- CBQT 機能を有効にする SET polar_enable_cbqt to on; -- サブリンクのプッシュダウン機能を有効にする SET polar_cbqt_pushdown_sublink to on; EXPLAIN ANALYZE SELECT * FROM (SELECT a, sum(b) b FROM t_big GROUP BY a)v WHERE a IN (SELECT a FROM t_small); /* CBQT 機能とサブリンクプッシュダウン機能を有効にします。 */結果例:
QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------- GroupAggregate (cost=17.96..17.99 rows=2 width=12) (actual time=0.060..0.063 rows=2 loops=1) Group Key: t_big.a -> Sort (cost=17.96..17.96 rows=2 width=8) (actual time=0.052..0.053 rows=2 loops=1) Sort Key: t_big.a Sort Method: quicksort Memory: 25kB -> Nested Loop (cost=1.46..17.95 rows=2 width=8) (actual time=0.032..0.046 rows=2 loops=1) -> Unique (cost=1.03..1.04 rows=2 width=4) (actual time=0.014..0.018 rows=2 loops=1) -> Sort (cost=1.03..1.03 rows=2 width=4) (actual time=0.013..0.014 rows=2 loops=1) Sort Key: t_small.a Sort Method: quicksort Memory: 25kB -> Seq Scan on t_small (cost=0.00..1.02 rows=2 width=4) (actual time=0.006..0.007 rows=2 loops=1) -> Index Scan using t_big_a_idx on t_big (cost=0.42..8.44 rows=1 width=8) (actual time=0.009..0.010 rows=1 loops=2) Index Cond: (a = t_small.a) Planning Time: 0.644 ms Execution Time: 0.150 ms (15 rows) /* 結果例 */SQL 文の実行コストが CBQT のコストしきい値に達しない場合、CBQT 機能は有効になりません。 元のクエリプランが使用されます。 次の例では、元のクエリプランのコストは 59511.17 で、
polar_cbqt_cost_thresholdパラメータは 500000 に設定されています。-- CBQT 機能を有効にする SET polar_enable_cbqt to on; --- CBQT コストしきい値を設定する SET polar_cbqt_cost_threshold to 500000; -- サブリンクのプッシュダウン機能を有効にする SET polar_cbqt_pushdown_sublink to on; EXPLAIN ANALYZE SELECT * FROM (SELECT a, sum(b) b FROM t_big GROUP BY a)v WHERE a IN (SELECT a FROM t_small); /* SQL 文の実行コストが CBQT のコストしきい値に達しない場合 */結果例:
QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------- Merge Semi Join (cost=1.46..59511.17 rows=10000 width=12) (actual time=0.059..1253.452 rows=2 loops=1) Merge Cond: (t_big.a = t_small.a) -> GroupAggregate (cost=0.42..46910.13 rows=1000000 width=12) (actual time=0.041..1127.255 rows=1000000 loops=1) Group Key: t_big.a -> Index Scan using t_big_a_idx on t_big (cost=0.42..31910.13 rows=1000000 width=8) (actual time=0.029..414.488 rows=1000000 loops=1) -> Sort (cost=1.03..1.03 rows=2 width=4) (actual time=0.014..0.015 rows=2 loops=1) Sort Key: t_small.a Sort Method: quicksort Memory: 25kB -> Seq Scan on t_small (cost=0.00..1.02 rows=2 width=4) (actual time=0.005..0.006 rows=2 loops=1) Planning Time: 0.280 ms Execution Time: 1253.558 ms (11 rows) /* 結果例 */CBQT 状態空間の検索戦略を設定します。 次の例では、2 つのサブリンクをプッシュダウンできますが、2 番目のサブリンクのみをプッシュダウンするのが最適です。
polar_cbqt_strategy を linear (線形検索戦略) に設定します。 CBQT は最適なプランを選択します。
-- CBQT 機能を有効にする SET polar_enable_cbqt to on; --- CBQT 状態空間の検索戦略を設定する SET polar_cbqt_strategy to linear; -- サブリンクのプッシュダウン機能を有効にする SET polar_cbqt_pushdown_sublink to on; EXPLAIN SELECT * FROM (SELECT a, sum(b) b FROM t_big GROUP BY a)v WHERE a IN (SELECT a FROM t_big) UNION ALL SELECT * FROM (SELECT a, sum(b) b FROM t_big GROUP BY a)v WHERE a IN (SELECT a FROM t_small); /* CBQT 状態空間の検索戦略を設定します。 */結果例:
QUERY PLAN ------------------------------------------------------------------------------------------------------------- Append (cost=0.85..105692.60 rows=500002 width=12) -> Merge Semi Join (cost=0.85..98174.56 rows=500000 width=12) Merge Cond: (t_big_1.a = t_big.a) -> GroupAggregate (cost=0.42..46910.13 rows=1000000 width=12) Group Key: t_big_1.a -> Index Scan using t_big_a_idx on t_big t_big_1 (cost=0.42..31910.13 rows=1000000 width=8) -> Index Only Scan using t_big_a_idx on t_big (cost=0.42..26264.42 rows=1000000 width=4) -> GroupAggregate (cost=17.96..17.99 rows=2 width=12) Group Key: t_big_2.a -> Sort (cost=17.96..17.96 rows=2 width=8) Sort Key: t_big_2.a -> Nested Loop (cost=1.46..17.95 rows=2 width=8) -> Unique (cost=1.03..1.04 rows=2 width=4) -> Sort (cost=1.03..1.03 rows=2 width=4) Sort Key: t_small.a -> Seq Scan on t_small (cost=0.00..1.02 rows=2 width=4) -> Index Scan using t_big_a_idx on t_big t_big_2 (cost=0.42..8.44 rows=1 width=8) Index Cond: (a = t_small.a) (18 rows) /* 結果例 */polar_cbqt_strategy を twophase (2 フェーズ検索戦略) に設定します。 両方のサブリンクがプッシュダウンされます。
-- CBQT 機能を有効にする SET polar_enable_cbqt to on; --- CBQT 状態空間の検索戦略を設定する SET polar_cbqt_strategy to twophase; -- サブリンクのプッシュダウン機能を有効にする SET polar_cbqt_pushdown_sublink to on; EXPLAIN SELECT * FROM (SELECT a, sum(b) b FROM t_big GROUP BY a)v WHERE a IN (SELECT a FROM t_big) UNION ALL SELECT * FROM (SELECT a, sum(b) b FROM t_big GROUP BY a)v WHERE a IN (SELECT a FROM t_small); /* polar_cbqt_strategy を twophase に設定 */結果例:
QUERY PLAN ------------------------------------------------------------------------------------------------------------------ Append (cost=0.85..113192.60 rows=1000002 width=12) -> GroupAggregate (cost=0.85..88174.56 rows=1000000 width=12) Group Key: t_big.a -> Merge Semi Join (cost=0.85..73174.56 rows=1000000 width=8) Merge Cond: (t_big.a = t_big_1.a) -> Index Scan using t_big_a_idx on t_big (cost=0.42..31910.13 rows=1000000 width=8) -> Index Only Scan using t_big_a_idx on t_big t_big_1 (cost=0.42..26264.42 rows=1000000 width=4) -> GroupAggregate (cost=17.96..17.99 rows=2 width=12) Group Key: t_big_2.a -> Sort (cost=17.96..17.96 rows=2 width=8) Sort Key: t_big_2.a -> Nested Loop (cost=1.46..17.95 rows=2 width=8) -> Unique (cost=1.03..1.04 rows=2 width=4) -> Sort (cost=1.03..1.03 rows=2 width=4) Sort Key: t_small.a -> Seq Scan on t_small (cost=0.00..1.02 rows=2 width=4) -> Index Scan using t_big_a_idx on t_big t_big_2 (cost=0.42..8.44 rows=1 width=8) Index Cond: (a = t_small.a) (18 rows) /* 結果例 */
CBQT の反復回数を設定します。
polar_cbqt_iteration_limitを 1 に設定して、CBQT の反復回数を 1 に制限します。 前のシナリオでは、2 番目のサブリンクのみをプッシュダウンするのが最適であっても、反復回数が制限されているため、CBQT はその状態を適用しません。-- CBQT 機能を有効にする SET polar_enable_cbqt to on; --- CBQT 状態空間の検索戦略を設定する SET polar_cbqt_strategy to twophase; --- CBQT 反復回数を設定する SET polar_cbqt_iteration_limit to 1; -- サブリンクのプッシュダウン機能を有効にする SET polar_cbqt_pushdown_sublink to on; EXPLAIN SELECT * FROM (SELECT a, sum(b) b FROM t_big GROUP BY a)v WHERE a IN (SELECT a FROM t_big) UNION ALL SELECT * FROM (SELECT a, sum(b) b FROM t_big GROUP BY a)v WHERE a IN (SELECT a FROM t_small); /* CBQT の反復回数を設定します。 */結果例:
QUERY PLAN ------------------------------------------------------------------------------------------------------------------ Append (cost=0.85..113192.60 rows=1000002 width=12) -> GroupAggregate (cost=0.85..88174.56 rows=1000000 width=12) Group Key: t_big.a -> Merge Semi Join (cost=0.85..73174.56 rows=1000000 width=8) Merge Cond: (t_big.a = t_big_1.a) -> Index Scan using t_big_a_idx on t_big (cost=0.42..31910.13 rows=1000000 width=8) -> Index Only Scan using t_big_a_idx on t_big t_big_1 (cost=0.42..26264.42 rows=1000000 width=4) -> GroupAggregate (cost=17.96..17.99 rows=2 width=12) Group Key: t_big_2.a -> Sort (cost=17.96..17.96 rows=2 width=8) Sort Key: t_big_2.a -> Nested Loop (cost=1.46..17.95 rows=2 width=8) -> Unique (cost=1.03..1.04 rows=2 width=4) -> Sort (cost=1.03..1.03 rows=2 width=4) Sort Key: t_small.a -> Seq Scan on t_small (cost=0.00..1.02 rows=2 width=4) -> Index Scan using t_big_a_idx on t_big t_big_2 (cost=0.42..8.44 rows=1 width=8) Index Cond: (a = t_small.a) (18 rows) /* 結果例 */