OR条件を2つ以上の個別のクエリのUNION ALLに変換する - PolarDB

PolarDB for PostgreSQL は、OR条件を2つ以上の個別のクエリのUNION ALLに変換できるクエリ最適化機能を提供します。この変換により、データベースは最も費用効果の高い実行パスを選択することができ、より効率的なクエリ実行が可能になります。

背景情報

PostgreSQLオプティマイザは、SQLクエリのOR条件を最適化する能力が限られています。 SQLクエリのOR条件が1つのテーブルのみを含み、条件の各部分が適切にインデックス付けされている場合、オプティマイザはBitmapOrインデックスパスを生成します。例：

EXPLAIN SELECT * FROM my_test WHERE (id = 123 OR name = '123' OR salary = 123.0);
                                      QUERY PLAN
---------------------------------------------------------------------------------------
 Bitmap Heap Scan on my_test  (cost=12.90..24.33 rows=3 width=15)
   Recheck Cond: ((id = 123) OR ((name)::text = '123'::text) OR (salary = 123.0))
   ->  BitmapOr  (cost=12.90..12.90 rows=3 width=0)
         ->  Bitmap Index Scan on my_test_id_idx  (cost=0.00..4.30 rows=1 width=0)
               Index Cond: (id = 123)
         ->  Bitmap Index Scan on my_test_name_idx  (cost=0.00..4.30 rows=1 width=0)
               Index Cond: ((name)::text = '123'::text)
         ->  Bitmap Index Scan on my_test_salary_idx  (cost=0.00..4.30 rows=1 width=0)
               Index Cond: (salary = 123.0)
(9 rows)

SQLクエリのOR条件に複数のテーブルが含まれる場合、オプティマイザはOR条件の一部を、テーブルが結合された後に適用されるフィルタ条件として扱います。これにより、SQLクエリの実行効率が低下する可能性があります。例：

EXPLAIN ANALYZE SELECT * FROM t1 JOIN t2 ON t1.id = t2.id WHERE (t1.num = 1 OR t2.cnt = 2);
                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=299.00..660.50 rows=110 width=51) (actual time=5.992..15.673 rows=110 loops=1)
   Hash Cond: (t1.id = t2.id)
   Join Filter: ((t1.num = 1) OR (t2.cnt = 2))
   Rows Removed by Join Filter: 9890
   ->  Seq Scan on t1  (cost=0.00..174.00 rows=10000 width=25) (actual time=0.012..2.080 rows=10000 loops=1)
   ->  Hash  (cost=174.00..174.00 rows=10000 width=26) (actual time=5.855..5.857 rows=10000 loops=1)
         Buckets: 16384  Batches: 1  Memory Usage: 704kB
         ->  Seq Scan on t2  (cost=0.00..174.00 rows=10000 width=26) (actual time=0.007..1.779 rows=10000 loops=1)
 Planning Time: 1.237 ms
 Execution Time: 15.836 ms
(10 rows)

このクエリのOR条件の一部は単一の結合条件として扱われ、オプティマイザはt1.num列またはt2.cnt列のインデックスを使用してクエリを高速化することはできません。代わりに、オプティマイザはt1およびt2テーブルでフルスキャンを実行する必要があります。クエリを最適化するには、OR条件を、それぞれがor条件の一部を処理する2つ以上の個別のクエリのUNION ALLに変換します。例：

