【PostgreSQL】INCLUDE句でインデックスオンリースキャンを試してみる
はじめに
PostgreSQLではインデックスオンリースキャンがサポートされており、カバリングインデックスを作成することで実現できます。この記事ではカバリングインデックスによるインデックスオンリースキャンを実際に試してみます。
インデックスオンリースキャンとは
PostgreSQLでは、テーブルのデータが格納されている領域をヒープと呼び、インデックスはヒープとは別の領域に格納されています。
通常のインデックススキャンでは、まずインデックスからデータを取得し、それをもとにヒープにアクセスして実際のデータを取得するという2段階の処理が必要です。インデックス内では類似データが近くに配置されていても、ヒープ上でも物理的に近くにあるとは限りません。そのため、インデックスを使っていても、ヒープへのアクセスがボトルネックになることがあります。
インデックスオンリースキャンは、ヒープへアクセスせず、インデックスだけでクエリに応答する仕組みです。I/Oを大幅に削減できるため、クエリのパフォーマンスを大きく改善できる可能性があります。
インデックスオンリースキャンで実際に性能を向上させるためには、以下の条件を満たす必要があります。
- 使用するインデックスの種類がインデックスオンリースキャンをサポートしていること
- B-Treeインデックスはサポートしていますが、他の種類は一部のみ、または未サポートです
- クエリがインデックスに含まれる列のみを参照していること
- 対象データが可視性マップで「可視」であること
- 可視性マップとは、「このページは全トランザクションから見える」という情報を記録したものです。可視でない場合はヒープへのアクセスが必要になるため、インデックスオンリースキャンが使われたとしても性能向上は見込めません
頻繁に更新されるテーブルの場合は、可視性マップの更新が追いついていない可能性があるため効果は限定的です。一方、更新が少なく読み取りが多いようなデータには有効です。
参考：
カバリングインデックスの作成方法
カバリングインデックスは、CREATE INDEXのINCLUDE句を使って作成します。
CREATE INDEX インデックス名 ON テーブル名 (検索キー列, ...) INCLUDE (追加したい列, ...);
注意点として、INCLUDE句に指定した列は、インデックスを使用した検索や並び替えには使えません。ただし、値はインデックスに格納されているため、フィルタリングはインデックス上だけで行えます。
つまり、WHERE句に指定したとしても、インデックスによる効率的な絞り込みはできません。ただし、インデックスを取得した後であれば、インデックス上に値が格納されていることにより、テーブル本体を見に行かなくても値でフィルタできるということです。
WHERE句で効率よく絞り込みたい列はキー列として指定し、SELECTで取得するだけの列や、キーで絞り込んだ後に追加でフィルタリングしたい列をINCLUDEに指定するのが基本的な使い方です。
試してみる
実際に実行して確認してみます。
データの準備
以下のような架空のテーブルを作成します。
センサーデータを格納したテーブルで、センサーIDや設置場所、気温、湿度などの情報を持ちます。
CREATE TABLE sensor_readings (
id BIGSERIAL PRIMARY KEY,
sensor_id INTEGER NOT NULL,
temperature NUMERIC(5,2) NOT NULL,
humidity NUMERIC(5,2) NOT NULL,
pressure NUMERIC(7,2) NOT NULL,
location VARCHAR(50) NOT NULL,
battery_level SMALLINT NOT NULL,
signal_strength SMALLINT NOT NULL,
recorded_at TIMESTAMP NOT NULL
);
このテーブルに300万件ほどのデータを投入します。
SELECT COUNT(*) AS total_rows FROM sensor_readings;
total_rows
------------
3,000,000
使用するクエリ
アプリケーションは以下の2つのクエリを使用するものとします。
クエリ１：センサーIDと日時から設置場所を取得する
SELECT location
FROM sensor_readings
WHERE
sensor_id = 1 AND
recorded_at BETWEEN '2020-01-01 00:00:00.000' AND '2020-04-01 00:00:00.000';
クエリ２：センサーIDと日時から気温、湿度を取得する
SELECT temperature, humidity
FROM sensor_readings
WHERE
sensor_id = 1 AND
recorded_at BETWEEN '2020-01-01 00:00:00.000' AND '2020-04-01 00:00:00.000';
※本来はパラメータ（ホスト変数）を使用するところですが検証用なので値を直接指定しています。
インデックスを使用しない場合
まずはインデックスなしの状態でクエリの実行計画を確認します。
Gather (cost=1000.00..54815.96 rows=260 width=10) (actual time=420.725..505.208 rows=262.00 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=14268 read=17647
-> Parallel Seq Scan on sensor_readings (cost=0.00..53789.96 rows=108 width=10) (actual time=357.490..437.729 rows=87.33 loops=3)
Filter: ((recorded_at >= '2020-01-01 00:00:00'::timestamp without time zone) AND (recorded_at <= '2020-04-01 00:00:00'::timestamp without time zone) AND (sensor_id = 1))
Rows Removed by Filter: 999913
Buffers: shared hit=14268 read=17647
Planning:
Buffers: shared hit=10
Planning Time: 0.153 ms
Execution Time: 505.255 ms
Parallel Seq Scanとなっており、テーブル全体に対してシーケンシャルスキャンが行われていることがわかります。
loops=3とあるように、ワーカーが3つに分かれて並列実行されています。Rows Removed by Filter: 999913 から、1ループあたり約100万件のデータをスキャンしていることが読み取れます。
通常のB-Treeインデックスを使用した場合
sensor_idとrecorded_atを含む複合インデックスを作成します。
CREATE INDEX idx_normal ON sensor_readings (sensor_id, recorded_at);
この状態で実行計画を確認します。
クエリ１
Bitmap Heap Scan on sensor_readings (cost=7.75..978.30 rows=260 width=10) (actual time=0.069..0.328 rows=262.00 loops=1)
Recheck Cond: ((sensor_id = 1) AND (recorded_at >= '2020-01-01 00:00:00'::timestamp without time zone) AND (recorded_at <= '2020-04-01 00:00:00'::timestamp without time zone))
Heap Blocks: exact=262
Buffers: shared hit=266
-> Bitmap Index Scan on idx_normal (cost=0.00..7.68 rows=260 width=0) (actual time=0.037..0.038 rows=262.00 loops=1)
Index Cond: ((sensor_id = 1) AND (recorded_at >= '2020-01-01 00:00:00'::timestamp without time zone) AND (recorded_at <= '2020-04-01 00:00:00'::timestamp without time zone))
Index Searches: 1
Buffers: shared hit=4
Planning Time: 0.066 ms
Execution Time: 0.350 ms
クエリ２
Bitmap Heap Scan on sensor_readings (cost=7.75..978.30 rows=260 width=12) (actual time=0.072..0.306 rows=262.00 loops=1)
Recheck Cond: ((sensor_id = 1) AND (recorded_at >= '2020-01-01 00:00:00'::timestamp without time zone) AND (recorded_at <= '2020-04-01 00:00:00'::timestamp without time zone))
Heap Blocks: exact=262
Buffers: shared hit=266
-> Bitmap Index Scan on idx_normal (cost=0.00..7.68 rows=260 width=0) (actual time=0.037..0.038 rows=262.00 loops=1)
Index Cond: ((sensor_id = 1) AND (recorded_at >= '2020-01-01 00:00:00'::timestamp without time zone) AND (recorded_at <= '2020-04-01 00:00:00'::timestamp without time zone))
Index Searches: 1
Buffers: shared hit=4
Planning Time: 0.068 ms
Execution Time: 0.326 ms
インデックスが使われるようになり、実行時間が劇的に改善しました。
Heap Blocks: exact=262は、262個のヒープブロックにアクセスしていることを示しており、インデックスで絞り込んだ後もテーブル本体（ヒープ）にアクセスしていることがわかります。
クエリ１ではlocation、クエリ２ではtemperatureとhumidityを取得する必要がありますが、これらの値はインデックスに含まれておらず、ヒープを参照する必要があるためです。
カバリングインデックスを使用した場合
クエリ１が取得しているlocation列をインデックスに含めたカバリングインデックスを作成します。
INCLUDE句で指定した列はインデックスにその値が格納されるため、ヒープへのアクセスなしに値を返せるようになります。
※ただし、INCLUDE句で指定した列はインデックスの検索キーにはなりません。例えば、WHERE location = 'hoge'のようにキー列を使わずlocationだけで絞り込む場合、このインデックスによる効率的な検索は行われません。
CREATE INDEX idx_covering_location ON sensor_readings (sensor_id, recorded_at) INCLUDE (location);
実行計画を確認します。
クエリ１
Index Only Scan using idx_covering_location on sensor_readings (cost=0.43..14.28 rows=260 width=10) (actual time=0.031..0.053 rows=262.00 loops=1)
Index Cond: ((sensor_id = 1) AND (recorded_at >= '2020-01-01 00:00:00'::timestamp without time zone) AND (recorded_at <= '2020-04-01 00:00:00'::timestamp without time zone))
Heap Fetches: 0
Index Searches: 1
Buffers: shared hit=5
Planning Time: 0.080 ms
Execution Time: 0.073 ms
Index Only Scanと表示されており、インデックスオンリースキャンになっていることがわかります。
Heap Fetches: 0でヒープへのアクセスがゼロになっていることが読み取れます。
クエリ２
Bitmap Heap Scan on sensor_readings (cost=7.75..978.30 rows=260 width=12) (actual time=0.086..0.355 rows=262.00 loops=1)
Recheck Cond: ((sensor_id = 1) AND (recorded_at >= '2020-01-01 00:00:00'::timestamp without time zone) AND (recorded_at <= '2020-04-01 00:00:00'::timestamp without time zone))
Heap Blocks: exact=262
Buffers: shared hit=266
-> Bitmap Index Scan on idx_normal (cost=0.00..7.68 rows=260 width=0) (actual time=0.046..0.046 rows=262.00 loops=1)
Index Cond: ((sensor_id = 1) AND (recorded_at >= '2020-01-01 00:00:00'::timestamp without time zone) AND (recorded_at <= '2020-04-01 00:00:00'::timestamp without time zone))
Index Searches: 1
Buffers: shared hit=4
Planning Time: 0.084 ms
Execution Time: 0.379 ms
クエリ２で取得するtemperatureとhumidityは今回作成したカバリングインデックスには含まれていません。そのため idx_covering_locationではなく通常のidx_normalが選択され、Bitmap Heap Scanになっています。
両方のクエリをカバーするカバリングインデックスを使用した場合
クエリ１とクエリ２の両方が参照する列をすべてINCLUDEに指定したインデックスを作成します。
CREATE INDEX idx_covering_all ON sensor_readings (sensor_id, recorded_at) INCLUDE (location, temperature, humidity);
実行計画を確認します。
クエリ１
Index Only Scan using idx_covering_all on sensor_readings (cost=0.43..14.28 rows=260 width=10) (actual time=0.033..0.082 rows=262.00 loops=1)
Index Cond: ((sensor_id = 1) AND (recorded_at >= '2020-01-01 00:00:00'::timestamp without time zone) AND (recorded_at <= '2020-04-01 00:00:00'::timestamp without time zone))
Heap Fetches: 0
Index Searches: 1
Buffers: shared hit=5
Planning Time: 0.100 ms
Execution Time: 0.115 ms
クエリ２
Index Only Scan using idx_covering_all on sensor_readings (cost=0.43..14.28 rows=260 width=12) (actual time=0.026..0.054 rows=262.00 loops=1)
Index Cond: ((sensor_id = 1) AND (recorded_at >= '2020-01-01 00:00:00'::timestamp without time zone) AND (recorded_at <= '2020-04-01 00:00:00'::timestamp without time zone))
Heap Fetches: 0
Index Searches: 1
Buffers: shared hit=5
Planning Time: 0.086 ms
Execution Time: 0.076 ms
クエリ１、クエリ２のどちらもIndex Only Scanになりました。
どちらもHeap Fetches: 0で、ヒープへのアクセスが完全になくなっています。
INCLUDE列を検索キーとして使用した場合
クエリ２で、temperature列をWHERE句に指定するように修正します。
SELECT temperature, humidity
FROM sensor_readings
WHERE
sensor_id = 1 AND
temperature BETWEEN 0 AND 2;
実行計画は以下となりました。
Index Only Scan using idx_covering_all on sensor_readings (cost=0.43..159.55 rows=128 width=12) (actual time=0.373..0.374 rows=0.00 loops=1)
Index Cond: (sensor_id = 1)
Filter: ((temperature >= '0'::numeric) AND (temperature <= '2'::numeric))
Rows Removed by Filter: 3000
Heap Fetches: 0
Index Searches: 1
Buffers: shared hit=26
Planning Time: 0.181 ms
Execution Time: 0.396 ms
Index Only Scanとなっており、ヒープへのアクセスはありません。
インデックスから対象データを検索する際は、sensor_idのみが使用されています。つまり、temperatureはインデックスの検索には使用されていません。ただし、その後temperatureの値でフィルタリングする際に、インデックス上に格納された値を使用できます。そのため、ヒープへのアクセスはなしとなっています。
次に、temperatureだけでフィルタリングする場合を確認します。
SELECT temperature, humidity
FROM sensor_readings
WHERE
temperature BETWEEN 0 AND 2;
Gather (cost=1000.00..64578.40 rows=129134 width=12) (actual time=0.220..750.457 rows=126000.00 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=3656 read=28259
-> Parallel Seq Scan on sensor_readings (cost=0.00..50665.00 rows=53806 width=12) (actual time=4.408..710.105 rows=42000.00 loops=3)
Filter: ((temperature >= '0'::numeric) AND (temperature <= '2'::numeric))
Rows Removed by Filter: 958000
Buffers: shared hit=3656 read=28259
Planning Time: 0.082 ms
Execution Time: 755.510 ms
Parallel Seq Scanとなり、インデックスが使われずフルスキャンになったことがわかります。
インデックスのサイズ比較
カバリングインデックスはヒープへのアクセスを減らせる一方で、INCLUDEで追加した列の値をインデックスに持つ分、インデックスサイズが大きくなります。
各インデックスのサイズを確認してみます。
SELECT
indexrelname,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE relname = 'sensor_readings'
ORDER BY pg_relation_size(indexrelid) DESC;
indexrelname | index_size
-------------------------------------
idx_covering_all | 178 MB
idx_covering_temp_hum | 142 MB
idx_covering_location | 131 MB
idx_normal | 90 MB
sensor_readings_pkey | 64 MB
INCLUDEに指定する列が増えるほどインデックスサイズが大きくなっていることがわかります。例えば、idx_normalと比べると、idx_covering_allは約2倍になっています。
そのため、以下のデメリットに注意する必要があります。
- ディスク容量をより多く消費する
- テーブルの更新操作が遅くなる
カバリングインデックスに限らず通常のインデックスについてもいえることですが、サイズと性能のトレードオフを考慮しながら、本当に必要なクエリに対してのみ適用することが重要です。
複合インデックスとの違い
次のようにlocationもtemperatureもhumidityも全部インデックスのキー列に含めた場合でも、実行計画としてはインデックスオンリースキャンとなります。
CREATE INDEX idx_all ON sensor_readings (sensor_id, recorded_at, location, temperature, humidity);
B-Treeインデックスはソートされた状態で保持されるため、これらの列はソートキーになります。このとき不要な列が含まれているとソートが意図しない結果となる可能性があります。
そのため、SELECTのみに使用したい列は複合インデックスではなく、INCLUDEに指定する方が無駄なく必要な処理が行えます。
まとめ
カバリングインデックスを使用したインデックスオンリースキャンと、実行計画を確認してみました。
この記事がどなたかの参考になれば幸いです。
