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

適用範囲

この機能は、マイナーエンジンバージョンが 2.0.14.8.11.0 以降の PolarDB for PostgreSQL (PostgreSQL 14) クラスターでサポートされています。

背景情報

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 サブクエリのプルアップを制御できます。IN または ANY を使用する相関サブクエリの場合、サブクエリが外部クエリの変数を参照していてもプルアップできます。これにより、オプティマイザーの検索スペースが広がり、より良い実行計画の生成に役立ちます。

使用方法

polar_enable_pullup_with_lateral パラメーターは、相関 ANY_SUBLINK サブクエリのプルアップを有効または無効にします。有効な値は次のとおりです：

• ON (デフォルト)：相関 ANY_SUBLINK サブクエリのプルアップを有効にします。

• OFF：相関 ANY_SUBLINK サブクエリのプルアップを無効にします。

例

データの準備

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)

この例から、サブクエリがプルアップされると、オプティマイザーがサブクエリと外部クエリをセミ結合にまとめることがわかります。これにより、サブクエリ内のフィルター条件が外部クエリの結果セットを大幅に削減できるようになります。これにより、実行時間が大幅に短縮されます。サブクエリがプルアップされない場合、外部クエリから返される行をフィルター処理できません。