EXPLAIN ANALYZE
SELECT * FROM t1 JOIN t2 ON t1.id = t2.id WHERE t1.num = 1
UNION ALL
SELECT * FROM t1 JOIN t2 ON t1.id = t2.id WHERE t2.cnt = 2 AND (t1.num != 1 OR (t1.num = 1) IS NULL);
                                                               QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
 Append  (cost=85.48..412.26 rows=110 width=51) (actual time=0.350..4.832 rows=110 loops=1)
   ->  Hash Join  (cost=85.48..297.98 rows=100 width=51) (actual time=0.349..4.653 rows=100 loops=1)
         Hash Cond: (t2.id = t1.id)
         ->  Seq Scan on t2  (cost=0.00..174.00 rows=10000 width=26) (actual time=0.009..1.719 rows=10000 loops=1)
         ->  Hash  (cost=84.23..84.23 rows=100 width=25) (actual time=0.318..0.320 rows=100 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 14kB
               ->  Bitmap Heap Scan on t1  (cost=5.06..84.23 rows=100 width=25) (actual time=0.065..0.265 rows=100 loops=1)
                     Recheck Cond: (num = 1)
                     Heap Blocks: exact=73
                     ->  Bitmap Index Scan on t1_num_idx  (cost=0.00..5.04 rows=100 width=0) (actual time=0.037..0.037 rows=100 loops=1)
                           Index Cond: (num = 1)
   ->  Nested Loop  (cost=4.65..112.63 rows=10 width=51) (actual time=0.049..0.159 rows=10 loops=1)
         ->  Bitmap Heap Scan on t2 t2_1  (cost=4.36..33.46 rows=10 width=26) (actual time=0.026..0.045 rows=10 loops=1)
               Recheck Cond: (cnt = 2)
               Heap Blocks: exact=10
               ->  Bitmap Index Scan on t2_cnt_idx  (cost=0.00..4.36 rows=10 width=0) (actual time=0.017..0.018 rows=10 loops=1)
                     Index Cond: (cnt = 2)
         ->  Index Scan using t1_id_idx on t1 t1_1  (cost=0.29..7.91 rows=1 width=25) (actual time=0.009..0.009 rows=1 loops=10)
               Index Cond: (id = t2_1.id)
               Filter: ((num <> 1) OR ((num = 1) IS NULL))
 Planning Time: 1.150 ms
 Execution Time: 5.014 ms
(22 rows)

変換後、オプティマイザはt1.num列とt2.cnt列のインデックスを使用して、処理するデータ量を減らし、実行パフォーマンスを向上させることができます。

サポートされているバージョン

PolarDB for PostgreSQLクラスターは、次のいずれかのデータベースエンジンバージョンを実行します。

改訂バージョンが14.13.27.0以降のPostgreSQL 14

説明

次のステートメントを実行して、PolarDB for PostgreSQL クラスターのデータベースエンジンリビジョンバージョンを照会できます。

SELECT version();

クラスターのデータベースエンジン改訂版をアップグレードするには、「バージョンのアップグレード」をご参照ください。

Parameters

パラメーターを使用して、2つ以上の個別のクエリのOR条件のUNION ALLへの変換を管理できます。次の表に、パラメーターを示します。

パラメーター

説明

polar_cbqt_cost_threshold

OR条件変換しきい値。 OR条件を使用するクエリの推定実行コストがしきい値を超える場合、オプティマイザはOR条件をUNION ALLの個別クエリに変換します。有効値: (0,+ ∞) デフォルト値: 50000

パラメーターを0に設定すると、システムは各クエリのOR条件を個別のクエリのUNION ALLに変換します。このパラメーターを0に設定しないことを推奨します。これにより、SQLの実行時間が増加し、パフォーマンスが低下する可能性があります。

polar_cbqt_convert_or_to_union_all_mode

OR条件のUNION ALLへの変換を有効にするかどうかを指定します。有効な値：

OFF (デフォルト): 2つ以上の個別のクエリのOR条件のUNION ALLへの変換を無効にします。
ON: OR条件を2つ以上の個別のクエリのUNION ALLに変換できます。
FORCE: 変換されたパスの合計コストが元のパスの合計コストよりも高い場合でも、OR条件を2つ以上の個別のクエリのUNION ALLに変換します。

使用上の注意

polar_cbqt_cost_thresholdパラメーターを0に設定するか、polar_cbqt_convert_or_to_union_all_modeパラメーターをFORCEに設定すると、or条件が2つ以上の個別のクエリのUNION ALLに変換されます。特定のクエリのOR条件を個別のクエリのUNION ALLに強制的に変換するには、ヒントを使用することを推奨します。詳細については、このトピックの「ヒントの使用」セクションを参照してください。
OR条件が1つのテーブルのみを含む場合、オプティマイザはOR条件を変換しません。
強制変換モードでは、適切なクエリが複数存在する場合、オプティマイザはコストが最も低い実行パスを選択します。
OR条件に6つ以上のパラメーターが含まれている場合、オプティマイザはOR条件を変換しません。
SQLクエリに6つ以上のOR条件が含まれている場合、オプティマイザは最初の6つのOR条件のみを変換します。

例

データの準備

CREATE TABLE t1(id int, num int, dsc text, log_date text);
CREATE TABLE t2(id int, cnt int, change text, op_date text);

INSERT INTO t1 SELECT i, i%100, 'test'||1, to_char('1990-10-10'::date + i, 'YYYY-MM-DD') FROM generate_series(1,10000)i;
INSERT INTO t2 SELECT i, i%1000, 'now'||i, to_char('1990-10-10'::date + i, 'YYYY-MM-DD') FROM generate_series(1,10000)i;

CREATE INDEX ON t1(id);
CREATE INDEX ON t1(num);
CREATE INDEX ON t2(id);
CREATE INDEX ON t2(cnt);

ANALYZE t1;
ANALYZE t2;

基本機能

OR条件の2つ以上の個別のクエリのUNION ALLへの変換を無効にします。

SET polar_cbqt_cost_threshold to 100;
SET polar_cbqt_convert_or_to_union_all_mode to off;

EXPLAIN ANALYZE SELECT * FROM t1 JOIN t2 ON t1.id = t2.id WHERE (t1.num = 1 OR t2.cnt = 2);

サンプル結果:

                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=299.00..660.50 rows=110 width=51) (actual time=5.992..15.673 rows=110 loops=1)
   Hash Cond: (t1.id = t2.id)
   Join Filter: ((t1.num = 1) OR (t2.cnt = 2))
   Rows Removed by Join Filter: 9890
   ->  Seq Scan on t1  (cost=0.00..174.00 rows=10000 width=25) (actual time=0.012..2.080 rows=10000 loops=1)
   ->  Hash  (cost=174.00..174.00 rows=10000 width=26) (actual time=5.855..5.857 rows=10000 loops=1)
         Buckets: 16384  Batches: 1  Memory Usage: 704kB
         ->  Seq Scan on t2  (cost=0.00..174.00 rows=10000 width=26) (actual time=0.007..1.779 rows=10000 loops=1)
 Planning Time: 1.237 ms
 Execution Time: 15.836 ms
