PostgreSQL の pg_stats.correlation と CLUSTER コマンドについて検証してみた (その1)

こんにちは。サイオステクノロジー OSS サポート担当 Y です。

今回は PostgreSQL の pg_stats.correlation と CLUSTER コマンドについて動作検証をしてみました。(※以下の内容は PostgreSQL 10.1 にて検証/調査しています。)

■はじめに

pg_statistic に格納されている統計情報を参照するためのビュー pg_stats には、correlation というカラムがあります。

この correlation の値は、特定のカラムに格納されているデータの “物理的な順序” と “論理的な順序” の相関を表しています。

(https://www.postgresql.org/docs/10/static/view-pg-stats.html)

例えば、”1″ ~ “10” の数値が格納されている列について、correlation の値は以下のようになります。

また、物理的に順番に並んでいないカラム (データの物理的な順番がバラバラなカラム) ほど correlation の値は “0 に近い値” になります。

■検証

言葉での説明だとわかり辛いので、以下のようなテーブルで実際に検証してみます。

テーブル “hoge” の各カラムについて、hoge.a には昇順に hoge.b にはランダムな順番に hoge.c には降順にデータを格納しています。

※カラム “ctid” は各レコードの物理的な位置の情報 (ブロックの ID とオフセットの値) を保持しているカラムです。詳細は割愛しますが、ctid については以下のドキュメントに記載されています。

(https://www.postgresql.org/docs/10/static/ddl-system-columns.html)

このテーブル “hoge” について、統計情報を更新するために ANALYZE 文を実行した上で、pg_stats.correlation の値を確認してみます。

このようにカラム内のデータの並び順が昇順の場合は “1”、降順の場合は “-1” になっていることが確認できます。また、順番がバラバラなカラムの値は “0 に近い値” になっていることが確認できます。

次に各カラムの物理的なデータを並び替えた上で pg_stats.correlation の値を確認してみます。

PostgreSQL には CLUSTER というコマンドがあり、インデックスを利用してデータの物理的な順序を並べなおすことができます。

CLUSTER コマンドを利用するためにはインデックスが必要であるため、まずは各カラムにインデックスを作成します。

次に作成したインデックスを使って CLUSTER コマンドを実行します。今回は先ほどランダムな順番でデータが格納されていた “hoge.b” カラムを対象に CLUSTER コマンドを実行します。

CLUSTER を実行すると、CLUSTER 実行前と違い hoge.b の値が昇順に並べられていること、及びそれに伴って hoge.a, hoge.c の値の順序がバラバラになってしまっていることが確認できます。

この状態において、統計情報を更新するために ANALYZE 文を実行した上で、再度 pg_stats.correlation の値を確認してみます。

上記の通り物理的な順序を並べなおしたカラム hoge.b の correlation の値が “1” (昇順) になり、hoge.b の並べ替えに伴って順番がバラバラになってしまった hoge.a と hoge.c の correlation の値が “0 に近い値” になっていることが確認できます。

■最後に

さて、今回は pg_stats.correlation の値の検証のためにデータを並び替える目的で CLUSTER コマンドを実行しましたが、実際の DB の運用では CLUSTER コマンドを使って物理的なデータの順序を並べ替えることで性能改善が見込める場合があります。

次回は CLUSTER コマンドを利用した性能検証を実施してみようと思います。

>> 雑誌等の執筆依頼を受付しております。
   ご希望の方はお気軽にお問い合わせください!

ご覧いただきありがとうございます! この投稿はお役に立ちましたか?

役に立った 役に立たなかった

0人がこの投稿は役に立ったと言っています。

コメント投稿

メールアドレスは表示されません。


*