PostgreSQLのSQLの特徴

第3章では、PostgreSQLの開発に便利な、バージョン10、11の新機能を紹介します。

そこでまずは、RDBMSの開発には欠かせないSQLの話です。本節ではSQLを通してPostgreSQLの特徴をつかみ、開発効率を上げていきましょう。

そのあとの節では、Window関数、ストアドファンクション／プロシージャ、JITコンパイラ、パラレルクエリという開発で役立つ新機能の解説を行います。各節の紹介を通して、PostgreSQLの魅力を堪能してください。

PostgreSQLは標準SQLを重視する

PostgreSQLは、標準SQLを重視しています。SQLには標準規格があり、SQLの構文や振る舞いが明記されています。PostgreSQLの開発では、標準SQLの最新の公式バージョンに準拠することを重視しており、標準的な機能や考え方に矛盾しないように気が配られています。そのため標準SQLで必須とされた機能の多くは、多少構文や機能に違いがあってもサポートされています。

結果として、Oracle DBなどのほかのRDBMSと互換性のあるSQLが多く、ほかのRDBMSを利用したことがある人であれば、PostgreSQLを覚えるのは苦ではありません。

標準SQLに準拠している機能

標準SQLに準拠している機能の一覧は、公式ドキュメントに明記されています。基本的なSQLの構文はもちろん、型の定義からトランザクション分離レベルまでサポートしています。

標準SQLに準拠していない機能

残念ながら、PostgreSQLをまだ標準SQLのすべての機能をサポートしているわけではありません。サポートされていない機能の一覧も公式ドキュメントに明記されています。ここに明記されているように、MERGE文は後述のUPSERTのみ、サポートしています。遅延可能制約は一部の制約が利用可能で、その対象は外部キー制約と一意キー制約です。

PostgreSQLは今後も標準SQLに準拠していくための機能追加を進めていく予定です。まだサポートされていない標準SQLの一覧を眺めると、少し未来のPostgreSQLの姿が想像できます。

PostgreSQL独自のSQLと機能

PostgreSQLは標準SQLを重視していますが、独自の構文などで機能を提供することもあります。独自の構文を利用すると互換性を失いますが、それを補って余りある効果が期待されるときは採用しています。以降では、そんなPostgreSQLの独自機能を紹介します。

UPSERT─⁠─追加と更新を同時に行う

データの挿入時に該当のキーにデータがなければINSERT、あればUPDATEを行うことをUPSERTと呼びます。

これは、標準SQLではMERGE文として定義されており、Oracle DBではMERGE文として提供されています。MySQLではINSERT ... ON DUPLICATE KEY UPDATEの構文でサポートしています。PostgreSQLは、これに似たINSERT ... ON CONFLICT DO UPDATEの構文をサポートしました。

PostgreSQLのUPSERTは、バージョン9.5からサポートされた機能です。本来のMERGE文はDELETEの機能も必要なのですが、実装の難易度が高く、実現しませんでした。議論の結果、PostgreSQLは標準SQLとしてのMERGE文にこだわって実装されないことより、要望の多かったUPSERTのみを切り出して実装することにしました。

待望の機能としてリリースされたUPSERTは、便利で多くのシーンで活躍しています。このように、標準SQLだけでなく、必要とされている機能をどのように提供するかということに真摯に向き合っているのがPostgreSQLの開発です。

独自の関数

RDBMSで独自に提供されている機能の筆頭と言えば関数で、PostgreSQLも例外ではありません。その中でも特徴的な機能は、JSON関数と演算子です。PostgreSQLにはJSONを保存するためのJSON型とJSONB型があり、それを扱うための関数です。昨今、データフォーマットとしてJSONを利用することは一般的になっており、PostgreSQLはそのニーズにいち早く応えています。

Window関数─⁠─集計の味方

標準SQLにはWindow関数が定義されており、PostgreSQLもサポートしています。Window関数はPostgreSQL 8.4で導入され、定期的に機能追加されてきました。PostgreSQL 11でもWindow関数に新機能が提供されており、より便利になっています。

Window関数の役割

まず、Window関数の役割を見てみましょう。Window関数は、ランキングなどを取り出すときによく使われます。MySQLにも最近MySQL 8で追加されました。

ここでは、実際に売上表から売上ランキングを検索してみます。

図1のSQLを使って売上表を作成します。図1では、第1章で紹介したメタコマンドの\xを利用して、縦表示と横表示を切り替えています。

