こんにちは。サイオステクノロジー OSS サポート担当 Y です。
今回は先日リリースされた PostgreSQL 11 の新機能である “Partitionwise Join (パーティション単位での結合)” を検証してみました。(※以下の内容は CentOS 7.5/PostgreSQL 11.0 にて検証しています。)
■はじめに
昨年リリースされた PostgreSQL 10 の新機能である Declarative Partitioning に関連する新機能として、PostgreSQL 11 では、パーティショニングされたテーブルの子テーブル同士 (パーティション同士) を結合する機能が実装されています。
今回は、PostgreSQL 11.0 を使ってこの Partitionwise Join を試してみようと思います。
■検証
それではさっそく、検証してみます。
まずは、PostgreSQL 11.0 にてパーティショニングされたテーブルを 2つ用意し、各テーブルにテスト用のデータを 300万レコードずつ INSERT します。
[テーブルその 1]
postgres=# CREATE TABLE hoge (a int primary key, b text) PARTITION BY RANGE (a);
CREATE TABLE
postgres=# CREATE TABLE hoge_1 PARTITION OF hoge FOR VALUES FROM (1) TO (1000001);
CREATE TABLE
postgres=# CREATE TABLE hoge_2 PARTITION OF hoge FOR VALUES FROM (1000001) TO (2000001);
CREATE TABLE
postgres=# CREATE TABLE hoge_3 PARTITION OF hoge FOR VALUES FROM (2000001) TO (3000001);
CREATE TABLE
postgres=#
postgres=# \d+ hoge
Table "public.hoge"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+----------+--------------+-------------
a | integer | | not null | | plain | |
b | text | | | | extended | |
Partition key: RANGE (a)
Indexes:
"hoge_pkey" PRIMARY KEY, btree (a)
Partitions: hoge_1 FOR VALUES FROM (1) TO (1000001),
hoge_2 FOR VALUES FROM (1000001) TO (2000001),
hoge_3 FOR VALUES FROM (2000001) TO (3000001)
[テーブルその 2]
postgres=# CREATE TABLE fuga (c int primary key, d text) PARTITION BY RANGE (c);
CREATE TABLE
postgres=# CREATE TABLE fuga_1 PARTITION OF fuga FOR VALUES FROM (1) TO (1000001);
CREATE TABLE
postgres=# CREATE TABLE fuga_2 PARTITION OF fuga FOR VALUES FROM (1000001) TO (2000001);
CREATE TABLE
postgres=#
postgres=# CREATE TABLE fuga_3 PARTITION OF fuga FOR VALUES FROM (2000001) TO (3000001);
CREATE TABLE
postgres=#
postgres=# \d+ fuga
Table "public.fuga"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+----------+--------------+-------------
c | integer | | not null | | plain | |
d | text | | | | extended | |
Partition key: RANGE (c)
Indexes:
"fuga_pkey" PRIMARY KEY, btree (c)
Partitions: fuga_1 FOR VALUES FROM (1) TO (1000001),
fuga_2 FOR VALUES FROM (1000001) TO (2000001),
fuga_3 FOR VALUES FROM (2000001) TO (3000001)
[テスト用のレコードを INSERT] postgres=# INSERT INTO hoge SELECT a, md5((current_timestamp)::text) FROM generate_series(1,3000000) as a; INSERT 0 3000000 postgres=# postgres=# ANALYZE hoge; ANALYZE postgres=# postgres=# SELECT count(*) FROM hoge; count --------- 3000000 (1 row) postgres=# postgres=# SELECT count(*) FROM hoge_1; count --------- 1000000 (1 row) postgres=# postgres=# SELECT count(*) FROM hoge_2; count --------- 1000000 (1 row) postgres=# postgres=# SELECT count(*) FROM hoge_3; count --------- 1000000 (1 row) postgres=# postgres=# INSERT INTO fuga SELECT a, md5((current_timestamp)::text) FROM generate_series(1,3000000) as a; INSERT 0 3000000 postgres=# postgres=# ANALYZE fuga; ANALYZE postgres=# postgres=# SELECT count(*) FROM fuga; count --------- 3000000 (1 row) postgres=# postgres=# SELECT count(*) FROM fuga_1; count --------- 1000000 (1 row) postgres=# postgres=# SELECT count(*) FROM fuga_2; count --------- 1000000 (1 row) postgres=# postgres=# SELECT count(*) FROM fuga_3; count --------- 1000000 (1 row)
今回検証する “Partitionwise Join” は、デフォルトで無効 (enable_partitionwise_join = off) になっているため、この機能を使う場合は設定を有効にする必要がありますが、まずはデフォルト (無効) の状態でクエリを実行してみます。
postgres=# SHOW enable_partitionwise_join;
enable_partitionwise_join
---------------------------
off
(1 row)
postgres=#
postgres=# EXPLAIN SELECT * FROM hoge LEFT OUTER JOIN fuga ON hoge.a = fuga.c;
QUERY PLAN
-----------------------------------------------------------------------------------
Hash Left Join (cost=130940.00..320006.00 rows=3000000 width=74)
Hash Cond: (hoge_1.a = fuga_1.c)
-> Append (cost=0.00..70002.00 rows=3000000 width=37)
-> Seq Scan on hoge_1 (cost=0.00..18334.00 rows=1000000 width=37)
-> Seq Scan on hoge_2 (cost=0.00..18334.00 rows=1000000 width=37)
-> Seq Scan on hoge_3 (cost=0.00..18334.00 rows=1000000 width=37)
-> Hash (cost=70002.00..70002.00 rows=3000000 width=37)
-> Append (cost=0.00..70002.00 rows=3000000 width=37)
-> Seq Scan on fuga_1 (cost=0.00..18334.00 rows=1000000 width=37)
-> Seq Scan on fuga_2 (cost=0.00..18334.00 rows=1000000 width=37)
-> Seq Scan on fuga_3 (cost=0.00..18334.00 rows=1000000 width=37)
(11 rows)
上記の様に、テーブル hoge の子テーブル (パーティション) を Seq Scan した結果と、テーブル fuga の子テーブル (パーティション) を Seq Scan した結果を別々に取得し、その結果同士を結合するような実行計画になっています。
また、EXPLAIN ANALYZE 文にて実際にクエリを実行すると以下の様な感じになりました。
postgres=# EXPLAIN ANALYZE SELECT * FROM hoge LEFT OUTER JOIN fuga ON hoge.a = fuga.c;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Hash Left Join (cost=130940.00..320006.00 rows=3000000 width=74) (actual time=1709.720..5013.099 rows=3000000 loops=1)
Hash Cond: (hoge_1.a = fuga_1.c)
-> Append (cost=0.00..70002.00 rows=3000000 width=37) (actual time=0.050..832.624 rows=3000000 loops=1)
-> Seq Scan on hoge_1 (cost=0.00..18334.00 rows=1000000 width=37) (actual time=0.050..185.556 rows=1000000 loops=1)
-> Seq Scan on hoge_2 (cost=0.00..18334.00 rows=1000000 width=37) (actual time=0.049..186.219 rows=1000000 loops=1)
-> Seq Scan on hoge_3 (cost=0.00..18334.00 rows=1000000 width=37) (actual time=0.024..181.314 rows=1000000 loops=1)
-> Hash (cost=70002.00..70002.00 rows=3000000 width=37) (actual time=1707.118..1707.118 rows=3000000 loops=1)
Buckets: 65536 Batches: 64 Memory Usage: 3665kB
-> Append (cost=0.00..70002.00 rows=3000000 width=37) (actual time=0.022..789.654 rows=3000000 loops=1)
-> Seq Scan on fuga_1 (cost=0.00..18334.00 rows=1000000 width=37) (actual time=0.021..188.579 rows=1000000 loops=1)
-> Seq Scan on fuga_2 (cost=0.00..18334.00 rows=1000000 width=37) (actual time=0.044..167.124 rows=1000000 loops=1)
-> Seq Scan on fuga_3 (cost=0.00..18334.00 rows=1000000 width=37) (actual time=0.013..139.597 rows=1000000 loops=1)
Planning Time: 0.438 ms
Execution Time: 5135.598 ms
(14 rows)
それでは次に、Partitionwise Join を有効 (enable_partitionwise_join = on) にして同じクエリを実行してみます。
postgres=# SET enable_partitionwise_join TO on;
SET
postgres=#
postgres=# SHOW enable_partitionwise_join;
enable_partitionwise_join
---------------------------
on
(1 row)
postgres=#
postgres=# EXPLAIN SELECT * FROM hoge LEFT OUTER JOIN fuga ON hoge.a = fuga.c;
QUERY PLAN
-------------------------------------------------------------------------------------------------
Append (cost=38647.00..276435.13 rows=3000000 width=74)
-> Hash Left Join (cost=38647.00..94170.00 rows=1000000 width=74)
Hash Cond: (hoge_1.a = fuga_1.c)
-> Seq Scan on hoge_1 (cost=0.00..18334.00 rows=1000000 width=37)
-> Hash (cost=18334.00..18334.00 rows=1000000 width=37)
-> Seq Scan on fuga_1 (cost=0.00..18334.00 rows=1000000 width=37)
-> Merge Left Join (cost=1.92..83632.68 rows=1000000 width=74)
Merge Cond: (hoge_2.a = fuga_2.c)
-> Index Scan using hoge_2_pkey on hoge_2 (cost=0.42..34317.43 rows=1000000 width=37)
-> Index Scan using fuga_2_pkey on fuga_2 (cost=0.42..34317.43 rows=1000000 width=37)
-> Merge Left Join (cost=5.49..83632.46 rows=1000000 width=74)
Merge Cond: (hoge_3.a = fuga_3.c)
-> Index Scan using hoge_3_pkey on hoge_3 (cost=0.42..34317.43 rows=1000000 width=37)
-> Index Scan using fuga_3_pkey on fuga_3 (cost=0.42..34317.43 rows=1000000 width=37)
(14 rows)
すると、上記の様に hoge と fuga の子テーブル (パーティション) 同士を先に結合 (“hoge_1.a = fuga_1.c”, “hoge_2.a = fuga_2.c”, “hoge_3.a = fuga_3.c”) した上で、それらの結果をまとめるような実行計画になっています。
また、EXPLAIN ANALYZE 文にて実際にクエリを実行すると以下の様な感じになりました。
postgres=# EXPLAIN ANALYZE SELECT * FROM hoge LEFT OUTER JOIN fuga ON hoge.a = fuga.c;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------
Append (cost=38647.00..276435.13 rows=3000000 width=74) (actual time=469.090..3581.987 rows=3000000 loops=1)
-> Hash Left Join (cost=38647.00..94170.00 rows=1000000 width=74) (actual time=469.089..1464.573 rows=1000000 loops=1)
Hash Cond: (hoge_1.a = fuga_1.c)
-> Seq Scan on hoge_1 (cost=0.00..18334.00 rows=1000000 width=37) (actual time=0.023..165.813 rows=1000000 loops=1)
-> Hash (cost=18334.00..18334.00 rows=1000000 width=37) (actual time=468.513..468.514 rows=1000000 loops=1)
Buckets: 65536 Batches: 32 Memory Usage: 2625kB
-> Seq Scan on fuga_1 (cost=0.00..18334.00 rows=1000000 width=37) (actual time=0.017..170.618 rows=1000000 loops=1)
-> Merge Left Join (cost=1.92..83632.68 rows=1000000 width=74) (actual time=0.074..916.511 rows=1000000 loops=1)
Merge Cond: (hoge_2.a = fuga_2.c)
-> Index Scan using hoge_2_pkey on hoge_2 (cost=0.42..34317.43 rows=1000000 width=37) (actual time=0.050..239.283 rows=1000000 loops=1)
-> Index Scan using fuga_2_pkey on fuga_2 (cost=0.42..34317.43 rows=1000000 width=37) (actual time=0.014..265.910 rows=1000000 loops=1)
-> Merge Left Join (cost=5.49..83632.46 rows=1000000 width=74) (actual time=0.090..941.230 rows=1000000 loops=1)
Merge Cond: (hoge_3.a = fuga_3.c)
-> Index Scan using hoge_3_pkey on hoge_3 (cost=0.42..34317.43 rows=1000000 width=37) (actual time=0.044..252.964 rows=1000000 loops=1)
-> Index Scan using fuga_3_pkey on fuga_3 (cost=0.42..34317.43 rows=1000000 width=37) (actual time=0.040..267.821 rows=1000000 loops=1)
Planning Time: 1.050 ms
Execution Time: 3709.040 ms
(17 rows)
今回検証を行なった環境/クエリでは、Partitionwise Join が無効になっている場合に比べて、クエリの実行時間が短くなっている (性能が改善している) ようです。
なお、enable_partitionwise_join のドキュメントを参照すると、この Partitionwise Join を実行するための実行計画の作成は CPU 及び Memory リソースを多く消費するため、デフォルトでは無効になっている旨の記載があります。
■最後に
PostgreSQL 11 では、今回検証した Partitionwise Join のような機能強化が複数実施されており、クエリ等の性能改善が期待できそうです。
しかし、(今回の検証では性能改善が見受けられましたが) 前述したドキュメントにも記載されている通りサーバリソース (CPU/Memory) を多く消費する可能性もあるようなので、新規機能を有効にするか否か、新規機能の恩恵を受けられるか否か (性能が改善するか否か) については、実際の環境毎に検証を実施した方が良さそうです。
PostgreSQL 11 では、今回検証した Partitionwise Join に似た機能として “Partitionwise Aggregate (パーティション単位での集計)” 機能も実装されているので、次回はそちらの機能を検証してみようと思います。
