PostgreSQL の INDEX ONLY SCAN

こんにちは、石原です。

最近、PostgreSQL で実行計画を見る機会が多かったため、そこから気になった内容について記載していこうと思います。
今回は「INDEX ONLY SCAN」について触れていきます。

※本内容で載せる検証結果は、AWS RDS (PostgreSQL 15.12) 上で実施した内容となります。

実行計画
索引の有無
索引を使った検索
INDEX ONLY SCAN が使用できる条件
実行計画上における判断
「可視」への更新
最後に

実行計画

一般的なところからになりますが、クエリの実行において正しい結果を返すことは当然ですが、クエリを投げてから結果を返すまでの時間が変化することがあります。
色々な要因によって変化はしますが、その中でも重要なのが「実行計画」です。
ユーザーがクエリを投げた際、内部では受け取ったクエリの内容を分析し、「どのようにテーブルを確認するべきか」、また「どの順番で結合させていけば良いのか」など、いち早く結果を返す方法を中で計算して道のりを示してくれるのが「実行計画」です。

本内容では、その中の「どのようにテーブルを確認するべきか」の部分に注目します。

索引の有無

テーブルを確認する方法としてよくあるのが「索引」を用いるか否かです。
一般的に索引を用いることでクエリのパフォーマンスが上がることがあります。
クエリの条件やテーブルのレコード数などにもよりますが、特定のレコードを探そうとした場合、索引が存在しない場合はテーブルをフルスキャン(すべてのレコードをチェック)する動作となる分、不要なブロックアクセスを行うため遅延を招きます。
索引が存在する場合、索引作成時に指定した列の情報を使って、目的のレコード情報をテーブルフルスキャンするよりも早く探し出すことができる可能性があります。
索引作成時に指定した列の値を索引自体が持つので条件などによっては、テーブルへのアクセスを行わずに結果を返すことも可能となります。
なお、索引に実データが入っていることを確認する方法として「bt_page_items」の data 列から確認可能です。
以下の場合、索引で用いている ID1 と ID2 に対して 3 と 9 をデータとして挿入しています。
結果として、data 列に 3 と 9 の出力を確認することができます。

---サンプル表の作成
postgres=> create table exam3(id1 integer,id2 integer,name text);
CREATE TABLE
---複合索引の作成
postgres=> create index exam3idx1 on exam3(id1,id2);
CREATE INDEX
---データの挿入
postgres=> INSERT INTO exam3 values (3,9,'AAA');
INSERT 0 1
---索引の確認
postgres=> select * from bt_page_items('exam3idx1',1);
 itemoffset |  ctid | itemlen | nulls | vars |          data           | dead |  htid | tids
------------+-------+---------+-------+------+-------------------------+------+-------+------
          1 | (0,1) |      16 | f     | f    | 03 00 00 00 09 00 00 00 | f    | (0,1) |
(1 row)

索引を使った検索

索引を使用した検索は大きく二つに分けられます。

通常の INDEX を使った検索 (INDEX→TABLE にアクセスする)

以下の流れでアクセスを行います。
TABLE にアクセスが必要な分、処理時間が長くなります。

①INDEX を使って対象の行を探す。
②ヒープ（TABLE のデータ領域）にアクセスして実際のデータを取得する。

INDEX ONLY SCAN（INDEX だけで完結）

ヒープにアクセスせずに INDEX だけで必要なデータを取得できます。
これにより、TABLE アクセスを行う必要がないため、検索が高速になります。

INDEX ONLY SCAN が使用できる条件

検索が早くなる INDEX ONLY SCAN はどうしたら使用されるようになるのでしょうか。
まとめると以下の条件を全て満たす必要があります。
※条件を満たしても、内部の計算で INDEX ONLY SCAN よりも別の方法の方が早いと PostgreSQL が判断した場合はそちらが選ばれます。

索引の種類が対応していること

B-tree 索引は常に対応しています。
他の索引は非対応ですが、GiST や SP-GiST は一部の演算子クラスのみ対応しています。

クエリが索引に含まれる列だけを参照していること

索引で完結するということは、索引作成時に指定した列情報だけでクエリが完結している必要があります。
以下の例の場合、ID1 列と ID2 列の索引を作成していますが、実行したクエリにおいて ID1 と ID2 のみで完結している場合、INDEX ONLY SCAN が確認できます。
しかしながら、索引に含まれていない ID3 を使用するクエリの場合はINDEX ONLY SCAN にはなっていません。
※実行計画は、クエリ実行前に「explain(analyze,buffers)」といった記載を行うことで確認できます。

