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

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

【4/18開催】VSCode Dev Containersで楽々開発環境構築祭り〜Python/Reactなどなど〜
Visual Studio Codeの拡張機能であるDev Containersを使ってReactとかPythonとかSpring Bootとかの開発環境をラクチンで構築する方法を紹介するイベントです。
https://tech-lab.connpass.com/event/311864/

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

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

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

■はじめに

昨年リリースされた PostgreSQL 10 の新機能である Declarative Partitioning に関連する新機能として、PostgreSQL 11 では、パーティショニングされたテーブルにて集計処理を実施する際に、子テーブル (パーティション) 単位で集計処理を実施する機能が実装されています。

今回は、PostgreSQL 11.0 を使ってこの Partitionwise Aggregate を試してみようと思います。

■検証

それではさっそく、検証してみます。

まずは、PostgreSQL 11.0 にてパーティショニングされたテーブルを用意し、テスト用のデータを 300万レコードほど INSERT します。

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)

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

今回検証する “Partitionwise Aggregate ” は、デフォルトで無効 (enable_partitionwise_aggregate = off) になっているため、この機能を使う場合は設定を有効にする必要がありますが、まずはデフォルト (無効) の状態でクエリを実行してみます。

postgres=# SHOW enable_partitionwise_aggregate;
 enable_partitionwise_aggregate 
--------------------------------
 off
(1 row)

postgres=# 
postgres=# EXPLAIN SELECT count(*) FROM hoge;
                                           QUERY PLAN                                           
------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=47877.22..47877.23 rows=1 width=8)
   ->  Gather  (cost=47877.01..47877.22 rows=2 width=8)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=46877.01..46877.02 rows=1 width=8)
               ->  Parallel Append  (cost=0.00..43752.00 rows=1250001 width=0)
                     ->  Parallel Seq Scan on hoge_1  (cost=0.00..12500.67 rows=416667 width=0)
                     ->  Parallel Seq Scan on hoge_2  (cost=0.00..12500.67 rows=416667 width=0)
                     ->  Parallel Seq Scan on hoge_3  (cost=0.00..12500.67 rows=416667 width=0)
(8 rows)

上記の様に、テーブル hoge からレコードを取得 (各パーティションを Parallel Seq Scan してテーブル全体のレコードを取得) した後に、その結果に対して集約処理を実行するような実行計画になっています。

また、EXPLAIN ANALYZE 文にて実際にクエリを実行すると以下の様な感じになりました。

postgres=# EXPLAIN ANALYZE SELECT count(*) FROM hoge;
                                                                    QUERY PLAN                                                                    
--------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=47877.22..47877.23 rows=1 width=8) (actual time=483.260..483.261 rows=1 loops=1)
   ->  Gather  (cost=47877.01..47877.22 rows=2 width=8) (actual time=481.876..483.366 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=46877.01..46877.02 rows=1 width=8) (actual time=472.819..472.819 rows=1 loops=3)
               ->  Parallel Append  (cost=0.00..43752.00 rows=1250001 width=0) (actual time=0.050..381.142 rows=1000000 loops=3)
                     ->  Parallel Seq Scan on hoge_1  (cost=0.00..12500.67 rows=416667 width=0) (actual time=0.039..170.552 rows=1000000 loops=1)
                     ->  Parallel Seq Scan on hoge_2  (cost=0.00..12500.67 rows=416667 width=0) (actual time=0.051..120.476 rows=333333 loops=3)
                     ->  Parallel Seq Scan on hoge_3  (cost=0.00..12500.67 rows=416667 width=0) (actual time=0.041..97.435 rows=500000 loops=2)
 Planning Time: 0.225 ms
 Execution Time: 483.452 ms
(11 rows)

それでは次に、Partitionwise Aggregate を有効 (enable_partitionwise_aggregate = on) にして同じクエリを実行してみます。

postgres=# SET enable_partitionwise_aggregate TO on;
SET
postgres=# 
postgres=# SHOW enable_partitionwise_aggregate;
 enable_partitionwise_aggregate 
