この記事について
本記事は、2022年2月時点で社内向けに作成された技術資料をもとに再編集したものです。
掲載されている技術情報や仕様、価格などは当時の内容に基づいており、現在の状況とは異なる可能性があります。
ご利用の際は、各種公式ドキュメントにて最新情報をご確認のうえ、参考にしていただければ幸いです。
インデックスについて復習
論理的には
あらかじめデータを特定の順序で整理しておくものです。
エクセルで事前にソートしておくイメージに近いかもしれませんが、インデックスは複数作成でき、全体をスキャンせずに特定のデータを見つけることができます。
複合インデックスについて
※「エクセルで事前にソートしておくイメージ」の喩えを続けて使います。※
例えば「苗字」と「名前」で別々のカラムがあるとします。
このとき複合インデックスを作成すると、まず苗字でソートされ、同じ苗字の中ではさらに名前でソートされたような構造になります。 (なので、名前だけでは検索することができません。)
また後述する「カバリングインデックス(Index Only Scan)」を意識して設計することもあります。
実装的には
インデックスの内部構造は、一般的に B木(B-Tree) で実装されています。
B木は、検索・挿入・削除といった操作を効率よく処理でき、検索時間の計算量は O(log n)
です。
出典:PostgreSQL公式ドキュメント – 9.9. B-tree インデックス
https://www.postgresql.org/docs/current/indexes-types.html#INDEXES-TYPES-BTREE
EXPLAIN を読んでみる
実際にステートメントを実行したいときは EXPLAIN ANALYZE
と書きます。
( INSERT
などを間違えて実行しないよう注意してください)
使用したDBはこちらです。
version: '3'
services:
db:
image: postgres:14
container_name: 'db'
environment:
POSTGRES_USER: user
POSTGRES_PASSWORD: user
POSTGRES_DB: user
TZ: Asia/Tokyo
ports:
- "5432:5432"
volumes:
- database:/var/lib/postgresql/data
volumes:
database:
driver: local
docker-compose.yml
スキーマとデータ
数字が入っているだけのシンプルなテーブルです。
name
カラムは、説明のために便宜的に追加しています。
CREATE TABLE IF NOT EXISTS entries (
n INTEGER NOT NULL,
name TEXT
);
CREATE INDEX idx_n ON entries (n);
-- CREATE INDEX idx_n_name ON entries (n, name); -- 複合インデックス
INSERT INTO entries(n, name) SELECT n, '太郎' AS name FROM generate_series(1, 10000000) AS n;
INSERT INTO entries(n, name) VALUES(1000, '花子');
INSERT INTO entries(n, name) SELECT n, 'john' AS name FROM generate_series(1, 10000000) AS n;
Index Scan
インデックスを使用した通常の検索です。
EXPLAIN ANALYZE SELECT
n, name
FROM
entries
WHERE
n = 123456
;
↓
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Index Scan using idx_n on entries (cost=0.44..11.44 rows=2 width=10) (actual time=0.029..0.035 rows=2 loops=1)
Index Cond: (n = 123456)
Planning Time: 0.257 ms
Execution Time: 0.052 ms
(4 rows)
IN
句に多くの値を含めた場合
EXPLAIN ANALYZE SELECT
n, name
FROM
entries
WHERE
n IN (10, 20, 30, 40, 50, 60, 70, 80, 90, 100, 110, 120, 130, 140, 150, 160, 170, 180, 190, 200, 10000, 10001, 10002, 10003, 10004, 10005, 10006);
↓
user=# EXPLAIN ANALYZE SELECT n, name FROM entries WHERE n IN (10, 20, 30, 40, 50, 60, 70, 80, 90, 100, 110, 120, 130, 140, 150, 160, 170, 180, 190, 200, 10000, 10001, 10002, 10003, 10004, 10005, 10006);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_n on entries (cost=0.44..291.23 rows=57 width=10) (actual time=0.016..0.068 rows=54 loops=1)
Index Cond: (n = ANY ('{10,20,30,40,50,60,70,80,90,100,110,120,130,140,150,160,170,180,190,200,10000,10001,10002,10003,10004,10005,10006}'::integer[]))
Planning Time: 0.062 ms
Execution Time: 0.081 ms
(4 rows)
Index Scan (range)
EXPLAIN ANALYZE SELECT
n, name
FROM
entries
WHERE
n BETWEEN 1000000 AND 1000100
;
↓
user=# EXPLAIN ANALYZE SELECT n, name FROM entries WHERE n BETWEEN 1000000 AND 1000100;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Index Scan using idx_n on entries (cost=0.44..628.10 rows=208 width=10) (actual time=0.058..0.218 rows=202 loops=1)
Index Cond: ((n >= 1000000) AND (n <= 1000100))
Planning Time: 0.262 ms
Execution Time: 0.267 ms
(4 rows)
下記は範囲が複数ある場合(今回は2つ)です。
EXPLAIN ANALYZE SELECT
n, name
FROM
entries
WHERE
n BETWEEN 1000000 AND 1000100
OR n < 100
;
↓
user=# EXPLAIN ANALYZE SELECT n, name FROM entries WHERE n BETWEEN 1000000 AND 1000100 OR n < 100;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on entries (cost=12.78..1632.80 rows=423 width=10) (actual time=0.091..0.197 rows=400 loops=1)
Recheck Cond: (((n >= 1000000) AND (n <= 1000100)) OR (n < 100))
Heap Blocks: exact=5
-> BitmapOr (cost=12.78..12.78 rows=423 width=0) (actual time=0.072..0.073 rows=0 loops=1)
-> Bitmap Index Scan on idx_n (cost=0.00..6.52 rows=208 width=0) (actual time=0.049..0.050 rows=202 loops=1)
Index Cond: ((n >= 1000000) AND (n <= 1000100))
-> Bitmap Index Scan on idx_n (cost=0.00..6.05 rows=215 width=0) (actual time=0.021..0.021 rows=198 loops=1)
Index Cond: (n < 100)
Planning Time: 0.250 ms
Execution Time: 0.275 ms
(10 rows)
Index Scan2つを組み合わせて使われているようです。
Seq Scan
インデックスを使用しない検索では、 Seq Scan
と表示されます。
EXPLAIN ANALYZE SELECT
n, name
FROM
entries
WHERE
name = '花子';
↓
user=# EXPLAIN ANALYZE SELECT n, name FROM entries WHERE name = '花子';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..213276.62 rows=1 width=10) (actual time=842.915..846.399 rows=1 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on entries (cost=0.00..212276.52 rows=1 width=10) (actual time=703.329..770.147 rows=0 loops=3)
Filter: (name = '花子'::text)
Rows Removed by Filter: 6666667
Planning Time: 0.451 ms
JIT:
Functions: 6
Options: Inlining false, Optimization false, Expressions true, Deforming true
Timing: Generation 2.176 ms, Inlining 0.000 ms, Optimization 0.631 ms, Emission 9.292 ms, Total 12.100 ms
Execution Time: 847.586 ms
(12 rows)
インデックスが存在していても、使われるとは限りません。
EXPLAIN ANALYZE SELECT
n, name
FROM
entries
WHERE
n < 9000000
;
↓
user=# EXPLAIN ANALYZE SELECT n, name FROM entries WHERE n < 9000000;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Seq Scan on entries (cost=0.00..358111.05 rows=17929078 width=10) (actual time=2.639..1346.411 rows=17999999 loops=1)
Filter: (n < 9000000)
Rows Removed by Filter: 2000002
Planning Time: 0.088 ms
JIT:
Functions: 2
Options: Inlining false, Optimization false, Expressions true, Deforming true
Timing: Generation 0.405 ms, Inlining 0.000 ms, Optimization 0.214 ms, Emission 2.301 ms, Total 2.920 ms
Execution Time: 1751.380 ms
(9 rows)
Index Only Scan
クエリに必要なすべてのカラムがインデックスに含まれているとき(これを「カバリングインデックス」と呼びます)、PostgreSQLは「Index Only Scan」という効率的な実行計画を選択できます。
ですが、インデックスを増やしすぎると、メモリ使用量の増加により キャッシュ効率が下がりパフォーマンスが低下することがあります。
EXPLAIN ANALYZE SELECT
n
FROM
entries
WHERE
n = 32768;
↓
user=# EXPLAIN ANALYZE SELECT n FROM entries WHERE n = 32768;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Index Only Scan using idx_n on entries (cost=0.44..4.47 rows=2 width=4) (actual time=0.161..0.167 rows=2 loops=1)
Index Cond: (n = 32768)
Heap Fetches: 0
Planning Time: 0.174 ms
Execution Time: 0.204 ms
(5 rows)
Rails で EXPLAIN する
今回調べていて初めて知ったのですが、ActiveRecord では #explain
メソッドが使えるようです。
> Item.where(id: 1).explain
Item Load (0.4ms) SELECT "items".* FROM "items" WHERE "items"."id" = $1 [["id", 1]]
=> EXPLAIN for: SELECT "items".* FROM "items" WHERE "items"."id" = $1 [["id", 1]]
QUERY PLAN
------------------------------------------------------------
Seq Scan on items (cost=0.00..1.09 rows=1 width=1401)
Filter: (id = '1'::bigint)
(2 rows)
このケースではデータが少ないのでインデックスが使われず、 Seq Scan
となっています。
参考文献
マークス・ウィナンド『SQLパフォーマンス詳解』、松浦隼人訳
https://sql-performance-explained.jp/
技術情報の補足
本記事でご紹介している手法や事例は、筆者が関わった2022年当時の開発案件におけるナレッジをまとめたものです。
現在の技術スタックやフレームワークのバージョンにおいては、より適切な手法がある場合がありますので、あくまで参考情報としてご覧いただき、必要に応じて最新の公式情報をご確認ください。