PostgreSQLスキルアップノート（自己啓発のための個人サイト）
インデックスオンリースキャンその３・ヒープアクセスが発生するケース
【一覧に戻る】
マニュアルへのリンクは/9.2/としています。
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
■■■■ PostgreSQL スキルアップノート
■■■■
■◆■■ インデックスオンリースキャンその３・ヒープアクセスが発生するケース
■■■■
■■■■
■■■■ 使用環境：PostgreSQL9.2.3 (CentOS6.2)
2013/03/20
(C) 2013 ohdb
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
インデックスオンリースキャンは可視性マップのビット（1bit/page）をみて、
「そのページ内の行がすべて誰からも可視」（＝ビットが立っている）
の場合のみ、ヒープへのアクセスをスキップできる。
更新などが入ると、そのページは可視性マップのビットがクリアされる。
この状態になると、実行計画上はIndexOnlyと言いながらも、実は部分的にヒープへのアク
セスが発生し、性能にかなり影響してしまう。(9.2現在)
この状態はバキュームされるまで続くことになる。
【マニュアル】
第56章データベースの物理的な格納／可視性マップ→●[マニュアル]
付録 E. リリースノート／リリース9.2／パフォーマンス→●[マニュアル]
【マニュアル参考】
−
【その他】
−
■１■ 準備（autovacuum停止）
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
今回の確認に支障がでないように、念のためautovacuumを止める。
【□】 pg_ctl restart -o '-c autovacuum=off'
■２■ 準備（環境作成）
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
1000万件のテーブルを用意する。
方法は前回と同じなので説明省略。
以下をまとめて実行することでデータロード、インデックス、バキューム/アナライズまで完了する。
【□】 テーブルの作成
┌────────────────────────────┐
DROP TABLE t1;
CREATE TABLE t1 (
c1 integer NOT NULL, -- PK
c2 char(3) NOT NULL, -- IDX(t1_c2c3_idx)
c3 integer NOT NULL, -- IDX(t1_c2c3_idx)
c4 text,
c5 text
)
WITH (FILLFACTOR=80);
\set nm1 100
\set nm2 1000000
\set nm 'floor(:nm1+random()*(:nm2+1-:nm1))'
\set au 'chr(floor(65+random()*(90+1-65))::int)'
\timing on
INSERT INTO t1 SELECT *,:au||:au||:au,:nm,rpad(:nm::text,50,'A'),rpad(:nm::text,50,'A') FROM generate_series(1,10000000);
ALTER TABLE t1 ADD PRIMARY KEY (c1);
CREATE INDEX t1_c2c3_idx ON t1(c2,c3);
VACUUM ANALYZE t1;
\pset pager off
SELECT * FROM t1 LIMIT 20;
└────────────────────────────┘
【□】繰り返し使うEXPLAIN文をpsql変数にセット。（ .psqlrcへの登録が便利です）
┌────────────────────────────┐
\set ep1 'EXPLAIN (ANALYZE,VERBOSE,BUFFERS)'
\set ep2 'EXPLAIN ANALYZE'
└────────────────────────────┘
■３■ インサート&VACUUM直後の状態確認
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
【□】 :ep1 SELECT c2,sum(c3) FROM t1 WHERE c2 like 'A%' GROUP BY c2;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=0.00..14554.22 rows=545 width=8) (actual time=0.922..390.878 rows=676 loops=1)
Output: c2, sum(c3)
Buffers: shared hit=283998
-> Index Only Scan using t1_c2c3_idx on public.t1 (cost=0.00..12985.79 rows=312596 width=8) (actual time=0.095..257.157 rows=385763 loops=1)
Output: c2, c3
Index Cond: ((t1.c2 >= 'A'::bpchar) AND (t1.c2 < 'B'::bpchar))
Filter: (t1.c2 ~~ 'A%'::text)
Heap Fetches: 0 ←★注目点
Buffers: shared hit=283998
Total runtime: 391.153 ms
■４■ 更新をかけてみる（全体の１パーセント程度）
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
1000万のうちの１％（10万件）を更新。(非インデックスのc4カラム）
【□】 UPDATE t1 SET c4='AAAAA' WHERE mod(c1,100)=0;
UPDATE 100000
時間: 113594.313 ms
【□】 :ep1 SELECT c2,sum(c3) FROM t1 WHERE c2 like 'A%' GROUP BY c2;
1回目SELECT
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=0.00..15080.49 rows=554 width=8) (actual time=33.303..124821.704 rows=676 loops=1)
Output: c2, sum(c3)
Buffers: shared hit=287604 read=163043 written=33070
-> Index Only Scan using t1_c2c3_idx on public.t1 (cost=0.00..13498.13 rows=315364 width=8) (actual time=0.138..124576.220 rows=383163 loops=1)
Output: c2, c3
Index Cond: ((t1.c2 >= 'A'::bpchar) AND (t1.c2 < 'B'::bpchar))
Filter: (t1.c2 ~~ 'A%'::text)
Heap Fetches: 168679 ←★
Buffers: shared hit=287604 read=163043 written=33070
Total runtime: 124822.218 ms ←※
2回目SELECT
Heap Fetches: 168679 ←★
Buffers: shared hit=287605 read=163042 written=344
Total runtime: 2021.071 ms
3回目SELECT
Heap Fetches: 168679 ←★
Buffers: shared hit=287604 read=163043
Total runtime: 1798.542 ms
・行に更新がかかるとその行が含まれるページは可視性マップ(VM)のビットがクリアされる。
・可視性マップ(VM)のビットがクリアされているページはヒープをスキップできない。
・この状態はバキュームされるまで続く。
つまり、Index Only Scanなのに実はIndex Onlyではなく、ヒープへのアクセスが発生している。
上の例では168679回のヒープアクセスが発生し処理時間も数倍以上悪化した。
なお、同じ行数の更新でも、固まった箇所ではなく広範囲のたくさんのページへ更新を発生させ
てしまうとかなりのロスが発生することになる。
【参考】１回目の極端な性能劣化の理由（※部分）
上記１回目のruntimeが極端に遅い（124秒）のは、インデックスオンリースキャンのヒープアクセス
が主要因ではなく、UPDATEの後始末をSELECTにやらせている影響の方が大きい。
今回の場合、UPDATEによって行の追記が発生し、そのラインポインタのリダイレクトの処理を
後続１回目のSELECTにさせていることから33070ブロックの書き込みが発生（実行計画のwritten)し
それが性能劣化の主要因となっていると思われる。
関連→●[サイト内]参照
したがって１回目124秒というレスポンスは無視し、２回目以降の値に着目した方が今回のテーマには適切。
■５■ VACUUMをかけてみる
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
【□】 VACUUM ANALYZE t1;
【□】 :ep1 SELECT c2,sum(c3) FROM t1 WHERE c2 like 'A%' GROUP BY c2;
1回目SELECT
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=0.00..13831.26 rows=534 width=8) (actual time=1.008..426.781 rows=676 loops=1)
Output: c2, sum(c3)
Buffers: shared hit=280941 read=1028
-> Index Only Scan using t1_c2c3_idx on public.t1 (cost=0.00..12301.58 rows=304869 width=8) (actual time=0.144..287.171 rows=383163 loops=1)
Output: c2, c3
Index Cond: ((t1.c2 >= 'A'::bpchar) AND (t1.c2 < 'B'::bpchar))
Filter: (t1.c2 ~~ 'A%'::text)
Heap Fetches: 0 ←★
Buffers: shared hit=280941 read=1028
Total runtime: 427.099 ms
2回目SELECT
Heap Fetches: 0 ←★
Buffers: shared hit=281969
Total runtime: 392.849 ms
3回目SELECT
Heap Fetches: 0 ←★
Buffers: shared hit=281969
Total runtime: 387.718 ms
バキュームによってHeap Fetchesは0にもどり、処理時間も最初と同レベルに戻った。
以上
