【PostgreSQL 11 新機能】Partitionwise Join を検証してみた

◆ Live配信スケジュール ◆
サイオステクノロジーでは、Microsoft MVPの武井による「わかりみの深いシリーズ」など、定期的なLive配信を行っています。
⇒ 詳細スケジュールはこちらから
⇒ 見逃してしまった方はYoutubeチャンネルをご覧ください

【5/21開催】Azure OpenAI ServiceによるRAG実装ガイドを公開しました
生成AIを活用したユースケースで最も一番熱いと言われているRAGの実装ガイドを公開しました。そのガイドの紹介をおこなうイベントです!!
https://tech-lab.connpass.com/event/315703/

こんにちは。サイオステクノロジー OSS サポート担当 Y です。

今回は先日リリースされた PostgreSQL 11 の新機能である “Partitionwise Join (パーティション単位での結合)” を検証してみました。(※以下の内容は CentOS 7.5/PostgreSQL 11.0 にて検証しています。)

1 ■はじめに
2 ■検証
3 ■最後に

■はじめに

昨年リリースされた 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 (パーティション単位での集計)” 機能も実装されているので、次回はそちらの機能を検証してみようと思います。