pg_nosql_benchmarkの改善案 - 日々の記録別館

かなり今更感のある話ではあるが・・・
EnterpriseDB社がGithubで公開しているpg_nosql_benchmark、これまでも手元の環境で何回か測定して、その結果を第七回中国地方DB勉強会 in 松江で発表とかしてたんだが、このベンチマーク、実はPostgreSQL的にちょっとアレな感じのインデックス設定とクエリになってるんだよな。

何が問題？

簡単に言えば
GINインデックスを設定しているけど、
クエリはそのインデックスを使用していない
という、アレゲな感じになっている。

このベンチマークではSELECTの前に、

CREATE INDEX ${F_TABLE}_idx ON ${F_TABLE} USING gin(data);

みたいにginインデックスを設定しているんだけど、検索時には

SELECT data FROM json_tables WHERE (data->>'brand') = 'ACME';

のように、 ->>'キー名' = 値のような条件を与えている。
これだとSeqScanになる。

正直言って、このレギュレーション(?)で、よくMongoDBのfind()よりも速い結果が出たんだなと。
(なお、MongoDBに設定したensureIndex()が有効に使われているのは、別途、 lib/mongo_func_lib.sh を書き換え、find().explain()の出力結果で確認した)

少なくともMongoDBがインデックスを(たぶん)使っていて、PostgreSQL側が使っていないのはどうなのよ、というのもあったので、勝手に改善案を考えてみた。

改善案1(btree1式インデックス設定)

最初に考えつくのは発行されるクエリパターンに合わせて、インデックスの作成方式を変えるというもの。
元々あったginインデックスは無用の長物なので、ばっさり削除。
pg_nosql_benchmarkでは、"name", "brand", "type"の3つのキーから取り出した値を、文字列と比較している。
なので、(カラム名->>'キー') の式インデックスを3種類設定すれば良い。

pg_nosql_benchmark内だと lib/pg_func_lib.sh の pg_create_index_collection () という関数を変更。

#   typeset -r F_SQL="CREATE INDEX ${F_TABLE}_idx ON ${F_TABLE} USING gin(data);"
   typeset -r F_SQL1="CREATE INDEX ${F_TABLE}_idx_name ON ${F_TABLE} USING btree((data->>'name'));"
   typeset -r F_SQL2="CREATE INDEX ${F_TABLE}_idx_brand ON ${F_TABLE} USING btree((data->>'brand'));"
   typeset -r F_SQL3="CREATE INDEX ${F_TABLE}_idx_type ON ${F_TABLE} USING btree((data->>'type'));"

   process_log "creating index on postgreSQL collections."
#   run_sql "${F_PGHOST}" "${F_PGPORT}" "${F_DBNAME}" "${F_PGUSER}" \
#           "${F_PGPASSWORD}" "${F_SQL}" \
#            >/dev/null
   run_sql "${F_PGHOST}" "${F_PGPORT}" "${F_DBNAME}" "${F_PGUSER}" \
           "${F_PGPASSWORD}" "${F_SQL1}" \
            >/dev/null
   run_sql "${F_PGHOST}" "${F_PGPORT}" "${F_DBNAME}" "${F_PGUSER}" \
           "${F_PGPASSWORD}" "${F_SQL2}" \
            >/dev/null
   run_sql "${F_PGHOST}" "${F_PGPORT}" "${F_DBNAME}" "${F_PGUSER}" \
           "${F_PGPASSWORD}" "${F_SQL3}" \
            >/dev/null

改善案2(ginインデックス用にクエリ変更)

次の方式は、ginインデックスに合わせて発行するクエリを変更するというもの。
元々のクエリが

SELECT data FROM json_tables WHERE  (data->>'brand') = 'ACME';

のような感じになっているので、これを

SELECT data FROM json_tables WHERE  data @> '{"brand":"ACME"}';

のような感じに変更する。

pg_nosql_benchmark内だと lib/pg_func_lib.sh の pg_select_benchmark () という関数を変更。

   typeset -r F_SELECT1="SELECT data
                         FROM ${F_COLLECTION}
                           WHERE  data @> '{\"brand\":\"ACME\"}';"
   typeset -r F_SELECT2="SELECT data
                         FROM ${F_COLLECTION}
                           WHERE  data @> '{\"name\":\"Phone Service Basic Plan\"}';"
   typeset -r F_SELECT3="SELECT data
                         FROM ${F_COLLECTION}
                          WHERE  data @> '{\"name\":\"AC3 Case Red\"}';"
   typeset -r F_SELECT4="SELECT data
                          FROM ${F_COLLECTION}
                            WHERE  data @> '{\"type\":\"service"}';"