新卒2年目のyk_itgです。早いもので社会人2年目も残り1ヶ月となりました。
パフォーマンスチューニングの開発をする際に、indexはどのようなカラムに貼るのが良いのか気になったので、今回はそこで調べたことを書いてみます。
PostgreSQLのバージョン： 11.5
まずはindexを貼ってみる
まずはindexを貼るためのテーブルを作っていきます。
テーブルには主キーのidの他に以下の2つのカラムを定義します。
- sequence_number
- 一意な数字が入る2つのカラムを持つテーブルを用意します。
- type
- 3種類の値(1, 2, 3)のいずれかが入る
CREATE TABLE test ( id integer primary key, sequence_number integer, type integer );
そのテーブルに1つのtypeごとに100万件、計300万件のレコードを入れて、
INSERT INTO test(id, sequence_number, type) SELECT i, i, '1' FROM generate_series(1,1000000) AS i; INSERT INTO test(id, sequence_number, type) SELECT i, i, '2' FROM generate_series(1000001,2000000) AS i; INSERT INTO test(id, sequence_number, type) SELECT i, i, '3' FROM generate_series(2000001,3000000) AS i;
sequence_numberとtypeの2つのカラムにindexを貼ります。
CREATE INDEX index_test_number ON test(sequence_number); CREATE INDEX index_test_type ON test(type);
indexが貼れたかどうかは、システムビューのpg_indexesで確認できます。
postgres=# SELECT * FROM pg_indexes WHERE tablename = 'test'; schemaname | tablename | indexname | tablespace | indexdef ------------+-----------+-------------------+------------+----------------------------------------------------------------------------- public | test | test_pkey | | CREATE UNIQUE INDEX test_pkey ON public.test USING btree (id) public | test | index_test_type | | CREATE INDEX index_test_type ON public.test USING btree (type) public | test | index_test_number | | CREATE INDEX index_test_number ON public.test USING btree (sequence_number) (3 rows)
主キーのカラムにはindexが元々 付いているようです。
indexを使ってみる
作ったindexがどのように使われているのかEXPLAIN ANALYZEで確認していきます。
比較をしやすいようにindexを貼る sequence_number と type を WHERE句に入れて、まずは何も貼っていない状態で試してみます。
実行前にANALYZEを忘れずにしておきましょう。
また、indexを使ってもらえるようにenable_seqscanをoffにし、見やすいようにパラレルクエリもoffにしておきます。
ANALYZE test; SET enable_seqscan TO 'off'; SET max_parallel_workers_per_gather TO '0'; EXPLAIN ANALYZE SELECT id, sequence_number, type FROM test WHERE type = 1 AND sequence_number = 1000000; ---------------------------------------------------------------------------------------------------------------------- Seq Scan on test (cost=10000000000.00..10000061217.00 rows=1 width=12) (actual time=87.978..235.451 rows=1 loops=1) Filter: ((type = 1) AND (sequence_number = 1000000)) Rows Removed by Filter: 2999999 Planning Time: 0.124 ms Execution Time: 235.470 ms (5 rows)
235.470 msとそこそこ時間がかかっていることがわかります。
続いて2つのカラムにindexを貼った状態で試してみます。
EXPLAIN ANALYZE SELECT id, sequence_number, type
FROM test WHERE type = 1 AND sequence_number = 1000000;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Index Scan using index_test_number on test (cost=0.43..8.45 rows=1 width=12) (actual time=0.022..0.023 rows=1 loops=1)
Index Cond: (sequence_number = 1000000)
Filter: (type = 1)
Planning Time: 0.168 ms
Execution Time: 0.044 ms
(5 rows)
何も貼っていない状態と比べると、235.470 msと0.044 msで何倍も早くなっていることがわかりますね！
ただ、sequence_numberだけでなく、typeにもindexを貼ったのに使われていません。
今度はtypeにだけindexを貼った状態で試してみます。
EXPLAIN ANALYZE SELECT id, sequence_number, type
FROM test WHERE type = 1 AND sequence_number = 1000000;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Index Scan using index_test_type on test (cost=0.43..36304.43 rows=1 width=12) (actual time=184.283..184.284 rows=1 loops=1)
Index Cond: (type = 1)
Filter: (sequence_number = 1000000)
Rows Removed by Filter: 999999
Planning Time: 0.526 ms
Execution Time: 184.341 ms
(6 rows)
今度はtypeのindexを使ってくれました！
ただ、indexを貼ったのにも関わらず、235.470 msから184.341 msに代わっただけで、sequence_numberの方の0.044 msと比べるとほとんど効果が出ていないことがわかります。
この違いは何でしょうか。
カーディナリティ
typeのindexの効果が出ない理由は、テーブルの行数に対してtypeのカーディナリティが低いためです。
カーディナリティ*1とはそのカラムにある値の種類の絶対値（今回のtypeの場合は 3)のことを指します。
テーブルの行数300万に対して、typeのカーディナリティは3しかなかったため、ほとんど効果が出なかったわけです。逆に一意でカーディナリティが高いsequence_numberのindexではかなり効果が出ています。
カーディナリティを変えて比べてみる
行数は300万のままで、typeのカーディナリティを変えて何パターンか実行してみました。
結果は以下のようになりました。※実行時間は同じ環境でも変動があります。
| typeのカーディナリティ | indexありの実行時間(ms) |
|---|---|
| 2 | 324.752 |
| 3 | 223.665 |
| 4 | 193.7 |
| 10 | 66.488 |
| 100 | 6.953 |
| 1000 | 1.064 |
| 10000 | 0.073 |
| 100000 | 0.034 |
カーディナリティが1000を超えたあたりからだんだん効果が薄くなっていくのがわかります。
参考資料
- カーディナリティについてまとめてみた
- カーディナリティ
- 用語集
- 41.32. pg_indexes
- postgreSQLでインデックス一覧の表示
エンジニア中途採用サイト
ラクスでは、エンジニア・デザイナーの中途採用を積極的に行っております！
ご興味ありましたら是非ご確認をお願いします。
https://career-recruit.rakus.co.jp/career_engineer/カジュアル面談お申込みフォーム
どの職種に応募すれば良いかわからないという方は、カジュアル面談も随時行っております。
以下フォームよりお申込みください。
rakus.hubspotpagebuilder.comラクスDevelopers登録フォーム
https://career-recruit.rakus.co.jp/career_engineer/form_rakusdev/イベント情報
会社の雰囲気を知りたい方は、毎週開催しているイベントにご参加ください！
◆TECH PLAY
techplay.jp
◆connpass
rakus.connpass.com
*1:問合せの結果行数を指すこともあるそうです
