どうも。村上です。
今回で4回目のPostgreSQLです。
やっとタイトルにあるIndex Only Scanに来ました。
追記型アーキテクチャ
バキューム
Visibility Map
プラン演算子 ← 前回ココまで
Index Only Scan ← 今回ココ
ではでは、いよいよIndex Only Scanです。
Index Only Scanとは？？？
Index Only Scanとはその名の通り、IndexだけでScanするプラン演算子です！！
前回を思い出して下さい。
Seq Scanはテーブルをスキャンします。
Index ScanはIndexをスキャン後にテーブルにアクセスし、該当行を取得します。
つまり、検索時は必ずテーブルにアクセスする必要がありました。
でも、例えばこんなテーブル
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
Column | Type | Modifiers ------------------+-----------------------------+---------------------------------------------------- id | integer | not null default nextval('users_id_seq'::regclass) login | character varying(255) | not null mail_address | character varying(255) | not null family_name | character varying(255) | not null first_name | character varying(255) | not null family_name_kana | character varying(255) | first_name_kana | character varying(255) | birthday | date | gender | integer | post_number | character varying(255) | prefecture | character varying(255) | city | character varying(255) | address | character varying(255) | building | character varying(255) | telephone | character varying(255) | created_at | timestamp without time zone | updated_at | timestamp without time zone | Indexes: "users_pkey" PRIMARY KEY, btree (id) "users_login_idx" btree (login) |
こんなSQLを実行する時
|
1 |
select login from users; |
わざわざテーブルを参照しなくてもよくないですか？
「Indexをスキャンした時に、取得する項目である『login』はIndex情報にあるので、
テーブルからデータを引っこ抜いて『login』の項目を取得せずとも、Indexにある『login』を取得すれば良いのでは？」
と思います。
つまり、
「Indexから情報が取得できるのなら、テーブルにアクセスせずに、IndexだけのScanでデータを返しましょうよ。」
というのがIndex Only Scanです。
なぜ今までなかったの？？？
Index Only Scanの内容を聞くと当たり前のように聞こえるし、他のDBMSではすでに実装されている機能です。
PostgreSQLにもあって当然のように感じるけど、なぜ今までなかったのでしょうか？
実はPostgreSQLでは、Index Only Scanの実装が難しいとされていました。
それはなぜか？
PostgreSQLが追記型アーキテクチャを採用していたからです。
例えば、login、mail_addressの複合Indexがusersテーブルに作成されていたとします。
その時に、
|
1 |
select mail_address from users where login = 'XXXX'; |
とSQLを実行すると、作成している複合インデックスが使用されます。
複合インデックスのため、mail_addressも同じインデックスにあるので、この値を返せばいいように思いますが、そうではありません。
もし、SELECT実行時にmail_addressの値が別トランザクションから変更されれば、インデックス内のメールアドレスは正しくありません。
つまり、IndexにはMVCCを判断する情報を持っていないため、テーブルにアクセスしてみないと正確な値がわからないのです。
では、バージョン9.2から追加されたIndex Only Scanはどうやって実現されているのでしょうか？
Index Only Scanの実現
MVCCを判断する情報(該当レコードが更新されたのか、削除されたのかという情報)はテーブルにしかないので、PostgreSQLではテーブルの参照なしにデータを取得するのは難しいということでした。
ここで、思い出して下さい。
テーブル以外にもデータが変更されたどうかの情報を持っている機能があったことを。。。。
それは。。。
「Visibility Map」です。
Visibility Mapは「すべてのトランザクションから可視かどうかを1ページ1ビットで持つ」というものでした。
Visibility Mapを参照すれば、可視かどうかわかります。
つまり、トランザクション実行中かどうかが判断でき、データが変更されているかどうかがわかります。
もし、変更されていないのであれば、Indexから対象の項目の値を取得しても問題ありません。
Index Only ScanのExplain
では、Index Only ScanのExplainを見てみましょう。
|
1 2 3 4 |
=# explain select login from users; QUERY PLAN ----------------------------------------------------------------------------------------------- Index Only Scan using users_login_idx on users (cost=0.00..346914.04 rows=10000140 width=19) |
Good!!!
ちなみにselect count(*)でも効きます。
|
1 2 3 4 5 |
=# explain select count(*) from users; QUERY PLAN ----------------------------------------------------------------------------------------------- Aggregate (cost=284701.67..284701.68 rows=1 width=0) -> Index Only Scan using users_pkey on users (cost=0.00..259701.32 rows=10000140 width=0) |
Nice!!!
次にSeq Scan とのコスト差を見てみましょう。
Seq Scan
|
1 2 3 4 5 |
=# explain analyze select login from users; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------- Seq Scan on users (cost=0.00..385071.40 rows=10000140 width=19) (actual time=6.315..9402.347 rows=10000000 loops=1) Total runtime: 10287.434 ms |
Index Only Scan
|
1 2 3 4 5 6 |
=# explain analyze select login from users; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------- Index Only Scan using users_login_idx on users (cost=0.00..346914.04 rows=10000140 width=19) (actual time=16.714..4888.194 rows=10000000 loops=1) Heap Fetches: 0 Total runtime: 5720.321 ms |
はやい！！
まとめ
ということで、Index Only Scanについて説明してきました。
でも、今回で最終回ではありません。
この連載記事のタイトルに「奮闘記」という単語が付いています。
それはなぜか！？
Index Only Scanはなかなかな気分屋なんです。
効いたり効かなかったり、効いても遅かったり。。。
なんで、そこら辺の奮闘した内容を次回に書きたいと思います。
追記型アーキテクチャ
バキューム
Visibility Map
プラン演算子
Index Only Scan ← 今回ココ
奮闘記 ← 追加！！
