サブリンクのプッシュダウン - PolarDB - Alibaba Cloud ドキュメントセンター

PolarDB for PostgreSQL では、サブリンクをプッシュダウンするようにクエリを書き換えることができます。この機能は、IN 句と ANY 句を含む SQL 文の実行効率を向上させます。

背景情報

PostgreSQL では、ANY タイプのサブリンク (IN 句と ANY 句) は通常、セミジョイン (semi join) としてプルアップされます。ただし、リンクされたテーブルがセミジョインとしてプルアップできないサブクエリの場合、PostgreSQL はパラメーター化されたパスを作成できません。その結果、サブクエリは独立して実行されます。これは、サブクエリに大量のデータが含まれる場合、SQL パフォーマンスに影響します。

たとえば、次の SQL 文のサブクエリには GROUP BY 句が含まれており、プルアップできません。実行時間は主に t_big テーブルのスキャンとソートに依存します。 t_big テーブルのサイズが大きくなるにつれて、実行時間も長くなります。

EXPLAIN ANALYZE SELECT * FROM (SELECT a, sum(b) b FROM t_big GROUP BY a)v WHERE a IN (SELECT a FROM t_small);
                                                                   QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
 Merge Semi Join  (cost=0.55..59523.15 rows=10000 width=12) (actual time=0.064..1237.621 rows=2 loops=1)
   Merge Cond: (t_big.a = t_small.a)
   ->  GroupAggregate  (cost=0.42..46910.99 rows=1000000 width=12) (actual time=0.033..1113.615 rows=1000000 loops=1)
         Group Key: t_big.a
         ->  Index Scan using t_big_a_idx on t_big  (cost=0.42..31910.99 rows=1000000 width=8) (actual time=0.024..420.575 rows=1000000 loops=1)
   ->  Index Only Scan using t_small_a_idx on t_small  (cost=0.13..12.16 rows=2 width=4) (actual time=0.028..0.030 rows=2 loops=1)
         Heap Fetches: 2
 Planning Time: 0.256 ms
 Execution Time: 1237.700 ms
(9 rows)

ANY タイプのサブリンクをサブクエリにプッシュダウンできる場合、サブクエリのインデックスを使用して実行効率を向上させることができます。

EXPLAIN ANALYZE SELECT * FROM (SELECT a, sum(b) b FROM t_big WHERE a IN (SELECT a FROM t_small) GROUP BY a)v;
                                                             QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=17.96..17.99 rows=2 width=12) (actual time=0.061..0.064 rows=2 loops=1)
   Group Key: t_big.a
   ->  Sort  (cost=17.96..17.96 rows=2 width=8) (actual time=0.054..0.056 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.031..0.045 rows=2 loops=1)
               ->  Unique  (cost=1.03..1.04 rows=2 width=4) (actual time=0.014..0.017 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.005..0.006 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.010..0.011 rows=1 loops=2)
                     Index Cond: (a = t_small.a)
 Planning Time: 0.527 ms
 Execution Time: 0.143 ms
(15 rows)

前提条件

お使いの 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; 文を実行してリビジョンバージョンをクエリできます。リビジョンバージョンをアップグレードするには、「バージョン管理」をご参照ください。

シナリオ

サブリンクのプッシュダウン機能は、IN 句または ANY 句によって参照される GROUP BY 文を含むサブクエリ、特にサブクエリに大きなテーブルが含まれる場合に適しています。 IN 句または ANY 句をサブクエリにプッシュダウンすると、大きなテーブルのインデックスを使用できるようになり、データアクセス量が削減されます。

制限事項

サブリンクのプッシュダウン機能には、次の制限が適用されます。

IN 句または ANY 句は、GROUP BY 文を含むサブクエリを参照する必要があります。そうでない場合、オープンソース PostgreSQL はパラメーター化されたパスを作成します。サブリンクのプッシュダウン機能は使用されません。
IN 句または ANY 句の列は、GROUP BY 文の列に含まれている必要があります。そうでない場合、プッシュダウン後の SQL 文は元の SQL 文と同等にならない可能性があります。
現在のクエリブロックには、外部結合を含めることはできません。そうでない場合、プッシュダウン後の SQL 文は元の SQL 文と同等になりません。
a in (select a from t) や a = any(select a from t) など、IN 句または ANY 句では 1 つの列のみを参照できます。
SELECT 文と CREATE TABLE AS 文のみがサポートされています。

