テーブルスキャン
postgresqlでは、テーブルスキャンに該当する演算子が5つあります。
- Seq scan: インデックスを使用せず、全件を検索
- Index scan: インデックスを使用してスキャン
- Bitmap scan: ビットマップを使用してスキャン
- Index only scan: 問い合わせがインデックスに含まれるカラムのみで完結する場合のスキャン
- Tid scan: 検索条件がタプルID(ctid)のスキャン
以下より引用
www.slideshare.net
Seq scanというのはいわゆる、フルスキャンでテーブルの中身を全て確認する必要があるため、一般的には動作が遅くなります。その一方でインデックスを適切に設置することで、Index scanとなる場合やIndex only scan、ビットマップインデックスを設定していればBitmap scanが使用されます。
Tid scanはレコードが物理的に格納されている位置を識別するためのctidを使用した検索時に使用されるもので、使用頻度はかなり低いでしょう。またctidはデータの更新、削除によって変わるため、使用には慎重になる必要があります。
補足: ビットマップインデックスについて
インデックスには、広くB-treeインデックスというツリー型のインデックスが使用されています。理由としては全てのケースで安定した性能が出るためです。しかしながら、B-treeインデックスはOR検索では使用することが出来ない、カラムのカーディナリティー(値の種類)が低い場合に、検索効率が悪いというデメリットがあります。
その点、ビットマップインデックスはカラムのカーディナリティー数に対応してビットマップを作成するため、カーディナリティーが低い場合に検索効率が良く、ツリーを辿る必要がなくビットマップを複合することで、OR検索時にもインデックスを使用することが出来ます。
例: 注文テーブルのsizeにビットマップインデックスを作成する
| order_id | kind | size |
|---|---|---|
| 1 | food | M |
| 2 | drink | S |
| 3 | food | M |
| 4 | food | L |
作成されるビットマップ
| size | 1行目 | 2行目 | 3行目 | 4行目 |
|---|---|---|---|---|
| S | 0 | 1 | 0 | 0 |
| M | 1 | 0 | 1 | 0 |
| L | 0 | 0 | 0 | 1 |
作成されるビットマップの大きさ: カラムのカーディナリティー数 x レコード数
どのscanが使用されるかを実際に確認する
以下、結果を確認するためにstudentという適当なテーブルを用意しています。
Table "public.student" Column | Type | Collation | Nullable | Default ------------+-----------------------+-----------+----------+--------- student_id | integer | | not null | name | character varying(50) | | not null | gender | character(1) | | | Indexes: "student_pkey" PRIMARY KEY, btree (student_id) Check constraints: "student_gender_check" CHECK (gender = ANY (ARRAY['0'::bpchar, '1'::bpchar, '2'::bpchar]))
studentにはランダムツールを用いて作成したレコードが1万件、記録してあります。
また、student_idを主キーとして登録しているので、自動的にB-treeインデックスが作成されています。
動作確認: psql (PostgreSQL) 11.15
Seq scan
フルスキャンが必要になるのは、インデックスが作成されていないカラムを用いた検索をする場合や、テーブルの値を全て取得するような場合です。
examples=# EXPLAIN SELECT * FROM student; QUERY PLAN -------------------------------------------------------------- Seq Scan on student (cost=0.00..166.00 rows=10000 width=22) (1 row)
examples=# EXPLAIN SELECT * FROM student WHERE name = 'Coy Champlin'; QUERY PLAN ---------------------------------------------------------- Seq Scan on student (cost=0.00..191.00 rows=1 width=22) Filter: ((name)::text = 'Coy Champlin'::text) (2 rows)
student_id | name | gender ------------+--------------+-------- 1 | Coy Champlin | 1
Index scan
先ほどとは打って変わり、インデックスが作成されているカラムを使用した検索時にはIndex scanが使用されます。Seq scanと比べてテーブルをフルスキャンする必要がないため、同じレコードを取得したにも関わらず、コストが抑えられています。
examples=# EXPLAIN SELECT * FROM student WHERE student_id = 1; QUERY PLAN ----------------------------------------------------------------------------- Index Scan using student_pkey on student (cost=0.29..8.30 rows=1 width=22) Index Cond: (student_id = 1) (2 rows)
student_id | name | gender ------------+--------------+-------- 1 | Coy Champlin | 1
BETWEENやINでもIndex scanとなりました。
examples=# EXPLAIN SELECT * FROM student WHERE student_id BETWEEN 1 AND 100; QUERY PLAN -------------------------------------------------------------------------------- Index Scan using student_pkey on student (cost=0.29..10.29 rows=100 width=22) Index Cond: ((student_id >= 1) AND (student_id <= 100)) (2 rows)
examples=# EXPLAIN SELECT * FROM student WHERE student_id IN (1, 2, 3); QUERY PLAN ------------------------------------------------------------------------------ Index Scan using student_pkey on student (cost=0.29..16.91 rows=3 width=22) Index Cond: (student_id = ANY ('{1,2,3}'::integer[])) (2 rows)
そして、OR検索時には...なんとプランナーがビットマップを作成してBitmap scanを使用してくれました。
examples=# EXPLAIN SELECT * FROM student WHERE student_id = 1 OR student_id = 2 OR student_id = 3; QUERY PLAN --------------------------------------------------------------------------------- Bitmap Heap Scan on student (cost=12.88..23.01 rows=3 width=22) Recheck Cond: ((student_id = 1) OR (student_id = 2) OR (student_id = 3)) -> BitmapOr (cost=12.88..12.88 rows=3 width=0) -> Bitmap Index Scan on student_pkey (cost=0.00..4.29 rows=1 width=0) Index Cond: (student_id = 1) -> Bitmap Index Scan on student_pkey (cost=0.00..4.29 rows=1 width=0) Index Cond: (student_id = 2) -> Bitmap Index Scan on student_pkey (cost=0.00..4.29 rows=1 width=0) Index Cond: (student_id = 3) (9 rows)
コストを見る限り、対象のレコードに対してだけ、アクセスをしてビットマップを作っているようです。
Bitmap scan
ビットマップインデックスが必要になりますが、postgresqlではインデックスの作成時に明示的にビットマップインデックスを作成することが出来ません。ただし、インデックスを作成したカラムに対して検索を実施すると、Bitmap scanとなっています。どういった条件でBitmap scanに切り替わるのかは分かりませんでしたが、プランナーが非常に賢いです。
examples=# CREATE INDEX ON student(gender); examples=# EXPLAIN SELECT * FROM student WHERE gender = '1'; QUERY PLAN ------------------------------------------------------------------------------------- Bitmap Heap Scan on student (cost=65.87..173.13 rows=3301 width=22) Recheck Cond: (gender = '1'::bpchar) -> Bitmap Index Scan on student_gender_idx (cost=0.00..65.04 rows=3301 width=0) Index Cond: (gender = '1'::bpchar) (4 rows)
Index only scan
インデックスが作成されたカラムだけで作成して、SELECT区で取得するカラムをインデックスが作成されたカラムだけにすればIndex only scanとなります。
examples=# EXPLAIN SELECT student_id FROM student WHERE student_id = 1; QUERY PLAN --------------------------------------------------------------------------------- Index Only Scan using student_pkey on student (cost=0.29..8.30 rows=1 width=4) Index Cond: (student_id = 1) (2 rows)
examples=# EXPLAIN SELECT student_id, gender FROM student WHERE student_id = 1; QUERY PLAN ---------------------------------------------------------------------------- Index Scan using student_pkey on student (cost=0.29..8.30 rows=1 width=6) Index Cond: (student_id = 1) (2 rows)
インデックスが作成されていないnameを取得するとIndex scanとなる。
examples=# EXPLAIN SELECT * FROM student WHERE student_id = 1; QUERY PLAN ----------------------------------------------------------------------------- Index Scan using student_pkey on student (cost=0.29..8.30 rows=1 width=22) Index Cond: (student_id = 1) (2 rows)
また、Bitmap scanの場合にはIndex only scanにならないよう。
examples=# EXPLAIN SELECT student_id, gender FROM student WHERE gender = '1'; QUERY PLAN ------------------------------------------------------------------------------------- Bitmap Heap Scan on student (cost=65.87..173.13 rows=3301 width=6) Recheck Cond: (gender = '1'::bpchar) -> Bitmap Index Scan on student_gender_idx (cost=0.00..65.04 rows=3301 width=0) Index Cond: (gender = '1'::bpchar) (4 rows)
Tid scan
使うことがほとんどないと思いますが、試してみます。
examples=# SELECT ctid FROM student WHERE student_id = 1; ctid ------- (0,1)
examples=# EXPLAIN SELECT * FROM student WHERE ctid = '(0,1)'; QUERY PLAN -------------------------------------------------------- Tid Scan on student (cost=0.00..4.01 rows=1 width=22) TID Cond: (ctid = '(0,1)'::tid) (2 rows)
あとがき
僕は所属企業でHR向けのアプリケーションを作っています。今から半年前までの約２年間、サービス拡大のために様々な新機能を作成しては公開してを繰り返しました。しかしながら、当時はアプリケーションは多くの問題を抱えていました。
- コードが複雑で手を加えにくい
- 仕様が複雑で認識違いやバグが多発する
- パフォーマンスが低い(スロークエリ)処理が複数確認された
とても、新機能を開発出来る状態ではなくなりました。
そんな中で、バグ修正をメインで行なってきた自分ですが、最近はデータベースへの関心もあり、スロークエリを探して修正方法はないものかと思案することに夢中になっています。その際に、役に立つのがEXPLAINというクエリでした。
例えば、EXPLAIN SELECT * FROM hoge;とすると、このクエリがどのように実行されるのかというプランナーが作成した結果を見ることが出来ます。しかし、出力結果には何やら意味の分からない言葉がたくさん羅列しています。
examples=# EXPLAIN SELECT * FROM hoge; QUERY PLAN -------------------------------------------------------------- Seq Scan on hoge (cost=0.00..xx.00 rows=xxx width=xx) (1 row)
特にテーブルのスキャンに最もコストがかかるため、いつどんな時にどのスキャンが実行されるのかを判断出来るようにと思い、今回の記事の執筆に至りました。
