PolarDB:PartitionedTableScan

背景情報

では、テーブルのパーティション数が制限されないため、サブパーティショニングではパーティション数が指数関数的に増加します。

例えば、テーブルが100のパーティションにハッシュ分割され、次に各パーティションが100のサブパーティションにハッシュ分割される場合、このパーティション分割されたテーブルには合計10,000のパーティションが存在することになる。 このテーブルに対してクエリを実行すると、実行計画は次のようになります。

part_hashからselect * の分析を説明します。
                                 クエリ計画
-----------------------------------------------------------------------------
 追加 (コスト=0.00 .. 344500.00行=16300000幅=22)
   -> part_hash_sys0102のSeqスキャン (コスト=0.00 .. 26.30行=1630幅=22)
   -> part_hash_sys0103のSeqスキャン (コスト=0.00 .. 26.30行=1630幅=22)
   -> part_hash_sys0104のSeqスキャン (コスト=0.00 .. 26.30行=1630幅=22)
  ...
  ...
  ...
   -> part_hash_sys10198のSeqスキャン (コスト=0.00 .. 26.30行=1630幅=22)
   -> part_hash_sys10199のSeqスキャン (コスト=0.00 .. 26.30行=1630幅=22)
   -> part_hash_sys10200のSeqスキャン (コスト=0.00 .. 26.30行=1630幅=22)
 計画時間: 3183.644 ms
 実行時間: 633.779 ms
(10003行)
総メモリ: 216852KB 

計画に示されているように、クエリの完了には長い時間がかかります。これは、オプティマイザがパーティションテーブルの実行計画を生成する方法に関連しています。 パーティションテーブルが少数のパーティションしか有していない場合、プロセスは高速であり、満足なユーザエクスペリエンスを提供する。 ただし、パーティションの数が増えると、プロセスが遅くなり、パーティション分割されていないテーブルよりもパーティション分割されたテーブルのパフォーマンスが悪くなることがあります。

たとえば、前のクエリでは、テーブルのpart_hashに10,000のパーティションがあります。 その計画時間は3秒に達しました。 対照的に、テーブルが分割されていない場合、このクエリの計画時間は約0.1ミリ秒になります。 長い計画時間に加えて、パーティション分割テーブルのクエリでは、メモリ使用量が多くなり、OOMの問題が発生する可能性があります。

パーティション分割テーブルのこの欠点は、次のクエリプランに示すように、結合がクエリに含まれる場合にさらに悪化します。

create table part_hash2 (a int, b int, c varchar (10))
PARTITION by HASH(a) SUBPARTITION by HASH (b) PARTITIONS 100 SUBPARTITIONS 100;

a.a=b.bでpart_hash a join part_hash2 bから選択カウント (*) を分析すると説明します。b.c = '0001';
                                                                            クエリ計画
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
 集計の完了 (コスト=48970442.90 .. 48970442.91行=1幅=8) (実際の時間=6466.854 .. 6859.935行=1ループ=1)
   -> 収集 (コスト=48970442.68 .. 48970442.89行=2幅=8) (実際の時間=397.780 .. 6859.902行=3ループ=1)
         Workers Planned: 2
         労働者の立ち上げ: 2
         -> 部分集計 (コスト=48969442.68 .. 48969442.69行=1幅=8) (実際の時間=4.748 .. 11.768行=1ループ=3)
               -> マージ結合 (コスト=1403826.01 .. 42177776.01行=2716666667幅=0) (実際の時間=4.736 .. 11.756行=0ループ=3)
                     結合: (a.a = b.b)
                     -> 並べ替え (コスト=1093160.93 .. 1110135.93行=6790000幅=4) (実際の時間=4.734 .. 8.588行=0ループ=3)
                           ソートキー: a.a
                           ソート方法: quicksortメモリ: 25kB
                           ワーカー0: ソート方法: quicksortメモリ: 25kB
                           ワーカー1: ソート方法: quicksortメモリ: 25kB
                           -> 並列追加 (コスト=0.00 .. 229832.35行=6790000幅=4) (実際の時間=4.665 .. 8.518行=0ループ=3)
                                 -> Parallel Seq part_hash_sys0102aでスキャン (コスト=0.00 .. 19.59行=959幅=4) (実際の時間=0.001 .. 0.001行=0ループ=1)
                                 -> Parallel Seq part_hash_sys0103 a_1でスキャン (コスト=0.00 .. 19.59行=959幅=4) (実際の時間=0.001 .. 0.001行=0ループ=1)
                                 -> Parallel Seq part_hash_sys0104 a_2でスキャン (コスト=0.00 .. 19.59行=959幅=4) (実際の時間=0.001 .. 0.001行=0ループ=1)
                                  ...
                     -> 並べ替え (コスト=310665.08 .. 310865.08行=80000幅=4) (実行されない)
                           ソートキー: b.b
                           -> 追加 (コスト=0.00 .. 304150.00行=80000幅=4) (実行されない)
                                 -> Seq part_hash2_sys0102 bのスキャン (コスト=0.00 .. 30.38行=8幅=4) (実行されない)
                                       フィルター: ((c)::text = '0001'::text)
                                 -> part_hash2_sys0103 b_1のSeqスキャン (コスト=0.00 .. 30.38行=8幅=4) (実行されない)
                                       フィルター: ((c)::text = '0001'::text)
                                 -> part_hash2_sys0104 b_2のSeqスキャン (コスト=0.00 .. 30.38行=8幅=4) (実行されない)
                                       フィルター: ((c)::text = '0001'::text)
                                  ... 

 計画時間: 221082.616 ms
 実行時間: 9500.148 ms
