AnalyticDB for PostgreSQLは、静的パーティションプルーニングと動的パーティションプルーニングをサポートしています。 パーティションテーブルのクエリに対してパーティションプルーニングが有効になっている場合、システムはパーティションの制約と定義に基づいて不要なパーティションをスキップします。 これにより、スキャンする必要のあるデータ量が減り、クエリのパフォーマンスが向上します。
AnalyticDB for PostgreSQLは、列ベースのパーティション分割をサポートしています。 ファクトテーブルは複数のパーティションに分割できます。 システムは、指定されたクエリ条件を満たすパーティションのみをスキャンします。 これにより、クエリのパフォーマンスが向上します。 パーティションプルーニングは、パーティションテーブルで一般的に使用されるパフォーマンス最適化方法です。
制限事項
パーティションプルーニングは、パーティションテーブルにのみ適用できます。
パーティションプルーニングは、範囲またはリストパーティション列の範囲、方程式、およびIN述語のみをサポートします。 AnalyticDB for PostgreSQL V7.0はハッシュパーティションをサポートしています。 ハッシュパーティションは、等価条件を使用してのみプルーニングできます。
動的パーティションプルーニングは、パーティション列の
=やINなどの等価条件のみをサポートします。パーティションプルーニングの効果は、データの分散に関連しています。 パーティション制約がパーティションを効果的に剪定できない場合、パフォーマンスが低下し、テーブル全体がスキャンされます。
静的パーティションプルーニング
概要
パーティション制約が明確な式の場合、パーティション制約式を使用して、クエリ実行前に実行プランから不要なパーティションを除外できます。 この方法は、静的パーティションプルーニングと呼ばれます。
AnalyticDB for PostgreSQLは、静的述語を使用して、静的パーティションプルーニングを実行する時刻を決定します。 次の静的述語がサポートされています:=、> 、>= 、< 、<= 、およびIN。
静的パーティションプルーニングの結果は、EXPLAINステートメントの応答から取得できます。
例
例1:
=述語を使用してパーティションプルーニングを実行する-- Create a partitioned table. CREATE TABLE sales (id int, year int, month int, day int, region text) DISTRIBUTED BY (id) PARTITION BY RANGE (month) SUBPARTITION BY LIST (region) SUBPARTITION TEMPLATE ( SUBPARTITION usa VALUES ('usa'), SUBPARTITION europe VALUES ('europe'), SUBPARTITION asia VALUES ('asia'), DEFAULT SUBPARTITION other_regions) (START (1) END (13) EVERY (1), DEFAULT PARTITION other_months ); -- Perform partition pruning. EXPLAIN SELECT * FROM sales WHERE year = 2008 AND month = 1 AND day = 3 AND region = 'usa';クエリ条件は、レベル1パーティション1のレベル2パーティション
「usa」に該当します。 したがって、レベル2パーティション「usa」内のデータのみがクエリ中にスキャンされます。 EXPLAINステートメントの応答は、52のレベル2パーティションの1つだけがスキャンされることを示しています (partitions selected: 1) 。Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=24) -> Sequence (cost=0.00..431.00 rows=1 width=24) -> Partition Selector for sales (dynamic scan id: 1) (cost=10.00..100.00 rows=34 width=4) Partitions selected: 1 (out of 52) -> Dynamic Seq Scan on sales (dynamic scan id: 1) (cost=0.00..431.00 rows=1 width=24) Filter: ((year = 2008) AND (month = 1) AND (day = 3) AND (region = 'usa'::text))例2:
>=およびIN述語を使用してパーティションプルーニングを実行するEXPLAIN SELECT * FROM sales WHERE month in (1,5) AND region >= 'usa'; QUERY PLAN ----------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=24) -> Sequence (cost=0.00..431.00 rows=1 width=24) -> Partition Selector for sales (dynamic scan id: 1) (cost=10.00..100.00 rows=34 width=4) Partitions selected: 6 (out of 52) -> Dynamic Seq Scan on sales (dynamic scan id: 1) (cost=0.00..431.00 rows=1 width=24) Filter: ((month = ANY ('{1,5}'::integer[])) AND (region >= 'usa'::text))
静的パーティションプルーニングは、LIKEや <> などの演算子をサポートしていません。 たとえば、WHERE条件をregion LIKE 'usa' に変更した場合、パーティションプルーニングは実行できません。
EXPLAIN
SELECT * FROM sales
WHERE region LIKE 'usa';
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=24)
-> Sequence (cost=0.00..431.00 rows=1 width=24)
-> Partition Selector for sales (dynamic scan id: 1) (cost=10.00..100.00 rows=34 width=4)
Partitions selected: 52 (out of 52)
-> Dynamic Seq Scan on sales (dynamic scan id: 1) (cost=0.00..431.00 rows=1 width=24)
Filter: (region ~~ 'usa'::text)動的パーティションプルーニング
概要
パーティション制約式にサブクエリが含まれるPREPARE-EXECUTEシナリオの場合、クエリ実行前に実行プランからパーティション制約を取得することはできません。 クエリの実行中にパーティションプルーニングを実行するには、外部パラメーターとサブクエリ応答を使用する必要があります。 この方法は、動的パーティションプルーニングと呼ばれます。
EXPLAIN ANALYZEステートメントの応答から、動的パーティションプルーニングの結果を取得できます。
動的パーティションプルーニングを使用してJOIN操作を最適化
ファクトテーブルとディメンションテーブルを結合するデータウェアハウスシナリオでは、AnalyticDB For PostgreSQLを使用して、動的パーティションプルーニングを使用して、パーティション分割されたファクトテーブルに対するjoin操作を最適化できます。
ほとんどの場合、ファクトテーブルは大きく、ディメンションテーブルは小さくなります。 結合キーがファクトテーブル (大きなテーブル) のパーティションキーとして使用されている場合、AnalyticDB for PostgreSQLは、ディメンションテーブル (小さなテーブル) のデータに基づいて大きなテーブルのパーティション制約を動的に生成します。 このように、システムは不要なパーティションをスキップして、JOIN演算子が含むデータ量を削減します。
動的パーティションプルーニングは、JOIN演算子の内部テーブルのデータを使用して、外部テーブル (パーティションテーブル) のパーティションフィルタを動的に生成します。 これにより、不要なパーティションをスキップできます。 次の図は、動的パーティションプルーニングなしで、パーティション分割された大きなテーブルと小さなテーブルを結合するプロセスを示しています。
結合キーをパーティションキーとして使用する場合、パーティション分割テーブル内のすべてのパーティションをスキャンしてから、小さなテーブルに結合する必要があります。 動的パーティションプルーニングが有効になっている場合、システムは最初に小さなテーブルをスキャンし、パーティションフィルタを生成してから、そのパーティションフィルタを大きなテーブルのSCAN演算子に送信します。 このようにして、一部のパーティションのみがスキャンされ、JOINオペレータに送信されます。 次の図は、動的パーティションプルーニングを使用して、パーティション分割された大きなテーブルと小さなテーブルを結合するプロセスを示しています。
動的パーティションプルーニングを使用して、パーティション分割された大きなテーブルに基づく大きなテーブルと小さなテーブルの結合を加速することを推奨します。
例
例1: WHERE句にサブクエリが含まれている場合の動的パーティションプルーニングの実行
CREATE TABLE t1 (a int, b int); INSERT INTO t1 VALUES (3,3), (5,5); EXPLAIN ANALYZE SELECT * FROM sales WHERE month = ( SELECT MIN(a) FROM t1 );WHERE句には、クエリの実行前に実行プランから応答が得られないサブクエリが含まれます。 パーティション制約は、クエリの実行中にのみ取得できます。 INSERT INTO VALUESステートメントは、
MIN(a)の値が3であることを示す。 この場合、month列の値が3である4つのパーティションのみがsalesテーブルからスキャンされます。 この結果は、EXPLAIN ANALYZEステートメント (Partitions scanned: Avg 4.0) の応答から検証できます。Gather Motion 3:1 (slice3; segments: 3) (cost=0.00..862.00 rows=1 width=24) (actual time=5.134..5.134 rows=0 loops=1) -> Hash Join (cost=0.00..862.00 rows=1 width=24) (never executed) Hash Cond: (sales.month = (min((min(t1.a))))) -> Sequence (cost=0.00..431.00 rows=1 width=24) (never executed) -> Partition Selector for sales (dynamic scan id: 1) (cost=10.00..100.00 rows=34 width=4) (never executed) Partitions selected: 52 (out of 52) -> Dynamic Seq Scan on sales (dynamic scan id: 1) (cost=0.00..431.00 rows=1 width=24) (never executed) Partitions scanned: Avg 4.0 (out of 52) x 3 workers. Max 4 parts (seg0). -> Hash (cost=100.00..100.00 rows=34 width=4) (actual time=0.821..0.821 rows=1 loops=1) -> Partition Selector for sales (dynamic scan id: 1) (cost=10.00..100.00 rows=34 width=4) (actual time=0.817..0.817 rows=1 loops=1) -> Broadcast Motion 1:3 (slice2) (cost=0.00..431.00 rows=3 width=4) (actual time=0.612..0.612 rows=1 loops=1) -> Aggregate (cost=0.00..431.00 rows=1 width=4) (actual time=1.204..1.205 rows=1 loops=1) -> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=4) (actual time=1.047..1.196 rows=3 loops=1) -> Aggregate (cost=0.00..431.00 rows=1 width=4) (actual time=0.012..0.012 rows=1 loops=1) -> Seq Scan on t1 (cost=0.00..431.00 rows=1 width=4) (actual time=0.005..0.005 rows=2 loops=1)例2: 動的パーティションプルーニングを使用してJOIN操作を最適化する
EXPLAIN SELECT * FROM sales JOIN t1 ON sales.month = t1.a WHERE sales.region = 'usa';t1テーブルは、(3,3)と(5,5)の2つのレコードのみを含む。 t1テーブルがパーティションキーmonthに基づいてsalesテーブルと結合されている場合、動的パーティションプルーニングは、month列の値が3および5であるパーティションのみをスキャンします。 サンプルステートメントは、region列の値が'usa'に設定されていることを示しています。 この場合、2つのパーティションのみがスキャンされます。 この結果は、EXPLAIN ANALYZEステートメント (Partitions scanned: Avg 2.0) の応答から検証できます。QUERY PLAN --------------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice2; segments: 3) (actual time=3.204..16.022 rows=6144 loops=1) -> Hash Join (actual time=2.212..11.938 rows=6144 loops=1) Hash Cond: (sales.month = t1.a) Extra Text: (seg1) Hash chain length 1.0 avg, 1 max, using 2 of 524288 buckets. -> Sequence (actual time=0.317..4.197 rows=6144 loops=1) -> Partition Selector for sales (dynamic scan id: 1) (never executed) Partitions selected: 13 (out of 52) -> Dynamic Seq Scan on sales (dynamic scan id: 1) (actual time=0.311..3.391 rows=6144 loops=1) Filter: (region = 'usa'::text) Partitions scanned: Avg 2.0 (out of 52) x 3 workers. Max 2 parts (seg0). -> Hash (actual time=0.316..0.316 rows=2 loops=1) -> Partition Selector for sales (dynamic scan id: 1) (actual time=0.208..0.310 rows=2 loops=1) -> Broadcast Motion 3:3 (slice1; segments: 3) (actual time=0.008..0.012 rows=2 loops=1) -> Seq Scan on t1 (actual time=0.004..0.004 rows=1 loops=1)
よくある質問
Q: クエリでパーティションプルーニングが実行されるかどうかを確認するにはどうすればよいですか?
A: EXPLAINステートメントを実行し、ステートメントの応答を表示できます。
パーティションセレクターが表示されている場合、パーティションプルーニングが有効になります。Q: パーティションプルーニングは、ネイティブPostgreSQLオプティマイザとORCAの両方でサポートされていますか?
A: はい、プランナーとORCAの両方が静的または動的パーティションプルーニングをサポートしています。 彼らの実行計画は少し異なります。
Q: パーティションの剪定が失敗したのはなぜですか?
A: パーティションプルーニングでは、パーティションキーに基づいてデータをフィルタリングまたは結合する必要があります。 静的パーティションプルーニングでは、
=、>、>=、<、<=、およびINのみがサポートされます。 動的パーティションプルーニングでは、等価条件のみがサポートされます。