はじめに
SQLを書いていて「このクエリ、なんか遅いな」と感じたとき、一番最初に疑うべきはインデックスだと思っています。今日はそのインデックスについて、実際にローカルでDBを立てて手を動かしながら、どこに貼るのがベストプラクティスなのかを検証していこうと思います。
結論から言うと、インデックスは「WHERE・JOIN・ORDER BYで使われる列」に貼るのが基本で、それ以外の場所にむやみに貼ると逆に遅くなることすらあります。この感覚を、実際の実行計画を見ながら確認していきます。
1. まずはローカルにDB環境を作る
今回はDockerでPostgreSQLを1本立てて検証します。手元にDockerさえあれば数分で用意できます。
docker run --name pg-index-demo -e POSTGRES_PASSWORD=postgres -p 5432:5432 -d postgres:16
起動したら、psqlで接続します。
docker exec -it pg-index-demo psql -U postgres
検証用に、注文データを想定したテーブルを作ります。実際のサービスでありがちな「usersテーブル」と「ordersテーブル」の2つです。
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email TEXT NOT NULL,
status TEXT NOT NULL
);
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INT NOT NULL,
status TEXT NOT NULL,
created_at TIMESTAMP NOT NULL
);
データが少ないとインデックスの効果が見えないので、100万件のダミーデータを流し込みます。
INSERT INTO users (email, status)
SELECT 'user' || i || '@example.com', 'active'
FROM generate_series(1, 100000) AS i;
INSERT INTO orders (user_id, status, created_at)
SELECT
(random() * 99999 + 1)::int,
(ARRAY['pending','paid','shipped','cancelled'])[floor(random()*4+1)],
NOW() - (random() * 365 || ' days')::interval
FROM generate_series(1, 1000000) AS i;
2. インデックスなしで検索してみる
まずは何も手を加えず、特定ユーザーの注文を検索してみます。
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 12345;
手元で実際に実行してみると、こういう実行計画が返ってきました。
Gather (cost=1000.00..12841.43 rows=11 width=23) (actual time=12.597..29.450 rows=8 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on orders (cost=0.00..11840.33 rows=5 width=23) (actual time=6.466..19.566 rows=3 loops=3)
Filter: (user_id = 12345)
Rows Removed by Filter: 333331
Planning Time: 0.216 ms
Execution Time: 29.508 ms
「Parallel Seq Scan」というのは、テーブルを2つのワーカーで手分けしながら頭から全部舐めて条件に合う行を探しているということです。100万行のうち欲しいのはたった8行なのに、並列化してもなお29.5msかかっています。これがインデックスがない状態の実態です(ちなみに手元の環境ではワーカーが2つ自動的に割り当てられましたが、CPUコア数や設定によっては単純な「Seq Scan」1本になることもあります)。
3. インデックスを貼ってみる
ではuser_id列にインデックスを貼ってみます。
CREATE INDEX idx_orders_user_id ON orders (user_id);
もう一度同じクエリを実行してみます。
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 12345;
Bitmap Heap Scan on orders (cost=4.51..47.30 rows=11 width=23) (actual time=0.028..0.038 rows=8 loops=1)
Recheck Cond: (user_id = 12345)
Heap Blocks: exact=8
-> Bitmap Index Scan on idx_orders_user_id (cost=0.00..4.51 rows=11 width=0) (actual time=0.024..0.024 rows=8 loops=1)
Index Cond: (user_id = 12345)
Planning Time: 0.207 ms
Execution Time: 0.066 ms
実行時間が29.5msから0.066ms、400倍以上速くなりました。「Bitmap Heap Scan」は、まずインデックス側で該当する行の場所だけをビットマップとして特定し(Bitmap Index Scan)、そのあとで実際に必要なブロックだけをテーブル本体から読みにいく、という2段構えの読み方です。取得件数がある程度あるときにPostgreSQLがよく選ぶ実行計画で、件数がごく少なければ単純な「Index Scan」になることもあります。いずれにしても、テーブル全体を読むのではなくインデックスという「目次」を使って必要な行にピンポイントでアクセスできるようになったからこそ、この差が出ています。数字で見ると、インデックスの効果がここまではっきり出るのかと、実際に試すたびに驚きます。
4. どこにインデックスを貼るのがベストプラクティスか
ここまでで効果は分かったので、次は「どこに貼るべきか」を整理していきます。
WHERE句・JOIN句で絞り込みに使う列
今回のuser_idのように、検索条件やJOINの結合キーになる列は最優先で候補にすべきです。特に外部キーとして使う列には、迷わずインデックスを貼っています。
ORDER BYやGROUP BYで使う列
並び替えや集計に使う列にもインデックスは効きます。試しにcreated_at列で並び替えてみます。
EXPLAIN ANALYZE
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20;
インデックスがない状態では、こうなりました。
Limit (cost=30503.05..30505.38 rows=20 width=24) (actual time=64.656..67.883 rows=20 loops=1)
-> Gather Merge (cost=30503.05..127732.14 rows=833334 width=24) (actual time=64.637..67.861 rows=20 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=29503.03..30544.69 rows=416667 width=24) (actual time=60.649..60.651 rows=17 loops=3)
Sort Key: created_at DESC
Sort Method: top-N heapsort Memory: 27kB
-> Parallel Seq Scan on orders (cost=0.00..18415.67 rows=416667 width=24) (actual time=4.635..32.824 rows=333333 loops=3)
Planning Time: 0.311 ms
Execution Time: 68.084 ms
全行を並列でスキャンしてから上位20件だけをソートで抜き出しているため、68msかかっています。ここでcreated_atにインデックスを貼ってみます。
CREATE INDEX idx_orders_created_at ON orders (created_at DESC);
Limit (cost=0.42..2.08 rows=20 width=24) (actual time=0.024..0.084 rows=20 loops=1)
-> Index Scan using idx_orders_created_at on orders (cost=0.42..82966.79 rows=1000000 width=24) (actual time=0.023..0.080 rows=20 loops=1)
Planning Time: 0.390 ms
Execution Time: 0.105 ms
68msから0.105msに短縮されました。インデックスがソート済みの順序をあらかじめ持っているため、その順番をそのまま辿るだけで上位20件が取れるようになったからです。
カーディナリティ(値の種類の多さ)を意識する
ここは見落としがちなポイントです。試しに、値の種類が4つしかないstatus列にインデックスを貼ってみます。
CREATE INDEX idx_orders_status ON orders (status);
EXPLAIN ANALYZE
SELECT * FROM orders WHERE status = 'paid';
Bitmap Heap Scan on orders (cost=2835.89..12641.23 rows=253867 width=23) (actual time=4.482..25.556 rows=249556 loops=1)
Recheck Cond: (status = 'paid'::text)
Heap Blocks: exact=6632
-> Bitmap Index Scan on idx_orders_status (cost=0.00..2772.43 rows=253867 width=0) (actual time=3.872..3.873 rows=249556 loops=1)
Index Cond: (status = 'paid'::text)
Planning Time: 0.225 ms
Execution Time: 30.955 ms
インデックスは一応使われていて(Bitmap Heap Scan)、インデックスを外して同じクエリを実行すると62msかかったので、効果はゼロではありません。ただしuser_idのときの400倍のような劇的な差ではなく、せいぜい2倍程度です。全体の4分の1(約25万行)がpaidに該当するため、1行ずつインデックス経由で拾うメリットがそこまで大きくならないからです。
では、もっとカーディナリティが低い場合はどうなるか。試しに、trueとfalseがほぼ50%ずつのis_paid列(BOOLEAN)にインデックスを貼ってみます。
ALTER TABLE orders ADD COLUMN is_paid BOOLEAN;
UPDATE orders SET is_paid = (random() < 0.5);
CREATE INDEX idx_orders_is_paid ON orders (is_paid);
EXPLAIN ANALYZE
SELECT * FROM orders WHERE is_paid = true;
Seq Scan on orders (cost=0.00..24249.00 rows=500967 width=24) (actual time=76.010..176.902 rows=499646 loops=1)
Filter: is_paid
Rows Removed by Filter: 500354
Planning Time: 0.334 ms
Execution Time: 196.384 ms
インデックスを貼ったにもかかわらず、完全に無視されてSeq Scanになりました。値がほぼ2択で、しかもどちらの値でも全体の半分がヒットしてしまうため、インデックス経由で1行ずつ拾うより、テーブルを順番に読んだほうがオプティマイザにとって速いと判断されるからです。実際、あとでpg_stat_user_indexesを確認しても、このidx_orders_is_paidはidx_scanが0のまま、つまり一度も使われていませんでした。カーディナリティが低い列に単独でインデックスを貼っても、使われないどころか書き込みコストだけが増えるケースがあるということを、ここで実感しました。
複合インデックスは列の順番が命
「特定ユーザーのpaid注文」のように複数条件で絞り込むことが多いなら、複合インデックスが効きます。
CREATE INDEX idx_orders_user_status ON orders (user_id, status);
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 12345 AND status = 'paid';
Index Scan using idx_orders_user_status on orders (cost=0.42..16.48 rows=3 width=24) (actual time=0.185..0.187 rows=1 loops=1)
Index Cond: ((user_id = 12345) AND (status = 'paid'::text))
Planning Time: 0.638 ms
Execution Time: 0.319 ms
0.3msで終わっています。ここで大事なのは列の順番です。カーディナリティが高い(値の種類が多い)user_idを先頭に置くのが基本で、逆にすると絞り込みの効きが悪くなります。等号(=)で絞る列を先頭、範囲検索(>や<、BETWEEN)で絞る列を後ろに置く、というのが自分の中でのルールになっています。
5. 貼りすぎ注意 ─ インデックスのデメリット
ここまで「貼れば速くなる」という話をしてきましたが、インデックスはタダではありません。INSERT・UPDATE・DELETEのたびに、テーブル本体だけでなくすべてのインデックスも更新する必要があるため、書き込みが多いテーブルにインデックスを増やしすぎると、今度は書き込み性能が落ちます。
また、ディスク容量もインデックスの分だけ余分に消費します。「使われていないインデックス」は保守コストしか生んでいないので、定期的にpg_stat_user_indexesなどで実際の使用状況を確認し、使われていないものは削除する、というのを運用の中に組み込んでおくのがいいと思っています。
SELECT relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0;
実際に手元の環境でこれを流すと、先ほどのis_paid列のインデックスがしっかり引っかかってきました。
relname | indexrelname | idx_scan
---------+--------------------+----------
orders | idx_orders_is_paid | 0
(1 row)
まとめ
今回ローカルで検証してみて改めて感じたのは、インデックスは「なんとなく全部の列に貼っておく」ものではなく、実際のクエリパターンとカーディナリティを見て、狙って貼るものだということです。
迷ったときの自分なりのチェックリストはこうです。WHEREやJOINで絞り込みに使う列か。ORDER BYやGROUP BYで使う列か。カーディナリティは十分に高いか。複合インデックスなら等号条件の列が先頭になっているか。そして、貼った後は実際にEXPLAIN ANALYZEで効いているかを必ず確認する。
「なんとなく遅い」で終わらせず、実行計画を見て判断する癖をつけることが、インデックス設計では一番大事なポイントなんじゃないかなと思っています。