PostgreSQLでパーティション数が多いほどプラン生成時間が長くなる問題の確認

PostgreSQLのパーティショニング機能には、パーティション数が多いほど親パーティションに対するクエリのプラン生成時間が長くなる問題がある。これは、PostgreSQL10のネイティブ・パーティショニング機能でも従来のパーティショニング機能でも同様である。

今回は以下5パターンにおけるクエリのプラン生成時間を比較して、パーティション数が多いほどプラン生成時間が長くなることを確認する。

1. パーティション分割しない
2. 10パーティションに分割
3. 100パーティションに分割
4. 500パーティションに分割
5. 1000パーティションに分割

PostgreSQL10のネイティブ・パーティショニングの場合

-- 指定の数だけ分割されたパーティションを作成する関数を登録する
CREATE OR REPLACE FUNCTION create_partition_native (
  parent TEXT, num INT
) RETURNS VOID AS $$
DECLARE
  i INT;
  child TEXT;
BEGIN
  -- パーティション作成は、v10のネイティブ・パーティショニング機能の構文で行う
  EXECUTE 'CREATE TABLE ' || parent || ' (key INT, val TEXT)' ||
    ' PARTITION BY LIST (key)';
  FOR i IN 1 .. num LOOP
    child := parent || '_' || to_char(i, 'FM0000000');
    EXECUTE 'CREATE TABLE ' || child || ' PARTITION OF ' || parent ||
      ' (PRIMARY KEY(key)) FOR VALUES IN (' || i || ')';
  END LOOP;
END;
$$ LANGUAGE plpgsql;

-- 分割数が10、100、500、1000のパーティションを作成する
SELECT create_partition_native('native_0010', 10);
SELECT create_partition_native('native_0100', 100);
SELECT create_partition_native('native_0500', 500);
SELECT create_partition_native('native_1000', 1000);

-- 各分割数のパーティション上でクエリのEXPLAIN ANALYZEを実行し、プラン生成時間を確認する
EXPLAIN ANALYZE SELECT * FROM native_0010_0000009 WHERE key = 9;
EXPLAIN ANALYZE SELECT * FROM native_0010 WHERE key = 9;
EXPLAIN ANALYZE SELECT * FROM native_0100 WHERE key = 9;
EXPLAIN ANALYZE SELECT * FROM native_0500 WHERE key = 9;
EXPLAIN ANALYZE SELECT * FROM native_1000 WHERE key = 9;

各パターン、5回ずつEXPLAIN ANALYZEを実行したときのプラン生成時間の中間値は以下のとおり。

項番	パターン	プラン生成時間(ms)
1	パーティション分割しない	0.045
2	10パーティションに分割	0.191
3	100パーティションに分割	1.397
4	500パーティションに分割	70.353
5	1000パーティションに分割	138.918

PostgreSQL10のネイティブ・パーティショニング機能でも、パーティション数が多いほどプラン生成時間が長くなることを確認できる。

従来のパーティショニング機能の場合

-- 指定の数だけ分割されたパーティションを作成する関数を登録する
CREATE OR REPLACE FUNCTION create_partition_traditional (
  parent TEXT, num INT
) RETURNS VOID AS $$
DECLARE
  i INT;
  child TEXT;
BEGIN
  -- パーティション作成は、従来のパーティショニング機能の構文で行う
  -- 本来はパーティションへのINSERT用にトリガを作成するが、今回の検証には不要なため作成しない
  EXECUTE 'CREATE TABLE ' || parent || ' (key INT PRIMARY KEY, val TEXT)';
  FOR i IN 1 .. num LOOP
    child := parent || '_' || to_char(i, 'FM0000000');
    EXECUTE 'CREATE TABLE ' || child || ' (LIKE ' || parent ||
      ' INCLUDING INDEXES, CHECK(key = ' || i || ')) INHERITS (' ||
      parent || ')';
  END LOOP;
END;
$$ LANGUAGE plpgsql;

-- 分割数が10、100、500、1000のパーティションを作成する
SELECT create_partition_traditional('traditional_0010', 10);
SELECT create_partition_traditional('traditional_0100', 100);
SELECT create_partition_traditional('traditional_0500', 500);
SELECT create_partition_traditional('traditional_1000', 1000);

-- 各分割数のパーティション上でクエリのEXPLAIN ANALYZEを実行し、プラン生成時間を確認する
EXPLAIN ANALYZE SELECT * FROM traditional_0010_0000009 WHERE key = 9;
EXPLAIN ANALYZE SELECT * FROM traditional_0010 WHERE key = 9;
EXPLAIN ANALYZE SELECT * FROM traditional_0100 WHERE key = 9;
EXPLAIN ANALYZE SELECT * FROM traditional_0500 WHERE key = 9;
EXPLAIN ANALYZE SELECT * FROM traditional_1000 WHERE key = 9;

各パターン、5回ずつEXPLAIN ANALYZEを実行したときのプラン生成時間の中間値は以下のとおり。

項番	パターン	プラン生成時間(ms)
1	パーティション分割しない	0.054
2	10パーティションに分割	0.202
3	100パーティションに分割	1.425
4	500パーティションに分割	74.960
5	1000パーティションに分割	158.204

従来のパーティショニング機能でも、パーティション数が多いほどプラン生成時間が長くなることを確認できる。