PostgreSQL9.2で1億件のレコードを
取り扱うのにどの程度の性能が得られるのか実験した。
通常の業務システムで、単独テーブルのレコード数が
１億レコードのデータを扱うことはまず無いでしょう。
適切な括りで分割するなりしてレスポンスを確保すると思うが、
１億レコードが一般的なパソコンで、そこそこ動くことに驚いた。
全件のレコード取得が遅いのであって、
通常のインデックスを使用したwhere抽出は、問題なく働く。
動作環境
WinXP Pro Core2Duo E7500@2.93GHz、メモリ2GB
PostgreSQL9.2
>テーブル作成
CREATE TABLE hugeTable1
(uniqueID character varying(255) NOT NULL,
key1 INTEGER, key2 INTEGER, key3 INTEGER,
key4 INTEGER, key5 INTEGER, key6 INTEGER,
key7 INTEGER, key8 INTEGER);
>レコードの作成 約13分掛かりました
INSERT INTO hugeTable1
SELECT '2012121211987600' ||
to_char(S1.seed * 10000000 + S2.seed * 1000000 + S3.seed * 100000 +
S4.seed * 10000 + S5.seed * 1000 + S6.seed * 100 + S7.seed * 10 + S8.seed,'FM00000000'),
S1.seed, S2.seed, S3.seed, S4.seed, S5.seed, S6.seed, S7.seed, S8.seed
FROM
seeds AS S1, seeds AS S2, seeds AS S3,
seeds AS S4, seeds AS S5, seeds AS S6, seeds AS S7, seeds AS S8 ;
クエリーは、成功しました: 100000000 行の影響があり, 実行時間は、756766 ミリ秒でした。
>件数の確認
SELECT count(*) FROM hugeTable;
count=100000000
1回目の応答時間、約242秒
2回目以降の応答時間、90秒程度
シーケンススキャンで動作している。
"Aggregate (cost=2386364.40..2386364.41 rows=1 width=0)"
" -> Seq Scan on hugetable1 (cost=0.00..2136364.32 rows=100000032 width=0)"
>プライマリーキーを作成。1755秒掛かりました。
ALTER TABLE hugeTable1 ADD PRIMARY KEY(uniqueID);
NOTICE: ALTER TABLE / ADD PRIMARY KEYはテーブル"hugetable1"に暗黙的なインデックス"hugetable1_pkey"を作成します
クエリーは、1755656 ミリ秒で結果なしでうまく帰りました
>プライマリーキー作成後の件数確認
select count(*) from hugeTable1;
1回目の応答時間、80438 ms.
2回目以降の応答時間、84141 ms.
プライマリーキー作成後もシーケンススキャン
"Aggregate (cost=2386364.00..2386364.01 rows=1 width=0)"
" -> Seq Scan on hugetable1 (cost=0.00..2136364.00 rows=100000000 width=0)"
postgresql.confを設定しIndexOnlyScanを試した。
シーケンススキャンの80秒に対し、5倍以上時間が掛かり438秒になった。
> クエリの実行:
select count(*) from hugeTable1;
クエリ全体 実行時間:438250 ms.
1 行検索しました
"Aggregate (cost=5315086.10..5315086.11 rows=1 width=0)"
" -> Index Only Scan using hugetable1_pkey on hugetable1 (cost=0.00..5065086.10 rows=100000000 width=0)"
>テーブルとインデックスのサイズを確認
select pg_relation_size('hugetable1');
クエリ全体 実行時間:31 ms.
1 行検索しました
9309093888
テーブルのサイズ 約9.3GB
select pg_relation_size('hugetable1_pkey');
クエリ全体 実行時間:16 ms.
1 行検索しました
4973518848
インデックスのサイズ 約4.97GB