---サンプル表の作成
postgres=> create table exam4(id1 integer,id2 integer,id3 integer);
CREATE TABLE
---ID1とID2の複合索引を作成
postgres=> create index exam4idx1 on exam4(id1,id2);
CREATE INDEX
---1000件挿入とVACUUMの実施
postgres=> INSERT INTO exam4 (id1,id2,id3) SELECT i,i,i FROM generate_series(1, 1000) as i;
INSERT 0 1000
postgres=> vacuum exam4;
VACUUM
---ID1とID2を利用したクエリ ⇒ INDEX ONLY SCAN になる
postgres=> explain(analyze,buffers)
postgres-> select id1,id2 from exam4 where id1=1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Index Only Scan using exam4idx1 on exam4 (cost=0.28..4.29 rows=1 width=8) (actual time=0.015..0.016 rows=1 loops=1)
Index Cond: (id1 = 1)
Heap Fetches: 0
Buffers: shared hit=3
Planning Time: 0.090 ms
Execution Time: 0.032 ms
(6 rows)
---ID1とID3を利用したクエリ ⇒ INDEX ONLY SCAN にならない
postgres=> explain(analyze,buffers)
postgres-> select id1,id3 from exam4 where id1=1;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Index Scan using exam4idx1 on exam4 (cost=0.28..8.29 rows=1 width=8) (actual time=0.019..0.021 rows=1 loops=1)
Index Cond: (id1 = 1)
Buffers: shared hit=3
Planning Time: 0.097 ms
Execution Time: 0.036 ms
(5 rows)

検索された各行が問い合わせのスナップショットに対して「可視」であること

実は実行計画上では上記の2つの条件を満たすことで「Index Only Scan」が選択される可能性が出てきます。
ここがこの INDEX ONLY SCAN のトラップとも言える個所です。
実行計画上で「Index Only Scan」となっていても、実際は INDEX ONLY SCAN として実行されていないことがあるということです。
ここで一度動作を整理しておきます。

INDEX ONLY SCAN の動作
①索引にアクセスする
②「可視性マップ」でページの確認
　⇒ 「可視」となっている ⇒ ヒープを見に行かずに済む ⇒ 索引だけの検索になるため早い！
　⇒ 「可視」となっていない ⇒ ヒープを見に行く ⇒ テーブルアクセスが発生するため遅い！

上記について補足します。
クエリを実行した場合、「読み取り一貫性」を実現する動きがあるため、単純に該当のテーブルの全レコード情報を対象としません。
トランザクションを意識し、そのセッションが見えてもよいレコード情報を元にして、そこから条件などによって返すべき結果が出力されます。
ただし、索引にはトランザクションの状況に関しては情報を持っていないため、厳密には索引だけで結果を返すことができません。
厳密に確認するためにテーブル(ヒープ)を見に行く必要がありますが、PostgreSQL ではそこに一工夫加えています。
それが「可視性マップ」です。
可視性マップとは各テーブルの更新状況を応じてページ単位で管理しています。
例えば、あるテーブルのあるページに属するレコードが更新されることによって可視性マップに更新がかかります。
具体的に以下をご確認ください。

---①サンプル表の作成
postgres=> create table exam4(id integer,name text);
CREATE TABLE
postgres=> INSERT INTO exam4 (id,name) SELECT i,md5(random()::text) FROM generate_series(1, 241) as i;
INSERT 0 241
postgres=> create index examidx4 on exam4(id);
CREATE INDEX
postgres=> vacuum exam4;
VACUUM
---②可視性マップの確認
postgres=> select * from pg_visibility_map('exam4');
 blkno | all_visible | all_frozen
-------+-------------+------------
     0 | t           | f
     1 | t           | f
     2 | t           | f
(3 rows)
---③テーブルの状況
postgres=> select ctid,xmin,xmax,id from exam4;
 ctid    | xmin  | xmax | id
---------+-------+------+-----
 (0,1)   | 62700 |    0 |   1　┓
 (0,2)   | 62700 |    0 |   2　┃
：                             ┣ ページ：０
 (0,119) | 62700 |    0 | 119　┃
 (0,120) | 62700 |    0 | 120　┛
 (1,1)   | 62700 |    0 | 121　┓
 (1,2)   | 62700 |    0 | 122　┃
：                             ┣ ページ：１
 (1,119) | 62700 |    0 | 239　┃　
 (1,120) | 62700 |    0 | 240　┛
 (2,1)   | 62700 |    0 | 241　━ ページ：２
(241 rows)
---④UPDATE の実行
postgres=> update exam4 SET id = 1 WHERE id = 1;
UPDATE 1
---⑤可視性マップの確認 
postgres=> select * from pg_visibility_map('exam4');
 blkno | all_visible | all_frozen
