◆ 複合インデックスのカラムを 1 つだけ検索に使用すると 1 番目ならインデックスが使用される
◆ 2 番目は使われたり使われなかったり

珍しく RDB の index についてです
保存できて検索できればそんなにインデックスを気にすることもないのですが ちょっと気になることがあったので調べてみました

使用するのは PostgreSQL で複合インデックスについてです

順番に意味あるの

複合インデックスは順番に意味があって 片方のカラムだけで検索した場合 1 つ目のカラムならインデックスが使われるときいたことがありました

PRIMAY KEY (col1, col2)

と index を定義したなら

WHERE col1 = 100 AND col2 = 200

はもちろんですが

WHERE col1 = 100

でも使用されて 逆に

WHERE col2 = 200

では使用できないということです
それが本当なら enum+ 連番 の場合は enum は数種類で連番のほうが多くの種類があるので 連番を先に指定したほうが良さそうに思えます

とりあえず試してみるのが楽そうなので試してみました

テーブル定義

CREATE TABLE prod.test1
(
type integer NOT NULL,
seq integer NOT NULL,
body text,
CONSTRAINT test1_pkey PRIMARY KEY (type, seq)
);

CREATE TABLE prod.test2
(
type integer NOT NULL,
seq integer NOT NULL,
body text,
CONSTRAINT test2_pkey PRIMARY KEY (seq, type)
);

ほぼ同じテーブルです
インデックスの順序だけ異なります

このテーブルに同じデータを入れます
データは

type: 0 ~ 9
seq: 0 ~ 9999

の計 100000 レコードです

enum 作るのは面倒なので 10 種類の integer にしました
追加後に analyze 済みです

explain

いろいろクエリを用意して explain analyze をしていきます

type と seq で WHERE

まずは type, seq の両方を指定したときです

explain analyze select * from prod.test1 where type = 1 and seq = 1
"Index Scan using test1_pkey on test1 (cost=0.29..8.31 rows=1 width=12) (actual time=0.016..0.016 rows=1 loops=1)"
" Index Cond: ((type = 1) AND (seq = 1))"
"Planning time: 0.078 ms"
"Execution time: 0.035 ms"

Index Scan がされてますね



2 つ目のテーブルも同じくです

explain analyze select * from prod.test2 where type = 1 and seq = 1
"Index Scan using test2_pkey on test2 (cost=0.42..8.44 rows=1 width=12) (actual time=0.019..0.020 rows=1 loops=1)"
" Index Cond: ((seq = 1) AND (type = 1))"
"Planning time: 0.079 ms"
"Execution time: 0.042 ms"

同じデータなのに こっちのほうがわずかに遅いです

type だけで WHERE

type だけでやってみます

explain analyze select * from prod.test1 where type = 1
"Index Scan using test1_pkey on test1 (cost=0.29..24.35 rows=10 width=12) (actual time=0.016..0.019 rows=10 loops=1)"
" Index Cond: (type = 1)"
"Planning time: 0.068 ms"
"Execution time: 0.034 ms"

type → seq の順でインデックスを作った方は Index Scan がされています



explain analyze select * from prod.test2 where type = 1
"Seq Scan on test2 (cost=0.00..1791.00 rows=10 width=12) (actual time=0.013..7.962 rows=10 loops=1)"
" Filter: (type = 1)"
" Rows Removed by Filter: 99990"
"Planning time: 0.065 ms"
"Execution time: 7.981 ms"

seq → type の順でインデックスを作った方は Seq Scan です
インデックスが使われてないようです
複合インデックスの順の話は正しかった ようです

seq だけで WHERE

今度は seq だけで検索します

explain analyze select * from prod.test1 where seq = 1
"Seq Scan on test1 (cost=0.00..1931.00 rows=9920 width=12) (actual time=0.012..9.192 rows=10000 loops=1)"
" Filter: (seq = 1)"
" Rows Removed by Filter: 90000"
"Planning time: 0.067 ms"
"Execution time: 9.656 ms"

type → seq の順でインデックスを作って seq で検索すると Seq Scan です
こっちも複合インデックスの 2 番目に指定したカラムの検索ではインデックスが使われてないです