テーブルの用意
demo=# CREATE TABLE public."売上" (id serial NOT NULL,
"売上日時" timestamp NOT NULL DEFAULT now(),
"商品名" character varying(64) NOT NULL,
"価格" numeric NOT NULL DEFAULT 0,
"ジャンル" text NOT NULL);

バルクINSERTでテストデータを作る
demo=# INSERT INTO "売上" ("売上日時","商品名","価格
","ジャンル") VALUES
(now()-((random()*10000)::int%365)*interval '1 day',
'WEB+DB PRESS Vol.107',1480,'紙'),
(now()-((random()*10000)::int%365)*interval '1 day',
'WEB+DB PRESS Vol.106',1480,'電子'),
(now()-((random()*10000)::int%365)*interval '1 day',
'WEB+DB PRESS Vol.105',1480,'紙'),
(now()-((random()*10000)::int%365)*interval '1 day',
'WEB+DB PRESS Vol.104',1480,'電子');

demo=# \x
Expanded display is on.

データの中身の確認
demo=# SELECT * FROM "売上" WHERE
"商品名" = 'WEB+DB PRESS Vol.105' LIMIT 1;

-[ RECORD 1 ]-------------------------------
id       | 22251
売上日時 | 2018-09-08 07:42:00.067831
商品名   | WEB+DB PRESS Vol.105
価格     | 1480
ジャンル | 紙

この売上表の8月から10月の各月における、日別の売上件数の上位3件を取得します。

Window関数を利用しない場合

まずは、Window関数を使わずに集計します。Window関数を利用しない場合、図2のようになります。月ごとのSQLは対象月が増えるほどUNIONが増えます。そして、UNIONの数だけSQLは実行されます。対象月が毎月増えていく場合、UNIONも毎月増やす必要があり、SQLのメンテナンスに苦労することが想像できます。

8～10月の各月の日別の売上件数の上位3件を表示

demo=# SELECT "売上日", "件数" FROM
 (SELECT "売上日時"::date AS "売上日",
   COUNT(*) AS "件数" FROM "売上"
   WHERE "売上日時" >= '2018/10/01 00:00:00'
     AND "売上日時" <= '2018/10/31 23:59:59'
   GROUP BY "売上日"
ORDER BY "件数" DESC,売上日 LIMIT 3) AS oct
UNION ALL SELECT * FROM
 (SELECT "売上日時"::date AS "売上日",
   COUNT(*) AS "件数" FROM "売上"
   WHERE "売上日時" >= '2018/09/01 00:00:00'
     AND "売上日時" <= '2018/09/30 23:59:59'
   GROUP BY "売上日"
ORDER BY "件数" DESC,売上日 LIMIT 3) AS Sep
UNION ALL SELECT * FROM
 (SELECT "売上日時"::date AS "売上日",
   COUNT(*) AS "件数" FROM "売上"
   WHERE "売上日時" >= '2018/08/01 00:00:00'
     AND "売上日時" <= '2018/08/31 23:59:59'
   GROUP BY "売上日"
ORDER BY "件数" DESC,売上日 LIMIT 3) AS Aug;

売上日      | 件数
------------+------
 2018-10-13 |  101
 2018-10-01 |   99
 2018-10-05 |   97
 2018-09-04 |  104
 2018-09-10 |  100
 2018-09-26 |   99
 2018-08-25 |  108
 2018-08-19 |  102
 2018-08-24 |  100
(9 rows)

Window関数を利用した場合

Window関数を利用すると、この複雑な処理をまとめたSQLにできます。クエリ回数が減るため、性能も高速です。

Window関数を利用した例が図3です。Window関数は、使いたい関数名のあとにOVER()で対象を指定します。PARTITION BY句は、対象に指定されたカラムの値を使って集合を作ります。この集合のことをウィンドウと呼びます。図3の例では、売上月ごとにPARTITION BYで指定したウィンドウの結果をソートしており、さらにWindow関数を使って順位列を作成しています。

8～10月の各月の日別の売上件数の上位3件を表示

