はじめに

にゃーん。
今回はPostgreSQL 13で強化されたSQL関数のうち、算術関数に関するものを紹介します。

PostgreSQL 13のSQL関数の改善点

PostgreSQL 13のSQL関数の改善点はここを参照(2020-05-17時点)。

算術関数に関する改善

大別すると以下のように分類できそうです。

新機能のSQL関数(gcd, lcm)の追加
精度を制御するSQL関数(min_scale, trim_scale)の追加
性能向上(sqrt, ln)の性能改善

新機能のSQL関数(gcd, lcm)の追加

これに該当するRelease Notesの項目はこれ。

Add greatest-common-denominator (gcd) and least-common-multiple (lcm) functions (Vik Fearing)

最大公約数とか、最小公倍数とか、小学生高学年（だったっけ？）以来って気もするなあ。さて、gcdとlcmの内容をpsqlの\dfで見てみるとこうなっている。

postgres=# \df gcd
                         List of functions
   Schema   | Name | Result data type | Argument data types | Type
------------+------+------------------+---------------------+------
 pg_catalog | gcd  | bigint           | bigint, bigint      | func
 pg_catalog | gcd  | integer          | integer, integer    | func
 pg_catalog | gcd  | numeric          | numeric, numeric    | func
(3 rows)

postgres=# \df lcm
                         List of functions
   Schema   | Name | Result data type | Argument data types | Type
------------+------+------------------+---------------------+------
 pg_catalog | lcm  | bigint           | bigint, bigint      | func
 pg_catalog | lcm  | integer          | integer, integer    | func
 pg_catalog | lcm  | numeric          | numeric, numeric    | func
(3 rows)

postgres=#

引数として整数型をとると思っていたけど、numericも扱えるのか。へー。

最大公約数(gcd)の実行例

postgres=# SELECT gcd(18, 24);
 gcd
-----
   6
(1 row)

postgres=# SELECT gcd(1.8, 2.40);
 gcd
------
 0.60
(1 row)

postgres=#

最小公倍数(lcm)の実行例

postgres=# SELECT lcm(18, 24);
 lcm
-----
  72
(1 row)

postgres=# SELECT lcm(0.18, 0.24);
 lcm
------
 0.72
(1 row)

postgres=#

なかなか面白いSQL関数が追加されたと思うけど、今の会社の仕事として、このSQL関数を使うような業務があるのかは・・・わからない。

精度を制御するSQL関数(min_scale, trim_scale)の追加

PostgreSQL 13ではnumeric型の精度を制御する関数が追加された。
これに該当するRelease Notesの項目はこれ。

Add function min_scale() that returns the number of digits to the right the decimal point that is required to represent the numeric value with full precision (Pavel Stehule)
Add function trim_scale() to reduce the scale of a number by removing trailing zeros (Pavel Stehule)

min_scale()

たとえば、1::numeric / 3という式(numeric型)を完全な精度で表すために何桁必要か、というのを求めるのに、min_scale()を用いる。

postgres=# SELECT 1::numeric / 3;
        ?column?
------------------------
 0.33333333333333333333
(1 row)

postgres=# SELECT min_scale(1::numeric / 3);
 min_scale
-----------
        20
(1 row)

postgres=# SELECT min_scale(1::numeric(64, 32) / 3);
 min_scale
-----------
        32
(1 row)

postgres=#

精度を事前に算出して、それを元に表示幅を制御したりするときに使えるのかなあ。

trim_scale()

この関数は、末尾が0になっているnumeric型の数値を末尾0にならない精度まで切り詰める、という関数のようだ。

postgres=# SELECT 1.098 + 1.002;
 ?column?
----------
    2.100
(1 row)

postgres=# SELECT trim_scale(1.098 + 1.002);
 trim_scale
------------
        2.1
(1 row)

性能向上(sqrt, ln)の性能改善

PostgreSQL 13ではsqrt()とln()の性能が良くなったらしい。
これに該当するRelease Notesの項目はこれ。

Improve the performance and accuracy of square root and natural log (ln) output (Dean Rasheed)

ということで、簡単に実験してみる。
以下のようなinteger型とnumeric型の列を持つテーブルを作成し、10万件のnumeric型のランダムな値を挿入しておく。

testdb=# CREATE TABLE test (id int, data numeric);
CREATE TABLE
testdb=# INSERT INTO test VALUES (generate_series(1, 100000), random() * 10000);
INSERT 0 100000
testdb=#

この状態で、一回全件検索とかテキトーにして、testテーブル内容をキャッシュに乗せる。
で、data列に対してsqrt()とln()をかける(10万回、この関数が動作する)。そのときのEXPLAIN ANALZYEの結果(Execution time)を見てみる。

PostgreSQL 12の例

test=# EXPLAIN ANALYZE SELECT sqrt(data) FROM test;
                                                  QUERY PLAN
--------------------------------------------------------------------------------------------------------------
 Seq Scan on test  (cost=0.00..1791.00 rows=100000 width=32) (actual time=0.028..607.931 rows=100000 loops=1)
 Planning Time: 0.046 ms
 Execution Time: 647.434 ms
(3 rows)

test=# EXPLAIN ANALYZE SELECT ln(data) FROM test;
                                                  QUERY PLAN
---------------------------------------------------------------------------------------------------------------
 Seq Scan on test  (cost=0.00..1791.00 rows=100000 width=32) (actual time=0.059..3297.845 rows=100000 loops=1)
 Planning Time: 0.043 ms
 Execution Time: 3337.823 ms
(3 rows)

PostgreSQL 13の例

testdb=# EXPLAIN ANALYZE SELECT sqrt(data) FROM test;
                                                  QUERY PLAN
--------------------------------------------------------------------------------------------------------------
 Seq Scan on test  (cost=0.00..1791.00 rows=100000 width=32) (actual time=0.028..141.228 rows=100000 loops=1)
 Planning Time: 0.062 ms
 Execution Time: 183.141 ms
(3 rows)

testdb=#

testdb=# EXPLAIN ANALYZE SELECT ln(data) FROM test;
                                                  QUERY PLAN
---------------------------------------------------------------------------------------------------------------
 Seq Scan on test  (cost=0.00..1791.00 rows=100000 width=32) (actual time=0.078..1928.757 rows=100000 loops=1)
 Planning Time: 0.061 ms
 Execution Time: 1971.396 ms
(3 rows)

結果まとめ(雑)

バージョン	PostgreSQL 12	PostgreSQL 13	処理時間比率(%)¹
sqrt() (ms)	647	183	28.3%
ln() (ms)	3338	1971	59.0%

こうかはばつぐんだ！

おわりに

今回はPostgreSQL 13の算術関数に関する改善点をざっと紹介してみました。
機能追加もそうですが、まだまだこんなに性能改善する余地があったとは・・・ちょい驚き。

PostgreSQL 13の処理時間をPostgreSQL 12の処理時間で割った数値 ↩