-------+-------------+------------
　　　0 | f　★　　　　 | f
　　　1 | t 　　　　　　| f
　　　2 | f　★　　　　 | f
(3 rows)
---⑥テーブルの状況 
postgres=> select ctid,xmin,xmax,id from exam4;
 ctid　　 | xmin　| xmax | id
---------+-------+------+-----
                            ★1
 (0,2)   | 62700 | 0    | 2
：
 (1,120) | 62700 | 0    | 240
 (2,1)   | 62700 | 0    | 241
 (2,2)   | 62717 | 0    | 1 ★2
(241 rows)

①サンプル表の作成
ここでは簡単なテーブル、索引の作成、レコードを241件挿入、vacuum を実行してます。
②可視性マップの確認
「pg_visibility_map」より確認可能です。
blkno 列が各テーブルのページ番号、all_vasible 列が可視を判断する列です。
「t」は可視 OK, 「f」は可視 NG です。
この段階では全て「t」になっているのでテーブルアクセスは不要な状況です。
③テーブルの状況
暗黙的に定義されたシステム列を指定しています。
ctid 列は（<ページ番号>, <レコード番号>）となっており、この行がどのページに属しているか確認することができます。
現在、0 ～ 2 ページを使用しており、これは可視性マップで確認した結果と関連付けることができます。
④UPDATE の実行
データ上では変更ないが ID=1 を ID=1 へ UPDATE しています。
理由については⑥をご参照ください。
⑤可視性マップの確認
UPDATE の実行により、可視性マップに変化が確認できます。
ページ番号が 0 と 2 の可視性が t から f へと変わっています。
⑥テーブルの状況
1 から 1 へ UPDATE しましたが、内部では元々あったレコード(★1)を消し、1 のレコードを追加(★2)する動きをします。
★1 のレコードはページ番号1、★2 のレコードはページ番号2 に属しています。
結果、ページとしては更新があったと認識し、可視性マップでは可視ができない状態へと変更が行われています。

まとめると、可視性マップは各ページに属するレコードが1つでも更新があれば「可視」NG となる動作です。
つまり、「可視」とは更新がないページのことであり、更新がないからわざわざヒープを確認する必要がないため、そのまま索引で確認できた結果を使って返せばよいということがわかります。
また更新があれば、テーブルアクセスが必要になってしまうという動作となります。

よって実行計画を立てる段階では、可視性マップを確認することがないため、一旦は「Index Only Scan」を選択しますが、実際は実行タイミングで可視性マップをみて判断を行っている状況です。

実行計画上における判断

実行計画上から、「Index Only Scan」になっているが、実際にヒープアクセスの有無はどのように判断するべきか説明します。
以下は上記の続きとして WHERE 句に特定の値を指定して実行計画を出力させたものです。
単純に実行しただけではレコード数が少ない分、INDEX ONLY SCAN の条件を満たしてもその通りにならないため、ヒント句も使用しています。
では、判断箇所ですが一緒に出力されている「Heap Fetches」になります。
この値が 1 以上であれば、ヒープアクセスを行っていることになります。
上記の pg_visibility_map の結果から、ページ番号が 0 および 2 は all_visible 列の値が f となり、1 は t となっています。
それを踏まえ、各ページ数に属する値を WHERE 句で指定して実行した結果が以下のとおりです。
ページ番号が 0 および 2 では、「Heap Fetches」が 1 となっており、1 は 0 になっています。
よって、ページ番号 1 に属する値で絞り込みを行った場合のみ、索引だけで結果を返しているといえます。

---ページ番号：０
postgres=> explain(analyze,buffers)
postgres-> /*+ IndexOnlyScan(exam4 examidx4) */
postgres-> select id from exam4 where id=2;
                                                   QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Index Only Scan using examidx4 on exam4 (cost=0.14..8.16 rows=1 width=4) (actual time=0.017..0.018 rows=1 loops=1)
Index Cond: (id = 2)
Heap Fetches: 1 ★
Buffers: shared hit=3
Planning Time: 0.118 ms
Execution Time: 0.030 ms
(6 rows)
---ページ番号：１
postgres=> explain(analyze,buffers)
postgres-> /*+ IndexOnlyScan(exam4 examidx4) */
postgres-> select id from exam4 where id=240;
                                                   QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Index Only Scan using examidx4 on exam4 (cost=0.14..8.16 rows=1 width=4) (actual time=0.014..0.015 rows=1 loops=1)
