AnalyticDB:クエリパフォーマンスの最適化

テーブル統計の収集

クエリ オプテマイザーは、テーブル統計に基づいて実行計画を生成します。古い、または不足している統計は、オプテマイザーが不適切な見積もりを行い、非効率なプランにつながる可能性があります。

大量データロード後、またはテーブルの行の20%以上が更新された後に ANALYZE を実行してください。

-- Collect statistics on all tables
ANALYZE;

-- Collect statistics on all columns of table t
ANALYZE t;

-- Collect statistics on a specific column
ANALYZE t(a);

ほとんどのワークロードでは、変更されたテーブルに対して ANALYZE t を実行するだけで十分です。より厳密な制御が必要な場合にのみ、列レベルの ANALYZE を使用してください。例えば、結合キー、フィルター条件、またはインデックスキー列として使用される列に対してです。

クエリ オプテマイザーの選択

AnalyticDB for PostgreSQL には2つのクエリ オプテマイザーが含まれています。それぞれ異なるワークロード向けに最適化されています。

セッションレベルでオプテマイザーを切り替えます。

-- Enable the Legacy query optimizer
SET optimizer = off;

-- Enable the ORCA query optimizer
SET optimizer = on;

-- Check the current optimizer
SHOW optimizer;
-- on = ORCA query optimizer
-- off = Legacy optimizer

インスタンスレベルでオプテマイザーを変更するには、「チケットを起票」してください。

インデックスを使用したクエリの高速化

インデックスは、フィルター条件に基づいてテーブルのごく一部をスキャンするクエリを高速化します。AnalyticDB for PostgreSQL は3種類のインデックスタイプをサポートしています。

例: B ツリーインデックスの追加

インデックスがない場合、オプテマイザーは全表スキャンを実行します。

postgres=# EXPLAIN SELECT * FROM t WHERE b = 1;
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..431.00 rows=1 width=16)
   ->  Table Scan on t  (cost=0.00..431.00 rows=1 width=16)
         Filter: b = 1
 Settings:  optimizer=on
 Optimizer status: PQO version 1.609
(5 rows)

b 列に B ツリーインデックスを作成します。

postgres=# CREATE INDEX i_t_b ON t USING btree (b);
CREATE INDEX

インデックスを使用すると、オプテマイザーは代わりにインデックススキャンを使用します。

postgres=# EXPLAIN SELECT * FROM t WHERE b = 1;
                                 QUERY PLAN
-----------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..2.00 rows=1 width=16)
   ->  Index Scan using i_t_b on t  (cost=0.00..2.00 rows=1 width=16)
         Index Cond: b = 1
 Settings:  optimizer=on
 Optimizer status: PQO version 1.609
(5 rows)

クエリプランの表示

クエリプランは、データベースがクエリを実行する方法を記述するオペレーターのツリーです。プランを読み解くことで、クエリが低速である理由を特定できます。

クエリプランの読み解き方

• プランはツリー構造です。実行はリーフノード (下部) から開始され、ルートに向かって流れます。

• cost=<startup>..<total> は、オプテマイザーの推定コストであり、実際のミリ秒ではありません。高いコスト見積もりは、オプテマイザーがその操作にコストがかかると予測していることを意味します。絶対的な尺度としてではなく、比較のために使用してください。

• rows= は推定行数です。推定行と実際の行の間に大きな乖離がある場合、古い統計を示していることが多いため、ANALYZE を実行して修正してください。

EXPLAIN と EXPLAIN ANALYZE

• EXPLAIN は、クエリを実行せずにプランを表示します。

• EXPLAIN ANALYZE は、クエリを実行し、実際の実行時間と行数をプランに追加します。

-- Display plan only (no execution)
postgres=# EXPLAIN SELECT a, b FROM t;
                                      QUERY PLAN
------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..4.00 rows=100 width=8)
   ->  Seq Scan on t  (cost=0.00..4.00 rows=34 width=8)
 Optimizer status: legacy query optimizer
(3 rows)

-- Run query and show actual timings
postgres=# EXPLAIN ANALYZE SELECT a, b FROM t;
                                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..4.00 rows=100 width=8)
   Rows out:  100 rows at destination with 2.728 ms to first row, 2.838 ms to end, start offset by 0.418 ms.
   ->  Seq Scan on t  (cost=0.00..4.00 rows=34 width=8)
         Rows out:  Avg 33.3 rows x 3 workers.  Max 37 rows (seg2) with 0.088 ms to first row, 0.107 ms to end, start offset by 2.887 ms.
 Slice statistics:
   (slice0)    Executor memory: 131K bytes.
   (slice1)    Executor memory: 163K bytes avg x 3 workers, 163K bytes max (seg0).
 Statement statistics:
   Memory used: 128000K bytes
 Optimizer status: legacy query optimizer
 Total runtime: 3.739 ms
