説明されてもピンとこないのでインデックスを実際に張ってみた

エンジニア1年生のSawaDです！
表題の通り，今回はデータベースのインデックスについて，簡単な検証記事となります🐘

記事の目的

• データベースにおけるインデックスの効果を，ハンズオンで確かめた結果の共有
• インデックス設計を行う上でのポイントをおさえる

座学はこちらの動画等で（丸投げ）

今回はハンズオンをメインということで、インデックスとは何ぞ、という座学は割愛します✂️
次のYoutube動画等が大変やさしくて詳しいです。とてもよく分かるようになりました🙏
下に、自分なりにポイントをまとめてみました。

ムーザルちゃんねる
SQL を速くするインデックス入門 : B-Tree や複合インデックスが理解できる

July Tech Festa　2021　アーカイブ動画
JTF2021 C07 『結局「インデックス」ってなんなんですか？ - PostgreSQL の仕組みからインデックスの理解を深める』

自分なりのポイント要約

• インデックス、とはその名の通り 「索引」 である
• 電話帳や辞書のようなイメージをもつとよい📖
  • 辞書を引くときに1ページ目から総当たりする人はいませんよね🤔
  • 頭から順にすべて調べるのではなく，構造化した索引から効率的にデータを取得することを可能にするしくみ、という概念理解でOK
• 一般的にその構造は B-Tree となり，階層ノードによる木構造によって，探索を効率化する
• ただし，なんでもインデックスを作ればよいというものではない
• まとめると，「検索によく使われ」「効率的にデータを絞り込むことのできるカラム」 に設定することが望ましい
  • 一般に，インデックスを張るとデータの追加・更新速度は落ちる
  • データの「絞り込み力」の弱いカラムでは，読み出しでさえ速度が落ちる場合もあるので注意

そうは言われても...

「知識」で終わってしまうんですよね😇
自分のような駆け出しエンジニアにとって、インデックスが目に見えて威力を発揮するような大規模データベースって、なかなか縁遠いものですから...

動画でも触れられているように、インデックスの効果をより実感できるのは，少なくとも数万レコードくらいからということですが，個人開発や勉強用のアプリで，それだけのレコード数になることは中々無いのではないでしょうか？🤔

手を動かしてみよう

そんな時こそ、実際に手を動かして確認してみましょう。
特に「ポイント」でまとめた最後、インデックスを張るとよいカラムとそうでないカラム の違いを確認することを目標にします💪

ローカル環境でPostgreSQLを使用します。
何万件というテストデータを投入するにあたって，PostgreSQLのコマンドでバッチ処理（ファイル読み込み）を行っていきます。
結構簡単にできるので，ぜひ皆さんも好きなデータを作って試して見て下さい👍

※ 筆者実行環境は本文末尾に記載します

それでは参りましょう！

次のディレクトリで，データベースクラスタの初期化（initdb）は実行済みとします

$ pwd
/Users/sawad/dev/labo/pghandson

$ ls
PG_VERSION            global/               pg_ident.conf         pg_replslot/          pg_stat_tmp/          pg_wal/               postmaster.opts
backup_files/         pg_commit_ts/         pg_logical/           pg_serial/            pg_subtrans/          pg_xact/
base/                 pg_dynshmem/          pg_multixact/         pg_snapshots/         pg_tblspc/            postgresql.auto.conf
batchfiles/           pg_hba.conf           pg_notify/            pg_stat/              pg_twophase/          postgresql.conf

今回、環境変数（$PGDATA 等）の設定等は省略します。
また、最初に作成したスーパーユーザ、postgresで接続するものとします。

サーバーを開始します

$ pg_ctl start
waiting for server to start....2024-12-07 00:50:33.569 JST [75812] LOG:  starting PostgreSQL 14.13 (Homebrew) on aarch64-apple-darwin23.6.0, compiled by Apple clang version 16.0.0 (clang-1600.0.26.4), 64-bit
2024-12-07 00:50:33.569 JST [75812] LOG:  listening on IPv6 address "::1", port 5432
2024-12-07 00:50:33.569 JST [75812] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2024-12-07 00:50:33.570 JST [75812] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2024-12-07 00:50:33.575 JST [75813] LOG:  database system was shut down at 2024-11-08 01:26:51 JST
2024-12-07 00:50:33.578 JST [75812] LOG:  database system is ready to accept connections
 done
server started

バッチファイルでテストデータを投入してみる

テスト用のDBを作成します

$ createdb -U postgres batch_test

psqlコマンドで中に入ります

$ psql -U postgres batch_test
psql (14.13 (Homebrew))
Type "help" for help.

batch_test=#

テストテーブルを作成します。
年齢，性別，コード（a〜jのランダム6文字）を仮で作ります。
ただし，性別は0/1の二値変数を想定しています。

