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

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

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

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

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

■検証

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

上記結果から、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 を実行してみます。

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

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

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

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

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

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

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

コメント投稿

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


*