PolarDB:相関サブクエリのプルアップ

前提条件

この機能は、PostgreSQL 14 (リビジョンバージョン: 14.8.11.0以降) を実行するPolarDB for PostgreSQLクラスターでサポートされています。

select version();

背景情報

PostgreSQLオプティマイザは、SUBLINKを使用して、サブクエリと関連演算子の組み合わせを式で表します。 次のタイプのSUBLINKがサポートされています。

• EXISTS_SUBLINK: EXISTS (SELECT ...) サブクエリを実装します。

• ALL_SUBLINK: ALL (SELECT ...) サブクエリを実装します。

• ANY_SUBLINK: ANY (SELECT ...) またはIN (SELECT ...) サブクエリを実装します。

オプティマイザは通常、ANY、IN、EXISTS、またはNOT EXISTS演算子で使用される相関サブクエリをプルアップしようとします。 このように、半結合または反結合を使用する最適化された実行プランが、相関サブクエリおよびその外部クエリに対して生成され得、これは、クエリ性能を改善する。 ANY_SUBLINKサブクエリが外部クエリの変数を参照する場合、サブクエリはプルアップされません。 その結果、サブクエリは、外部クエリと共に最適化されない。 サブクエリは独立して最適化することしかできないため、SQLの実行時間が大幅に増加します。

PolarDB for PostgreSQL では、パラメーターを使用して、相関するANY_SUBLINKサブクエリのプルアップを制御できます。 このパラメーターをONに設定すると、サブクエリが外部クエリの変数を参照する場合でも、オプティマイザはINまたはANY演算子で使用される相関サブクエリを取得します。 これにより、オプティマイザの検索範囲が広がり、オプティマイザはより良い実行計画を生成できるようになります。

使用法

polar_enable_pullup_with_lateralパラメーターを設定して、関連するANY_SUBLINKサブクエリをプルアップするかどうかを指定できます。 有効な値：

• ON (デフォルト)

• OFF

例

データの準備

CREATE TABLE t1 (a INT, b INT);
INSERT INTO t1 SELECT i, 1 FROM generate_series(1, 100000) i;
CREATE TABLE t2 AS SELECT * FROM t1;

相関サブクエリのプルアップ機能が無効になった後の実行計画と時間の表示

=> SET polar_enable_pullup_with_lateral TO OFF;

=> EXPLAIN (COSTS OFF, ANALYZE)
   SELECT * FROM t1
   WHERE t1.a IN (SELECT a FROM t2 WHERE t2.b = t1.b AND t2.b = 1);
                                   QUERY PLAN
---------------------------------------------------------------------------------
 Seq Scan on t1 (actual time=67.631..1641827.119 rows=100000 loops=1)
   Filter: (SubPlan 1)
   SubPlan 1
     ->  Result (actual time=0.005..13.124 rows=50000 loops=100000)
           One-Time Filter: (t1.b = 1)
           ->  Seq Scan on t2 (actual time=0.005..7.718 rows=50000 loops=100000)
                 Filter: (b = 1)
 Planning Time: 0.145 ms
 Execution Time: 1641847.702 ms
(9 rows)

相関サブクエリのプルアップ機能が有効化された後の実行計画と時間の表示

=> SET polar_enable_pullup_with_lateral TO ON;

=> EXPLAIN (COSTS OFF, ANALYZE)
   SELECT * FROM t1
   WHERE t1.a IN (SELECT a FROM t2 WHERE t2.b = t1.b AND t2.b = 1);
                                 QUERY PLAN
----------------------------------------------------------------------------
 Hash Semi Join (actual time=64.783..173.482 rows=100000 loops=1)
   Hash Cond: (t1.a = t2.a)
   ->  Seq Scan on t1 (actual time=0.016..25.440 rows=100000 loops=1)
         Filter: (b = 1)
   ->  Hash (actual time=64.550..64.551 rows=100000 loops=1)
         Buckets: 131072  Batches: 2  Memory Usage: 2976kB
         ->  Seq Scan on t2 (actual time=0.010..30.330 rows=100000 loops=1)
               Filter: (b = 1)
 Planning Time: 0.195 ms
 Execution Time: 178.050 ms
(10 rows)

前の例で示したように、相関サブクエリのプルアップ機能が有効になった後、サブクエリと外部クエリは半結合に最適化されます。 サブクエリのフィルタ条件は、外部クエリの結果を効果的にフィルタリングし、実行時間が大幅に短縮されます。 サブクエリがプルアップされていない場合、外部クエリに返される行をサブクエリでフィルタリングすることはできません。