batch_test=# CREATE TABLE test_table (
                age INTEGER,
                gender INTEGER CHECK (gender IN (0, 1)),
                code VARCHAR(6));


batch_test=# \d test_table
                   Table "public.test_table"
 Column |         Type         | Collation | Nullable | Default
--------+----------------------+-----------+----------+---------
 age    | integer              |           |          |
 gender | integer              |           |          |
 code   | character varying(6) |           |          |
Check constraints:
    "test_table_gender_check" CHECK (gender = ANY (ARRAY[0, 1]))

試しに投入して確認してみます。

batch_test=# INSERT INTO test_table (age, gender, code) VALUES (29, 0, 'abcdef');


batch_test=# SELECT * FROM test_table;
 age | gender |  code
-----+--------+--------
  29 |      0 | abcdef
(1 row)

\q コマンドでDBから一度抜けます
（以後、抜けたことを明記しない場合があります）

batch_test=# \q

作成したバッチ用ファイル置き場に、次のファイルを用意します。 コードはChatGPTに作成を依頼し、簡単に作成できました👏

$ mkdir batchfiles

$ touch batchfiles/index_test_mock.sql

index_test_mock.sql

-- ランダムなage(20〜65歳）とgender(0または1）を生成して挿入するバッチ
DO $$
DECLARE
	i INT;
	random_age INT;
	random_gender INT;
    random_code VARCHAR(6);
    letters TEXT[] := ARRAY['a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j'];
BEGIN
	FOR i IN 1..100 LOOP	-- ループ処理開始
		random_age := floor(20 + (random() * 45))::int;    -- 20〜65のランダムな数値
		random_gender := floor(random() * 2)::int;   --0または1
        -- ランダムに6文字を選んで連結
        random_code := (
            SELECT string_agg(letters[floor(random() * array_length(letters, 1) + 1)::int], '')
            FROM generate_series(1, 6)
        );

		-- 挿入
		INSERT INTO test_table (age, gender, code) VALUES (random_age, random_gender, random_code);
	END LOOP;
END;
$$;

基本的なプログラミングがわかる方ならすぐに理解できると思います。
FOR i IN 1..100 LOOPとし，試しに100件のデータを生成するようにしています。テスト投入に成功したら，後ほどこの数字を大きくしていきます。

ファイルを読み込むには，psql -fオプションを使用します

$ psql -U postgres -d batch_test -f batchfiles/index_test_mock.sql

再度DBに入り，ちゃんと投入されたか調べてみます

$ psql -U postgres -d batch_test

batch_test=# SELECT * FROM test_table LIMIT (10);
 age | gender |  code
-----+--------+--------
  29 |      0 | abcdef
  54 |      0 | iaibee
  48 |      1 | ajabba
  54 |      0 | aihjcd
  43 |      0 | hjfdfc
  38 |      0 | eeeigg
  45 |      1 | fjbjbc
  24 |      1 | ddjffb
  25 |      0 | ficjed
  50 |      1 | ihbgdc
(10 rows)


batch_test=# SELECT count(*) FROM test_table;
 count
-------
   101
(1 row)

いい感じですね。無事にテストデータが投入できました👍️

本題: 大量のテストデータでインデックスの効果を確かめる

先ほどのバッチファイルを開き，ループ数を10万件にしてみます。

index_test_mock.sql

FOR i IN 1..100000 LOOP -- ループ処理開始

端数があるとややこしいので，一旦TRUNCATEします

$ psql -U postgres -d batch_test

batch_test=# TRUNCATE test_table;

batch_test=# SELECT count(*) FROM test_table;
 count
-------
     0
(1 row)

再びバッチファイルを読み込ませます。
完了後，件数を確認してみましょう。

$ psql -U postgres -d batch_test -f batchfiles/index_test_mock.sql

$ psql -U postgres -d batch_test

batch_test=# SELECT count(*) FROM test_table;
 count
--------
 100000
(1 row)

ここでSELECT * FROM test_table; としてしまうと，標準出力に10万行出力するコマンドが実行されてしまうので，注意して下さい。内容を見たい場合は，必ず LIMIT を使用しましょう。

OKですね！

ではいよいよ，インデックスの効果を確認していきます！📖📖📖

PostgreSQLには，EXPLAIN コマンドという便利なコマンドがあり，SQL文に対してどのような検索を行うか，実行計画を作成して表示してくれます🧭
ANALYZE オプションをつけると，実際に実行した結果の統計情報も表示してくれます。
具体的なデータで比較を行うことができるので、実際の開発でも大いに役立ちますね🔍

インデックスなしの検証

まずはインデックスを張らずに試してみます。

インデックスなし age, gender

batch_test=# EXPLAIN ANALYZE SELECT * FROM test_table WHERE age=30 AND gender = 1;
                                                  QUERY PLAN
---------------------------------------------------------------------------------------------------------------
 Seq Scan on test_table  (cost=0.00..2041.00 rows=1121 width=15) (actual time=0.025..10.113 rows=1110 loops=1)
   Filter: ((age = 30) AND (gender = 1))
   Rows Removed by Filter: 98890
 Planning Time: 0.451 ms
 Execution Time: 10.184 ms
(5 rows)

⭐️ここで，Seq Scan となっているのが，「全件検索」を表します。

実行時間は10.184 ms となりました。　（ここはマシンスペック等に依存することと思います）

同じマシン，同じ条件で実行しても，多少のブレは発生します。
本来は十分なサンプル数をとり，平均値などを比較すべきかもしれませんが，今回は割愛します。
それぞれ，何度か実行してみて，概ね外れ値と思われない値を記載しています。

もう1つ，codeでの検索もやってみましょう。
コードが 'abc'から始まるレコードが，100件程度作成されているようなので，これを試してみます。
（参考動画で少し触れられていましたが，インデックスは先頭文字列のLIKE検索でも効果を発揮するそうです）

インデックスなし code

batch_test=# SELECT count(*) FROM test_table WHERE code LIKE 'abc%';
 count
-------
   107
(1 row)


batch_test=# EXPLAIN ANALYZE SELECT count(*) FROM test_table WHERE code LIKE 'abc%';
                                                   QUERY PLAN
----------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=1791.03..1791.04 rows=1 width=8) (actual time=9.106..9.107 rows=1 loops=1)
   ->  Seq Scan on test_table  (cost=0.00..1791.00 rows=10 width=0) (actual time=0.087..9.088 rows=107 loops=1)
         Filter: ((code)::text ~~ 'abc%'::text)
         Rows Removed by Filter: 99893
 Planning Time: 0.113 ms
 Execution Time: 9.140 ms
(6 rows)

ageにインデックスを設定

では次に，ageにインデックスを張ってみます。
ageは20〜65までの数であり，今回のようにランダム生成であればざっくり50分の1くらいにデータを絞り込むことができる計算になります。
検索でもよく使われるカラムのはずですから，今回の場合インデックスを張るのに適している，ということになります。

インデックスの作成は，CREATE INDEX [インデックス名] ON [テーブル名] ([対象カラム]); で行います。

batch_test=# CREATE INDEX idx_age ON test_table (age);

先ほどと同じage & genderで検索してみます

ageインデックス age, gender

batch_test=# EXPLAIN ANALYZE SELECT * FROM test_table WHERE age=30 AND gender = 1;
                                                      QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on test_table  (cost=25.40..600.04 rows=1121 width=15) (actual time=0.731..2.797 rows=1110 loops=1)
   Recheck Cond: (age = 30)
   Filter: (gender = 1)
   Rows Removed by Filter: 1104
   Heap Blocks: exact=530
   ->  Bitmap Index Scan on idx_age  (cost=0.00..25.11 rows=2243 width=0) (actual time=0.553..0.553 rows=2214 loops=1)
         Index Cond: (age = 30)
 Planning Time: 0.705 ms
 Execution Time: 2.928 ms
(9 rows)

実行時間が10.184ms→2.928ms と大幅に速くなりました！👏👏
また，スキャン方法がSeq Scanではなく，Bitmap Heap Scan に変化した点にも注目です。
ざっくり，B-Tree構造を使った木探索が行われている，ということになるかと思います。

genderにインデックスを設定

今度は，genderにのみ，インデックスを設定してみます。
先ほど作成したageインデックスは削除しておきます。

batch_test=# DROP INDEX idx_age;

batch_test=# CREATE INDEX idx_gender ON test_table (gender);

batch_test=# \di
                  List of relations
 Schema |    Name    | Type  |  Owner   |   Table
--------+------------+-------+----------+------------
 public | idx_gender | index | postgres | test_table
(1 row)

\di コマンドで作成済みのインデックスを一覧表示できるので，きちんと想定通りになっていることを確認しておきましょう。

では，同じコマンドを実行してみます。

genderインデックス age, gender

batch_test=# EXPLAIN ANALYZE SELECT * FROM test_table WHERE age=30 AND gender = 1;
                                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on test_table  (cost=551.45..1842.19 rows=1121 width=15) (actual time=2.650..8.968 rows=1110 loops=1)
   Recheck Cond: (gender = 1)
   Filter: (age = 30)
   Rows Removed by Filter: 49099
   Heap Blocks: exact=541
   ->  Bitmap Index Scan on idx_gender  (cost=0.00..551.16 rows=49983 width=0) (actual time=2.546..2.547 rows=50209 loops=1)
         Index Cond: (gender = 1)
 Planning Time: 0.470 ms
 Execution Time: 9.056 ms
(9 rows)

スキャン方法は，ageインデックスの時と同じBitmap Heap Scan ですが，実行時間10.184ms→9.056ms に改善があまりみられません🤔

これが，「絞り込み力の弱い」 カラムにインデックスを張った結果ということになります。
genderは0または1の二値データですから，今回のようなランダム生成の場合，50%にしか絞り込むことができません。
インデックスの恩恵を受けづらいまたは，インデックスを参照する分非効率になっているということですね👀

codeにインデックスを設定

今度は，より絞り込み力の高いcodeで試してみます。

作業は先程と同じです。

batch_test=# DROP INDEX idx_gender;

batch_test=# CREATE INDEX idx_code ON test_table (code);

batch_test=# \di
                 List of relations
 Schema |   Name   | Type  |  Owner   |   Table
--------+----------+-------+----------+------------
 public | idx_code | index | postgres | test_table
(1 row)

では実行してみます。インデックスなしの場合と同じく，'abc'から始まるレコードを検索します。

codeインデックス code

batch_test=# EXPLAIN ANALYZE SELECT count(*) FROM test_table WHERE code LIKE 'abc%';
                                                            QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=4.84..4.85 rows=1 width=8) (actual time=0.093..0.093 rows=1 loops=1)
   ->  Index Only Scan using idx_code on test_table  (cost=0.29..4.81 rows=10 width=0) (actual time=0.073..0.083 rows=107 loops=1)
         Index Cond: ((code >= 'abc'::text) AND (code < 'abd'::text))
         Filter: ((code)::text ~~ 'abc%'::text)
         Heap Fetches: 0
 Planning Time: 0.372 ms
 Execution Time: 0.120 ms
