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

◆ Live配信スケジュール ◆
サイオステクノロジーでは、Microsoft MVPの武井による「わかりみの深いシリーズ」など、定期的なLive配信を行っています。
⇒ 詳細スケジュールはこちらから
⇒ 見逃してしまった方はYoutubeチャンネルをご覧ください
【3/22開催】テックブログを書こう!アウトプットのススメ
1年で100本ブログを出した新米エンジニアがPV数が伸びなくてもTech Blogを書き続ける理由とは?
https://tech-lab.connpass.com/event/312805/

【4/18開催】VSCode Dev Containersで楽々開発環境構築祭り〜Python/Reactなどなど〜
Visual Studio Codeの拡張機能であるDev Containersを使ってReactとかPythonとかSpring Bootとかの開発環境をラクチンで構築する方法を紹介するイベントです。
https://tech-lab.connpass.com/event/311864/

こんにちは。サイオステクノロジー 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 の値 | データの並び順
------------------+----------------------------------------------------
"1"               | 物理的に "1 -> 2 -> 3 -> ... -> 8 -> 9 -> 10" とい
                  | う順 (昇順) でデータが並んでいる
------------------+----------------------------------------------------
"-1"              | 物理的に "10 -> 9 -> 8 -> ... -> 3 -> 2 -> 1" とい
                  | う順 (降順) でデータが並んでいる
------------------+----------------------------------------------------

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

■検証

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

postgres=# CREATE TABLE hoge (a int, b int, c int);
CREATE TABLE
postgres=# 
postgres=# INSERT INTO hoge VALUES (generate_series(1,10), (random() * 10)::int, generate_series(10,1,-1)); 
INSERT 0 10
postgres=# 
postgres=# SELECT ctid, * FROM hoge;
  ctid  | a  | b | c  
--------+----+---+----
 (0,1)  |  1 | 7 | 10
 (0,2)  |  2 | 4 |  9
 (0,3)  |  3 | 3 |  8
 (0,4)  |  4 | 1 |  7
 (0,5)  |  5 | 6 |  6
 (0,6)  |  6 | 3 |  5
 (0,7)  |  7 | 7 |  4
 (0,8)  |  8 | 6 |  3
 (0,9)  |  9 | 6 |  2
 (0,10) | 10 | 2 |  1
(10 rows)

テーブル “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 の値を確認してみます。

postgres=# ANALYZE hoge;
ANALYZE
postgres=# 
postgres=# SELECT tablename, attname, correlation FROM pg_stats WHERE tablename = 'hoge';
 tablename | attname | correlation 
-----------+---------+-------------
 hoge      | a       |           1
 hoge      | b       |   0.0181818
 hoge      | c       |          -1
(3 rows)

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

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

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

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

postgres=# CREATE INDEX ON hoge (a);
CREATE INDEX
postgres=# 
postgres=# CREATE INDEX ON hoge (b);
CREATE INDEX
postgres=# 
postgres=# CREATE INDEX ON hoge (c);
CREATE INDEX
postgres=# 
postgres=# \d hoge
                Table "public.hoge"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          | 
 b      | integer |           |          | 
 c      | integer |           |          | 
Indexes:
    "hoge_a_idx" btree (a)
    "hoge_b_idx" btree (b)
    "hoge_c_idx" btree (c)

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

postgres=# CLUSTER hoge USING hoge_b_idx;
CLUSTER
postgres=# 
postgres=# SELECT ctid, * FROM hoge;
  ctid  | a  | b | c  
--------+----+---+----
 (0,1)  |  4 | 1 |  7
 (0,2)  | 10 | 2 |  1
 (0,3)  |  6 | 3 |  5
 (0,4)  |  3 | 3 |  8
 (0,5)  |  2 | 4 |  9
 (0,6)  |  5 | 6 |  6
 (0,7)  |  8 | 6 |  3
 (0,8)  |  9 | 6 |  2
 (0,9)  |  1 | 7 | 10
 (0,10) |  7 | 7 |  4
(10 rows)

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

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

postgres=# ANALYZE hoge;
ANALYZE
postgres=# 
postgres=# SELECT tablename, attname, correlation FROM pg_stats WHERE tablename = 'hoge';
 tablename | attname | correlation 
-----------+---------+-------------
 hoge      | a       |  -0.0181818
 hoge      | b       |           1
 hoge      | c       |   0.0181818
(3 rows)

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

■最後に

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

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

アバター画像
About サイオステクノロジーの中の人 41 Articles
サイオステクノロジーで働く中の人です。
ご覧いただきありがとうございます! この投稿はお役に立ちましたか?

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

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


ご覧いただきありがとうございます。
ブログの最新情報はSNSでも発信しております。
ぜひTwitterのフォロー&Facebookページにいいねをお願い致します!



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

Be the first to comment

Leave a Reply

Your email address will not be published.


*


質問はこちら 閉じる