このトピックでは、さまざまなシナリオでAnalyticDB for PostgreSQLインスタンスのクエリパフォーマンスを最適化する方法について説明します。
テーブル統計の収集
AnalyticDB for PostgreSQLのクエリオプティマイザは、クエリのプランを最適化し、クエリされたテーブルの統計に基づいてコストを見積もります。 クエリされたテーブルから統計が収集されない場合、または収集されたテーブルの統計が古い場合、クエリオプティマイザーは既定値または古い値に基づいてクエリプランを最適化します。 その結果、クエリオプティマイザは最適なクエリプランを生成できません。 大量のデータがロードされた後、またはテーブルデータの20% 以上が更新された後に、テーブルの統計を収集することをお勧めします。
ANALYZEステートメントを使用すると、すべてのテーブル、テーブルのすべての列、またはテーブルの特定の列の統計を収集できます。 ほとんどの場合、すべてのテーブルまたはテーブルのすべての列の統計を収集することをお勧めします。 ただし、テーブル統計の収集をより細かく制御したい場合は、結合キー、フィルター条件、またはインデックスが作成されている列の統計のみを収集するように選択できます。
例:
大量のデータをインポートしたら、次のステートメントを実行して、すべてのテーブルの統計を収集します。
ANALYZE;tテーブルに大量のデータが挿入、更新、または削除された後、次のステートメントを実行して、そのテーブルのすべての列の統計を収集します。
ANALYZE t;次のステートメントを実行して、tテーブルのa列の統計を収集します。
ANALYZE t(a);
クエリオプティマイザを選択するChoose a query optimizer
AnalyticDB for PostgreSQLには、レガシーとORCAの2つのクエリオプティマイザがあります。 各クエリオプティマイザには、さまざまなシナリオで長所と短所があります。
レガシークエリオプティマイザ
これはデフォルトのクエリオプティマイザです。 レガシークエリオプティマイザーは、SQLステートメントを最適化するのに時間がかかります。 これは、3つ以下のテーブルの結合を必要とする非常に同時性の高い単純なクエリや、INSERT、UPDATE、またはDELETEステートメントを使用して実行される非常に同時性の高いデータの書き込みまたは更新に最適です。
ORCAクエリオプティマイザ
ORCAクエリオプティマイザは、複雑なクエリを最適化するように設計されています。 より多くの実行パスを通過するため、各クエリに最適なプランを生成するには、レガシークエリオプティマイザよりも時間がかかります。 ワークロードの抽出、変換、読み込み (ETL) 、およびレポートを完了するために3つ以上のテーブルを結合する必要がある複雑なクエリには、ORCAクエリオプティマイザを選択することをお勧めします。 さらに、ORCAクエリオプティマイザは、サブクエリでテーブルを結合し、パーティションを動的にフィルタリングする必要がなくなります。 したがって、ORCAクエリオプティマイザを選択して、サブクエリと、パラメーター指定のフィルター条件が作成されたパーティションテーブルからデータをクエリするために使用されるサブクエリを含むSQL文を最適化することをお勧めします。
次の例は、セッションのレガシーオプティマイザとORCAオプティマイザの両方を構成する方法を示しています。
-- Enable the Legacy query optimizer.
set optimizer = off;
-- Enable the ORCA query optimizer.
set optimizer = on;次のステートメントを実行して、現在のオプティマイザを表示できます。
show optimizer;
A value of on indicates that the ORCA query optimizer is used.
A value of off indicates that the Legacy optimizer is used.デフォルトでは、AnalyticDB for PostgreSQL V4.3はレガシーオプティマイザを使用します。 AnalyticDB for PostgreSQL V6.0はORCAオプティマイザを使用します。
インスタンスのレガシーおよびORCAオプティマイザを構成するには、 チケットを起票して、サービスセンターにお問い合わせください。
インデックスを使用したクエリの高速化
クエリに同一の値または特定の範囲内の値を識別するために使用されるフィルタ条件が含まれていて、少量のデータしか取得できない場合は、データスキャンを迅速に行うためのフィルタ基準として使用される列にインデックスを作成できます。 AnalyticDB for PostgreSQLは、次の3種類のインデックスをサポートしています。
Bツリーインデックス: 列に多数の一意の値があり、データのフィルタリング、結合、またはソートに使用される場合、Bツリーインデックスを作成します。
ビットマップインデックス: 列に少数の一意の値があり、複数のフィルター条件が作成されている場合は、ビットマップインデックスを作成します。
GiSTインデックス: 地理的な場所、範囲、画像フィーチャ、またはジオメトリ値をクエリする場合は、GiSTインデックスを作成します。
例:
次のステートメントを実行してインデックスのないテーブルからデータをクエリすると、テーブルのすべてのデータがスキャンされ、クエリで指定されたフィルター条件に基づいてデータがフィルターされます。
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)次のステートメントを実行して、tテーブルの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)クエリプランの表示
クエリプランは、AnalyticDB for PostgreSQLがクエリを完了するために実行する一連のステップです。 これはアルゴリズムと同等です。 クエリプランに基づいてクエリ実行プロセスを分析し、SQL文の実行が遅い理由を調べることができます。 キーワードEXPLAINをクエリに追加すると、クエリプランのみが表示され、指定されたSQL文は実行されません。 キーワードEXPLAIN ANALYZEをクエリに追加すると、指定されたSQL文が実行され、クエリの実行情報が収集され、クエリプランに表示されます。
次の例は、キーワードEXPLAINがクエリに追加されたクエリプランを示しています。
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)次の例は、キーワードEXPLAIN ANALYZEがクエリに追加されたクエリプランを示しています。
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)
クエリプランは演算子で構成され、その情報を整理してデータを論理的な順序で処理します。
AnalyticDB for PostgreSQLは、次のタイプの演算子をサポートします。
データスキャン演算子: Seq Scan、Table Scan、Index Scan、Bitmap Scan。
結合演算子: ハッシュ結合、ネストされたループ、およびマージ結合。
集計演算子: ハッシュ集計とグループ集計。
配信演算子: Motion、Broadcast Motion、Gather Motionを再配信します。
その他の演算子: ハッシュ、ソート、制限、および追加。
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)
上記のクエリプランは次のように説明されています。
テーブルスキャン演算子はt1とt2テーブルをスキャンします。
Redistribute Motion演算子は、b列のハッシュ値に基づいてt1テーブルとt2テーブルのデータを計算ノードに再配信します。
ハッシュ演算子は、t2テーブルの結合に使用されるハッシュキーを作成します。
ハッシュ結合演算子は、t1およびt2テーブルのデータを結合する。
Gather Motion演算子は、計算結果をコーディネータノードに送信する。 次に、コーディネータノードは、計算結果をクライアントに送信する。
実際のクエリプランは、指定したSQL文によって異なります。
分散演算子を削除してクエリのパフォーマンスを向上させる
結合演算子または集計演算子を呼び出すと、AnalyticDB for PostgreSQLは、データの再配布 (モーションの再配布) またはブロードキャスト (モーションのブロードキャスト) にデータの配布に基づいて配布演算子を追加します。 分散オペレータは大量のネットワークリソースを占有します。 クエリのパフォーマンスを向上させるには、テーブルを作成し、ビジネスロジックを調整して、分散演算子のニーズを排除することを推奨します。
仕組み
結合する2つのテーブルの配布キーがビジネスロジックと一致しない場合は、配布キーを変更して、配布演算子の必要をなくすことができます。
例:
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テーブルの配布キーも列である場合、AnalyticDB for PostgreSQLは、t2テーブルのデータを再配布せずにt1テーブルとt2テーブルを結合します。
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)
結合する列のデータ型を最適化する
データ型の変換はデータの再配布を引き起こすため、データ型の明示的または暗黙的な変換を防ぐために、結合する列は同じデータ型である必要があります。
明示的な型変換
SQL文では、結合する列のデータ型が強制的に変換される場合があります。 これは明示型変換と呼ばれます。 たとえば、tテーブルのa列はintデータ型を使用しますが、結合によって数値データ型に変換されます。
列のデータ型が明示的に変換されると、その列のデータのハッシュ関数または値が変更されます。 そのため、参加する列ではデータ型の変換を避けることをお勧めします。
次の例に示すように、明示的な型変換はデータの再配布をトリガーします。
-- Execute a join without a data type conversion. 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) -- Execute a join with an explicit type conversion. 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つのテーブルの複数の列を結合したいが、一方の列が各テーブルで異なるデータ型を使用する場合は、その列のデータ型を変換する必要があります。 これは暗黙的な型変換と呼ばれます。
列のデータ型が暗黙的に変換された後、元のデータ型のハッシュ関数または値が新しいデータ型のハッシュ関数または値と異なる場合があります。 その結果、AnalyticDB for PostgreSQLはその列のデータを再配信します。 したがって、結合キーと同じデータ型の列を選択することをお勧めします。
次の例では、t1テーブルのa列は "timestamp without time zone" データ型を使用し、t2テーブルのa列は "timestamp without time zone" データ型を使用します。 これは、2つの列が異なるハッシュ関数を使用することを意味する。 その結果、AnalyticDB for PostgreSQLは、参加する前にデータを再配信します。
postgres=# CREATE TABLE t1 (a timestamp without time zone); CREATE TABLE postgres=# CREATE TABLE t2 (a timestamp with time zone); CREATE TABLE postgres=# 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)
データスキューの特定
クエリが異常に遅い場合、またはリソース使用量が不均一な場合は、データスキューが発生しているかどうかを確認します。
具体的には、各コンピュートノードに分散している行数を確認します。 テーブルの行が計算ノード間で不均等に分散されている場合、そのテーブルのデータはスキューされます。
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)テーブルのデータが歪んでいる場合は、次のいずれかの方法を使用して、そのテーブルの新しい配布キーを定義することを推奨します。
テーブルを再作成し、新しい配布キーを指定します。
ALTER TABLE t1 SET DISTRIBUTED BY (b);文を実行して、配布キーを変更します。
実行中のSQL文の表示
多数のSQL文が同時に実行されると、同時クエリが遅くなり、AnalyticDB for PostgreSQLインスタンスのリソースが不十分になる可能性があります。
AnalyticDB for PostgreSQLインスタンスのステータスは、pg_stat_activityビューから取得できます。 このビューは、すべての同時SQL文を一覧表示します。 このビューのquery_startフィールドに基づいて、クエリに異常な時間がかかったかどうかを判断できます。
例：
postgres=# SELECT * FROM pg_stat_activity;
datid | datname | procpid | sess_id | usesysid | usename | current_query | waiting | query_start | backend_start | client_addr | client_port | application_name | xact_start | waiting_reason
-------+----------+---------+---------+----------+------------+---------------------------------+---------+-------------------------------+-------------------------------+-------------+-------------+------------------+-------------------------------+----------------
10902 | postgres | 53666 | 7 | 10 | yineng.cyn | select * from pg_stat_activity; | f | 2019-05-13 20:27:12.058656+08 | 2019-05-13 20:16:14.179612+08 | | -1 | psql | 2019-05-13 20:27:12.058656+08 |
10902 | postgres | 54158 | 9 | 10 | yineng.cyn | select * from t t1, t t2; | f | 2019-05-13 20:26:28.138641+08 | 2019-05-13 20:17:40.368623+08 | | -1 | psql | 2019-05-13 20:26:28.138641+08 |
(2 rows)次のセクションでは、上記の例の重要なフィールドについて説明します。
procpid: クエリを実行したマスタープロセスのID。
usename: クエリを実行したユーザーのユーザー名。
current_query: クエリテキスト。
waiting: クエリが待機状態かどうかを示します。
query_start: クエリの開始時刻。
backend_start: クエリの実行に使用されるプロセスが開始された時刻。
xact_start: クエリが属するトランザクションが開始された時刻。
waiting_reason: クエリが待機状態のままである理由。
さらに、current_queryを追加できます! ='<IDLE>' conditionをcurrent_queryフィールドに入力して、実行中のSQL文を表示します。
SELECT * FROM pg_stat_activity WHERE current_query ! = '<IDLE>';次のステートメントを実行して、実行に最も時間がかかった上位5つのSQLステートメントを表示します。
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;ロックのステータスを確認する
AnalyticDB for PostgreSQLインスタンス内のオブジェクトがクエリによって長時間ロックされたままである場合、そのオブジェクトに関連する他のクエリは待機状態のままであり、正しく実行できない可能性があります。 次のステートメントを実行して、AnalyticDB for PostgreSQLインスタンスでロックされているテーブルを表示します。
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;テーブルのロックが解放されるのを待っているためにクエリが応答しない場合は、そのテーブルのロックを確認できます。 必要に応じて、次のいずれかの方法を使用してこの問題を解決します。
このクエリをキャンセルします。 pidパラメーターで示されるセッションがアイドルの場合、このメソッドは適していません。 さらに、クエリをキャンセルした後、データを削除し、クエリが属するトランザクションをロールバックする必要があります。
SELECT pg_cancel_backend(pid);説明pg_cancel_backend関数は、pg_stat_activity.current_queryパラメーターの値がIDLEのセッションでは有効になりません。 この状況では、pg_terminate_backend関数を呼び出してデータを削除できます。クエリが属するセッションを終了します。 セッションが終了した後、その中のコミットされていないトランザクションはロールバックされます。
SELECT pg_terminate_backend(pid);
ネストされたループを使用してテーブルを結合し、クエリパフォーマンスを向上させる
デフォルトでは、ネストされたループ結合はAnalyticDB for PostgreSQLでは無効になっています。 クエリによって返されるデータの量が少ない場合、クエリのパフォーマンスが最適ではない可能性があります。
例：
SELECT *
FROM t1 join t2 on t1.c1 = t2.c1
WHERE t1.c2 >= '230769548' and t1.c2 < '230769549'
LIMIT 100;前の例では、t1およびt2テーブルは大きい。 ただし、t1テーブルのフィルタ条件 (t1.c2 >= '230769548' およびt1.c2 < '23432442') は、ほとんどのデータレコードを除外し、LIMIT句はさらに、適格なデータレコードを制限します。 その結果、少量のデータのみが照会されます。 この状況では、ネストされたループを使用して2つのテーブルを結合できます。
ネストされたループを使用してテーブルを結合するには、SETステートメントを実行する必要があります。 例：
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)上記の例では、t1テーブルとt2テーブルをネストループを使用して結合し、クエリのパフォーマンスを最適化しています。