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

◆ Live配信スケジュール ◆
サイオステクノロジーでは、Microsoft MVPの武井による「わかりみの深いシリーズ」など、定期的なLive配信を行っています。
⇒ 詳細スケジュールはこちらから
⇒ 見逃してしまった方はYoutubeチャンネルをご覧ください

【5/21開催】Azure OpenAI ServiceによるRAG実装ガイドを公開しました
生成AIを活用したユースケースで最も一番熱いと言われているRAGの実装ガイドを公開しました。そのガイドの紹介をおこなうイベントです!!
https://tech-lab.connpass.com/event/315703/

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

前回は pg_stats.correlation に関する動作検証を実施しましたが、今回は CLUSTER コマンドによる性能改善について検証してみようと思います。(※以下の内容は PostgreSQL 10.1 にて検証/調査しています。)

結論から言ってしまうと、CLUSTER コマンドにてテーブル内のレコードの物理的な並びを順番に並び替えると、I/O 処理が減り性能が改善する可能性があります。

※実行するクエリやテーブル定義等に依存するため、一概に性能が改善するとは言えません。今回の検証では意図的に性能が改善するようなテーブルを作成しています。

1 ■検証
2 ■最後に
3 ■おまけ

■検証

それではさっそく、以下のようなテーブルを作成してみます。

※検証のために ALTER TABLE にて収集される統計情報量の設定を変更していますが、今回の記事の本題とは違うお話なので詳細については割愛します。

postgres=# CREATE TABLE fuga (a int, b text);
CREATE TABLE
postgres=# 
postgres=# INSERT INTO fuga SELECT (random() * 100)::int, md5(clock_timestamp()::text) FROM generate_series(1,3000000);
INSERT 0 3000000
postgres=# 
postgres=# CREATE INDEX ON fuga (a);
CREATE INDEX
postgres=# 
postgres=# ALTER TABLE fuga ALTER COLUMN a SET STATISTICS 10000;
ALTER TABLE
postgres=# 
postgres=# ANALYZE fuga;
ANALYZE
postgres=# 
postgres=# SELECT tablename, attname, correlation FROM pg_stats WHERE tablename = 'fuga' AND attname = 'a';
 tablename | attname | correlation 
-----------+---------+-------------
 fuga      | a       |  0.00955085
(1 row)

postgres=# 
postgres=# SELECT * FROM fuga LIMIT 5;
 a  |                b                 
----+----------------------------------
 45 | de176f210d93c2466112dbc364ec9ca1
 78 | 3c053d1f3d6695a625336639e3c4ae5a
 98 | 9c2dc1c1aa608f97ff813bceac750472
 70 | be2cfad6127a51a7ef63ca751af7e662
 67 | 4329ae1337c21299efc70860dd368d9e
(5 rows)

このテーブル “fuga” のカラム “a” には “0 ～ 100” までのランダムな数字、カラム “b” にはランダムな文字列が格納されています。

また、前回の記事にて紹介した pg_stats.correlation の値が “0 に近い値” になっているので、カラム “a” の数字は物理的にランダムな順序で格納されていることが分かります。

今回の検証では、このテーブルからカラム “a” の値が “1” であるレコードを選択するクエリを実行し、実行時間を確認してみます。

EXPLAIN 文を利用して実際にクエリの実行時間を確認してみると、以下のような結果になりました。

postgres=# EXPLAIN (ANALYZE, VERBOSE, BUFFERS) SELECT * FROM fuga WHERE a = 1;
                                                         QUERY PLAN                                                          
-----------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on public.fuga  (cost=566.51..27238.03 rows=30204 width=37) (actual time=7.497..32.630 rows=30204 loops=1)
   Output: a, b
   Recheck Cond: (fuga.a = 1)
   Heap Blocks: exact=17666
   Buffers: shared hit=17751
   ->  Bitmap Index Scan on fuga_a_idx  (cost=0.00..558.96 rows=30204 width=0) (actual time=4.157..4.157 rows=30204 loops=1)
         Index Cond: (fuga.a = 1)
         Buffers: shared hit=85
 Planning time: 0.115 ms
 Execution time: 34.635 ms
(10 rows)

postgres=# 
postgres=# EXPLAIN (ANALYZE, VERBOSE, BUFFERS) SELECT * FROM fuga WHERE a = 1;
                                                         QUERY PLAN                                                          
-----------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on public.fuga  (cost=566.51..27238.03 rows=30204 width=37) (actual time=7.293..31.603 rows=30204 loops=1)
   Output: a, b
   Recheck Cond: (fuga.a = 1)
   Heap Blocks: exact=17666
   Buffers: shared hit=17751
   ->  Bitmap Index Scan on fuga_a_idx  (cost=0.00..558.96 rows=30204 width=0) (actual time=4.096..4.096 rows=30204 loops=1)
         Index Cond: (fuga.a = 1)
         Buffers: shared hit=85
 Planning time: 0.110 ms
 Execution time: 33.480 ms
