【PostgreSQL】パフォーマンス改善事例:大規模テーブル結合の最適化

PostgreSQLで大規模テーブル同士の結合処理におけるパフォーマンス問題の発生経緯と、実行計画の確認方法、具体的な解決策を紹介します。

本記事では、PostgreSQLにおいて大規模テーブル同士の結合処理を行う際に発生したパフォーマンス問題と、その解決方法を紹介します。

パフォーマンス問題発生の経緯

大量データ環境において、 100万以上のレコードを持つテーブル同士を結合するクエリのパフォーマンス低下が試験によって検出されました。

実行計画を確認した結果、ハッシュ結合の際に一時ファイルへのI/Oが発生し、ディスクアクセスによるパフォーマンス悪化を引き起こしていると疑われました。

実行計画の確認方法

一時ファイルへの書き出し等が行われているかを確認する場合、 EXPLAINコマンドにBUFFERSオプションをつけて実行します。

EXPLAIN(ANALYZE,BUFFERS)
-- 以下にSQLを記述

出力例（一部抜粋）：

>  Parallel Hash Join  （略）
       Buffers: shared hit=XX read=XXXX, temp read=XX written=XX

>  Parallel Hash （略）
       Buckets: XX  Batches: XX  Memory Usage: XX kB
       Buffers: shared read=XX, temp written=XX

「temp written」や「temp read」が表示されている場合、一時ファイルへのI/Oが行われています。

上記の例では、

「Parallel Hash」のノードでtemp written
「Parallel Hash Join」のノードでtemp readとwritten が確認できます。

このことから、ハッシュ結合のためのハッシュテーブル作成処理で一時ファイルが作成され、ハッシュ結合処理でその一時ファイルに対して書き込み、読み取りを行っていることが読み取れます。

原因と解決方法について

一時ファイルへの書き出しが行われるのは、作成されるハッシュテーブルのサイズが割り当てられたワークメモリの量を超えてしまうことが原因です。

一時ファイルへの書き出しが行われるとディスクアクセスが発生するため、メモリ上だけの処理と比べてパフォーマンスが悪化します。そのため、ハッシュテーブルのサイズがワークメモリの割り当て量を超えないように工夫が必要です。

具体的には、以下の方法が考えられます。

SELECTするカラムを減らす

余分なカラムがSELECTされていると、ハッシュテーブルが肥大化します。クエリを見直し、必要最低限のカラムだけがSELECTされるようにします。

結合元テーブルのレコード数を減らす

結合元テーブルのレコード数が多いほどハッシュテーブルも大きくなります。 WHERE句による絞り込みなどを活用し、結合処理を行う前にレコード数を減らせないか検討します。

ワークメモリの設定を変更する

根本的な解決にはなりませんが、ワークメモリの割り当て量を増やすことでも解決可能です。

ワークメモリの割り当て量はpostgresql.confのwork_memで設定できます（デフォルトは4MB)。postgresql.confの設定を変更するとすべてのクエリに影響しますが、

SET work_mem = 'XXMB'

をクエリに追加するとセッション単位でワークメモリの値を変更可能です。

ただし、ワークメモリの割り当て量を増やすとリソースへの負荷が高まります。逆に性能悪化につながる恐れもあるので、慎重な設定が必要です。上記のような対策後、改めてEXPLAIN(ANALYZE,BUFFERS)で実行計画を取得し、temp readやtemp writeが消えていることを確認しましょう。

私の事例では方法2（レコード数の絞り込み）によりクエリの実行時間を約60%短縮できました。