--------------------------------
 on
(1 row)

postgres=# 
postgres=# EXPLAIN SELECT count(*) FROM hoge;
                                           QUERY PLAN                                           
------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=41627.66..41627.67 rows=1 width=8)
   ->  Gather  (cost=14542.33..41627.65 rows=6 width=8)
         Workers Planned: 2
         ->  Parallel Append  (cost=13542.33..40627.05 rows=3 width=8)
               ->  Partial Aggregate  (cost=13542.33..13542.34 rows=1 width=8)
                     ->  Parallel Seq Scan on hoge_1  (cost=0.00..12500.67 rows=416667 width=0)
               ->  Partial Aggregate  (cost=13542.33..13542.34 rows=1 width=8)
                     ->  Parallel Seq Scan on hoge_2  (cost=0.00..12500.67 rows=416667 width=0)
               ->  Partial Aggregate  (cost=13542.33..13542.34 rows=1 width=8)
                     ->  Parallel Seq Scan on hoge_3  (cost=0.00..12500.67 rows=416667 width=0)
(10 rows)

すると、上記の様に各子テーブル (パーティション) 毎に集計処理を実施した上で、それらの結果をまとめるような実行計画になっています。

また、EXPLAIN ANALYZE 文にて実際にクエリを実行すると以下の様な感じになりました。

postgres=# EXPLAIN ANALYZE SELECT count(*) FROM hoge;
                                                                    QUERY PLAN                                                                    
--------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=41627.66..41627.67 rows=1 width=8) (actual time=355.037..355.037 rows=1 loops=1)
   ->  Gather  (cost=14542.33..41627.65 rows=6 width=8) (actual time=278.873..355.122 rows=6 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Parallel Append  (cost=13542.33..40627.05 rows=3 width=8) (actual time=302.467..349.382 rows=2 loops=3)
               ->  Partial Aggregate  (cost=13542.33..13542.34 rows=1 width=8) (actual time=158.543..158.543 rows=1 loops=3)
                     ->  Parallel Seq Scan on hoge_1  (cost=0.00..12500.67 rows=416667 width=0) (actual time=0.064..116.096 rows=333333 loops=3)
               ->  Partial Aggregate  (cost=13542.33..13542.34 rows=1 width=8) (actual time=146.978..146.978 rows=1 loops=2)
                     ->  Parallel Seq Scan on hoge_2  (cost=0.00..12500.67 rows=416667 width=0) (actual time=0.038..98.607 rows=500000 loops=2)
               ->  Partial Aggregate  (cost=13542.33..13542.34 rows=1 width=8) (actual time=278.521..278.521 rows=1 loops=1)
                     ->  Parallel Seq Scan on hoge_3  (cost=0.00..12500.67 rows=416667 width=0) (actual time=0.049..171.056 rows=1000000 loops=1)
 Planning Time: 0.210 ms
 Execution Time: 355.224 ms
(13 rows)

今回検証を行なった環境/クエリでは、Partitionwise Aggregate が無効になっている場合に比べて、クエリの実行時間が短くなっている (性能が改善している) ようです。

なお、enable_partitionwise_aggregate のドキュメントを参照すると、この Partitionwise Aggregate を実行するための実行計画の作成は CPU 及び Memory リソースを多く消費するため、デフォルトでは無効になっている旨の記載があります。

■最後に

PostgreSQL 11 では、前回検証した Partitionwise Join や、今回検証した Partitionwise Aggregate のような機能強化が複数実施されており、クエリ等の性能改善が期待できそうです。

しかし、(今回の検証では性能改善が見受けられましたが) 前述したドキュメントにも記載されている通りサーバリソース (CPU/Memory) を多く消費する可能性もあるようなので、新規機能を有効にするか否か、新規機能の恩恵を受けられるか否か (性能が改善するか否か) については、実際の環境毎に検証を実施した方が良さそうです。