CLUSTERによる性能改善

postgres DB 技術

一歩でも外に足を踏み出すと街のクリスマスムードに襲われ、精神を蝕まれる。周囲には「クリスマス？ああ、そういえばそんな時期か。まあ、全然興味ないけど。」とか言いつつめっちゃ気にするタイプです。怯えています。

TL;DR

  • データを物理的に整列させる機能 CLUSTER がある
  • テーブルの特定のデータにアクセスが集中する場合に、参照性能が改善する
  • 利用にあたりトレードオフ、メンテナンスコストが生じる

CLUSTERとは

特定のインデックスを使って、テーブルのタプルの物理配置を整列させることにより、ディスクリードの効率を高める。詳しくはドキュメントを読んでください*1。

実験

特定条件でクラスタ化することにより、その条件を用いた参照時にディスクリードが減って高速化されることを確かめる。

実験用のテーブルを作成する。

CREATE TABLE test
(
    test_id int primary key,
    test_group_id int,
    test_text text
);

-- CLUSTER用のインデックスを作成
CREATE INDEX test_group_index ON test (test_group_id);

ディスクリードの効率化を確認したいので、shared_buffersはデフォルトの128MBから最小値の128KBに変更している。実験用のデータとして、以下データをINSERTする。

# test_textカラムには、一律、夏目漱石著『三四郎』の冒頭一段落を格納sる
sanshiro = "うとうととして目がさめると女はいつのまにか、隣のじいさんと話を始めている。このじいさんはたしかに前の前の駅から乗ったいなか者である。発車まぎわに頓狂な声を出して駆け込んで来て、いきなり肌をぬいだと思ったら背中にお灸のあとがいっぱいあったので、三四郎の記憶に残っている。じいさんが汗をふいて、肌を入れて、女の隣に腰をかけたまでよく注意して見ていたくらいである。"
# 10万レコードを格納する
for i in range(0, 100000):
    # 100通りのグループにランダムに振り分ける
    test_group_id = random.randint(0, 100)
    query = "insert into test (test_id, test_group_id, test_text) values (" + str(i) + "," + str(test_group_id) + ",'" + sanshiro + "')"
    cur.execute(query)

test_group_idが本当にランダムになっていることを確認する。pg_statsにcorrelationカラムをみれば、そのアトリビュートと物理位置との相関がわかる*2。

SELECT tablename, attname, correlation
FROM pg_stats
WHERE tablename = 'test';
tablename attname correlation
test test_id 1
test test_group_id 0.00316498
test test_text 1

1あるいは-1に近いほど整列していることになる。test_group_idは限りなく0に近いため、期待どおりランダムになっている。ちなみにtest_idはINSERTした順に物理的に配置されており、test_textは値が1通りしかないため、どちらもcorrelationは1となっている。

この状態で、test_group_idを条件にもつ参照をEXPLAIN ANALYZEしておく。結果は後ほど比較する。

EXPLAIN ANALYZE
SELECT * FROM test
WHERE test_group_id < 10;

これが、CLUSTERによってどれくらい改善するかを確認する。

CLUSTER test USING test_group_index;

これで、test_group_idで整列されたはず。correlationを確認する*3。

tablename attname correlation
test test_id -0.0072277
test test_group_id 1
test test_text 1

整列されている。この状態で、CLUSTER前と同じく、test_group_idを含む条件でEXPLAIN ANALYZEする。

結果

CLUSTER前と後で、test_group_idを条件とした同一のクエリを用いてEXPLAIN ANAYZEした結果を比較する。

BEFORE

Bitmap Heap Scan on test  (cost=221.74..7861.97 rows=9993 width=549) (actual time=4.556..29.745 rows=9938 loops=1)
  Recheck Cond: (test_group_id < 10)
  Heap Blocks: exact=5470
  ->  Bitmap Index Scan on test_group_index  (cost=0.00..219.24 rows=9993 width=0) (actual time=3.387..3.387 rows=9938 loops=1)
        Index Cond: (test_group_id < 10)
Planning time: 8.375 ms
Execution time: 30.638 ms

AFTER