(11 rows)

EXPLAIN ANALYZE 出力からのボトルネックの特定

実際のタイミングと行データを使用して、ボトルネックを分類します。

サポートされているオペレータータイプ

例: 結合クエリプランの読み解き

postgres=# EXPLAIN SELECT * FROM t1, t2 WHERE t1.b = t2.b;
                                              QUERY PLAN
-------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice3; segments: 3)  (cost=0.00..862.00 rows=1 width=32)
   ->  Hash Join  (cost=0.00..862.00 rows=1 width=32)
         Hash Cond: t1.b = t2.b
         ->  Redistribute Motion 3:3  (slice1; segments: 3)  (cost=0.00..431.00 rows=1 width=16)
               Hash Key: t1.b
               ->  Table Scan on t1  (cost=0.00..431.00 rows=1 width=16)
         ->  Hash  (cost=431.00..431.00 rows=1 width=16)
               ->  Redistribute Motion 3:3  (slice2; segments: 3)  (cost=0.00..431.00 rows=1 width=16)
                     Hash Key: t2.b
                     ->  Table Scan on t2  (cost=0.00..431.00 rows=1 width=16)
 Settings:  optimizer=on
 Optimizer status: PQO version 1.609
(12 rows)

このプランを下から上に読み解きます。

1. Table Scan — t1 と t2 をスキャンします。

2. Redistribute Motion — b のハッシュ値に基づいて、両方のテーブルから行をコンピュートノード間でシャッフルし、一致する行が同じノードに配置されるようにします。

3. Hash — 結合のために t2 にハッシュテーブルを構築します。

4. Hash Join — ハッシュキーで t1 と t2 を結合します。

5. Gather Motion — コンピュートノードからコーディネーターノードに結果を送信し、コーディネーターノードがそれらをクライアントに返します。

ディストリビュートオペレーターの削除

AnalyticDB for PostgreSQL がコンピュートノード間でデータを結合または集計する場合、データをシャッフルするために Redistribute Motion または Broadcast Motion オペレーターを挿入します。これらのオペレーターは、大量のネットワーク帯域幅を消費します。結合列の分散キーを揃えることで、データの再分散の必要性を排除できます。

仕組み

2つのテーブルが列 a で結合され、両方が a で分散されている場合、各コンピュートノードはすでに一致する行を保持しているため、データ移動は不要です。

例

SELECT * FROM t1, t2 WHERE t1.a = t2.a;

t1 は列 a で分散されています。もし t2 が列 b で分散されている場合 (不一致)、AnalyticDB for PostgreSQL は t2 を再分散する必要があります。

postgres=# EXPLAIN SELECT * FROM t1, t2 WHERE t1.a=t2.a;
                                                  QUERY PLAN
-------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice2; segments: 3)  (cost=0.00..862.00 rows=1 width=32)
   ->  Hash Join  (cost=0.00..862.00 rows=1 width=32)
         Hash Cond: t1.a = t2.a
         ->  Table Scan on t1  (cost=0.00..431.00 rows=1 width=16)
         ->  Hash  (cost=431.00..431.00 rows=1 width=16)
               ->  Redistribute Motion 3:3  (slice1; segments: 3)  (cost=0.00..431.00 rows=1 width=16)
                     Hash Key: t2.a
                     ->  Table Scan on t2  (cost=0.00..431.00 rows=1 width=16)
 Settings:  optimizer=on
 Optimizer status: PQO version 1.609
(10 rows)

もし t2 も a で分散されている場合、結合は再分散なしで実行されます。

postgres=# EXPLAIN SELECT * FROM t1, t2 WHERE t1.a=t2.a;
                                      QUERY PLAN
-------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..862.00 rows=1 width=32)
   ->  Hash Join  (cost=0.00..862.00 rows=1 width=32)
         Hash Cond: t1.a = t2.a
         ->  Table Scan on t1  (cost=0.00..431.00 rows=1 width=16)
         ->  Hash  (cost=431.00..431.00 rows=1 width=16)
               ->  Table Scan on t2  (cost=0.00..431.00 rows=1 width=16)
 Settings:  optimizer=on
 Optimizer status: PQO version 1.609