demo=# SELECT "売上日", "件数", "順位"
FROM (
  SELECT *, dense_rank() OVER (PARTITION BY "売上月"
   ORDER BY "件数" DESC, 売上日) AS "順位"
    FROM (
      SELECT
        extract(month from "売上日時") AS "売上月",
        "売上日時"::date AS "売上日",
        COUNT(*) AS "件数"
      FROM "売上"
    WHERE "売上日時" >= '2018/08/01 00:00:00'
      AND "売上日時" <= '2018/10/31 23:59:59'
    GROUP BY "売上日", "売上月"
  ) AS "集計"
) AS "集計ランキング"
WHERE "順位" <= 3
ORDER BY "売上月" DESC, "順位";

   売上日   | 件数 | 順位
------------+------+------
 2018-10-13 |  101 |    1
 2018-10-01 |   99 |    2
 2018-10-05 |   97 |    3
 2018-09-04 |  104 |    1
 2018-09-10 |  100 |    2
 2018-09-26 |   99 |    3
 2018-08-25 |  108 |    1
 2018-08-19 |  102 |    2
 2018-08-24 |  100 |    3
(9 rows)

これが、Window関数の便利さです。対象の月が変動しても、売上日時のWHERE句を指定するだけで対応できます。また、前述したようにDBの負荷を減らしながら集計できます。

主なWindow関数を表1にまとめました。使いこなしてデータ分析や集計を効率化していきましょう。

ウィンドウフレーム─⁠─Window関数の対象を決める

Window関数を使うときにPARTITION BYなどで作ったウィンドウに対し、どこまで集計対象にするかを決めることができます。これをウィンドウフレームと言います。

ウィンドウフレームは、フレームに指定する対象をモードで指定します。モードの種類には、行を指定するROWS BETWEEN、指定されたカラムの値の範囲を指定するRANGE BETWEEN、そしてPostageSQL 11から追加されたGROUPS BETWEENがあります。GROUPS BETWEENは、同一の行をひとまとまりにしたグループを指定します。

ウィンドウフレームのモードを指定したら、次はフレームに指定した対象の絞り込みの範囲を決めます。絞り込みの範囲はPRECEDINGとFOLLOWINGで指定します。PRECEDINGは指定した値より上、FOLLOWINGは指定した値より下を集計の対象にします。

実際の利用例は図4とおりです。図4はWITH句を使って、連番のid列とvalue列を持った擬似テーブルtを作っています。tに対してウインドウフレームの各モードを指定し、同じ絞り込みの範囲を指定した場合の結果を集計しています。

demo=# WITH t(id, value) AS (VALUES (1, 1), (2, 1), (3, 3), (4, 5), (5, 5), (6, 5), (7, 6))
SELECT id, value,
  array_agg(id) OVER ROWS as row_id, array_agg(value) OVER ROWS as row_value,
  array_agg(id) OVER RANGE as renge_id, array_agg(value) OVER RANGE as renge_value,
  array_agg(id) OVER GROUPS as groups_id, array_agg(value) OVER GROUPS as groups_value
FROM t WINDOW obj AS (ORDER BY value),
  ROWS AS (obj ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING),
  RANGE AS (obj RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING),
  GROUPS AS (obj GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING);

 id | value | row_id  | row_value | renge_id  | renge_value |   groups_id   | groups_value
----+-------+---------+-----------+-----------+-------------+---------------+---------------
  1 |     1 | {1,2}   | {1,1}     | {1,2}     | {1,1}       | {1,2,3}       | {1,1,3}
  2 |     1 | {1,2,3} | {1,1,3}   | {1,2}     | {1,1}       | {1,2,3}       | {1,1,3}
  3 |     3 | {2,3,4} | {1,3,5}   | {3}       | {3}         | {1,2,3,4,5,6} | {1,1,3,5,5,5}
  4 |     5 | {3,4,5} | {3,5,5}   | {4,5,6,7} | {5,5,5,6}   | {3,4,5,6,7}   | {3,5,5,5,6}
  5 |     5 | {4,5,6} | {5,5,5}   | {4,5,6,7} | {5,5,5,6}   | {3,4,5,6,7}   | {3,5,5,5,6}
  6 |     5 | {5,6,7} | {5,5,6}   | {4,5,6,7} | {5,5,5,6}   | {3,4,5,6,7}   | {3,5,5,5,6}
  7 |     6 | {6,7}   | {5,6}     | {4,5,6,7} | {5,5,5,6}   | {4,5,6,7}     | {5,5,5,6}
(7 rows)

ROWSが指定されているROWS AS (obj ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)は、「⁠自分のレコードよりも1つ前 AND 自分のレコードより1後ろ」と指定したことになります。