(10 rows)

postgres=# 
postgres=# EXPLAIN (ANALYZE, VERBOSE, BUFFERS) SELECT * FROM fuga WHERE a = 1;
                                                         QUERY PLAN                                                          
-----------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on public.fuga  (cost=566.51..27238.03 rows=30204 width=37) (actual time=7.587..32.863 rows=30204 loops=1)
   Output: a, b
   Recheck Cond: (fuga.a = 1)
   Heap Blocks: exact=17666
   Buffers: shared hit=17751
   ->  Bitmap Index Scan on fuga_a_idx  (cost=0.00..558.96 rows=30204 width=0) (actual time=4.192..4.192 rows=30204 loops=1)
         Index Cond: (fuga.a = 1)
         Buffers: shared hit=85
 Planning time: 0.100 ms
 Execution time: 34.809 ms
(10 rows)

共有メモリ上にデータがのっているか否か等の要素に依存して性能が変化する場合もあるので複数回実行していますが、実行時間はおおよそ 30ms 強といったところです。

説明/検証の順番が前後してしまうのですが、今回は “クエリ実行時に読み込んでいるブロック数” に着目したお話をしますので、CLUSTER コマンド実行前にもう少しこのテーブル “fuga” の詳細を確認しておきます。

検証用のテーブル内のレコードの物理的な位置を確認するために、カラム “a” の値が “1” のレコードの ctid, “fuga” テーブルのページ数, レコード数を確認してみます。

postgres=# SELECT ctid, * FROM fuga WHERE a = 1 order by ctid asc LIMIT 5;
  ctid  | a |                b                 
--------+---+----------------------------------
 (0,21) | 1 | ee06af1abeb52f55ea4254a991534ae8
 (0,26) | 1 | b9257e2f1e602f641704ea5e6723aed8
 (0,97) | 1 | b328f5b4c1a41ca5c4df6e88c155bdc4
 (2,21) | 1 | ef2e699d1a6c2e35d4da474cf958648e
 (3,67) | 1 | 4b3b9dc6c56a1621fd5862ec6cf99329
(5 rows)

postgres=# 
postgres=# SELECT ctid, * FROM fuga WHERE a = 1 order by ctid desc LIMIT 5;
    ctid     | a |                b                 
-------------+---+----------------------------------
 (24998,112) | 1 | 77efb71289d60fecc1a2a73368d57473
 (24997,89)  | 1 | 904b66b993d9f0ef84fdf29773ed49ce
 (24996,118) | 1 | bb0dd563ed0e4e74b9a6d5a165db5c93
 (24996,105) | 1 | 8456303422b4c3bfa8f6ba054be3f978
 (24996,79)  | 1 | fbd4f873f7580336c3c7bd33fa3e9e06
(5 rows)

postgres=# 
postgres=# SELECT relname, relpages FROM pg_class WHERE relname = 'fuga';
 relname | relpages 
---------+----------
 fuga    |    25000
(1 row)

postgres=# 
postgres=# SELECT count(a) FROM fuga WHERE a = 1;
 count 
-------
 30270
(1 row)

PostgreSQL のテーブルのデータは基本的に 8KB 単位の “ページ” (ブロック) に分割されており、各ページの中に複数のレコードが格納されています。(1ページに格納できるレコード数は、テーブルの定義に依存します)

ctid の左側の数値がブロック ID なので、このテーブルの場合ブロック ID が “0” ～ “24998” の範囲のページ (ブロック) 内に “a = 1″ のレコードが散らばって格納されていることが分かります。(もちろん、”a = 1” のレコードを含まないページも存在しているはずです)

また、pg_class.relpages の値から、テーブル “fuga” のページ数は 25000 であることが確認できるので、全部で 30270 レコード存在する “a = 1” のレコードがほぼテーブル全体に万遍なく散らばっている状況が推測されます。

ここで、改めて先ほどの EXPLAIN 文の内容を見てみると、以下のような項目が出力されています。

Heap Blocks: exact=17666

詳細については完全に調べられていないのですが、大雑把に言うとこの数値は、Bitmap Heap Scan 処理にて読み込んだページ数を示しているため、今回のクエリでは 17666 ページを読み込んでいることになります。

つまり、テーブル “fuga” の “a = 1” のレコードは 17666 ページに分散されて格納されていることになります。

そしてこれは、テーブル “fuga” から “a = 1″ のレコードを全て取得する場合、”17666 ページを読み込む必要がある” ということになります。

さて、それでは次に CLUSTER コマンドを実行してみましょう。