(30018行)
総メモリ: 679540KB 

したがって、スキャンされたデータを特定のパーティションに限定することができないクエリの場合、パーティション分割されたテーブルは、非パーティション分割されたテーブルよりもパフォーマンスが悪い場合があります。 パーティションプルーニングはパーティションを特定するのに役立ちますが、一部のOLAPシナリオではフルテーブルスキャンが必要な場合があるため、これはすべて解決策ではありません。

解決策

多数のパーティションを持つテーブルに対する長期クエリの問題を解決するために、はPartitionedTableScan演算子を提供しています。 追加演算子を使用する場合と比較して、効率が高く、計画時間が短く、メモリ使用量が少なくなります。

PartitionedTableScanを使用すると、2つのSQL文が実行され、計画時間とメモリ消費が大幅に削減されます。

part_hashからselect * の分析を説明します。
                                 クエリ計画
----------------------------------------------------------------------------------------------------------------------------------------------------
part_hashのPartitionedTableScan (コスト=0.00 .. 1.00行=1幅=22) (実時間=134.348 .. 134.352行=0ループ=1)(反復パーティション番号10000)
   スキャンパーティション: part_hash_sys0102、part_hash_sys0103、。.. part_hash_sys10198, part_hash_sys10199, part_hash_sys10200
   -> part_hashのSeqスキャン (コスト=0.00 .. 1.00行=1幅=22)
 計画時間: 293.778 ms
 実行時間: 384.202 ms
(5行)
総メモリ: 40276KB

a.a=b.bでpart_hash a join part_hash2 bから選択カウント (*) を分析すると説明します。b.c = '0001';
                                                                            クエリ計画
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
集計 (コスト=2.02 .. 2.03行=1幅=8) (実際の時間=152.322 .. 152.326行=1ループ=1)
   -> ネストされたループ (コスト=0.00 .. 2.02行=1幅=0) (実際の時間=152.308 .. 152.311行=0ループ=1)
         参加フィルター :( a.a = b.b)
         -> part_hashのPartitionedTableScan (コスト=0.00 .. 1.00行=1幅=4) (実際の時間=152.305 .. 152.306行=0ループ=1)(反復パーティション数10000)
               スキャンパーティション: part_hash_sys0102, part_hash_sys0103, part_hash_sys10198, part_hash_sys10199, part_hash_sys10200
               -> Seq part_hash aのスキャン (コスト=0.00 .. 1.00行=1幅=4)
         -> part_hash2 bのPartitionedTableScan (コスト=0.00 .. 1.00行=1幅=4) (実行されない)
               -> part_hash2 bのSeqスキャン (コスト=0.00 .. 1.00行=1幅=4)
                     フィルター: ((c)::text = '0001'::text)
 計画時間: 732.952 ms
 実行時間: 436.927 ms
(11行)
総メモリ: 68104KB 

次の表に比較を示します。

制限事項