ROWSが上下の行を見ていたのに対し、RANGEが指定されているRANGE AS (obj RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING)は、「⁠自分の値よりも1つ少ない AND 自分の値よりも1つ多い」と指定したことになります。

GROUPSが指定されているGROUPS AS (obj GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING)は、「⁠同じ値のグループよりも1つ前のグループ AND 同じ値のグループよりも1つ後ろのグループ」と指定したことになります。

EXCLUDE句による除外指定

PostageSQL 11からはさらに、ウィンドウフレームに対してEXCLUDE句で除外指定できるようになりました。次の4種類が指定できます。

EXCLUDE CURRENT ROW

自分の行を除外する

EXCLUDE GROUP

自分と同じ値の行を除外する

EXCLUDE TIES

重複した値の行を除外する

EXCLUDE NO OTHERS

ウィンドウフレーム以外の値を省略する

EXCLUDE NO OTHERSを指定した場合は、EXCLUDE句を省略したときと同様の動きをし、EXCLUDE句を指定できなかったPostgreSQL 10までの挙動をします。

実際の利用例は図5とおりです。

demo=# WITH t(value) AS (VALUES (1), (1), (3), (5), (5), (5), (6))
SELECT
 value, array_agg(value)
    OVER (obj ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  EXCLUDE CURRENT ROW) AS current_row, array_agg(value)
    OVER (obj ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  EXCLUDE GROUP) AS group, array_agg(value)
    OVER (obj ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  EXCLUDE TIES) AS ties, array_agg(value)
    OVER (obj ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  EXCLUDE NO OTHERS) AS no_others
FROM t WINDOW obj AS (ORDER BY value);

 value | current_row | group |  ties   | no_others
-------+-------------+-------+---------+-----------
     1 | {1}         |       | {1}     | {1,1}
     1 | {1,3}       | {3}   | {1,3}   | {1,1,3}
     3 | {1,5}       | {1,5} | {1,3,5} | {1,3,5}
     5 | {3,5}       | {3}   | {3,5}   | {3,5,5}
     5 | {5,5}       |       | {5}     | {5,5,5}
     5 | {5,6}       | {6}   | {5,6}   | {5,5,6}
     6 | {5}         | {5}   | {5,6}   | {5,6}
(7 rows)

ストアドファンクション／プロシージャ

PostgreSQLは従来から、ストアドファンクションを定義できます。PostgreSQL 11からは、ストアドプロシージャも定義できるようになりました。両方の使い方を見てみましょう。

ストアドファンクション─⁠─SQLから呼べる独自関数

ストアドファンクションとはユーザー定義関数の一つで、PL/pgSQLをはじめとした手続型言語で記述します。ストアドファンクションを使うと、データベース内での複数の処理をまとめることができます。たとえば一度の呼び出しで複数のSQLを実行できると、性能面でメリットがあります。トリガと組み合わせると、複雑なデータ処理の参照整合性を担保したまま処理できます。これにより、アプリケーション側のロジックを維持したままデータベースのリファクタリングを行えます。

デメリットとしては、アプリケーションからは見えないところで処理が実行されるためストアドファンクションの存在が暗黙知になりがちなことと、テストやデプロイに課題が多いことが挙げられます。

PL/pgSQLで書く

ストアドファンクションを定義する場合、主にPL/pgSQLを使います。ここでは慣れ親しんだFizzBuzz[1]を書いてみましょう。

demo=# CREATE OR REPLACE FUNCTION fizzbuzz()
demo-# RETURNS setof text as $$
demo$# BEGIN
demo$#   FOR i IN 1 .. 100 LOOP
demo$#     IF MOD(i, 15) = 0 THEN
demo$#       return next 'FizzBuzz';
demo$#     ELSIF MOD(i, 5) = 0 THEN
demo$#       return next 'Buzz';
demo$#     ELSIF MOD(i, 3) = 0 THEN
demo$#       return next 'Fizz';
demo$#     ELSE
demo$#       return next i::text;
demo$#     END IF;
demo$#   END LOOP;
demo$#   RETURN;
demo$# END;
demo$# $$ LANGUAGE plpgsql;
CREATE FUNCTION

demo=# SELECT array_agg(val) AS fizzbuzz FROM LATERAL
   (SELECT fizzbuzz() AS val) AS list;
 fizzbuzz