explain analyze select * from prod.test2 where seq = 1
"Bitmap Heap Scan on test2 (cost=274.38..941.13 rows=10060 width=12) (actual time=0.741..3.206 rows=10000 loops=1)"
" Recheck Cond: (seq = 1)"
" Heap Blocks: exact=537"
" -> Bitmap Index Scan on test2_pkey (cost=0.00..271.87 rows=10060 width=0) (actual time=0.682..0.682 rows=10000 loops=1)"
" Index Cond: (seq = 1)"
"Planning time: 0.066 ms"
"Execution time: 3.702 ms"

seq → type の順でインデックスを作って seq で検索すると Bitmap Index Scan が行われてます
なんで Bitmap Index Scan になるかはわかりませんがとりあえずインデックスが使われてるようです

Sort

ソートも試してみます
まずは type → seq のインデックスの test1 テーブルです

explain analyze select * from prod.test1 order by type
"Index Scan using test1_pkey on test1 (cost=0.29..4188.42 rows=100000 width=12) (actual time=0.017..49.886 rows=100000 loops=1)"
"Planning time: 0.057 ms"
"Execution time: 56.935 ms"
explain analyze select * from prod.test1 order by seq
"Sort (cost=9985.82..10235.82 rows=100000 width=12) (actual time=72.183..85.182 rows=100000 loops=1)"
" Sort Key: seq"
" Sort Method: external merge Disk: 2144kB"
" -> Seq Scan on test1 (cost=0.00..1681.00 rows=100000 width=12) (actual time=0.010..8.358 rows=100000 loops=1)"
"Planning time: 0.060 ms"
"Execution time: 90.826 ms"

1 つめのカラム type でのソートではインデックスが使われています
2 つめのカラム seq でのソートでは Seq Scan です



次は seq → type のインデックスの test2 テーブルです

explain analyze select * from prod.test2 order by type
"Sort (cost=9845.82..10095.82 rows=100000 width=12) (actual time=73.686..86.027 rows=100000 loops=1)"
" Sort Key: type"
" Sort Method: external sort Disk: 2152kB"
" -> Seq Scan on test2 (cost=0.00..1541.00 rows=100000 width=12) (actual time=0.008..8.139 rows=100000 loops=1)"
"Planning time: 0.051 ms"
"Execution time: 91.894 ms"
explain analyze select * from prod.test2 order by seq
"Index Scan using test2_pkey on test2 (cost=0.42..5601.59 rows=100000 width=12) (actual time=0.017..43.596 rows=100000 loops=1)"
"Planning time: 0.060 ms"
"Execution time: 48.120 ms"

こちらも 1 つめのカラムの seq ではインデックスが使われていて
2 つめのカラムの type でのソートでは Seq Scan になってます

インデックスが使われるケースも

複合インデックスをつけたカラムを単体カラムで検索することがあるなら そっちを 1 番目に設定したほうが良いということで納得していたのですが 2 番目のカラムでもインデックスが使われるケースもありました

explain analyze select * from prod.test1 where seq = 50
"Index Scan using test1_pkey on test1 (cost=0.29..1856.07 rows=1 width=12) (actual time=1.730..1.730 rows=0 loops=1)"
" Index Cond: (seq = 50)"
"Planning time: 0.075 ms"
"Execution time: 1.751 ms"

test1 テーブルなので type → seq の順のインデックスです
seq = 1 で検索したときは Seq Scan だったのですが seq = 50 にすると Index Scan になりました
1 だと先頭の方にあるのでインデックス使うよりシーケンシャルに見ていったほうが速いとオプティマイザが判断した のでしょうか
先頭部分だけ見れば良いとわかっているあたり優秀そうですが そうなってくると同じカラムでも検索条件によって実行計画が変わるのでどうインデックス貼るのがベストなのか判断しづらいです

やっぱり細かなことはオプティマイザ任せで遅くなったら適当にインデックスつけてみて改善したらそれでおっけいくらいで考えるほうがいいのかもしれません
本来ユーザがクエリをいろいろ調整したりインデックスを定義したりするんじゃなくて 統計情報から自動で全部やってくれるのが理想ですしね