(8 rows)

不一致を修正するには、t2 の分散キーを変更します。

ALTER TABLE t2 SET DISTRIBUTED BY (a);

結合列での一致するデータの型の使用

異なるデータの型の列を結合すると、データの型変換がトリガーされ、データの再分散が強制されます。結合キーの両側で同じデータの型を使用してください。

明示的な型変換

SQL ステートメントでの明示的なキャストは、列に適用されるハッシュ関数を変更し、オプテマイザーが両方のテーブルを再分散させる原因となります。

-- No type conversion: no redistribution
postgres=# EXPLAIN SELECT * FROM t1, t2 WHERE t1.a=t2.a;
                                      QUERY PLAN
-------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..862.00 rows=1 width=32)
   ->  Hash Join  (cost=0.00..862.00 rows=1 width=32)
         Hash Cond: t1.a = t2.a
         ->  Table Scan on t1  (cost=0.00..431.00 rows=1 width=16)
         ->  Hash  (cost=431.00..431.00 rows=1 width=16)
               ->  Table Scan on t2  (cost=0.00..431.00 rows=1 width=16)
 Settings:  optimizer=on
 Optimizer status: PQO version 1.609
(8 rows)

-- Explicit cast to numeric: triggers redistribution of both tables
postgres=# EXPLAIN SELECT * FROM t1, t2 WHERE t1.a=t2.a::numeric;
                                                  QUERY PLAN
-------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice3; segments: 3)  (cost=0.00..862.00 rows=1 width=32)
   ->  Hash Join  (cost=0.00..862.00 rows=1 width=32)
         Hash Cond: t1.a::numeric = t2.a::numeric
         ->  Redistribute Motion 3:3  (slice1; segments: 3)  (cost=0.00..431.00 rows=1 width=16)
               Hash Key: t1.a::numeric
               ->  Table Scan on t1  (cost=0.00..431.00 rows=1 width=16)
         ->  Hash  (cost=431.00..431.00 rows=1 width=16)
               ->  Redistribute Motion 3:3  (slice2; segments: 3)  (cost=0.00..431.00 rows=1 width=16)
                     Hash Key: t2.a::numeric
                     ->  Table Scan on t2  (cost=0.00..431.00 rows=1 width=16)
 Settings:  optimizer=on
 Optimizer status: PQO version 1.609
(12 rows)

暗黙の型変換

2つの結合列が互換性のない型を使用している場合、データベースは自動的に一方の型を変換します。例えば、timestamp without time zone と timestamp with time zone は異なるハッシュ関数を使用します。オプテマイザーは、ハッシュベースの結合の代わりに Broadcast Motion にフォールバックします。

postgres=# CREATE TABLE t1 (a timestamp without time zone);
CREATE TABLE
postgres=# CREATE TABLE t2 (a timestamp with time zone);
CREATE TABLE

postgres=# EXPLAIN SELECT * FROM t1, t2 WHERE t1.a=t2.a;
                                               QUERY PLAN
-------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice2; segments: 3)  (cost=0.04..0.11 rows=4 width=16)
   ->  Nested Loop  (cost=0.04..0.11 rows=2 width=16)
         Join Filter: t1.a = t2.a
         ->  Seq Scan on t1  (cost=0.00..0.00 rows=1 width=8)
         ->  Materialize  (cost=0.04..0.07 rows=1 width=8)
               ->  Broadcast Motion 3:3  (slice1; segments: 3)  (cost=0.00..0.04 rows=1 width=8)
                     ->  Seq Scan on t2  (cost=0.00..0.00 rows=1 width=8)
(7 rows)

暗黙の型変換を排除するには、テーブル定義時に列の型を揃えます。例えば、t1.a と t2.a の両方を timestamp without time zone として定義します。

データスキューの特定

データスキューとは、行がコンピュートノード間で不均等に分散される現象です。スキューのあるテーブルでは、1つのコンピュートノードが大部分の作業を行い、他のノードはアイドル状態になるため、クエリ全体の実行が遅くなります。

コンピュートノード間での行の分散を確認します。

postgres=# SELECT gp_segment_id, count(1) FROM t1 GROUP BY 1 ORDER BY 2 DESC;
 gp_segment_id | count
---------------+-------
             0 | 16415
             2 |    37
             1 |    32
(3 rows)

