PostgreSQL 大量件数のパフォーマンス検証

検証環境は次の通りです。

• Windows 10 Pro 1607
• i7-6700K 4.00 GHz
• CMK32GX4M2A2400C14 32.0 GB
• HGST HMS5C4040ALE640
• PostgreSQL 9.3

検索処理 1 回の集合体である processes テーブルと、検索結果 1 件 の集合体である results テーブルを作成します。

create table processes
(
    process_id bigserial not null
        constraint n_process_pkey
            primary key,
    status varchar(20) not null,
    create_datetime timestamp default now() not null
)
;

create table results
(
    result_id bigserial not null
        constraint results_pkey
            primary key,
    process_id bigint not null
        constraint results_processes_process_id_fk
            references processes,
    content_1 varchar(20),
    content_2 varchar(10),
    content_3 varchar(200)
)
;

create index results_process_id_index
    on results (process_id)
;

1 回の検索につき、最大結果件数は 10,000 件までの制限を設けます。 すべての処理は特定の検索 ID に関連する検索結果に対して行うので、 results.process_id にインデックスを張ります。 results の件数が膨大になっても、任意の process_id に関連するレコードは 10,000 件以内になるため、 速度劣化が生じないことを期待します。

まず、適当な件数のレコードを processes に作成します。

INSERT INTO
  processes (
    status
  )
SELECT
  'COMPLETE' as status
FROM
  GENERATE_SERIES(1, 100000)

次に、それぞれに関連したレコードを 2,000 件ずつ results に作成します。

INSERT INTO
  results (
    process_id
    ,content_1
    ,content_2
    ,content_3
  )
SELECT
  TRUNC(result_id / 2000) + 1 as process_id
  ,'ABCDE'
  ,'ABC'
  ,'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
FROM
  GENERATE_SERIES(1, ) as result_id

insert したレコード数とかかった時間は次のようになりました。

操作順	対象テーブル	insert 数	かかった時間
1	processes	100,000	903 ms
2	results	10,000,000	2m 57s 287ms
3	results	100,000,000	27m 14s 391ms
4	results	1,000,000,000	5h 21m 40s 788ms
5	results	10,000,000	2m 59s 930ms

レコードを増やすたびに、次の SQL を発行しましたが、 キャッシュに乗ってない状態でも 100ms 以下で変化せずに返ってきたため、 インデックスによる絞り込みで十分な件数に減らしていれば、 トータルのレコード数はパフォーマンスに影響しないことがわかりました。

SELECT
  *
FROM
  results
WHERE
  process_id = ?

ちなみに、 1,000,000,000 レコードの時点で消費しているストレージ容量は 229 GB です。