PostgreSQL のパフォーマンスチューンではまった

データベースに PostgreSQL を使った業務が運用開始になり、ちらほら障害らしき問い合わせが来る。今までに来た問い合わせで一番致命的だったのが問い合わせのパフォーマンスが悪いというもの。以前 Oracle で同じアプリケーションを動かしたときには数分で応答が返ってきたのに PostgreSQLに載せ代えたら2時間たっても終わらないとのこと。

原因を調べてみると2つのテーブルをJOINしているクエリにやたらと時間がかかっている。
一部のSQLを抜き出し、PgAdminIII で実行してみるとJOINしているキーはプライマリキーで結果も1~2件しか返ってこないはずなのに9秒もかかっている。 Explain で見てもおかしく見えない。テーブルスキャンは使われていないのになんでこう時間がかかるのかわからず2日も費やしてしまった。
クエリプラン

インターネットで調べても、SQLを書き換えてテーブルスキャンを避け、インデックスを使うようにする方法はヒットするが、完璧に見えるPlanが遅いという事象はほぼないに等しかった。

いろいろ情報をあさりまくって、最終的にカタログの pg_statio_user_tables というビューでどのようにデータブロックがアクセスされているか調べることにした。そこでわかったのは、インデックスでアクセスされているはずのテーブルの heap_blks_read, heap_blks_hit などの数値がクエリ後でぐんとあがる。これはおかしい。

以前別なDBMSでパフォーマンスチューンでテーブルごとに統計を取る必要があったことを思い出しそれを実行してみるとクエリ所要時間が9秒から11msまで落ちた。

前の場合はプランが統計を採る前と後ではまるで変わったので、今回は統計を採る必要がないと判断したのが間違いだった。 PgAdminIII で表示される統計は実際の動作と違う場合があることがわかった。

今日は、全テーブルに一括して統計を採るスクリプトを書かないと…


コメントを残す