この出力は深刻なスキューを示しています：ノード 0 が 99% の行を保持しています。分散キーを再割り当てることで修正します。

-- Option 1: change the distribution key in place
ALTER TABLE t1 SET DISTRIBUTED BY (b);

-- Option 2: recreate the table with a better distribution key
-- Create the new table, bulk-load data, then swap it in place of the original

均等な分散を保証するために、高カーディナリティで支配的な値がない分散キーを選択してください。

実行中の SQL ステートメントの表示

多数のクエリが同時に実行されている場合、インスタンスは応答の遅延やリソース不足を報告することがあります。pg_stat_activity ビューを使用して、現在実行中のクエリを確認できます。

postgres=# SELECT * FROM pg_stat_activity;

主なフィールド：

アクティブなクエリのみに絞り込みます。

SELECT * FROM pg_stat_activity WHERE current_query != '<IDLE>';

実行時間の最も長い上位5つのクエリを検索します。

SELECT current_timestamp - query_start AS runtime,
       datname,
       usename,
       current_query
FROM pg_stat_activity
WHERE current_query != '<IDLE>'
ORDER BY runtime DESC
LIMIT 5;

ロックステータスの確認

クエリが長期間ロックを保持している場合、同じオブジェクトに対する他のクエリは無期限に待機します。以下のクエリを実行して、どのテーブルがロックされており、どのセッションがロックを保持しているかを確認します。

SELECT pgl.locktype   AS locktype,
       pgl.database   AS database,
       pgc.relname    AS relname,
       pgl.relation   AS relation,
       pgl.transaction AS transaction,
       pgl.pid        AS pid,
       pgl.mode       AS mode,
       pgl.granted    AS granted,
       pgsa.current_query AS query
FROM pg_locks pgl
JOIN pg_class pgc ON pgl.relation = pgc.oid
JOIN pg_stat_activity pgsa ON pgl.pid = pgsa.procpid
ORDER BY pgc.relname;

待機中のクエリを解除するには、ロックを保持しているセッションをキャンセルまたは終了します。

-- Cancel the query (does not work if the session is IDLE)
SELECT pg_cancel_backend(pid);

-- Terminate the session and roll back its uncommitted transactions
SELECT pg_terminate_backend(pid);

pg_cancel_backend は、pg_stat_activity.current_query が IDLE を示している場合、効果がありません。その場合は pg_terminate_backend を使用してください。

ネステッドループ結合の有効化

ネステッドループ結合はデフォルトで無効になっています。選択性の高いフィルター条件および LIMIT 句により、少数の行を返すクエリに対して、ネステッドループ結合を有効化すると、クエリ時間を大幅に短縮できます。

有効化するタイミング

以下の条件を満たす場合に、この最適化が有効です。

• 1つのテーブルに対するフィルター条件が非常に少ない行を返す。

• LIMIT 句により結果セットがさらに制限される。

• 両方のテーブルの結合列にインデックスが存在する。

例

SELECT *
FROM t1 JOIN t2 ON t1.c1 = t2.c1
WHERE t1.c2 >= '230769548' AND t1.c2 < '230769549'
LIMIT 100;

セッションレベルでネステッドループ結合を確認および有効化します。

SHOW enable_nestloop;
 enable_nestloop
-----------------
 off

SET enable_nestloop = on;

SHOW enable_nestloop;
 enable_nestloop
-----------------
 on

EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.c1 = t2.c1
WHERE t1.c2 >= '230769548' AND t1.c2 < '23432442'
LIMIT 100;
                                            QUERY PLAN
-----------------------------------------------------------------------------------------------
 Limit  (cost=0.26..16.31 rows=1 width=18608)
   ->  Nested Loop  (cost=0.26..16.31 rows=1 width=18608)
         ->  Index Scan using t1 on c2  (cost=0.12..8.14 rows=1 width=12026)
               Filter: ((c2 >= '230769548'::bpchar) AND (c2 < '230769549'::bpchar))
         ->  Index Scan using t2 on c1  (cost=0.14..8.15 rows=1 width=6582)
               Index Cond: ((c1)::text = (T1.c1)::text)

このプランでは、両方のテーブルでインデックススキャンが使用され、ネステッドループ結合が実行され、全表スキャンおよびデータの再分散が回避されています。

enable_nestloop はセッションレベルの設定です。同じセッション内の他のクエリがハッシュ結合から恩恵を受ける場合は、クエリ後にリセットしてください。