------------------------------------------------------
 {1,2,Fizz,4,Buzz,Fizz,7,8,Fizz,Buzz,11,Fizz,13,14,Fizz
Buzz,16,17,Fizz,19,Buzz,Fizz,22,23,Fizz,Buzz ...
（省略）

一般的な関数と同じように、データベースの関数を定義できます。もちろん引数でSQLの検索結果を渡せます。また、ストアドファンクションの中でSQLを実行して結果を取得できます。

注意点は、ストアドファンクションは関数ですので戻り値が必要です。

ほかの言語で書く

PostgreSQLのストアドファンクションは多くの言語で定義できます。PL/pgSQL以外に標準でサポートしているものは、PL/PerlとPL/Pythonです。拡張を追加すれば、さらにPL/Java、PL/v8、PL/Rなどが使えます。

今回はPL/PythonでFizzBuzzを書いてみます。

demo=# CREATE EXTENSION plpythonu;

demo=# CREATE OR REPLACE FUNCTION
demo-# fizzbuzz_py(input integer)
demo-# ;RETURNS TEXT
demo$# AS $$
demo$# if input % 15 == 0:
demo$#     return "fizz buzz"
demo$# elif input % 3 == 0:
demo$#     return "fizz"
demo$# elif input % 5 == 0:
demo$#     return "buzz"
demo$# else:
demo$#     return str(input)
demo$# $$ LANGUAGE plpythonu;
CREATE FUNCTION

demo=# SELECT array_agg(val) AS fizzbuzz FROM LATERAL
   (SELECT fizzbuzz_py(GENERATE_SERIES(1, 13)) AS val)
AS list;
 fizzbuzz_py
-------------
 {1,2,Fizz,4,Buzz,Fizz,7,8,Fizz,Buzz,11,Fizz,13}
(13 rows)

今回は引数で値を受け取るようにしました。ロジックに大きな差はないのですが、慣れ親しんだ言語だと読みやすさが大きく違うのではないでしょうか。PL/PythonやPL/v8は、PL/pgSQLよりも性能面で有利という特徴もあります。

注意点として、PL/PythonはAmazon RDSでは非対応です。逆にPL/v8はAmazon RDSでは拡張を追加しなくても利用できます。

ストアドプロシージャ─⁠─SQLの処理をまとめる

PostgreSQL 11からは、ストアドプロシージャも定義できるようになりました。ストアドプロシージャにより、戻り値が不要な場合でも複数の処理をまとめることができます。

呼び出し方法は、SELECT文ではなく、CALLストアドプロシージャ名のようにCALL文で呼び出します。たとえば引数を受け取り、その値をもとにINSERTする処理は、次のようになります。

demo=# CREATE TABLE tbl_a (id int);
CREATE TABLE
demo=# CREATE PROCEDURE
demo-# insert_data(a integer, b integer)
demo-# LANGUAGE SQL
demo-# AS $$
demo$# INSERT INTO tbl_a VALUES (a);
demo$# INSERT INTO tbl_a VALUES (b);
demo$# $$;
CREATE PROCEDURE
demo=# CALL insert_data(1, 2);
CALL
demo=# select * from tbl_a;
 id
----
  1
  2
(2 rows)

Oracle DBからの移行やバッチ処理などで、ストアドプロシージャを求める声は多くありました。最新版のPostgreSQL 11から利用できるようになり、選択肢の幅がより一層広がりました。

JITコンパイラ─⁠─実行時に処理を先にコンパイルする

PostgreSQL 11では、なんとLLVM（Low Level Virtual Machine、低水準仮想マシン）による実行時コンパイラ、つまりJITコンパイラが追加されています。JITコンパイラが効果的なシーンは、計算量が多く、CPUがボトルネックになっており、長時間実行されるようなSQL文です。逆に小さな処理では、オーバーヘッドのほうが大きくなります。

JITコンパイラの使い方

JITコンパイラの利用の有無は、postgresql.confで設定します。デフォルトでは無効です。

先述したとおり、JITコンパイラは大きな処理でなければ非効率になります。そのため有効になっている場合も、実行計画で推定されたコストがjit_above_costの閾値（いきち）（デフォルト値は100000）を超えるSQLのみ、JITコンパイラが実行されます。この100000という閾値は簡単に超える値ではないため、基本的にはデフォルト値のままで問題ありません。

任意のタイミングで利用したい場合は、jit_above_costをチューニングしましょう。そのほかにもパラメータとして、

jit_optimize_above_cost

JITコンパイル時に最適化するかどうかを決めるコストの閾値を指定する。デフォルト値は500000

jit_inline_above_cost

JITコンパイル時にインライン化を行うかどうかを決めるコストの閾値を指定する。デフォルト値は500000

があります。基本的に変更することは少ないでしょうが、JITコンパイラのチューニングの際には必要になりますので、覚えておきましょう。

それでは、実際にJITコンパイラを使ってみましょう。先述したとおり、JITコンパイラはデフォルトで無効化されており、気軽に試せる閾値にもなっていません。そこで図6のように、JITコンパイラをonにするとともに、jit_above_costを10に下げて利用してみましょう。一時的な値の変更ですので、図6ではSET文で変更しています。

通常の実行計画

demo=# EXPLAIN ANALYZE SELECT SUM(relpages) FROM pg_class;
                                                 QUERY PLAN
-------------------------------------------------------------------------------------------------
 Aggregate (cost=16.27..16.29 rows=1 width=8) (actual time=0.236..0.236 rows=1 loops=1)
   -> Seq Scan on pg_class (cost=0.00..15.42 rows=342 width=4) (actual time=0.008..0.122 rows=342 loops=1)
 Planning Time: 3.317 ms
 Execution Time: 0.485 ms
(4 rows)

JITコンパイラが利用された場合の実行計画
demo=# SET jit TO on;
demo=# SET jit_above_cost TO 10;

demo=# EXPLAIN ANALYZE SELECT SUM(relpages) FROM pg_class;
                                                 QUERY PLAN
------------------------------------------------------------------------------------------------
 Aggregate (cost=16.27..16.29 rows=1 width=8) (actual time=9.850..9.850 rows=1 loops=1)
   -> Seq Scan on pg_class (cost=0.00..15.42 rows=342 width=4) (actual time=0.014..0.066 rows=342 loops=1)
 Planning Time: 0.132 ms
 JIT:
   Functions: 3
   Options: Inlining false, Optimization false, Expressions true, Deforming true
   Timing: Generation 0.894 ms, Inlining 0.000 ms, Optimization 0.652 ms, Emission 8.867 ms, Total 10.414 ms
 Execution Time: 114.956 ms
(8 rows)

JITコンパイラが使われた場合、コンパイルの時間なども実行計画に出力されます。JITコンパイラの実行計画を見るうえ主な項目は表2のとおりです。JITコンパイラをチューニングする場合は、これらの項目を見ながら調整しましょう。

単純にExecution Timeを見ると、JITコンパイラの利用の有無でどちらが有効であるかを比較できます。

JITコンパイラの使いどころ

図6のExecution Timeからわかるとおり、小さな処理ではJITコンパイラは逆に遅くなります。

では、実際にどの程度の処理からJITコンパイラの効果があるのでしょうか。「⁠SRA OSS Tech Blog」の「PostgreSQL 11検証報告」によると、およそ11の関数や演算子と3ヵ所以上のキャストがあり、1億件以上の行に対して処理するようなSQLの場合に効果が出るようです。

複雑な分析クエリや大きなテーブルに対するキャストや文字列加工が必要な場合は、JITコンパイラの利用を検討してもよいでしょう。また、JITコンパイラは11から入った新機能ですので、今後より進化していくことでしょう。

パラレルクエリ─⁠─並列実行で高速化

PostgreSQLの大きな魅力と言えば、9.6から採用されたパラレルクエリです。パラレルクエリを使うと、複雑な集計クエリなどを並列処理し、高速に行えます。この機能はマルチコアCPU時代にマッチした強力な機能です。

パラレルクエリの役割

パラレルクエリは図7のとおり、テーブルスキャンなどの読み込みに対して並列に処理し、計算結果を最後に結合して返すしくみです。テーブルスキャンの場合などは対象テーブルが大きければ大きいほど効果が出ますし、並列度をうまくチューニングできれば処理速度も数倍速くなります。

パラレルクエリの使い方

パラレルクエリはデフォルトで有効となっており、意識しなくても利用条件を満たせば自動的に使われます。

実際の実行計画を見てみましょう。図8のとおり、RDBMSの宿敵とも言えるテーブルスキャンを高速に処理できます。

10,000,000行のテストデータに対する実行
demo=# EXPLAIN ANALYZE SELECT id FROM t1 WHERE id % 3 = 0;
                                            QUERY PLAN
-------------------------------------------------------------------------------------------------
Gather (cost=1000.00..151833.03 rows=49999 width=4) (actual time=0.374..2555.601 rows=3333333 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  -> Parallel Seq Scan on t1 (cost=0.00..145833.13 rows=20833 width=4) (actual time=0.048..785.725 rows=1111111 loops=3)
       Filter: ((id % 3) = 0)
       Rows Removed by Filter: 2222222
 Planning Time: 0.168 ms
 Execution Time: 2873.127 ms

JITコンパイラほどではありませんが、パラレルクエリも小さなテーブルでは並列化のメリットよりオーバーヘッドのほうが問題になります。そのため、JITコンパイラと同様に、パラレルクエリを利用する閾値を指定します。パラレルクエリのパラメータは次のとおりです。

max_worker_processes

システム全体で起動できるバックグランドワーカの上限数。これにはパラレルワーカも含み、たとえば設定値が8の場合に、自分が定義したバックグランドワーカを4つ起動させると、パラレルワーカは最大4つしか起動しない。デフォルト値は8

max_parallel_workers_per_gather

パラレルクエリ処理中に起動できるパラレルワーカの上限数。設定値が0の場合、パラレルクエリは実行されない。デフォルト値は2

max_parallel_workers

システム全体で起動できるパラレルワーカの上限数。max_worker_processesの設定値を超えることはできない。デフォルト値は8

parallel_tuple_cost

パラレル処理時にほかのプロセスにデータを受け渡しするのに必要なコストに対するプランナの推測値。デフォルト値は0.1

parallel_setup_cost

パラレル処理を行うプロセスを起動するのに必要なコストに対するプランナの推測値。デフォルト値は1000

min_parallel_table_scan_size

テーブルの参照対象が設定値以上だとパラレルワーカを追加起動する。デフォルト値は8MB

min_parallel_index_scan_size

インデックスの参照対象が設定値以上だとパラレルワーカを追加起動する。デフォルト値は512KB

テストや使うべきと事前にわかっているアドホックなクエリを実行する場合は、force_parallel_mode=onにするとパラレルクエリを利用できます。force_parallel_modeはSET文で設定することもできます。

また、パラレルクエリのワーカ数は一般的にCPUの数と同様にするのが望ましいです。ワーカ数を増やして並列度を上げたい場合は、CPUのコア数が多いサーバを用意しましょう。

パラレルクエリの対象

先述したようにパラレルクエリはPostgreSQL 9.6からありましたが、インデックスを活用できなかったため、対象がテーブルスキャンで実行されるHashJoinやNested Loop Join、集約などのみでした。実際の多くのクエリはインデックスを利用するため、パラレルクエリを活用できるケースは限られていました。

それがPostgreSQL 10、11とバージョンが上がるたびに拡張され、今では多くのケースで実行されます。

10で追加されたパラレルクエリの対象

• Parallel Index Scan（b-treeのみ）
• Parallel Index Only Scan（b-treeのみ）
• サブクエリ
• Merge Join
• Parallel bitmap heap scan

11で追加されたパラレルクエリの対象

• Parallel Hash Join（9.6からより強化）
• CREATE TABLE AS SELECT
• CREATE MATERIALIZED VIEW
• UNION ALLによるAPPEND
• SELECT INTO
• CREATE INDEX

一般的に利用する参照部分は、PostgreSQL 11でパラレルクエリの対象になったと言えます。特にJOINやインデックスは日常的に利用されるクエリであるため、パラレルクエリの恩恵を受けることが多いでしょう。

また、パラレルクエリはMySQLにはなく、OSSDBの中でPostgreSQLが大きな優位性を持っている機能の一つです。

まとめ

本章では、PostgreSQLの開発者向けの機能を紹介しました。PostgreSQL 10、11では、今までアプリケーションで四苦八苦してチューニングしたり、シャーディングしていたような問題がRDBMSの機能で対応できるようになっています。これを機に新しいPostgreSQLを使って設計の幅を広げていきましょう。

本誌最新号をチェック！
WEB+DB PRESS Vol.130

2022年8月24日発売
B5判／168ページ
定価1,628円
（本体1,480円＋税10%）
ISBN978-4-297-13000-8

• 特集1
  イミュータブルデータモデルで始める
  実践データモデリング
  業務の複雑さをシンプルに表現！
• 特集2
  いまはじめるFlutter
  iOS/Android両対応アプリを開発してみよう
• 特集3
  作って学ぶWeb3
  ブロックチェーン、スマートコントラクト、NFT