2021年9月10日 星期五

Postgresql 實作四捨六入函數、SQL vs PL/pgsql 效能比較

一般而言在大部分資料庫在 ROUND 函數是採用四捨五入進位，但有部分資料庫例如 Teradata 是採用四捨六入五成雙，兩種不同算法的小數點的誤差在大量數據進行運算時可能會被放大成好幾位數的誤差，下面參考了一些資料在 Postgres 中實作四捨六入函數。


一開始是使用 PL/pgsql 的 if .. else.. 判斷進行實作，但是執行上發現自建函數的方式與原生C語言函數效能差異過大，約十倍左右，而在大量運算下執行時間會比使用原生 ROUND 函數慢很多，後來在測試階段發現，同樣的語法使用 SQL FUNCTION 進行實作會比 PL/pgsql 快很多，從參考資料裡面了解到應該是因為使用 PL/pgsql 會需要額外呼叫 PL/pgsql 的計算引擎造成額外開銷，因此在這類會被大量重複運用的函數在沒有複雜邏輯下會建議使用純 SQL 語法進行開發。

下面開始相關的實作測試。

相關的四捨六入規則可參考維基

1. 產生測試資料

DROP TABLE ta;
CREATE TABLE ta (test_value numeric);
INSERT INTO ta SELECT 10000*random() FROM generate_series(1,100000);

2. PL/pgsql 四捨六入函數與查詢結果。

CREATE OR REPLACE FUNCTION round_half_even(num numeric, decimal_count int) RETURNS numeric
    AS $$ 
begin
  if mod(abs(num*10.0^decimal_count),2) = 0.5 then
    return trunc(num,decimal_count);
  else
    return round(num,decimal_count);
  end if;
end
$$ LANGUAGE plpgsql IMMUTABLE ;
postgres=# explain analyze select round_half_even(test_value,2) from ta;
                                                 QUERY PLAN
-------------------------------------------------------------------------------------------------------------
 Seq Scan on ta  (cost=0.00..26541.00 rows=100000 width=32) (actual time=0.157..431.673 rows=100000 loops=1)
 Planning Time: 0.028 ms
 Execution Time: 442.792 ms
(3 rows)

3. SQL 四捨六入函數與查詢結果。

CREATE OR REPLACE FUNCTION round_half_even(num numeric, decimal_count int) RETURNS numeric
    AS $$ 
SELECT
CASE 
WHEN mod(abs(num*10.0^decimal_count),2) = 0.5
  THEN trunc(num,decimal_count)
ELSE
    round(num,decimal_count)
end
$$ LANGUAGE SQL IMMUTABLE ;
postgres=# explain analyze select round_half_even(test_value,2) from ta;
                                                 QUERY PLAN
------------------------------------------------------------------------------------------------------------
 Seq Scan on ta  (cost=0.00..3041.00 rows=100000 width=32) (actual time=0.013..182.349 rows=100000 loops=1)
 Planning Time: 0.175 ms
 Execution Time: 193.361 ms
(3 rows)

4. 使用內建 ROUND 函數查詢
postgres=# explain analyze select round(test_value,2) from ta;
                                                QUERY PLAN
-----------------------------------------------------------------------------------------------------------
 Seq Scan on ta  (cost=0.00..1791.00 rows=100000 width=32) (actual time=0.013..63.269 rows=100000 loops=1)
 Planning Time: 0.032 ms
 Execution Time: 73.190 ms
(3 rows)


5. 四捨六入函數查詢結果 (符合規則)

postgres=# select round_td(2.215,2);
 round_td
----------
     2.22
(1 row)

Time: 0.290 ms
postgres=# select round_td(2.225,2);
 round_td
----------
     2.22
(1 row)

Time: 0.281 ms
postgres=# select round_td(2.2252,2);
 round_td
----------
     2.23
(1 row)


從上面結果比較，可以明顯看到，同樣的運算在透過 SQL 函數執行遠比 PL/pgsql 執行來的快，雖然仍比不上內建 C 的函數，如果要再加速可能就要嘗試編寫 C 語言 Function。

PL/pgsql : 442.792 ms

SQL :  193.361 ms

ROUND : 73.190 ms


參考資料：

- 四捨六入

https://jingyan.baidu.com/article/219f4bf7e3c1f2de442d3812.html

https://www.twblogs.net/a/5bca85df2b7177735197a953

https://stackoverflow.com/questions/22613702/postgresql-roundv-numeric-s-int

- SQL vs PLPGSQL LANGUAGE PERFORMANCE

https://stackoverflow.com/questions/56935533/plpgsql-function-incurs-huge-performance-overhead

https://blog.sql-workbench.eu/post/stored-functions/

on
標籤： ,