PostgreSQLのhstoreを触ってみた

by @dekokun on 2013/08/05 22:38

PostgreSQLにはhstoreなる、キーと値の集合を単一の列に保持できる型があると聞き及びまして、普段MySQLな私ですがこれは一度触ってみなくてはいかんと触ってみたのでした。

普通のselectとの非常に簡単(かつ不正確)な速度比較とインデックス作成くらいしかやってませんが。

導入

今回、AWSのm1.small上に構築したCentOS6.3を使用しています。PostgreSQLのバージョンは最新のStableバージョンである9.2を使用。

PostgreSQL RPM Building Project - Yum Repositoryからstableバージョンをダウンロードし、

インストール

# rpm -ivh pgdg-centos92-9.2-6.noarch.rpm
# rpm --import /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG-92
# yum -y install postgresql92-server
# yum -y install postgresql92-contrib.x86_64

初期化

# service postgresql-9.2 initdb

デーモンスタート

# service postgresql-9.2 start
# su - postgres
$ psql

hstoreの有効化

postgres=# CREATE EXTENSION hstore;

計測

postgres=# \timing
Timing is on.

テーブル作成

postgres=# CREATE TABLE hstore_tbl AS
postgres-#     SELECT
postgres-#         ('"hoge"=>' || RANDOM() || ' , "fuga"=>2')::hstore AS hstore, cast(RANDOM() as text) AS fuga
postgres-#     FROM
postgres-#         generate_series(1,1000000)
postgres-#     ;
SELECT 1000000
Time: 5500.272 ms
postgres=# SELECT * FROM hstore_tbl LIMIT 1;
                  hstore                   |       fuga
-------------------------------------------+-------------------
 "fuga"=>"2", "hoge"=>"0.0839038179256022" | 0.392216250766069
(1 row)

Time: 0.605 ms

hstoreなカラムの検索

postgres=# select * from hstore_tbl where hstore->'hoge' = '0.0839038179256022';
                  hstore                   |       fuga
-------------------------------------------+-------------------
 "fuga"=>"2", "hoge"=>"0.0839038179256022" | 0.392216250766069
(1 row)

Time: 302.522 ms

速度比較のための普通のカラムの検索

postgres=# select * from hstore_tbl where fuga = '0.392216250766069';
                  hstore                   |       fuga
-------------------------------------------+-------------------
 "fuga"=>"2", "hoge"=>"0.0839038179256022" | 0.392216250766069
(1 row)
Time: 205.692 ms

やはりhstoreな検索よりは早いですねぇ(一回実施した時の時間だけじゃなんとも言えないというのが正しい見方ですが、まぁ、何回か同じクエリを流してみてこんな感じだったので、雰囲気hstoreのほうが少し遅い、といっても速度比1.5倍ですが、まぁ。。という感じでいいでしょう)

インデックスを貼ってみた

postgres=# CREATE INDEX hstore_index ON hstore_tbl ((hstore->'hoge'));
CREATE INDEX
Time: 18843.262 ms
postgres=# CREATE INDEX text_index ON hstore_tbl (fuga);
CREATE INDEX
Time: 53691.437 ms
postgres=# select * from hstore_tbl where fuga = '0.392216250766069';
                  hstore                   |       fuga
-------------------------------------------+-------------------
 "fuga"=>"2", "hoge"=>"0.0839038179256022" | 0.392216250766069
(1 row)

Time: 0.756 ms
postgres=# select * from hstore_tbl where hstore->'hoge' = '0.0839038179256022';
                  hstore                   |       fuga
-------------------------------------------+-------------------
 "fuga"=>"2", "hoge"=>"0.0839038179256022" | 0.392216250766069
(1 row)

Time: 0.604 ms

hstore型なカラムの検索、ド派手に遅くなるんじゃないかと思っておりましたが意外とそんなことはないんですね。

インデックスも貼れますし、今後使用を検討してもいいかもしれませんね。

最後に、@r_rudyさんとのやり取り

@dekokun かなり便利ですよ。indexも貼れますし。ただ、データの取り出しがちょっと遅いので、普段使う分には問題ないですが、速度がものすごく重要なところには使わないほうがいいかと思います。
— しろう (@r_rudi) August 5, 2013

@dekokun 遅いのは内部実装がTextという文字列で持っているからなのですが、そこをmsgpackにするという試験実装をされた方がいまして、数倍速くなったそうです。PostgreSQLにはほかにもいろいろ便利な機能があるのでぜひ使ってください！
— しろう (@r_rudi) August 5, 2013

FDWとかも私、興味あります