(10 rows)

OR条件を2つ以上の個別のクエリのUNION ALLに変換できるようにします。

SET polar_cbqt_cost_threshold to 100;
SET polar_cbqt_convert_or_to_union_all_mode to on;

EXPLAIN ANALYZE SELECT * FROM t1 JOIN t2 ON t1.id = t2.id WHERE (t1.num = 1 OR t2.cnt = 2);

サンプル結果:

                                                               QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
 Append  (cost=85.48..411.16 rows=110 width=51) (actual time=0.396..4.822 rows=110 loops=1)
   ->  Hash Join  (cost=85.48..297.98 rows=100 width=51) (actual time=0.395..4.639 rows=100 loops=1)
         Hash Cond: (t2.id = t1.id)
         ->  Seq Scan on t2  (cost=0.00..174.00 rows=10000 width=26) (actual time=0.010..1.750 rows=10000 loops=1)
         ->  Hash  (cost=84.23..84.23 rows=100 width=25) (actual time=0.333..0.335 rows=100 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 14kB
               ->  Bitmap Heap Scan on t1  (cost=5.06..84.23 rows=100 width=25) (actual time=0.056..0.247 rows=100 loops=1)
                     Recheck Cond: (num = 1)
                     Heap Blocks: exact=73
                     ->  Bitmap Index Scan on t1_num_idx  (cost=0.00..5.04 rows=100 width=0) (actual time=0.028..0.028 rows=100 loops=1)
                           Index Cond: (num = 1)
   ->  Nested Loop  (cost=4.65..112.63 rows=10 width=51) (actual time=0.049..0.164 rows=10 loops=1)
         ->  Bitmap Heap Scan on t2  (cost=4.36..33.46 rows=10 width=26) (actual time=0.027..0.044 rows=10 loops=1)
               Recheck Cond: (cnt = 2)
               Heap Blocks: exact=10
               ->  Bitmap Index Scan on t2_cnt_idx  (cost=0.00..4.36 rows=10 width=0) (actual time=0.019..0.019 rows=10 loops=1)
                     Index Cond: (cnt = 2)
         ->  Index Scan using t1_id_idx on t1  (cost=0.29..7.91 rows=1 width=25) (actual time=0.010..0.010 rows=1 loops=10)
               Index Cond: (id = t2.id)
               Filter: ((num <> 1) OR ((num = 1) IS NULL))
 Planning Time: 2.903 ms
 Execution Time: 4.980 ms
(22 rows)

2つ以上の個別のクエリでOR条件をUNION ALLに変換できるようにすると、実行計画ではt1.num列とt2.cnt列のインデックスを使用して、手動変換と同じ効果を得ることができます。

OR条件の2つ以上の個別のクエリのUNION ALLへの変換を強制します

OR条件を2つ以上の個別のクエリのUNION ALLに変換できるようにします。

SET polar_cbqt_cost_threshold to 100;
SET polar_cbqt_convert_or_to_union_all_mode to on;

EXPLAIN ANALYZE SELECT * FROM t1, t2 WHERE t1.dsc= t2.change AND (t1.log_date = '2024-01-01' OR t2.op_date = '2024-01-01');

サンプル結果:

                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=299.00..660.50 rows=2 width=51) (actual time=14.321..14.325 rows=0 loops=1)
   Hash Cond: (t1.dsc = t2.change)
   Join Filter: ((t1.log_date = '2024-01-01'::text) OR (t2.op_date = '2024-01-01'::text))
   ->  Seq Scan on t1  (cost=0.00..174.00 rows=10000 width=25) (actual time=0.016..3.204 rows=10000 loops=1)
   ->  Hash  (cost=174.00..174.00 rows=10000 width=26) (actual time=6.506..6.508 rows=10000 loops=1)
         Buckets: 16384  Batches: 1  Memory Usage: 704kB
         ->  Seq Scan on t2  (cost=0.00..174.00 rows=10000 width=26) (actual time=0.006..1.755 rows=10000 loops=1)
 Planning Time: 0.932 ms
 Execution Time: 14.571 ms