使用方法

次のパラメーターを設定して、サブリンクのプッシュダウン機能を制御できます。

パラメーター

説明

polar_cbqt_pushdown_sublink

サブリンクのプッシュダウン機能を有効または無効にします。有効な値:

OFF (デフォルト): サブリンクのプッシュダウン機能を無効にします。
ON: サブリンクのプッシュダウン機能を有効にします。サブリンクは CBQT に基づいてプッシュダウンされます。
FORCE: サブリンクのプッシュダウン機能を強制的に有効にします。サブリンクは 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;

元のクエリ

元のクエリプランでは、結合条件 t_big.a = t_small.a をパラメーター化されたパスとしてプッシュダウンできません。 t_big テーブルで全表スキャンを実行する必要があり、これは非効率的です。

EXPLAIN ANALYZE SELECT * FROM (SELECT a, sum(b) b FROM t_big GROUP BY a)v WHERE a IN (SELECT a FROM t_small);

結果例:

                                                                   QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
 Merge Semi Join  (cost=1.46..59510.27 rows=10000 width=12) (actual time=0.049..1239.128 rows=2 loops=1)
   Merge Cond: (t_big.a = t_small.a)
   ->  GroupAggregate  (cost=0.42..46909.23 rows=1000000 width=12) (actual time=0.034..1113.324 rows=1000000 loops=1)
         Group Key: t_big.a
         ->  Index Scan using t_big_a_idx on t_big  (cost=0.42..31909.23 rows=1000000 width=8) (actual time=0.025..412.650 rows=1000000 loops=1)
   ->  Sort  (cost=1.03..1.03 rows=2 width=4) (actual time=0.012..0.013 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.219 ms
 Execution Time: 1239.208 ms
(11 rows)

CBQT を有効にしてサブリンクのプッシュダウンを有効にする

CBQT 機能とサブリンクのプッシュダウン機能を有効にすると、a in (select a from t_small) 句がサブクエリにプッシュダウンされます。結合条件に基づいて t_big テーブルのパラメーター化されたパスが生成され、スキャンされるデータ量と実行時間が大幅に削減されます。

説明

サブリンクのプッシュダウン機能を使用するには、元のクエリプランのコストが polar_cbqt_cost_threshold の値を超えている必要があります。

-- 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);

結果例:

                                                             QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=17.96..17.99 rows=2 width=12) (actual time=0.056..0.059 rows=2 loops=1)
   Group Key: t_big.a
   ->  Sort  (cost=17.96..17.96 rows=2 width=8) (actual time=0.051..0.052 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.045 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.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.007..0.008 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.010..0.010 rows=1 loops=2)
                     Index Cond: (a = t_small.a)
 Planning Time: 0.518 ms
 Execution Time: 0.141 ms
(15 rows)

ヒントを使用してサブリンクのプッシュダウンを有効にする

ヒントを使用して、SQL 文のサブリンクのプッシュダウン機能を有効にすることができます。その結果、a in (select a from t_small) 句がサブクエリにプッシュダウンされます。結合条件に基づいて t_big テーブルのパラメーター化されたパスが生成され、スキャンされるデータ量と実行時間が大幅に削減されます。

-- polar_cbqt_pushdown_sublink パラメーターのデフォルト値は off です
SET polar_cbqt_pushdown_sublink to off;

EXPLAIN ANALYZE /*+ Set(polar_cbqt_pushdown_sublink force) */ SELECT * FROM (SELECT a, sum(b) b FROM t_big GROUP BY a)v WHERE a IN (SELECT a FROM t_small);

結果例:

                                                             QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=17.96..17.99 rows=2 width=12) (actual time=0.073..0.076 rows=2 loops=1)
   Group Key: t_big.a
   ->  Sort  (cost=17.96..17.96 rows=2 width=8) (actual time=0.067..0.069 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.026..0.040 rows=2 loops=1)
               ->  Unique  (cost=1.03..1.04 rows=2 width=4) (actual time=0.011..0.015 rows=2 loops=1)
                     ->  Sort  (cost=1.03..1.03 rows=2 width=4) (actual time=0.010..0.011 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)
               ->  Index Scan using t_big_a_idx on t_big  (cost=0.42..8.44 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=2)
                     Index Cond: (a = t_small.a)
 Planning Time: 0.788 ms
 Execution Time: 0.156 ms
(15 rows)