PostgreSQLスキルアップノート（自己啓発のための個人サイト）
インデックスオンリースキャンその１・簡単な実行計画確認
【一覧に戻る】
マニュアルへのリンクは/9.2/としています。
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
■■■■ PostgreSQL スキルアップノート
■■■■
■◆■■ インデックスオンリースキャンその１・簡単な実行計画確認
■■■■
■■■■
■■■■ 使用環境：PostgreSQL9.2.3 (CentOS6.2)
2013/03/17
2013/03/20
(C) 2013 ohdb
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
テーブルデータ（ヒープ）へのアクセスをせずインデックスだけで済ませる高速なアクセス。
【マニュアル】
付録 E. リリースノート／リリース9.2／パフォーマンス→●[マニュアル]
【マニュアル参考】
第56章データベースの物理的な格納／可視性マップ→●[マニュアル]
【その他】
−
■１■ 概要
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
インデックスオンリースキャンはインデックスだけをアクセスし、ヒープ（データ）の方は
見に行かないため、処理が高速になる。
9.2で実装されたが、他のDBMSと比較すると決して先進的な機能ということではない。
従来は、インデックスの値だけで結果が出せるようなケースでも必ずヒープへのアクセスが
発生していたが、9.2からはヒープへのアクセスをスキップできるようになった。
ただしフルで機能が働くには条件があるので注意が必要。
■２■ インデックスオンリースキャンがはたらくケース
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
（１）インデックスだけで必要な値がとれる問い合わせであること
問い合わせに必要なカラムがすべてインデックスに収められていなければいけない。
（２）すべてのセッション可視の状態であること。（9.2の場合）
「すべてのセッションから可視」の行だけから成るページの場合に、
ヒープをスキップ（つまりインデックスオンリー）となる。
一言でいえばテーブルが静的な性質に近ければ近いほど条件を満たす。
頻繁に更新が入るような性質のテーブルだと状況によって効果の程度が違う。
（２）の詳細は、仕組み上「可視性マップ」と密接につながっているため
可視性マップも理解する必要がある。→●[サイト内]
第56章データベースの物理的な格納／可視性マップ→●[マニュアル]
┌────────────────────┐
│上記（２）は本ページでは触れません。 │
└────────────────────┘
■３■ 簡単な実行計画確認・準備
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
本ページで使うデータを作成する。
テーブル作成、ランダムデータ生成＆100万件データ挿入、プライマリキー作成、インデックス作成、
バキューム、アナライズ、EXPLAINの準備、をまとめて行う。
【□】 以下をまとめて一度にコピー＆ペーストで実行する
┌────────────────────────────┐
-- ここから ------
-- t1テーブル削除・再作成
DROP TABLE t1;
CREATE TABLE t1 (c1 integer NOT NULL,c2 char(3) NOT NULL,c3 integer NOT NULL,c4 text) WITH (FILLFACTOR=90);
-- ランダムデータ生成・指定した数値範囲
\set nm1 1
\set nm2 1000
\set nm 'floor(:nm1+random()*(:nm2+1-:nm1))'
-- ランダムデータ生成・英字（大文字）
\set au 'chr(floor(65+random()*(90+1-65))::int)'
\set au3 :au||:au||:au
\set au6 :au||:au||:au||:au||:au||:au
-- データ100万件インサート
-- \set fetch_count 10000
\timing on \pset pager off
INSERT INTO t1 SELECT generate_series(1,1000000),:au3,:nm*100,:au6;
-- プライマリ・インデックス・バキュームアナライズ作成
ALTER TABLE t1 ADD PRIMARY KEY (c1);
CREATE INDEX t1_c2c3_idx ON t1(c2,c3);
VACUUM ANALYZE t1;
-- テーブルサンプル表示・件数カウント
\d+ t1
\echo データ件数
SELECT COUNT(*) FROM t1;
\echo データ内容サンプル（先頭の5件）
SELECT * FROM t1 LIMIT 5;
-- EXPLAIN準備
\set ep1 'EXPLAIN (ANALYZE,VERBOSE,BUFFERS)'
\set ep2 'EXPLAIN ANALYZE'
-- ここまで ------
└────────────────────────────┘
実行計画の確認は、以降では
:ep1 SQL文; あるいは
:ep2 SQL文;
のようにして確認することができます。
:ep1や:ep2の定義内容は上のスクリプトを参照して下さい。
なお、ep1の定義内容EXPLAIN (ANALYZE,VERBOSE,BUFFERS) などの記法は、バッファ等、
より詳細な情報を取得する指定で、カッコとともに使います。（9.0以降の構文です）
■４■ インデックスオンリースキャンがはたらくケース
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
作成したテーブルのインデックスは以下の２つ
t1_pkey c1 (PRIMARY KEY)
t1_c2c3_idx c2,c3
とりだす項目がインデックスの中にすべて入っていれば（プランナの判断に応じて）機能は働く
【□】 :ep2 SELECT c1 FROM t1 WHERE c1 = 1000001;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
★Index Only Scan using t1_pkey on t1 (cost=0.00..4.38 rows=1 width=4) (actual time=0.179..0.179 rows=0 loops=1)
Index Cond: (c1 = 1000001)
Heap Fetches: 0
・・・・プライマリキーを使ってIndex Only Scan
とりだすカラムもc1だけなので可能。
【□】 :ep2 SELECT c2,c3 FROM t1 WHERE c2 LIKE 'A%';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
★Index Only Scan using t1_c2c3_idx on t1 (cost=0.00..1324.84 rows=31840 width=8) (actual time=0.092..25.260 rows=38654 loops=1)
Index Cond: ((c2 >= 'A'::bpchar) AND (c2 < 'B'::bpchar))
Filter: (c2 ~~ 'A%'::text)
Heap Fetches: 0
Total runtime: 33.646 ms
・・・・インデックスt1_c2c3_idxを使ってIndex Only Scan
とりだすカラムもインデックスの構成カラム（c2,c3）だけなので可能。
【□】 :ep2 SELECT c2,sum(c3) FROM t1 where c2 like 'A%' group by c2;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=0.00..1489.61 rows=557 width=8) (actual time=0.170..39.071 rows=676 loops=1)
->★Index Only Scan using t1_c2c3_idx on t1 (cost=0.00..1324.84 rows=31840 width=8) (actual time=0.102..24.179 rows=38654 loops=1)
Index Cond: ((c2 >= 'A'::bpchar) AND (c2 < 'B'::bpchar))
Filter: (c2 ~~ 'A%'::text)
Heap Fetches: 0
・・・インデックス・t1_c2c3_idxを使ってIndex Only Scan
とりだすカラムもインデックスの構成カラム（c2とc3の集計値）だけなので可能。
■５■ インデックスオンリースキャンがはたらかないケース
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
【□】 :ep2 SELECT c2,c4 FROM t1 where c2 like 'A%';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on t1 (cost=839.20..8425.80 rows=31840 width=11) (actual time=16.224..69.579 rows=38654 loops=1)
Filter: (c2 ~~ 'A%'::text)
-> Bitmap Index Scan on t1_c2c3_idx (cost=0.00..831.24 rows=39488 width=0) (actual time=14.748..14.748 rows=38654 loops=1)
Index Cond: ((c2 >= 'A'::bpchar) AND (c2 < 'B'::bpchar))
Total runtime: 77.499 ms
・・・・Bitmap Index ScanしてBitmap Heap Scan
c4がインデックスに含まれないため、機能は働かない。
スキャンはビットマップスキャン（ビットマップ走査計画型）という従来タイプのもの（後述）で
インデックスとヒープへアクセスが発生している。
【□】 :ep1 SELECT c1,c2 FROM t1 where c1 < 100001 AND c2 like 'AA%';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
★Bitmap Heap Scan on public.t1 (cost=1918.65..2468.90 rows=30 width=8) (actual time=14.414..14.628 rows=128 loops=1)
Output: c1, c2
Recheck Cond: (t1.c1 < 100001)
Filter: (t1.c2 ~~ 'AA%'::text)
Buffers: shared hit=405
->★BitmapAnd (cost=1918.65..1918.65 rows=155 width=0) (actual time=14.347..14.347 rows=0 loops=1)
Buffers: shared hit=283
->★Bitmap Index Scan on t1_c2c3_idx (cost=0.00..35.61 rows=1525 width=0) (actual time=0.626..0.626 rows=1495 loops=1)
Index Cond: ((t1.c2 >= 'AA'::bpchar) AND (t1.c2 < 'AB'::bpchar))
Buffers: shared hit=7
->★Bitmap Index Scan on t1_pkey (cost=0.00..1882.77 rows=101655 width=0) (actual time=13.470..13.470 rows=100000 loops=1)
Index Cond: (t1.c1 < 100001)
Buffers: shared hit=276
Total runtime: 14.716 ms
すべてのカラムがインデックスに収まっているが、c1はPK、c2はそれとは別のインデックスである。
このような場合、インデックスオンリースキャンはできない（はずである。 詳細未確認）
ただ、２個のインデックス(PKとt1_c2c3_idx）を同時に使った高速なアクセスは可能である。
上の例はビットマップスキャン（ビットマップ走査計画型）というタイプのスキャンにより２個のインデックスを同時に使っている。
ビットマップスキャンはインデックスオンリースキャンではないためヒープへのアクセスは発生する。
ビットマップスキャンは以下を参照。
→●[サイト内]
以上
