RAKUS Developers Blog | ラクス エンジニアブログ

株式会社ラクスのITエンジニアによる技術ブログです。

【PostgreSQL】indexとカーディナリティについて調べてみた

新卒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 ms0.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を超えたあたりからだんだん効果が薄くなっていくのがわかります。

参考資料

*1:問合せの結果行数を指すこともあるそうです

Copyright © RAKUS Co., Ltd. All rights reserved.