postgres=# CLUSTER fuga USING fuga_a_idx;
CLUSTER

CLUSTER コマンド実行後、EXPLAIN 文にて実行時間を確認してみます。

postgres=# EXPLAIN (ANALYZE, VERBOSE, BUFFERS) SELECT * FROM fuga WHERE a = 1;
                                                         QUERY PLAN                                                          
-----------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on public.fuga  (cost=571.02..27245.79 rows=30270 width=37) (actual time=1.645..5.557 rows=30270 loops=1)
   Output: a, b
   Recheck Cond: (fuga.a = 1)
   Heap Blocks: exact=253
   Buffers: shared hit=339
   ->  Bitmap Index Scan on fuga_a_idx  (cost=0.00..563.45 rows=30270 width=0) (actual time=1.606..1.606 rows=30270 loops=1)
         Index Cond: (fuga.a = 1)
         Buffers: shared hit=86
 Planning time: 0.130 ms
 Execution time: 7.552 ms
(10 rows)

postgres=# 
postgres=# EXPLAIN (ANALYZE, VERBOSE, BUFFERS) SELECT * FROM fuga WHERE a = 1;
                                                         QUERY PLAN                                                          
-----------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on public.fuga  (cost=571.02..27245.79 rows=30270 width=37) (actual time=1.660..5.573 rows=30270 loops=1)
   Output: a, b
   Recheck Cond: (fuga.a = 1)
   Heap Blocks: exact=253
   Buffers: shared hit=339
   ->  Bitmap Index Scan on fuga_a_idx  (cost=0.00..563.45 rows=30270 width=0) (actual time=1.618..1.618 rows=30270 loops=1)
         Index Cond: (fuga.a = 1)
         Buffers: shared hit=86
 Planning time: 0.101 ms
 Execution time: 7.479 ms
(10 rows)

postgres=# 
postgres=# EXPLAIN (ANALYZE, VERBOSE, BUFFERS) SELECT * FROM fuga WHERE a = 1;
                                                         QUERY PLAN                                                          
-----------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on public.fuga  (cost=571.02..27245.79 rows=30270 width=37) (actual time=1.553..5.390 rows=30270 loops=1)
   Output: a, b
   Recheck Cond: (fuga.a = 1)
   Heap Blocks: exact=253
   Buffers: shared hit=339
   ->  Bitmap Index Scan on fuga_a_idx  (cost=0.00..563.45 rows=30270 width=0) (actual time=1.515..1.515 rows=30270 loops=1)
         Index Cond: (fuga.a = 1)
         Buffers: shared hit=86
 Planning time: 0.143 ms
 Execution time: 7.283 ms
(10 rows)

先ほど同様複数回実行して結果を確認してみると、実行時間が 7ms 強程度に短くなっていることが確認できました。

ここで、再度 Heap Blocks の項目に注目してみると、以下のようになっています。

Heap Blocks: exact=253

この結果から、同じレコード (“a = 1”) を取り出すクエリにおいて、CLUSTER 実行後は読み込んでいるページ数が “17666” から “253” に減っていることが分かります。

上記の通り CLUSTER 実行後は、読み込むページ数が減った分 I/O 処理が減り、性能が良くなったという状況が確認できました。

では、先ほどと同様に CLUSTER 実行後の検証用のテーブル内のレコードの物理的な位置を確認してみましょう。

postgres=# SELECT ctid, * FROM fuga WHERE a = 1 order by ctid asc LIMIT 5;
   ctid   | a |                b                 
----------+---+----------------------------------
 (124,50) | 1 | ee06af1abeb52f55ea4254a991534ae8
 (124,51) | 1 | b9257e2f1e602f641704ea5e6723aed8
 (124,52) | 1 | b328f5b4c1a41ca5c4df6e88c155bdc4
 (124,53) | 1 | ef2e699d1a6c2e35d4da474cf958648e
 (124,54) | 1 | 4b3b9dc6c56a1621fd5862ec6cf99329
(5 rows)

postgres=# 
postgres=# SELECT ctid, * FROM fuga WHERE a = 1 order by ctid desc LIMIT 5;
   ctid   | a |                b                 
----------+---+----------------------------------
 (376,79) | 1 | 77efb71289d60fecc1a2a73368d57473
 (376,78) | 1 | 904b66b993d9f0ef84fdf29773ed49ce
 (376,77) | 1 | bb0dd563ed0e4e74b9a6d5a165db5c93
 (376,76) | 1 | 8456303422b4c3bfa8f6ba054be3f978
 (376,75) | 1 | fbd4f873f7580336c3c7bd33fa3e9e06
(5 rows)

postgres=# 
postgres=# SELECT relname, relpages FROM pg_class WHERE relname = 'fuga';
 relname | relpages 
