インデックスの効果について #PostgreSQL

データベースにおいて、インデックスはレコード検索の高速化を図るとき、意識すべき概念です。

今回は、PostgreSQL14を使い、インデックスの効果について見てみたいと思います。

そもそもインデックスとは何か？

データベースの性能を向上させる手段の１つです。indexは日本語で「索引」という意味です。「データを検索しやすいように並べ替えて、ラベルをつけたもの」と考えると良いでしょう。

インデックスは本の最後に書いてある「検索」のようなイメージです。五十音順などで並び替えて整理されていますよね。

インデックスがないと、書籍の全ページを見て探さなければなりませんが、インデックスがあれば、記載がある箇所にすぐに辿り着くことができます。

インデックスが効果を発揮する場面とは？

インデックスがあると、どのような場合に効果が大きいのでしょうか？
一般的に、以下の場合であります。

・レコード数が多い。
・検索対象の項目に値の重複、偏りが少ない
・テーブルの更新・追加・削除が少ない

実際に試してみましょう。
主キーなしのテーブルを作成してみます。DDLは下記です。

create table firstapp.dummy_personal_info_no_index (
  id integer not null
  , name_kanji character varying(30) not null
  , age integer
  , mail_address character varying(100)
  , regist_time timestamp with time zone default CURRENT_TIMESTAMP not null
  , delete_flg BOOLEAN default false not null
) ;

このテーブルに5億件のレコードを登録することにします。1ファイルあたり200万件のcsvを25個つくり、\copyコマンドで登録します。

\copy firstapp.dummy_personal_info_no_index from 'パス/test/testdata_1.csv' with (format csv);
\copy firstapp.dummy_personal_info_no_index from 'パス/test/testdata_2.csv' with (format csv);
\copy firstapp.dummy_personal_info_no_index from 'パス/test/testdata_3.csv' with (format csv);
\copy firstapp.dummy_personal_info_no_index from 'パス/test/testdata_4.csv' with (format csv);
（以下略）

【参考余談】PostgreSQLで大量のデータを生成する方法として、GENERATE_SERIES関数がありますが、これを使ってダミーレコードを登録しようとしたら、500万件程度でもタイムアウトエラーになってしまいました。。。数百万レベル以上はcopyコマンド一択のようです。

INSERT 
INTO firstapp.dummy_personal_info_no_index( 
    id
    , name_kanji
    , age
    , mail_address
    , regist_time
    , delete_flg
) 
select
    i
    , 'テスト太郎'
    , 25
    , 'kawaguchitatsuo_98657966@gmail.com'
    , CURRENT_TIMESTAMP
    , false 
from
    generate_series(1, 5000000) as i;

登録が完了したので、単純なクエリを流して実行時間を確認してみます。なお、キャッシュの影響を受けないよう、idは変えて10回繰り返します。

\timing
select * from firstapp.dummy_personal_info_no_index where id = 20000000;

複数回実行した結果は以下の通りでした。

1回目：時間: 4058.183 ミリ秒(00:04.058)
2回目：時間: 3551.239 ミリ秒(00:03.551)
3回目：時間: 3462.373 ミリ秒(00:03.462)
4回目：時間: 3462.373 ミリ秒(00:03.462)
5回目：時間: 3456.499 ミリ秒(00:03.456)
6回目：時間: 3362.784 ミリ秒(00:03.363)
7回目：時間: 3424.884 ミリ秒(00:03.425)
8回目：時間: 3397.485 ミリ秒(00:03.397)
9回目：時間: 3338.660 ミリ秒(00:03.339)
10回目：時間: 3334.317 ミリ秒(00:03.334)

今度はインデックスありのテーブルを作成してみます。DDLは下記です。

create table firstapp.dummy_personal_info (
  id integer not null
  , name_kanji character varying(30) not null
  , age integer
  , mail_address character varying(100)
  , regist_time timestamp with time zone default CURRENT_TIMESTAMP not null
  , delete_flg BOOLEAN default false not null
  , constraint dummy_personal_info_PKC primary key (id)
) ;

同様に5億件登録し、検索をしてみます。圧倒的に速くなりました。

1回目：時間: 29.975 ミリ秒
2回目：時間: 1.300 ミリ秒
3回目：時間: 0.395 ミリ秒
4回目：時間: 2.352 ミリ秒
5回目：時間: 1.934 ミリ秒
6回目：時間: 1.922 ミリ秒
7回目：時間: 7.671 ミリ秒
8回目：時間: 4.122 ミリ秒
9回目：時間: 8.038 ミリ秒
10回目：時間: 1.969 ミリ秒

ただし、実行時間だけでは不確かなところもあるので、explainコマンドを使って実行計画を見てみましょう。

インデックスなしの場合

explain select * from firstapp.dummy_personal_info_no_index where id = 14000480;

Gather  (cost=1000.00..829598.85 rows=1 width=50)
   Workers Planned: 2
   ->  Parallel Seq Scan on dummy_personal_info_no_index  (cost=0.00..828598.75 rows=1 width=50)
         Filter: (id = 14000480)

まずは、costという値ですが、これは処理の重さの指標です（秒数ではない）。数が大きいほど処理が重いです。cost=1000.00..829598.85と、2つ表示されていますね。

cost=A..B

A=初期コスト（select文に対して最初の行を返すコスト）
B=トータルコスト（最後の行を返すコスト）
であり、重要なのはトータルコストの方ですので、この値を意識しておきます。

次に、Workers Planned: 2-> Parallel Seq Scanとなっていますが、シーケンシャルスキャンを2つのワーカープロセスで並列的に行う予定と言っていますね。シーケンシャルスキャンとは、直訳すると「順次検索」であり、すべてのレコードを順に検索するということです。

インデックスありの場合

explain select * from firstapp.dummy_personal_info where id = 14000480;

Index Scan using dummy_personal_info_pkc on dummy_personal_info  (cost=0.56..8.58 rows=1 width=50)
   Index Cond: (id = 14000480)

Index Scan using dummy_personal_info_pkcとある通り、dummy_personal_info_pkcという名前のインデックスを使ってのインデックススキャンが行われます。

costを見ると、cost=0.56..8.58となっており、インデックスなしの場合に比べて圧倒的に小さい値になっていることがわかりました。