PostgreSQLスキルアップノート（自己啓発のための個人サイト）
インデックスオンリースキャンその２・実機で効果を比較
【一覧に戻る】
マニュアルへのリンクは/9.2/としています。
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
■■■■ PostgreSQL スキルアップノート
■■■■
■◆■■ インデックスオンリースキャンその２・実機で効果を比較
■■■■
■■■■
■■■■ 使用環境：PostgreSQL9.2.3 (CentOS6.2) & PostgreSQL9.1.7 (CentOS6.2)
2013/03/17
2013/03/20
(C) 2013 ohdb
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
9.2で実装されたインデックスオンリースキャンと9.1とでの実行計画、性能比較。
【マニュアル】
第56章データベースの物理的な格納／可視性マップ→●[マニュアル]
付録 E. リリースノート／リリース9.2／パフォーマンス→●[マニュアル]
【マニュアル参考】
−
【その他】
−
■１■ 準備（環境作成）
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
1000万件のテーブルを用意する。
c2が集計キー
c3が集計の対象
c2とc3でひとつのインデックスを構成しているため、インデックスオンリースキャンにより
テーブルデータ（ヒープ）へのアクセスをすることなくインデックスだけで集計できるはず。
【□】 テーブルの作成
┌────────────────────────────┐
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);
└────────────────────────────┘
※c2とc3でひとつの「複数列インデックス」を構成。
【□】 データのインサート
1000万件のインサート。
┌────────────────────────────┐
-- 整数用ランダム値生成(nm1以上nm2以下)
\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
-- \set fetch_count 10000
INSERT INTO t1 SELECT *,:au||:au||:au,:nm,rpad(:nm::text,50,'A'),rpad(:nm::text,50,'A') from generate_series(1,10000000);
└────────────────────────────┘
今回なぜか下の方法に限り大量のメモリを消費してout of memoryとなるため上の方法で回避した。
INSERT INTO t1 SELECT generate_series(1,10000000),:au||:au||:au,:nm,rpad(:nm::text,50,'A'),rpad(:nm::text,50,'A');
何が原因なのか、詳細は不明
【□】 インデックス作成とバキューム／アナライズ
┌────────────────────────────┐
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;
└────────────────────────────┘
c1 | c2 | c3 | c4 | c5
----+-----+--------+----------------------------------------------------+----------------------------------------------------
1 | JKQ | 154853 | 775362AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA | 934948AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
2 | TWM | 910452 | 643772AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA | 997434AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
3 | FFK | 89118 | 800578AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA | 234908AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
4 | TAT | 363670 | 555129AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA | 733625AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
5 | EKK | 51335 | 972468AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA | 767372AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
6 | NJE | 146780 | 511268AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA | 957289AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
7 | CGV | 573706 | 159040AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA | 456027AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
: : : : :
: : : : :
: : : : :
: : : : :
■２■ EXPLAIN文の準備
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
繰り返し利用に便利なようにEXPLAIN文をpsql変数にセットしておく。
【□】EXPLAIN文をpsql変数にいれる。
┌────────────────────────────┐
\timing on
\set ep1 'EXPLAIN (ANALYZE,VERBOSE,BUFFERS)'
\set ep2 'EXPLAIN ANALYZE'
└────────────────────────────┘
以降は
:ep1 SQL文;
:ep2 SQL文;
のようにして実行計画を確認する。
■３■ インデックスオンリースキャンの実行計画を確認・その１
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
バージョン9.2と9.1双方でまったく同じ方法で環境を作成しそれぞれ実行計画を取得した。
【□】:ep1 SELECT c2,sum(c3) FROM t1 GROUP BY c2;
Version 処理時間
----------------------------------------------
9.2 8.5秒 Index Only Scan
9.1 60.1秒 Seq Scan
----------------------------------------------
7.1倍
このSELECTではWHERE条件で絞り込んでいないため、9.2のIndexOnlyScanに対して
9.1ではテーブルへのシーケンススキャン（フルスキャン）となっている。
最初、行長の短い簡単なテーブルで確認したところ、9.2と9.1での性能差は３倍〜４倍程度
に留まっており性能差はそれほどでもなかった。
行長が短い場合、インデックスとヒープのサイズの開きが少ないため驚くほどの顕著な性能
差にはなってこない。
ところが、行長を長くした今回のテーブルでは、
負担の大きなヒープへのアクセスに対して、相対的に小さなインデックスアクセスとなり
より大きな性能差になった。
【9.2の場合】9.2.3
database1=# :ep1 SELECT c2,sum(c3) FROM t1 GROUP BY c2;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=0.00..309865.79 rows=17640 width=8) (actual time=0.871..8500.194 rows=17576 loops=1)
Output: c2, sum(c3)
Buffers: shared hit=7331059 read=27325
->★Index Only Scan using t1_c2c3_idx on public.t1 (cost=0.00..259689.38 rows=10000002 width=8) (actual time=0.080..5072.965 rows=10000000 loops=1)
Output: c2, c3
Heap Fetches: 0
Buffers: shared hit=7331059 read=27325
Total runtime: 8505.238 ms
・・・・t1のt1_c2c3_idxでIndex Only Scan 、その結果を集計。
（補足）Heap Fetches: 0はヒープへのフェッチを一切していないことを示す。
ただ、Index Only ScanだからといってHeap Fetchesが常に0とは限らない。
可視性マップのビットが「クリア」されているページがあればアクセスに行くことになる。
【9.1の場合】9.1.7
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=1964272.92..2039445.87 rows=17508 width=8) (actual time=49452.214..60048.318 rows=17576 loops=1)
Output: c2, sum(c3)
Buffers: shared hit=2739 read=288746, temp read=96439 written=96439
-> Sort (cost=1964272.92..1989272.21 rows=9999716 width=8) (actual time=49451.673..55907.152 rows=10000000 loops=1)
Output: c2, c3
Sort Key: t1.c2
Sort Method: external merge Disk: 175992kB
Buffers: shared hit=2739 read=288746, temp read=96439 written=96439
-> Seq Scan on public.t1 (cost=0.00..391482.16 rows=9999716 width=8) (actual time=9.291..8784.955 rows=10000000 loops=1)
Output: c2, c3
Buffers: shared hit=2739 read=288746
Total runtime: 60094.242 ms
・・・・t1をシーケンススキャン（フルスキャン）、その結果をc2カラムでソート、その結果を集計
■４■ インデックスオンリースキャンの実行計画を確認・その２
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
前項のSELECTで条件をもう少し絞り込んで実行した。
【□】:ep1 SELECT c2,sum(c3) FROM t1 where c2 like 'A%' GROUP BY c2;
Version 処理時間
----------------------------------------------
9.2 0.404秒 Index Only Scan
9.1 53.735秒 Bitmap Index/Bitmap Heap Scan
----------------------------------------------
133.0倍
結果は133倍という性能差となった。
9.2で性能が133倍になったというのは紛れもない事実だが、
その差の原因としては少し注意が必要な結果となった。
詳細は■５■項を追記。
【9.2の場合】9.2.3
database1=# :ep1 SELECT c2,sum(c3) FROM t1 where c2 like 'A%' GROUP BY c2;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=0.00..13347.55 rows=531 width=8) (actual time=1.151..403.515 rows=676 loops=1)
Output: c2, sum(c3)
Buffers: shared hit=282699
-> Index Only Scan using t1_c2c3_idx on public.t1 (cost=0.00..11839.66 rows=300515 width=8) (actual time=0.129..270.553 rows=383959 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=282699
Total runtime: 403.742 ms
・・・・t1のt1_c2c3_idxでIndex Only Scan 、その結果を集計。
【9.1の場合】9.1.7
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=320932.25..320937.60 rows=535 width=8) (actual time=53734.215..53734.575 rows=676 loops=1)
Output: c2, sum(c3)
Buffers: shared hit=3 read=187879 written=2
-> Bitmap Heap Scan on public.t1 (cost=7807.66..319406.23 rows=305203 width=8) (actual time=151.713..49646.275 rows=384463 loops=1)
Output: c1, c2, c3, c4, c5
Filter: (t1.c2 ~~ 'A%'::text)
Buffers: shared hit=3 read=187879 written=2
-> Bitmap Index Scan on t1_c2c3_idx (cost=0.00..7731.36 rows=368601 width=0) (actual time=144.924..144.924 rows=384463 loops=1)
Index Cond: ((t1.c2 >= 'A'::bpchar) AND (t1.c2 < 'B'::bpchar))
Buffers: shared hit=3 read=1050
Total runtime: 53734.835 ms
・・・・t1_c2c3_idx をビットマップインデックススキャン、その結果をビットマップスキャン、その結果をハッシュ集約
■５■ 追加確認・前項の性能差の本当の原因【重要】
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
前項の性能差は本当にインデックスオンリースキャンだけが要因なのだろうか？
実行計画をみていると、そのような疑問が生じてくる。
LIKEを使ったこれだけ緩い絞り込みなら、シーケンススキャン（フルスキャン）の方が速いのでは？
9.1の結果はプランナの誤判断も原因に含まれているのでは？
そこでインデックスへアクセスさせないように制御して確認することにした。
（参考）マニュアル
プランナメソッド設定→●[マニュアル]
「インデックス走査計画型」を無効 set enable_indexscan = off;
「インデックス走査計画型のみ※」を無効 set enable_indexonlyscan = off; ・・※これはindex only scanのこと
「ビットマップ走査計画型」を無効 set enable_bitmapscan = off; ・・・今回これを使用
なお、前項の実行計画より、今回発生しているのは正確には「ビットマップ走査計画型」
のタイプなので今回無効にするのは「ビットマップ走査計画型」である。
・確認の対象はもちろん9.1側の環境です。
【□】 ビットマップ走査型を使用させない設定にして実行
┌────────────────────────────┐
\set ep1 'EXPLAIN (ANALYZE,VERBOSE,BUFFERS)'
\set ep2 'EXPLAIN ANALYZE'
--
set enable_bitmapscan = off;
:ep1 SELECT c2,sum(c3) FROM t1 where c2 like 'A%' GROUP BY c2;
└────────────────────────────┘
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=418007.47..418012.82 rows=535 width=8) (actual time=20466.674..20466.913 rows=676 loops=1)
Output: c2, sum(c3)
Buffers: shared hit=2905 read=288580
-> Seq Scan on public.t1 (cost=0.00..416481.45 rows=305203 width=8) (actual time=21.664..20078.335 rows=384463 loops=1)
Output: c1, c2, c3, c4, c5
Filter: (t1.c2 ~~ 'A%'::text)
Buffers: shared hit=2905 read=288580
Total runtime: 20467.111 ms
★結論★
予想した通り、9.1は必ずしも最適な実行計画とは言えなかった。
インデックスへのアクセスをやめさせ、シーケンススキャン（フルスキャン）とすることで
9.1の「53秒」という性能は「20秒」に短縮された。
インデックスオンリースキャンがなくても20秒で終わるのなら
「133倍の効果」すべてがインデックスオンリースキャンの効果とはいえず、
9.1プランナの誤判断という要因も重なった複合的なものだったということになる。
Version そのまま測定 9.1SQLチューニングあり
-----------------------------------------------------------------------------
9.2 0.404秒 Index Only Scan −
9.1 53.735秒 Bitmap Index/Bitmap Heap Scan 20.467秒 Seq Scan
-----------------------------------------------------------------------------
133.0倍 50.7倍
以上、インデックスオンリースキャンの効果は、「50.7倍」とした。
以上、今回の総合的な確認結果としては、数倍〜50倍程度の性能差という結果になった。
■６■ 可視性マップの状態による影響・・次回
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
インデックスオンリースキャンは可視性マップのビット（1bit/page）をみて、
「そのページ内の行がすべて誰からも可視」（＝ビットが立っている）
の場合のみ、ヒープへのアクセスをスキップする。
上記の確認では、テーブル作成後、バキュームしたもっともよい状態で検証した。
全ページがすべて可視であり、インデックスオンリースキャンにとっては、一番有利な状況
にある。
可視でない「可能性のある」ページが相当数存在している場合は、その部分のインデックス
オンリースキャンができず、性能が悪くなることが予想される。
この関連の検証は次の機会へ。
以上
