Kの系譜

ママは肉食 パパは草食 娘は雑食

【OSS】PostgreSQL 10 多段パーティションの検証1

1. 概要

PostgreSQL 10 多段パーティションについて検証してみた

2. 環境

  • Windows 7 64bit
  • PostgreSQL 10

3. 多段パーティションの検証

こんな感じでパーティション組んでみる

f:id:kkkklog:20171130233819p:plain

postgres=# CREATE TABLE america (
postgres(#     state text,
postgres(#     city text,
postgres(#     data text
postgres(# )
postgres-# PARTITION BY LIST (state);
CREATE TABLE

americaテーブルを定義する。

PARTITINO BY LIST という構文でパーティションキー(state)を指定する。

そして、america の子となるパーティションテーブル、

California, NewYork, Massachusetts を定義する。

postgres=# CREATE TABLE California PARTITION OF america
postgres-# FOR VALUES IN ('カリフォルニア')
postgres-# PARTITION BY LIST (city);
CREATE TABLE
postgres=# CREATE TABLE NewYork PARTITION OF america
postgres-# FOR VALUES IN ('ニューヨーク');
CREATE TABLE
postgres=# CREATE TABLE Massachusetts PARTITION OF america
postgres-# FOR VALUES IN ('マサチューセッツ');
CREATE TABLE

postgres=# \d California
           Table "public.california"
 Column | Type | Collation | Nullable | Default
--------+------+-----------+----------+---------
 state  | text |           |          |
 city   | text |           |          |
 data   | text |           |          |
Partition of: america FOR VALUES IN ('カリフォルニア')
Partition key: LIST (city)
postgres=# \d NewYork
             Table "public.newyork"
 Column | Type | Collation | Nullable | Default
--------+------+-----------+----------+---------
 state  | text |           |          |
 city   | text |           |          |
 data   | text |           |          |
Partition of: america FOR VALUES IN ('ニューヨーク')
postgres-# \d Massachusetts
          Table "public.massachusetts"
 Column | Type | Collation | Nullable | Default
--------+------+-----------+----------+---------
 state  | text |           |          |
 city   | text |           |          |
 data   | text |           |          |
Partition of: america FOR VALUES IN ('マサチューセッツ')

FOR VALUES INでパーティションキーの値を指定する。

IN構文を使っているので、単一値だけではなく複数の値も指定可能っぽい(試してないけど)。

そして California の定義に着目。そう、パーティションの子側のテーブル自体にもPARTITION構文が使える。

つまり、多段パーティションの定義が可能になっている!

California テーブルの子テーブルとして、

California.SanFrancisco, California.LosAngeles, California.SanDiego も定義する。

postgres=# CREATE TABLE "California.SanFrancisco" PARTITION OF California FOR VA
LUES IN ('サンフランシスコ');
CREATE TABLE
postgres=# CREATE TABLE "California.LosAngeles"  PARTITION OF California FOR VAL
UES IN ('ロサンゼルス');
CREATE TABLE
postgres=# CREATE TABLE "California.SanDiego"  PARTITION OF California FOR VALUES IN ('サンディエゴ');
CREATE TABLE

postgres=#  \d California
           Table "public.california"
 Column | Type | Collation | Nullable | Default
--------+------+-----------+----------+---------
 state  | text |           |          |
 city   | text |           |          |
 data   | text |           |          |
Partition of: america FOR VALUES IN ('カリフォルニア
Partition key: LIST (city)
Number of partitions: 3 (Use \d+ to list them.)

データ挿入 テーブル定義ができたのでデータを挿入しよう。

postgres=# INSERT INTO america VALUES ('カリフォルニア','サンフランシスコ','ゴールデンゲートブリッジ');
INSERT 0 1
postgres=# INSERT INTO america VALUES ('カリフォルニア','サンフランシスコ','SNS企業');
INSERT 0 1
postgres=# INSERT INTO america VALUES ('カリフォルニア','ロサンゼルス','ドジャース');
INSERT 0 1
postgres=# INSERT INTO america VALUES ('カリフォルニア','ロサンゼルス','フライアウェイ');
INSERT 0 1
postgres=# INSERT INTO america VALUES ('カリフォルニア','サンディエゴ','サンディエゴ美術館');
INSERT 0 1
postgres=# INSERT INTO america VALUES ('ニューヨーク','バッファロー','水牛');
INSERT 0 1
postgres=# INSERT INTO america VALUES ('ニューヨーク','ロチェスター','ロチェスター工科大学');
INSERT 0 1
postgres=# INSERT INTO america VALUES ('マサチューセッツ','ボストン','オペラハウス');
INSERT 0 1
postgres=# commit;
WARNING:  there is no transaction in progress
COMMIT

state のフィールドに、'カリフォルニア', 'ニューヨーク'. 'マサチューセッツ'以外が指定したら

エラーになる。

この状態で親テーブルamericaを検索してみる。

postgres=# TABLE america;
      state       |       city       |           data
------------------+------------------+--------------------------
 ニューヨーク     | バッファロー     | 水牛
 ニューヨーク     | ロチェスター     | ロチェスター工科大学
 マサチューセッツ | ボストン         | オペラハウス
 カリフォルニア   | サンフランシスコ | ゴールデンゲートブリッジ
 カリフォルニア   | サンフランシスコ | SNS企業
 カリフォルニア   | ロサンゼルス     | ドジャース
 カリフォルニア   | ロサンゼルス     | フライアウェイ
 カリフォルニア   | サンディエゴ     | サンディエゴ美術館
(8 rows)

きちんと各パーティションをマージした結果が返却される。

EXPLAINをとってみると

postgres=# EXPLAIN TABLE america;
                                    QUERY PLAN

-----------------------------------------------------------------------------------
 Append  (cost=0.00..82.50 rows=3250 width=96)
   ->  Seq Scan on newyork  (cost=0.00..16.50 rows=650 width=96)
   ->  Seq Scan on massachusetts  (cost=0.00..16.50 rows=650 width=96)
   ->  Seq Scan on "California.SanFrancisco"  (cost=0.00..16.50 rows=650 width=96)
   ->  Seq Scan on "California.LosAngeles"  (cost=0.00..16.50 rows=650 width=96)
   ->  Seq Scan on "California.SanDiego"  (cost=0.00..16.50 rows=650 width=96)
(6 rows)

全パーティションを検索して Append している。

当たり前だけど、全てのパーティションを検索してAppendしている。

パーティションキーを付与する

WHERE句にパーティションキーを付与して検索してみる。

postgres=# SELECT * FROM america WHERE state = 'カリフォルニア';
     state      |       city       |           data
----------------+------------------+--------------------------
 カリフォルニア | サンフランシスコ | ゴールデンゲートブリッジ
 カリフォルニア | サンフランシスコ | SNS企業
 カリフォルニア | ロサンゼルス     | ドジャース
 カリフォルニア | ロサンゼルス     | フライアウェイ
 カリフォルニア | サンディエゴ     | サンディエゴ美術館
(5 rows)

このときのEXPLAIN結果を見ると

postgres=# EXPLAIN SELECT * FROM america WHERE state = 'カリフォルニア';
                                   QUERY PLAN

--------------------------------------------------------------------------------
-
 Append  (cost=0.00..54.38 rows=9 width=96)
   ->  Seq Scan on "California.SanFrancisco"  (cost=0.00..18.13 rows=3 width=96)
         Filter: (state = 'カリフォルニア'::text)
   ->  Seq Scan on "California.LosAngeles"  (cost=0.00..18.13 rows=3 width=96)
         Filter: (state = 'カリフォルニア'::text)
   ->  Seq Scan on "California.SanDiego"  (cost=0.00..18.13 rows=3 width=96)
         Filter: (state = 'カリフォルニア'::text)
(7 rows)

カリフォルニアに属するパーティションだけプルーニングしているのが分かる。

検索条件にstateとcityを付与する。

postgres=# SELECT * FROM america WHERE state = 'カリフォルニア' AND city = 'サンフランシスコ';
     state      |       city       |           data
----------------+------------------+--------------------------
 カリフォルニア | サンフランシスコ | ゴールデンゲートブリッジ
 カリフォルニア | サンフランシスコ | SNS企業
(2 rows)


postgres=# EXPLAIN SELECT * FROM america WHERE state = 'カリフォルニア' AND city = 'サンフランシスコ';
                                        QUERY PLAN
------------------------------------------------------------------------------------------
 Append  (cost=0.00..19.75 rows=1 width=96)
   ->  Seq Scan on "California.SanFrancisco"  (cost=0.00..19.75 rows=1 width=96)
         Filter: ((state = 'カリフォルニア'::text) AND (city = 'サンフランシスコ'::text))
(3 rows)

Californiaパーティションに属する California.SanFrancisco パーティションのみを

プルーニングしている。