LoginSignup
3
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

@nuko_yokohama(nuko yokohama)

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

Last updated at Posted at 2020-05-17

はじめに

にゃーん。
今回は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 処理時間比率(%)1
sqrt() (ms) 647 183 28.3%
ln() (ms) 3338 1971 59.0%

こうかはばつぐんだ！

おわりに

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

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

3
1
1

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
3
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?