Index Cond: (id = 240)
Heap Fetches: 0 ★
Buffers: shared hit=2
Planning Time: 0.143 ms
Execution Time: 0.026 ms
(6 rows)
---ページ番号：２
postgres=> explain(analyze,buffers)
postgres-> /*+ IndexOnlyScan(exam4 examidx4) */
postgres-> select id from exam4 where id=241;
                                                   QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Index Only Scan using examidx4 on exam4 (cost=0.14..8.16 rows=1 width=4) (actual time=0.017..0.018 rows=1 loops=1)
Index Cond: (id = 241)
Heap Fetches: 1 ★
Buffers: shared hit=3
Planning Time: 0.166 ms
Execution Time: 0.028 ms
(6 rows)

「可視」への更新

最後に、「可視」できなくなったページを「可視」に戻す方法をまとめます。

戻す方法は VACUUM コマンドになります。
勿論、AUTOVACUUM でも大丈夫です。

---可視性マップの確認
postgres=> select * from pg_visibility_map('exam4');
 blkno | all_visible | all_frozen
-------+-------------+------------
     0 | f           | f
     1 | t           | f
     2 | f           | f
(3 rows)
---VACUUM の実施
postgres=> vacuum exam4;
VACUUM
---可視性マップの確認
postgres=> select * from pg_visibility_map('exam4');
 blkno | all_visible | all_frozen
-------+-------------+------------
     0 | t　★　　　　 | f
     1 | t           | f
     2 | t　★　　　　 | f
(3 rows)

なお、ANALZYE や VACUUM FULL では対応できません。

---可視性マップの確認
postgres=> select * from pg_visibility_map('exam5');
 blkno | all_visible | all_frozen
-------+-------------+------------
     0 | f           | f
     1 | f           | f
(2 rows)
---ANALYZE の実施
postgres=> analyze exam5;
ANALYZE
postgres=> select * from pg_visibility_map('exam5');
 blkno | all_visible | all_frozen
-------+-------------+------------
     0 | f           | f
     1 | f           | f
(2 rows)
---VACUUM FULL の実施
postgres=> vacuum full exam5;
VACUUM
---可視性マップの確認
postgres=> select * from pg_visibility_map('exam5');
 blkno | all_visible | all_frozen
-------+-------------+------------
     0 | f           | f
     1 | f           | f
(2 rows)

AUTOVACUUM で解消するのであれば気にしなくてよいという判断もできますが、INSERT や UPDATE などの DML の更新量によっては対象に選ばれないことがあります。
その場合は明示的に実行する必要が出てきます。
ただし、VACUUM コマンドは必ずしも全てのレコードに対して実施されないことがあります。
その場合は「INDEX_CLEANUP」オプションを使用し、「ON」をつけて実行してください。
そのため、明示的に実施する場合はこちらを実行することをお勧めします。

---①サンプル表の作成
postgres=> create table exam5(id integer);
CREATE TABLE 
postgres=> INSERT INTO exam5 (id) SELECT i FROM generate_series(1, 1000000) as i; 
INSERT 0 1000000
postgres=> create index examidx5 on exam5(id); 
CREATE INDEX 
postgres=> vacuum exam5; 
VACUUM
---②可視性マップの確認
postgres=> select all_visible,count(*) from pg_visibility_map('exam5') group by all_visible;
 all_visible | count
-------------+-------
           t | 4425
(1 row)
---③UDPATE の実施
postgres=> update exam5 set id=id where id <= 20000;
UPDATE 20000
---④可視性マップの確認
postgres=> select all_visible,count(*) from pg_visibility_map('exam5') group by all_visible;
 all_visible | count
-------------+-------
           f |  179　★179ページ分「可視」できていない
           t | 4335
(2 rows)
---⑤VACUUM の実施
postgres=> vacuum exam5;
VACUUM
---⑥可視性マップの確認
postgres=> select all_visible,count(*) from pg_visibility_map('exam5') group by all_visible;
 all_visible | count
-------------+-------
           f | 89　★89ページ分「可視」できていない
           t | 4425
(2 rows)
---⑦INDEX_CLEANUP ON で実施
postgres=> vacuum (INDEX_CLEANUP on) exam5;
VACUUM
---⑧可視性マップの確認
postgres=> select all_visible,count(*) from pg_visibility_map('exam5') group by all_visible;
 all_visible | count
-------------+-------
           t | 4514　★
(1 row)

最後に

今回は INDEX ONLY SCAN について検証と共に見えた結果をまとめてみました。
改めて、AUTOVACUUM は PostgreSQL にとって重要であり、またかなり依存しているようにも思えます。

実行計画の表示だけで、簡単に判断すると痛い目にあうという本内容でしたが、実行計画の奥深さに感動しました。

もし実行計画を確認することがあれば本内容を気にしてもらえると幸いです。