AnalyticDB:AnalyticDB for PostgreSQL V7.0のパーティションテーブルの使用

パーティション分割テーブルの使用

テーブルがパーティション分割されている場合、システムは、テーブル全体をスキャンするのではなく、指定されたクエリ条件を満たすパーティションのみをスキャンします。 これにより、クエリのパフォーマンスが向上します。 詳細については、「AnalyticDB For PostgreSQL V6.0のパーティションテーブル」をご参照ください。

レンジパーティションテーブルの作成

レンジパーティションテーブルを作成してから、パーティションテーブルのパーティションとして新しいテーブルを作成できます。 例：

-- Create a partitioned table.
CREATE TABLE sales (id int, date date, amt decimal(10,2))
DISTRIBUTED BY (id)
PARTITION BY RANGE (date);

-- Add a partition.
CREATE TABLE sales_20160101 PARTITION OF sales
FOR VALUES FROM('2016-01-01') TO ('2016-01-02');

AnalyticDB for PostgreSQLを使用して、START値、END値、およびパーティション増分値を定義するEVERY句を指定することで、パーティションを生成できます。 デフォルトでは、START値は包括的で、END値は排他的です。 例：

CREATE TABLE sales (id int, date date, amt decimal(10,2))
DISTRIBUTED BY (id)
PARTITION BY RANGE (date)
(START (date '2016-01-01') INCLUSIVE
END (date '2017-01-01') EXCLUSIVE
EVERY (INTERVAL '1 day'));

数値データ型の列をパーティションキーとして使用するレンジパーティションテーブルを作成できます。 例:

CREATE TABLE rank (id int, rank int, year int, gender char(1), count int)
DISTRIBUTED BY (id)
PARTITION BY RANGE (year)
(START (2006) END (2016) EVERY (1), 
DEFAULT PARTITION extra); 

リストパーティションテーブルの作成

リストパーティションテーブルを作成してから、パーティションテーブルのパーティションとして新しいテーブルを作成できます。 例:

-- Create a partitioned table.
CREATE TABLE rank (id int, rank int, year int, gender 
char(1), count int) 
DISTRIBUTED BY (id)
PARTITION BY LIST (gender);

-- Add a partition.
CREATE TABLE rank_girls PARTITION OF rank FOR VALUES IN ('F');

リストパーティションテーブルは、特定のデータ型の列をパーティションキーとして使用します。 リストパーティションの場合は、作成する各パーティション (リスト値) のパーティション仕様を宣言する必要があります。 例:

CREATE TABLE rank (id int, rank int, year int, gender 
char(1), count int) 
DISTRIBUTED BY (id)
PARTITION BY LIST (gender)
(PARTITION girls VALUES ('F'), 
PARTITION boys VALUES ('M'), 
DEFAULT PARTITION other);

ハッシュパーティションテーブルの作成

ハッシュ分割は、パーティションキー値のハッシュ値に基づいて、データが分散されるパーティションを決定します。 ハッシュ分割テーブルを作成し、分割テーブルのパーティションとして新しいテーブルを作成できます。 ハッシュパーティションテーブルに各パーティションを追加する場合、MODULUS句を使用してテーブルを分割するパーティションの総数を指定し、REMAINDER句を使用して特定のデータを格納するパーティションを指定できます。 各パーティションは、指定されたモジュラスで除算されたときに、パーティションキー値のハッシュ値が指定された剰余を生成する行を保持します。

-- Create a partitioned table.
CREATE TABLE orders (id int, order_date date, customer_id int, amount decimal)
DISTRIBUTED BY (id)
PARTITION BY HASH (customer_id);

-- Add partitions.
CREATE TABLE orders_p0 PARTITION OF orders FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE orders_p1 PARTITION OF orders FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE orders_p2 PARTITION OF orders FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE orders_p3 PARTITION OF orders FOR VALUES WITH (MODULUS 4, REMAINDER 3);

AnalyticDB for PostgreSQLは組み込みのハッシュ関数を使用してパーティションキー値のハッシュ値を計算し、satisfies_hash_partitionシステム関数を使用して特定の値が特定のパーティションに格納されているかどうかを照会できます。 次のステートメントは、customer_idが2であるデータが、MODULUSが4に設定され、REMAINDERが0に設定されているpublic.ordersテーブルのパーティションに格納されているかどうかを照会するために使用され。

SELECT satisfies_hash_partition('public.orders'::regclass, 4, 0, variadic ARRAY[2]); 

式パーティションテーブルの作成