(7 rows)

なんと！　9.140ms → 0.120msと，びっくりするほど速くなりました！！　

ここでは，Index Only Scan というスキャン方法が計画されているようです。この実行計画は，賢いPostgreSQLが自動的に最適化を行ってくれるそうです👍

ざっと調べてみたところ，このIndex Only Scanは使用場面こそ限定的なものの，インデックステーブルだけを参照することでより処理を高速化できる方法のようです。
これらのスキャン方法の具体的な違いを理解することは，今後の課題としたいと思います📝

age, gender複合インデックス

最後に，複合インデックスも試しておきます。
名前の通りで，ageとgenderをかけ合わせたパターンに対して，インデックスを張っていきます。
作成は簡単で，CREATE INDEXの引数に複数カラムを指定するだけです。

batch_test=# DROP INDEX idx_code;

batch_test=# CREATE INDEX idx_age_gender ON test_table (age, gender);

batch_test=# \di
                    List of relations
 Schema |      Name      | Type  |  Owner   |   Table
--------+----------------+-------+----------+------------
 public | idx_age_gender | index | postgres | test_table
(1 row)

では，age,genderのWHERE句を実行してみましょう。

複合インデックス age, gender

batch_test=# EXPLAIN ANALYZE SELECT * FROM test_table WHERE age=30 AND gender = 1;
                                                          QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on test_table  (cost=19.78..577.60 rows=1121 width=15) (actual time=0.307..0.892 rows=1110 loops=1)
   Recheck Cond: ((age = 30) AND (gender = 1))
   Heap Blocks: exact=468
   ->  Bitmap Index Scan on idx_age_gender  (cost=0.00..19.50 rows=1121 width=0) (actual time=0.229..0.229 rows=1110 loops=1)
         Index Cond: ((age = 30) AND (gender = 1))
 Planning Time: 0.405 ms
 Execution Time: 1.066 ms
(7 rows)

インデックス無し10.184ms→　ageインデックスのみ 2.928ms →　複合 1.066ms　
と，理論通りに高速化することができました👏👏

まとめ

• インデックスはまさに「索引」
• 通常は全件検索（Seq Scan）が行われるところに，B-Tree構造を使った効率的な探索を導入することができる
• 原則，「検索によく使われ」，「絞り込み力の高い」カラムに指定するのがよい
• なんでもかんでもインデックスを設定すると，あまり改善しないかパフォーマンスの低下にも繋がるので注意
• EXPLAIN ANALYZE を使って確かめながら行おう
• スキャン方法のそれぞれの仕組みや理論の理解が今後の課題📝

筆者実行環境

	バージョン等
OS	macOS Sonoma　14.6.1
CPU/メモリ	Apple M3 Pro / 36GB
PostgreSQL	14.13（Homebrew）
Homebrew	4.4.4

$ psql -U postgres -c "SELECT version();"
                                                            version
-------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 14.13 (Homebrew) on aarch64-apple-darwin23.6.0, compiled by Apple clang version 16.0.0 (clang-1600.0.26.4), 64-bit
(1 row)