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

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

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

1 ■はじめに
2 ■検証
3 ■最後に

■はじめに

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 に近い値” になっていることが確認できます。