PostgreSQL 9.5 numeric/text型のソート性能向上
今日は9.5の小ネタを。
9.5のリリースノートを見ると、こんな項目がある。
Improve the speed of sorting character and numeric fields (Peter Geoghegan, Andrew Gierth, Robert Haas)
要するにnumeric型と文字型のソート性能が向上したよと。
今日はそれを試してみた。
測定内容
以下の様なinteger型、numeric型、text型のカラムを持つテーブルを作成しておく。
CREATE TABLE test (id int, data1 numeric, data2 text);
で、こんな感じでnumeric型カラムとtext型カラムにランダムなデータを投入しておく。
INSERT INTO test (id, data1, data2) VALUES ( generate_series(1, 1000000), random()::numeric * random()::numeric, md5(clock_timestamp()::text) || md5(clock_timestamp()::text) );
実際に投入されたデータはこんな感じ。
[nuko@localhost 9.5-sort]$ psql sort -c "TABLE test limit 3" id | data1 | data2 ----+----------------------------------+------------------------------------------------------------------ 1 | 0.266743782054714509580313927259 | 24d60288a55b8de99605e74ba5ae829482da2c40e77b1b84d5aee58054ea5304 2 | 0.251961716943947463457289780621 | 45d4362717ede3bc1081a51a402094dc99a479f60e97acd7f33eba0a0bc8b336 3 | 0.69297134017310048889839763536 | de8a90ae74ae112991e95b17a5155b263346df19d81089f137819d30ca8604a6 (3 rows)
で、このテーブルに対してid, data1, data2をソートキーとするSELECT文をEXPLAIN ALANYZE付きで実行して、sort operatorのactual timeの値を9.4と9.5-alpha2で比較してみる。
例えばnumeric型のdata1をソートキーにする場合は、こんな感じになる。
sort=# EXPLAIN ANALYZE SELECT * FROM test ORDER BY data1;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Sort (cost=123943.84..126443.84 rows=1000000 width=87) (actual time=494.638..650.491 rows=1000000 loops=1)
Sort Key: data1
Sort Method: quicksort Memory: 165202kB
-> Seq Scan on test (cost=0.00..24286.00 rows=1000000 width=87) (actual time=0.066..112.457 rows=1000000 loops=1)
Planning time: 0.038 ms
Execution time: 690.493 ms
(6 rows)あと、work_memを調整して、Disk SortのパターンとMemory Sortのパターンを測定してみる。
なお、Memory Sortにする場合、work_mem = '256MB' くらいにしておけばOK。
測定結果
サマリ
結論から言えば、numeric型に対する性能向上効果(特にMemroy Sortの場合)は非常に高いと思われる。
text型についても結構、有意にソート性能が向上していると判断できる。
各データタイプ毎の実測値を以下に示す。
interger型
integer型については9.5で特に性能向上はしていない。
numeric型
Disk Sort/Memory Sortともに性能向上効果が確認できる。特に、Memory Sortについては4倍以上高速化されている！
text型
text型についてもnumeric型ほど顕著ではないが、Disk Sort/Memory Sortともに有意なソート性能向上効果が確認できた。
おわりに
OracleのNUMBER型をPostgreSQLに移行する場合、numericは性能上不利なので、値域を調べて可能ならinteger等への型変換を行うべき、というノウハウが過去にあったけど、カリカリに性能を追求しなければ、NUMBERをそのままnumericに移行するという選択肢も今後は出てくるかもしれませんねえ。
