PolarDB for PostgreSQLのElastic Parallel Query (ePQ) 機能を使用すると、パーティションテーブルをクエリできます。
背景情報
データ量が増え続けると、テーブルのサイズが大きくなります。 管理を容易にし、クエリのパフォーマンスを向上させるために、パーティションテーブルを使用してデータを保存できます。 このようにして、テーブルは複数の子テーブルに分割されます。 さらに、各子テーブルをさらに分割して、マルチレベルパーティション階層を形成することができる。
PolarDB for PostgreSQLは、マルチノード並列クエリをサポートするePQ機能を提供します。 これにより、クラスター内で複数の計算ノードを使用して、読み取り専用クエリのパフォーマンスを向上させることができます。 ePQは、通常のテーブルだけでなくパーティションテーブルの効率的なマルチノード並列クエリもサポートしています。
ePQは、パーティションテーブルの次の基本機能をサポートしています。
パーティションポリシーが範囲、リスト、またはハッシュであるパーティションテーブルのスキャン
パーティションテーブルのインデックススキャン
結合されたパーティションテーブルのクエリ
さらに、ePQはパーティションテーブルに対して次の高度な機能をサポートしています。
パーティションの剪定
パーティションごとの結合
マルチレベルパーティションテーブルの並列クエリ
ePQは、複数列のパーティションキーを使用したパーティションテーブルの並列クエリをサポートしていません。
前提条件
PolarDB for PostgreSQLクラスターは、次のエンジンを実行します。
リビジョンバージョンが1.1.17以降のPostgreSQL 11
リビジョンバージョンが14.8.11.0以降のPostgreSQL 14
次のいずれかのステートメントを実行して、PolarDB for PostgreSQLクラスターのリビジョンバージョンを照会できます。
PostgreSQL 11
ショーpolar_version;PostgreSQL 14
select version();
手順
パーティション分割テーブルの並列クエリ
パーティショニングポリシーがrangeであるパーティションテーブルを作成し、3つのパーティションを作成します。
CREATE TABLE t1 (id INT) PARTITION BY RANGE(id); (0) から (200) までの値のt1の部分を作成します。(200) から (400) までの値のt1の部分を作成します。(400) から (600) までの値のt1の部分を作成します。ePQ機能とePQのパーティションテーブルスキャン機能を有効にします。
SET polar_enable_pxをオンにします。polar_px_enable_partitionをオンに設定します。パーティションテーブルに対してテーブル全体のスキャンを実行するステートメントの実行プランを照会します。
EXPLAIN (コストOFF) SELECT * FROM t1; クエリ計画 ------------------------------------------- PXコーディネーター6:1 (slice1; セグメント: 6) -> 追加 -> t1_p1の部分Seqスキャン -> t1_p2の部分Seqスキャン -> t1_p3の部分Seqスキャン Optimizer: PolarDB PX Optimizer (6行)前述の実行計画に示すように、ePQはプロセスのグループを開始して、パーティション分割テーブルの各子テーブルを並列にスキャンします。 各スキャンプロセスは、
Append演算子を使用して、各子テーブルのデータの一部をスキャンします (Partial Seq scan) 。 Motion演算子 (PX Coordinator) は、すべてのプロセスのスキャン結果をクエリを開始したプロセスに集約し、結果を返します。
静的パーティションプルーニング
クエリのフィルター条件でパーティションキーが使用されている場合、ePQオプティマイザはフィルター条件に基づいてスキャンするパーティションテーブルをプルーニングし、不要なパーティションがスキャンされないようにすることができます。 これにより、システムリソースの使用量が減り、クエリのパフォーマンスが向上します。 この例では、前のセクションで作成されたt1テーブルが使用されます。 次のステートメントを実行して、フィルター条件を使用してサンプルクエリの実行プランを照会します。
EXPLAIN (コストオフ) SELECT * FROM t1 WHERE id < 100;
クエリ計画
-------------------------------------------
PXコーディネーター6:1 (slice1; セグメント: 6)
-> 追加
-> t1_p1の部分Seqスキャン
フィルター: (id < 100)
Optimizer: PolarDB PX Optimizer
(5行) クエリのフィルター条件id < 100はパーティションキーを使用します。 したがって、ePQオプティマイザは、パーティション境界に基づいて、フィルタ条件を満たさないパーティションt1_p2およびt1_p3を実行プランから削除し、フィルタ条件を満たすパーティションt1_p1のみを保持します。
パーティションごとの結合
パーティション分割されたテーブルの結合中に、パーティション分割ポリシーと境界が同じであり、結合条件がパーティションキーに基づいている場合、ePQオプティマイザは、パーティション分割されたテーブルがパーティションごとに結合される実行計画を生成できます。 これにより、分割テーブルのデカルト積結合が防止され、システムリソースの使用量が削減され、クエリのパフォーマンスが向上します。
次の例では、パーティショニングポリシーがrangeである2つのパーティションテーブルが結合されています。
分割ポリシーおよび境界が同じである分割テーブル
t2およびt3を作成する。CREATE TABLE t2 (id INT) PARTITION BY RANGE(id); (0) から (200) までの値のt2の部分を作成します。(200) から (400) までの値のt2の部分を作成します。(400) から (600) までの値のt2の部分を作成します。CREATE TABLE t3 (id INT) PARTITION BY RANGE(id); (0) から (200) までの値のt3の部分を作成します。(200) から (400) までの値のt3の部分を作成します。(400) から (600) までの値のt3の部分を作成します。ePQ機能とePQのパーティションテーブルスキャン機能を有効にします。
SET polar_enable_pxをオンにします。polar_px_enable_partitionをオンに設定します。polar_px_enable_partitionwise_joinパラメーターをOFFに設定して、パーティション単位の結合を無効にします。 パーティションキーに基づいて、2つのパーティションテーブルに対してequi joinが実行されるクエリの実行プランを照会します。
SET polar_px_enable_partitionwise_join TO OFF; EXPLAIN (COSTS OFF) SELECT * FROM t2 JOIN t3 ON t2.id = t3.id; クエリ計画 ----------------------------------------------------------- PXコーディネーター6:1 (slice1; セグメント: 6) -> ハッシュ参加 ハッシュコンド :( t2_p1.id = t3_p1.id) -> 追加 -> t2_p1の部分Seqスキャン -> t2_p2の部分Seqスキャン -> t2_p3の部分Seqスキャン -> Hash -> PX放送6:6 (slice2; セグメント: 6) -> 追加 -> t3_p1の部分Seqスキャン -> t3_p2の部分Seqスキャン -> t3_p3の部分Seqスキャン Optimizer: PolarDB PX Optimizer (14行)前述の実行プランに示されているように、
slice1を実行する6つのプロセスのそれぞれは、Append演算子を使用して、パーティション分割テーブルt2の各パーティション内のデータの一部を順番にスキャンします。 モーション演算子PXブロードキャストは、slice2を実行する6つのプロセスによってブロードキャストされた分割t3の全データを受信するために使用される。 ローカルのHash Joinが完了すると、Motion演算子PX Coordinatorは結果を集計して返します。 この場合、t2のデータの各行とt3のデータの各行との間で結合が行われる。polar_px_enable_partitionwise_joinパラメーターをONに設定して、パーティションごとの結合を有効にします。 実行プランを再度照会します。
SET polar_px_enable_partitionwise_join TO ON; EXPLAIN (COSTS OFF) SELECT * FROM t2 JOIN t3 ON t2.id = t3.id; クエリ計画 ------------------------------------------------ PXコーディネーター6:1 (slice1; セグメント: 6) -> 追加 -> ハッシュ参加 ハッシュコンド :( t2_p1.id = t3_p1.id) -> t2_p1の部分Seqスキャン -> Hash -> t3_p1の完全なSeqスキャン -> ハッシュ参加 ハッシュコンド :( t2_p2.id = t3_p2.id) -> t2_p2の部分Seqスキャン -> Hash -> t3_p2の完全なSeqスキャン -> ハッシュ参加 ハッシュコンド :( t2_p3.id = t3_p3.id) -> t2_p3の部分Seqスキャン -> Hash -> t3_p3のフルSeqスキャン Optimizer: PolarDB PX Optimizer (18行)前述の実行プランに示すように、
slice1を実行する6つのプロセスのそれぞれは、Append演算子を使用することによって、分割テーブルt2の各パーティション内のデータの一部と、分割テーブルt3の対応するパーティション内のすべてのデータとを順番にスキャンする。 データに対してハッシュ結合が実行される。 次に、Motion演算子PX Coordinatorは結果を集約して返します。実行中は、分割テーブルt2の各パーティション
t2_p1、t2_p2、t2_p3と、対応する分割テーブルt3の各パーティションt3_p1、t3_p2、t3_p3との間でのみ結合が行われる。 無関係なパーティションでは結合は実行されません。 これにより効率が向上します。
マルチレベルパーティションテーブルの並列クエリ
マルチレベルパーティションテーブルでは、各レベルのパーティションキーは異なる場合があります。 例えば、テーブルは、時間によって分割され、次いで、領域によってさらに分割され得る。 クエリのフィルター条件で、マルチレベルのパーティションテーブルの各レベルでパーティションキーが使用されている場合、ePQオプティマイザは静的パーティションプルーニングをサポートして、スキャンする必要のないパーティションを削除します。
次の図に示すように、フィルタ条件WHERE date = '202201' AND region = 'beijing' は、レベル1のパーティションキーdateとレベル2のパーティションキーregionの両方を使用します。 この場合、ePQオプティマイザはすべての無関係なパーティションを削除し、条件を満たすパーティションのみを含む実行プランを生成できます。 このようにして、必要なパーティションのみがエグゼキュータによってスキャンされます。
例
マルチレベルのパーティションテーブルを作成します。
CREATE TABLE r1 (a INT, b TIMESTAMP) RANGEによるパーティション (b); ('2000-01-01 ') から ('2010-01-01') までの値のr1の部分を作成します。(1) から (1000000) までの値のr1_p1のパーティションを作成します。(1000000) から (2000000) までの値のr1_p1の部分を作成します。('2010-01-01 ') から ('2020-01-01') までのr1のパーティションを作成します。(1) から (1000000) までの値のr1_p2のパーティションを作成します。(1000000) から (2000000) までの値のr1_p2の部分を作成します。ePQ機能とePQのパーティションテーブルスキャン機能を有効にします。
SET polar_enable_pxをオンにします。polar_px_enable_partitionをオンに設定します。polar_px_optimizer_multilevel_partitioningパラメーターをOFFに設定して、ePQのマルチレベルパーティションスキャン機能を無効にします。 フィルター条件で両方のパーティションキーを使用するSQL文の実行プランを照会します。 PostgreSQLの組み込みオプティマイザの静的パーティションプルーニングに基づく実行計画が生成されます。
SET polar_px_optimizer_multilevel_partitioningをオフにします。EXPLAIN (コストオフ) SELECT * FROM r1 WHERE a < 1000000 AND b < '2009-01 00:00:00 '; クエリ計画 ---------------------------------------------------------------------------------------- r1_p1_p1 r1のSeqスキャン フィルター :( (a < 1000000) AND (b < '2009-01-01 00:00:00 '::timestamp without time zone)) (2行)polar_px_optimizer_multilevel_partitioningパラメーターをONに設定して、ePQのマルチレベルパーティションスキャン機能を有効にします。 実行プランを再度照会します。
SET polar_px_optimizer_multilevel_partitioningをオンにします。EXPLAIN (コストオフ) SELECT * FROM r1 WHERE a < 1000000 AND b < '2009-01 00:00:00 '; クエリ計画 ---------------------------------------------------------------------------------------------------- PXコーディネーター6:1 (slice1; セグメント: 6) -> 追加 -> r1_p1_p1の部分Seqスキャン フィルター :( (a < 1000000) AND (b < '2009-01-01 00:00:00 '::timestamp without time zone)) Optimizer: PolarDB PX Optimizer (5行)上記の実行計画に示すように、ePQオプティマイザは、マルチレベルのパーティションテーブルに対して静的パーティションプルーニングを実行します。
slice1を実行する6つのプロセスは、フィルター条件を満たすr1_p1_p1パーティションに対して並列スキャン (Partial Seq scan) を実行し、モーションオペレーターPXコーディネーターを使用してスキャンされたデータを集約してからデータを返すだけです。