PostgreSQL 13がやってくる！(7-2) - SQL関数の強化 - 算術関数

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

# PostgreSQL 13のSQL関数の改善点
PostgreSQL 13のSQL関数の改善点は[ここ](https://momjian.us/pgsql_docs/release-13.html#id-1.11.6.5.5.7)を参照(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`で見てみるとこうなっている。

```sql
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)の実行例
```sql
postgres=# SELECT gcd(18, 24);
 gcd
-----
   6
(1 row)

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

postgres=#
```
### 最小公倍数(lcm)の実行例
```sql
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()`を用いる。

```sql
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にならない精度まで切り詰める、という関数のようだ。

```sql
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型のランダムな値を挿入しておく。

```sql
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の例

```sql
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)
```
```sql
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の例

```sql
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=#
```
```sql
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|処理時間比率(%)[^1]|
|:--|--:|--:|--:|
|sqrt() (ms)|647|183|28.3%|
|ln() (ms)|3338|1971|59.0%|

**こうかはばつぐんだ！**

[^1]: PostgreSQL 13の処理時間をPostgreSQL 12の処理時間で割った数値

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