PostgreSQLスキルアップノート（自己啓発のための個人サイト）
データベースページ・ページ内に空きがある場合のUPDATE（ラインポインタのリダイレクト）
【一覧に戻る】
マニュアルへのリンクは/9.2/としています。
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
■■■■ PostgreSQL スキルアップノート
■■■■
■◆■■ データベースページ・ページ内に空きがある場合のUPDATE（ラインポインタのリダイレクト）
■■■■
■■■■
■■■■ 使用環境：PostgreSQL9.2.3 (CentOS6.2)
2013/03/20
(C) 2013 ohdb
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
追記型のPostgreSQLでは、UPDATE前後のデータは物理的に全くの別物（別の行）なので、
UPDATEが発生すると、行の追記と同時にインデックス側も新しく追記しなくてはならなかっ
た。（たとえインデックス項目以外のUPDATEであったとしても）
しかし、HOT（HOT更新）の登場によって、同一ページ内、かつインデックスのカラムでなけ
れば、「ラインポインタのリダイレクト（付け替え）」が行われ、これによってインデック
ス側を書き換えなくても、インデックスから行への対応付けが維持できる。
「ラインポインタのリダイレクト」は、VACUUMを待たなくても、UPDATE後にアクセスする別
のSQL文が勝手にやってくれている。
【マニュアル】
付録 F. 追加で提供されるモジュール・pageinspect→●[マニュアル]
【マニュアル参考】
−
【その他】
−
■１■ autovacuum停止
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
今回の確認に支障がでないように、autovacuumを止める。
【□】 pg_ctl restart -o '-c autovacuum=off'
■２■ テーブルの作成
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
【□】 テーブル作成し約１００万件のデータを挿入 (1000001件）
┌────────────────────────────┐
DROP TABLE t1;
CREATE TABLE t1 (
c1 int,
c2 text,
c3 text,
c4 text,
c5 text
) WITH (FILLFACTOR=80);
\timing on
INSERT INTO t1 SELECT *,*,*,*,* FROM generate_series(1000000,2000000);
CREATE INDEX t1_c2_idx ON t1(c2);
VACUUM ANALYZE t1;
\pset pager off
SELECT * FROM t1 LIMIT 20;
└────────────────────────────┘
■３■ UPDATE実行
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
１．UPDATE実行
──────────────────────────────
【□】 UPDATE t1 SET c4='AAAAAAA' WHERE mod(c1,10)=0;
UPDATE 100001
時間: 2822.563 ms
２．ページの状態出力
──────────────────────────────
【□】 以下によりページの状態を出力。 （bashコマンドラインより一度にコピー＆ペーストする）
以下スクリプトの詳細は →●[サイト内]
# ==ここから====
DBNAME=database1 # 対象のデータベース名を入力。
TBL=t1 # 対象のテーブル名を入力。
BLKNO=0 # 対象のブロック番号を入力。
FORK=main # main,fsm,vmのいずれか。
(
echo "database= ${DBNAME} table= ${TBL} fork= ${FORK} blk= ${BLKNO} "`date "+%Y-%m-%d %H:%M:%S"`
echo ""
psql "$DBNAME" -P pager=off -c "SELECT * from heap_page_items(get_raw_page('$TBL','$FORK',$BLKNO))";
psql "$DBNAME" -P pager=off -t -A -c "SELECT get_raw_page('$TBL','$FORK',$BLKNO)"\
|awk '{skp=2;print substr($0,1,skp);i=skp+1;print " OFFSET";\
while (lin<=127) {printf("%4d-%4d %-128s\n",lin*64,lin*64+63,substr($0,i,128));lin++;i=i+128}}'\
)\
|tee page_${DBNAME}_${TBL}_${FORK}_${BLKNO}_`date "+%Y%m%d_%H%M%S"`.txt
# ==ここまで====
３．出力結果の確認
──────────────────────────────
実際の出力結果は、■資料１■を参照。→●[サイト内]
資料１の前半はheap_page_itemsにより得られたlpの一覧。
後半はget_raw_pageにより得られた16進のダンプ。
確認は主に前半部分の内容が中心。
後半の16進のダンプの方は参考程度に記載。
ちなみに前半の「lp_off」の値から16進ダンプの対応する位置を確認できる。
資料１からわかること
・このページ内にあるlp（ラインポインタ）は計106個である。
・うちt_max（削除XID）に値のあるものは更新によって削除された行のlpである。
lp=1,11,21,31,41,51,61,71,81,91の計10個
・lp=97〜106がUPDATEで新たに発生したlpである。
・ページ内に余裕があった（FILFACTOR=80)ので、UPDATEによる追記行はすべて元と同じページ内に追記されている。
■４■ UPDATE後にSELECT実行
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
１．SELECT実行
──────────────────────────────
【□】 EXPLAIN (ANALYZE,VERBOSE,BUFFERS) SELECT * from t1 WHERE c1=0;
１回目
database1=# EXPLAIN (ANALYZE,VERBOSE,BUFFERS) SELECT * from t1 WHERE c1=0;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Seq Scan on public.t1 (cost=0.00..22917.01 rows=1 width=36) (actual time=1385.646..1385.646 rows=0 loops=1)
Output: c1, c2, c3, c4, c5
Filter: (t1.c1 = 0)
Rows Removed by Filter: 1000001
Buffers: shared hit=1 read=10416 written=4005 ★書き込みwrittenが発生
Total runtime: 1385.676 ms
２回目
database1=# EXPLAIN (ANALYZE,VERBOSE,BUFFERS) SELECT * from t1 WHERE c1=0;
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Seq Scan on public.t1 (cost=0.00..22917.01 rows=1 width=36) (actual time=117.044..117.044 rows=0 loops=1)
Output: c1, c2, c3, c4, c5
Filter: (t1.c1 = 0)
Rows Removed by Filter: 1000001
Buffers: shared hit=4006 read=6411 ★書き込みは発生しない
Total runtime: 117.072 ms
以上のように１回目のSELECTの中でwrittenが発生している。
ここでラインポインタのリダイレクトが行われている。
２．ページの状態出力
──────────────────────────────
【□】 前項の「２．ページの状態出力」と同じ方法でページ状態を出力する。
３．出力結果の確認
──────────────────────────────
実際の出力結果は、■資料２■を参照。→●[サイト内]
資料２からわかること
UPDATE直後のSELECTによってブロックのデータに対して行われた主な書き込み内容は以下の通り。
・lp_flagsを「２」としてラインポインタのリダイレクトを行った。
(lp=1,11,21,31,41,51,61,71,81,91の計10個のlp)
この場合のlp_offはバイト位置を示すoffset値ではなくリダイレクト先のラインポインタが入る。
例：lp=1はlp=97にリダイレクトしたと読み取れる。
この書き込みによって、インデックス側からヒープ側に参照がきても、付け替え先の行の内容を正しく
取得することができる。
■５■ まとめ
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
今回のケースでは、非IDXカラムのUPDATEでかつ、ページ内に空きがあったので、
UPDATEでは追記行は同じブロックの中に書き込まれ、その後に実行したSELECTでライン
ポインタのリダイレクト処理が行われた。
それによってSELECT文の中で「written」が4005回発生したことが実行計画から読み取れる。
１回目の処理時間が２回目より10倍以上悪くなるのは、単にバッファヒットだけの問題で
はないということが分かる。
もちろん、その前にVACUUMがかかれば、VACUUMが処理してくれる。
■６■ 参考比較
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
HOT更新でないUPDATEならラインポインタのリダイレクトは発生しない。
たとえば、ページに空きがなかったり、UPDATE対象がインデックスカラムの場合など。
ページに空きがないケース
→●[サイト内]
しかしこの場合でも後続SQLによるwrittenはHOTの場合と同様に発生する。
この場合はページ内で参照先を付け替えることができないので、lp_flags=2(LP_REDIRECT)
ではなく3（LP_DEAD)への書き換えが行われている。
したがって大量UPDATE直後の別SQLで性能劣化する事象は、HOT更新に限ったものではない。
ちなみにページに空きはあっても、インデックスカラムをUPDATEした場合は、同じページ内
に行が追記されるもののランポインタのリダイレクトは発生せず、その代わりに上記同様、
3（LP_DEAD)への書き換えが行われている。
以上