Bitmap Heap Scan on test  (cost=189.74..7829.97 rows=9993 width=549) (actual time=1.543..4.978 rows=9938 loops=1)
  Recheck Cond: (test_group_id < 10)
  Heap Blocks: exact=710
  ->  Bitmap Index Scan on test_group_index  (cost=0.00..187.24 rows=9993 width=0) (actual time=1.430..1.430 rows=9938 loops=1)
        Index Cond: (test_group_id < 10)
Planning time: 0.076 ms
Execution time: 5.566 ms

なんということでしょう。30 ms だった実行時間が 5 ms になっているではありませんか。

考察

Bitmap Heap Scanになっている。注目すべきは3行目のHeap Blocksで、CLUSTER前は exact=5470 だったのが、CLUSTER後には exact=710となっている。

Bitmap Heap Scanの挙動としては、まず、text_group_indexを条件句で検索し、該当するブロックをワーキングメモリに読み出す。その次に、ブロックの中で条件句に合致するタプルを結果として返す。Heap Blocks: exact: はワーキングメモリに読み出されたブロックの数を表している。

CLUSTERにより、条件に合致するタプルが物理的に整列されることは、ディスク上で同一ブロックに格納されやすくなることを意味する。これにより、ディスクからワーキングメモリに読み出すブロック数が大幅に削減され、パフォーマンスが改善している。

なお、 WHERE test_group_id = 10 などの一致条件でも同様に効果を発揮する。今回は確実にBitmap Heap Scanを選択させるために範囲条件とした*4。

諸注意

めっちゃ速くなる（ことがある）ことを示したが、注意するべきこともある。

まず、ランダムなアクセスの性能は改善しない。上記例で、CLUSTER実行前(INSERTしたての状態)は、test_idのcorrelationが1となっている。これは物理的に整列しているので、test_id < 100 など範囲条件の場合にはディスクリードが最小限になる。しかし、test_id = 5などのように一致条件の場合は、整列していようがいまいが読み込まれるブロック数は1となるため、一致条件による参照が多い場合には効果がない。test_group_idの場合は、 test_group_id = 5 と一致条件だったとしても、複数のタプルを読み込む必要があるため、CLUSTERには一定の効果がある*5。そのため、CLUSTERが効果を発揮する、利用すべきケースとしては、以下が挙げられる。

  • ほとんどの場合に範囲条件つき、あるいは近い値の複合条件で参照される（例：CLUSTER前のtest_id）
  • 同一の値が複数レコードに存在し、一致条件あるいは範囲条件で参照される（例：CLUSTER後のtest_group_id）

次に、物理配置は一通りしかありえないため、トレードオフが生じる。上記例でいうと、test_group_idでCLUSTERすることにより、test_idのcorrelationが0に近い値になっている。そのため、この状態でtest_idの範囲条件による参照はむしろ遅くなる可能性がある。

そして、クラスタは更新系のクエリにより徐々に崩れていく。Postgresは追記型のアーキテクチャを実装しており、あるブロック内のタプルに対するUPDATEが実行された場合、そのタプルは物理的には別のブロックに移動する。そのため、効果を持続させるには、定期的にCLUSTERを実行して整列しなおす必要がある。

さいごに

最近立て続けにPostgresの小ネタを書いているが、基礎的な内容のわりに、意外とウェッブ上にあまり情報がない気がする。こうやって雑に実験してブログに書くことのメリットの一つは、誤った理解があればだれかが教えてくれるということだと思うので、ぜひこのポンコツめに指摘をしてほしいし、それをきっかけにいいかんじになってクリスマスデートとかしたい。おっさんは鶏の生肉喰って吐いてろ。

*1:https://www.postgresql.jp/document/9.6/html/sql-cluster.html

*2:https://www.postgresql.jp/document/9.6/html/view-pg-stats.html

*3:統計情報を更新するためにVACUUM ANALYZEしてから

*4:一致条件の場合、プランナがIndex Scanを選択する場合もあるため

*5:ただし一致条件の場合は、プランナがBitmap Heap ScanではなくIndex Scanを選択する場合もある