【PostgreSQL】大量のテストデータを作成し、インデックスを貼ることでパフォーマンスを改善してみる

テーブルを作成→大量のテストデータを投入→インデックスを作成→実際にパフォーマンスが改善されたことを確認するまでを手順化していきます。

※あくまで手順であって細かい説明は省略しています。

大量のテストデータを作成し、インデックスを貼ることでパフォーマンスを改善

今回使用した環境

インターネット接続可能のオンラインの環境

64 ビット オペレーティング システム

Windows 10 22H2

PostgreSQL 14.4

まずはテーブル作成

よくありがちなトランザクションテーブル「tr_test」を作ります。

CREATE TABLE tr_test
(
    test_code character varying(12),
    test_date date,
    test_contents character varying(50),
    test_kinds character varying(1),
    test_price numeric(10,0),
    CONSTRAINT tr_test_pkey PRIMARY KEY (test_code)
)

大量のテストデータを投入

以下のSQLで「tr_test」テーブルに対し、1億件のデータを投入します。筆者の環境では完了するまでに10分強ほどの時間がかかりました。

insert into tr_test
select lpad(i::character varying, 12, '0') as test_code
       ,'2010-01-01'::date + (random() * 5000)::integer as test_date
       ,format('内容%s', i) as test_contents
       ,(random() * 9)::integer as test_kinds
       ,(random() * 1000)::integer * 100 as test_price
  from generate_series(1, 100000000) as i

実際に実行する前に1行目の「insert into」の行はコメントアウトし、7行目の「100000000」を「10」に変更してselect文の状態で実行し、投入されるデータを確認しておくことをおすすめします。

ちなみにselect文の状態で実行すると以下のようなイメージとなります。

インデックスの作成

以下のインデックス作成のSQLを実行します。ここでは「test_date」の条件で検索したときにインデックスが使用されるようにします。

インデックスの種類はbtreeです。

CREATE INDEX tr_test_idx ON tr_test USING btree (test_date)

動作確認

以下のSQLを実行します。「explain analyze」をselect文の先頭に付けることで実行計画を取得できます。

explain analyze
select * from tr_test where test_date = '2023-01-13'

この「tr_test」テーブルは1億件のデータが存在するのに対し、上記のSQLは2万件ほどのデータを取得するのでインデックスは効くのではないかと思われます。

そして実際に実行してみた結果が以下の通りです。

上がインデックス作成前に取得した実行計画、下がインデックス作成後に取得した実行計画です。

作成前は「Parallel Seq Scan」になっており、テーブル全体 ( かな？ ) をスキャンしているという結果になっています。

作成後は「Bitmap Index Scan」になっており、インデックスが使用されているという結果になっています。

実際に処理時間もインデックス作成前後で3359ミリ秒→128ミリ秒に短縮されました。

以上となります。

ここまでお読みいただきありがとうございました。

PostgreSQL

Posted by だゆう