• PartitionedTableScanを使用して実行できるのは、SELECTコマンドのみです。 DMLステートメントはサポートされていません。

• PartitionedTableScanをパーティション単位の結合と一緒に使用することはできません。 パーティションごとの結合機能が有効になっている場合、実行計画にはPartitionedTableScan演算子は含まれません。

手順

このセクションでは、例を使用してPartitionedTableScan機能の使用方法を説明します。

デモ用に次のパーティションテーブルが作成されています。

CREATE TABLE prt1 (a int, b int, c varchar) PARTITION BY Hash(a) パーティション16;

PartitionedTableScanの有効化

PartitionedTableScan機能を有効または無効にするには、polar_num_parts_for_partitionedscanパラメーターを設定します。

例：

します。説明select * from prt1;
                           クエリ計画
-----------------------------------------------------------------
 prt1のPartitionedTableScan (コスト=0.00 .. 1.00行=1幅=40)
   -> Seqスキャンprt1 (コスト=0.00 .. 1.00行=1幅=40)
(2行) 

ヒントの使用

ヒントPARTEDSCAN (テーブルエイリアス) を使用して、PartitionedTableScanの使用を指示できます。 例:

EXPLAIN select /* + PARTEDSCAN(prt1) */ select * from prt1;
                           クエリ計画
-----------------------------------------------------------------
 prt1のPartitionedTableScan (コスト=0.00 .. 1.00行=1幅=40)
   -> Seqスキャンprt1 (コスト=0.00 .. 1.00行=1幅=40)
(2行) 

PartitionedTableScan in parallel

には、大量のデータに対するクエリのパフォーマンスを向上させる並列追加機能があります。 parallel appendと同様に、PartitionedTableScanも並行して実行できます。 機能の名前はPartitionedTableScan Appendです。 パーティション間モードとハイブリッドモードの2つの並列モードをサポートしています。

PartitionedTableScanパーティションに追加

このモードでは、各ワーカーがパーティションを処理し、並列に動作します。

EXPLAIN (COSTS OFF) select /* + PARTEDSCAN(prt1) */ * from prt1;
                 クエリ計画
---------------------------------------------
 収集
   Workers Planned: 4
   -> Parallel PartitionedTableScan on prt1
         -> Seqスキャンprt1
(4行) 

上記のクエリプランでは、4つのワーカーが実行に使用され、それぞれが1つのパーティションで動作します。 クエリプランのParallel PartitionedTableScan演算子は、このモードのインジケーターです。

Hybrid PartitionedTableScanの追加

ハイブリッドモードでは、システムは、パーティション内およびパーティション間の両方で並列に実行を処理します。

EXPLAIN (COSTS OFF) select /* + PARTEDSCAN(prt1) */ * from prt1;
                 クエリ計画
---------------------------------------------
 収集
   計画されている労働者: 8
   -> Parallel PartitionedTableScan on prt1
         -> パラレルSeqスキャンprt1
(4行) 

この例では、8つのワーカーが使用されます。 このクエリプランには、Parallel PartitionedTableScanインジケーターが含まれています。

各モードにはコストモデルがあり、オプティマイザはコストの低いモードを選択します。

パーティションの剪定

PartitionedTableScanは、3つのステージすべてでパーティションプルーニングをサポートします。 パーティションプルーニングの詳細については、「パーティションプルーニング」をご参照ください。

パフォーマンステスト

PartitionedTableScanはより高い効率を提供します。 このパフォーマンステストは、PartitionedTableScanによって有効になっているパフォーマンスの改善を示します。

テストには次のSQL文が使用されます。

select * from prt1 b = 10;

select /* + PARTEDSCAN(prt1) */ * from prt1 b = 10; 

単一のSQL文の計画時間

単一のSQL文のメモリ使用量

1秒あたりのクエリ (QPS)

pgbench -i -- scale=10
pgbench -c 64 -j 64 -n -T60
クエリ:
説明select * from prt1 b = 10;
select /* + PARTEDSCAN(prt1) */ * from prt1 b = 10; 

結論

上記のパフォーマンステストの結果は、PartitionedTableScanがパフォーマンスを大幅に改善できることを示しています。 ビジネスに関係するテーブルに多数のパーティションがあり、クエリの実行に時間がかかる場合は、パフォーマンスを向上させるためにPartitionedTableScanを使用することをお勧めします。