PostgreSQLでインデックスを試してみる

PostgreSQLでインデックスを試してみて、その効果を見てみます。

テーブル名tblのテーブルを用意しました。
カラムは、SERIAL PRIMARY KEYであるidとINTEGERであるnumからなります。

データは、idは自動インクリメントでnumに0~100の乱数で、100万件挿入しています。

mydb=# SELECT COUNT(*) FROM tbl;
  count  
---------
 1000000
(1 row)

「インデックスなしの場合はどれくらいの処理時間は？」

まずは、インデックスなしで検索と挿入を試してみます。
処理時間は、EXPLAIN ANALYZEを使って計測します。

検索

まずは、検索を試してみます。
クエリは↓です。

EXPLAIN ANALYZE SELECT COUNT(*) FROM tbl WHERE num < 5;

結果、処理時間は次の通りになりました。

 Planning Time: 0.058 ms
 Execution Time: 30.180 ms

Execution Timeは、約30msです。

挿入

続いて、挿入も試してみます。
10000件のデータを挿入してみます。
クエリは↓です。

EXPLAIN ANALYZE INSERT INTO tbl (num) SELECT * FROM generate_series(0, 10000);

結果は次の通りです。

 Planning Time: 0.030 ms
 Execution Time: 19.708 ms

だいたい19msです。

さて、インデックスを作成した場合、どれくらい改善されるでしょうか。

まずは、インデックスを作成します。
PostgreSQLでは、CREATE INDEX文でインデックスを作成できます。

CREATE INDEX on tbl (num);

検索

インデックスなしの場合と同じクエリを発行します。

EXPLAIN ANALYZE SELECT COUNT(*) FROM tbl WHERE num < 5;

結果は次の通りです。

 Planning Time: 0.334 ms
 Execution Time: 3.893 ms

約4msです。
インデックスなしの場合、約30msだったので、かなり短縮されました。

挿入

挿入もインデックスありで試してみます。
クエリはインデックスなしの場合と同じです。

EXPLAIN ANALYZE INSERT INTO tbl (num) SELECT * FROM generate_series(0, 10000);

結果は次の通りです。

 Planning Time: 0.024 ms
 Execution Time: 31.768 ms

約30msになりました。
インデックス作成前は約19msだったので、挿入にかかる時間は長くなりました。

終わり