AnalyticDB for PostgreSQL V7.0は、非常に柔軟で使いやすい式パーティション分割をサポートしています。

-- Create a partitioned table. 
CREATE TABLE sales (id int, sale_time timestamp, amt decimal(10,2))
DISTRIBUTED BY (id)
PARTITION BY LIST (date_trunc('month', sale_time));

-- Add a partition.
CREATE TABLE sales_202401 PARTITION OF sales FOR VALUES IN('2024-01-01'::timestamp);

マルチレベルパーティションテーブルの作成

マルチレベルのパーティションを持つテーブルを作成できます。 次のサンプルステートメントは、3レベルのパーティショニングテーブルを作成する方法を示しています。 範囲パーティション内のデータは月ごとに分割され、リストパーティション内のデータは地域ごとに分割されます。

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);

パーティション分割テーブルクエリの最適化

• パーティションの粒度

  時間で区切られたテーブルの場合、粒度は日、週、または月になります。 より細かい粒度は、各パーティションにおいてより少ない量のデータを生成するが、より多くのパーティションを生成する。 パーティションの数は絶対標準では測定されません。 通常、パーティションの数は約200と非常に多いと見なされます。 多数のパーティションは、データベースのパフォーマンスに大きな影響を与えます。 たとえば、クエリオプティマイザが実行計画を生成するのに時間がかかる場合があり、VACUUMなどのメンテナンス操作が遅くなる場合があります。

  マルチレベル分割テーブルでは、分割ファイルの数が急激に増加することがある。 たとえば、24か月と100の都市を含むシナリオで、テーブルが月と都市で分割されている場合、テーブルパーティションの総数は2,400になります。 テーブルが物理テーブルの各列を格納する列指向のテーブルであり、テーブルに100の列が含まれている場合、システムはテーブルに対して100,000を超えるファイルを管理する必要があります。 パーティション分割方法を決定するときは、必要なパーティションの数を見積もる必要があります。

• パーティションの剪定

  AnalyticDB for PostgreSQLは、パーティションテーブルのパーティションプルーニングをサポートしています。 詳細については、「パーティションプルーニング」をご参照ください。

パーティションテーブルの維持

パーティションテーブルでは、さまざまなSQL文を使用してパーティションを管理できます。 例：

• パーティションの作成

  既定のパーティションが存在する場合は、パーティションを作成する代わりに既定のパーティションを分割できます。 詳細については、このトピックの「パーティションの分割」セクションをご参照ください。

  CREATE TABLE sales_p2 PARTITION OF sales FOR VALUES FROM('2017-02-01') TO('2017-02-28');

  パーティションテーブルに少なくとも1つのパーティションがある場合は、ALTER table ADD partition構文を使用してパーティションを作成することもできます。

  ALTER TABLE sales ADD partition p2 start ('2017-02-01') end ('2017-02-28');

• パーティションのアタッチAttach a partition

  パーティションテーブルと同じテーブル構造を持つ既存のテーブルを、パーティションテーブルのパーティションとしてアタッチできます。

  ALTER TABLE sales ATTACH PARTITION sales_p2 FOR VALUES FROM('2017-02-01') TO('2017-02-28');

• パーティションのデタッチ

  パーティションテーブルのパーティションをデタッチできます。 パーティションをデタッチすると、そのパーティションに対応する子パーティションテーブルはパーティションテーブルから関連付けられませんが、削除されません。

  ALTER TABLE sales DETACH PARTITION sales_p2;

• パーティションとそのサブパーティションの削除

  パーティションを削除すると、そのパーティションに対応する子パーティションテーブルが削除されます。

  ALTER TABLE sales DROP PARTITION p2;

• パーティションの名前変更

  ALTER TABLE sales RENAME PARTITION p2 TO Feb17;

• パーティションのクリア

  ALTER TABLE sales TRUNCATE PARTITION p1;

• パーティションの交換

  ALTER TABLE sales EXCHANGE PARTITION p2 WITH TABLE {cos_table_name} ;

• パーティションの分割

  -- Split the p3 partition into two partitions with the boundary value set to 2017-03-20.
  ALTER TABLE sales SPLIT partition p3 at ('2017-03-20') into (partition p2, partition p3);

  パーティションテーブルにデフォルトのパーティションがある場合は、デフォルトのパーティションを分割することによってのみパーティションを作成できます。

  ALTER TABLE sales SPLIT DEFAULT PARTITION START('2017-03-01') END('2017-03-31') into(partition p3, default partition);