(9 rows)

OR条件を2つ以上の個別のクエリのUNION ALLに強制的に変換します。

SET polar_cbqt_cost_threshold to 100;
SET polar_cbqt_convert_or_to_union_all_mode to force;

EXPLAIN ANALYZE SELECT * FROM t1, t2 WHERE t1.dsc= t2.change AND (t1.log_date = '2024-01-01' OR t2.op_date = '2024-01-01');

サンプル結果:

                                                   QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
 Append  (cost=199.01..871.05 rows=2 width=51) (actual time=9.915..9.923 rows=0 loops=1)
   ->  Hash Join  (cost=199.01..410.52 rows=1 width=51) (actual time=5.046..5.050 rows=0 loops=1)
         Hash Cond: (t2.change = t1.dsc)
         ->  Seq Scan on t2  (cost=0.00..174.00 rows=10000 width=26) (actual time=0.015..0.015 rows=1 loops=1)
         ->  Hash  (cost=199.00..199.00 rows=1 width=25) (actual time=5.014..5.016 rows=0 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 8kB
               ->  Seq Scan on t1  (cost=0.00..199.00 rows=1 width=25) (actual time=5.013..5.013 rows=0 loops=1)
                     Filter: (log_date = '2024-01-01'::text)
                     Rows Removed by Filter: 10000
   ->  Hash Join  (cost=199.01..460.52 rows=1 width=51) (actual time=4.865..4.867 rows=0 loops=1)
         Hash Cond: (t1.dsc = t2.change)
         ->  Seq Scan on t1  (cost=0.00..224.00 rows=9999 width=25) (actual time=0.015..0.016 rows=1 loops=1)
               Filter: ((log_date <> '2024-01-01'::text) OR ((log_date = '2024-01-01'::text) IS NULL))
         ->  Hash  (cost=199.00..199.00 rows=1 width=26) (actual time=4.828..4.829 rows=0 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 8kB
               ->  Seq Scan on t2  (cost=0.00..199.00 rows=1 width=26) (actual time=4.827..4.827 rows=0 loops=1)
                     Filter: (op_date = '2024-01-01'::text)
                     Rows Removed by Filter: 10000
 Planning Time: 0.777 ms
 Execution Time: 10.088 ms
(20 rows)

変換前後の実行プランの総コストを基準にすると、OR条件を変換しない場合の実行プランの総コストは低くなります。非強制モードでは、オプティマイザは、コスト推定に基づいてOR条件を変換するかどうかを決定し、変換されたパスを常に使用するとは限らない。強制モードでは、オプティマイザは、推定コストに関係なく、変換されたパスを使用するよう強制される。強制モードは、オプティマイザがコストを正確に推定できず、OR条件の変換が必要なシナリオに適しています。

ヒントの使用

特定のクエリのOR条件を2つ以上の個別のクエリのUNION ALLに強制的に変換するには、ヒント機能を使用することもできます。

SET polar_cbqt_convert_or_to_union_all_mode to off;

EXPLAIN ANALYZE /*+ Set(polar_cbqt_convert_or_to_union_all_mode force) Set(polar_cbqt_cost_threshold 0) */ SELECT * FROM t1, t2 WHERE t1.dsc= t2.change and (t1.log_date = '2024-01-01' or t2.op_date = '2024-01-01');

サンプル結果:

                                                   QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
 Append  (cost=199.01..871.05 rows=2 width=51) (actual time=9.684..9.691 rows=0 loops=1)
   ->  Hash Join  (cost=199.01..410.52 rows=1 width=51) (actual time=4.711..4.714 rows=0 loops=1)
         Hash Cond: (t2.change = t1.dsc)
         ->  Seq Scan on t2  (cost=0.00..174.00 rows=10000 width=26) (actual time=0.013..0.013 rows=1 loops=1)
         ->  Hash  (cost=199.00..199.00 rows=1 width=25) (actual time=4.682..4.684 rows=0 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 8kB
               ->  Seq Scan on t1  (cost=0.00..199.00 rows=1 width=25) (actual time=4.681..4.681 rows=0 loops=1)
                     Filter: (log_date = '2024-01-01'::text)
                     Rows Removed by Filter: 10000
   ->  Hash Join  (cost=199.01..460.52 rows=1 width=51) (actual time=4.969..4.970 rows=0 loops=1)
         Hash Cond: (t1.dsc = t2.change)
         ->  Seq Scan on t1  (cost=0.00..224.00 rows=9999 width=25) (actual time=0.018..0.018 rows=1 loops=1)
               Filter: ((log_date <> '2024-01-01'::text) OR ((log_date = '2024-01-01'::text) IS NULL))
         ->  Hash  (cost=199.00..199.00 rows=1 width=26) (actual time=4.935..4.936 rows=0 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 8kB
               ->  Seq Scan on t2  (cost=0.00..199.00 rows=1 width=26) (actual time=4.934..4.934 rows=0 loops=1)
                     Filter: (op_date = '2024-01-01'::text)
                     Rows Removed by Filter: 10000
 Planning Time: 0.798 ms
 Execution Time: 9.858 ms
(20 rows)

OR条件変換しきい値

基本機能の例では、元のSQL実行プランのコストは660.50です。 OR条件変換しきい値をこの値よりも高い値に設定して、同じSQLクエリを再度実行した場合:

SET polar_cbqt_cost_threshold to 1000; -- Set the OR condition conversion threshold.
SET polar_cbqt_convert_or_to_union_all_mode to force; -- Force the conversion of the OR condition.

EXPLAIN ANALYZE SELECT * FROM t1 JOIN t2 ON t1.id = t2.id WHERE (t1.num = 1 OR t2.cnt = 2);

サンプル結果:

                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=299.00..660.50 rows=110 width=51) (actual time=6.374..15.802 rows=110 loops=1)
   Hash Cond: (t1.id = t2.id)
   Join Filter: ((t1.num = 1) OR (t2.cnt = 2))
   Rows Removed by Join Filter: 9890
   ->  Seq Scan on t1  (cost=0.00..174.00 rows=10000 width=25) (actual time=0.011..2.038 rows=10000 loops=1)
   ->  Hash  (cost=174.00..174.00 rows=10000 width=26) (actual time=6.266..6.268 rows=10000 loops=1)
         Buckets: 16384  Batches: 1  Memory Usage: 704kB
         ->  Seq Scan on t2  (cost=0.00..174.00 rows=10000 width=26) (actual time=0.006..1.778 rows=10000 loops=1)
 Planning Time: 0.663 ms
 Execution Time: 16.036 ms
(10 rows)

上記の結果は、強制モードであっても、オプティマイザが元のパスを選択することを示しています。これは、元のプランの総コストがしきい値を超えないためです。オプティマイザは、OR条件を2つ以上の個別のクエリのUNION ALLに変換しません。