---------+----------
 fuga    |    25000
(1 row)

postgres=# 
postgres=# SELECT count(a) FROM fuga WHERE a = 1;
 count 
-------
 30270
(1 row)

上記結果から、CLUSTER 実行後のテーブルではブロック ID が “124” ～ “376” の範囲のページに “a = 1” のレコードが格納されていることが分かります。(念のため確認していますが、テーブル全体のページ数や “a = 1″ のレコード数は変わっていません)

ページ数について計算してみると 376 – (124 – 1) = 253 となり、”a = 1” のレコードは 253 ページにわたって格納されていることが分かります。

この 253 ページという数字は、EXPLAIN 文にて確認した “Heap Blocks: exact=253” の数値とも一致しており、テーブル “fuga” から “a = 1″ のレコードを全て取得する場合、”253 ページを読み込んでいる” ということが確認できます。

■最後に

さて、今回の検証では CLUSTER コマンドにて性能が改善する可能性について検証してみました。

繰り返しとなってしまいますが、今回取り上げた CLUSTER コマンドによる性能改善についてはクエリやテーブル定義に依存するため、CLUSTER コマンドを実行すれば必ず性能が改善するとは限りません。

今回紹介したように、データを順番に並び替えることによってアクセスするページ数を減らすことで性能が改善する可能性があるため、WHERE 句にて範囲指定をするようなクエリ (WHERE a >= 100 AND a <= 300 等) だと性能改善が見込めるかもしれません。

また、CLUSTER コマンドでは対象テーブルに対して ACCESS EXCLUSIVE ロックが取得されるため CLUSTER 実行中はテーブルに対する読み書きができなくなる、等の複数のデメリットもあります。

そのため、商用環境で CLUSTER コマンドの実行を検討する場合は、検証環境等を利用した十分な検証を実施した方が良さそうです。

■おまけ

お気づきの方もいらっしゃるかもしれませんが、今回の検証では CLUSTER コマンド実行前後で同じ実行計画 (Bitmap Heap Scan) が利用されるようにするため、CLUSTER コマンド実行後に ANALYZE を実行していません。

ということで、実際に CLUSTER コマンド実行後のテーブル “fuga” に ANALYZE を実行してみます。

postgres=# ANALYZE fuga;
ANALYZE
postgres=# 
postgres=# SELECT tablename, attname, correlation FROM pg_stats WHERE tablename = 'fuga' AND attname = 'a';
 tablename | attname | correlation 
-----------+---------+-------------
 fuga      | a       |           1
(1 row)

すると、以下のように “Index Scan” が実行されるようになりました。

postgres=# EXPLAIN (ANALYZE, VERBOSE, BUFFERS) SELECT * FROM fuga WHERE a = 1;
                                                             QUERY PLAN                                                             
------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using fuga_a_idx on public.fuga  (cost=0.43..1122.15 rows=30270 width=37) (actual time=0.024..6.195 rows=30270 loops=1)
   Output: a, b
   Index Cond: (fuga.a = 1)
   Buffers: shared hit=339
 Planning time: 0.108 ms
 Execution time: 8.102 ms
(6 rows)

postgres=# 
postgres=# EXPLAIN (ANALYZE, VERBOSE, BUFFERS) SELECT * FROM fuga WHERE a = 1;
                                                             QUERY PLAN                                                             
------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using fuga_a_idx on public.fuga  (cost=0.43..1122.15 rows=30270 width=37) (actual time=0.025..6.502 rows=30270 loops=1)
   Output: a, b
   Index Cond: (fuga.a = 1)
   Buffers: shared hit=339
 Planning time: 0.097 ms
 Execution time: 8.419 ms
(6 rows)

postgres=# 
postgres=# EXPLAIN (ANALYZE, VERBOSE, BUFFERS) SELECT * FROM fuga WHERE a = 1;
                                                             QUERY PLAN                                                             
------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using fuga_a_idx on public.fuga  (cost=0.43..1122.15 rows=30270 width=37) (actual time=0.025..6.846 rows=30270 loops=1)
   Output: a, b
   Index Cond: (fuga.a = 1)
   Buffers: shared hit=339
 Planning time: 0.105 ms
 Execution time: 8.796 ms
(6 rows)

よくよく見ると、”Bitmap Heap Scan” では 7ms 強だった実行時間が “Index Scan” だと 8ms 強になっています。

僅か 1ms 程の差ですが、状況によっては ANALYZE 実行直後 (統計情報が最新の状態) でも実行時間が長くなるプランが選択されることもあるようです。

この辺りは基本的に PostgreSQL のプランナが判断している部分なので、実際の環境でパフォーマンスチューニングを行う際は、PostgreSQL 側の設定の見直しやクエリの見直し等を地道にやっていくしかないと思われます。