テストテーブルの作成、1000万件のレコードを作る。

PostgreSQL9.2で1000万件程度のレコードを
取り扱うのにどの程度の性能が得られるのか実験するために
テストテーブルを作成する方法を記録する。

動作環境
WinXP Pro Core2Duo E7500@2.93GHz、メモリ2GB
PostgreSQL9.2

>テーブル作成
CREATE TABLE seeds
(
  seed integer NOT NULL,
  CONSTRAINT seeds_pkey PRIMARY KEY (seed)
);

INSERT INTO seeds(seed)  VALUES (0);
INSERT INTO seeds(seed)  VALUES (1);
INSERT INTO seeds(seed)  VALUES (2);
INSERT INTO seeds(seed)  VALUES (3);
INSERT INTO seeds(seed)  VALUES (4);
INSERT INTO seeds(seed)  VALUES (5);
INSERT INTO seeds(seed)  VALUES (6);
INSERT INTO seeds(seed)  VALUES (7);
INSERT INTO seeds(seed)  VALUES (8);
INSERT INTO seeds(seed)  VALUES (9);

CREATE TABLE hugeTable
(uniqueID character varying(255) NOT NULL,
 key1 INTEGER, key2 INTEGER, key3 INTEGER,
 key4 INTEGER, key5 INTEGER, key6 INTEGER,
 key7 INTEGER);

>レコードの作成　81秒掛かりました。
INSERT INTO hugeTable
 SELECT '2012121211987600' ||
 to_char(S1.seed * 1000000 + S2.seed * 100000 + S3.seed * 10000 +
 S4.seed * 1000 + S5.seed * 100 + S6.seed * 10 + S7.seed,'FM0000000'),
 S1.seed, S2.seed, S3.seed, S4.seed, S5.seed, S6.seed, S7.seed
 FROM
 seeds AS S1, seeds AS S2, seeds AS S3,
 seeds AS S4, seeds AS S5, seeds AS S6, seeds AS S7 ;

>件数の確認
SELECT count(*) FROM hugeTable;
count=10000000

1回目の応答時間、約18秒
2回目以降の応答時間、1.7秒程度

>プライマリーキーは最後につけましょう。170秒掛かりました。
ALTER TABLE hugeTable ADD PRIMARY KEY(uniqueID);

>クエリープランを見てみるがIndex Only Scanでは無い様子。
EXPLAIN (COSTS off, ANALYZE on) SELECT count(*) FROM hugetable;
"Aggregate (actual time=4753.404..4753.404 rows=1 loops=1)"
"  ->  Seq Scan on hugetable (actual time=0.024..2766.608 rows=10000000 loops=1)"
"Total runtime: 4753.442 ms"


>テーブルの内容
"20121212119876000000000";0;0;0;0;0;0;0
"20121212119876000000001";0;0;0;0;0;0;1
"20121212119876000000002";0;0;0;0;0;0;2
　↓
"20121212119876009999997";9;9;9;9;9;9;7
"20121212119876009999998";9;9;9;9;9;9;8
"20121212119876009999999";9;9;9;9;9;9;9



>テーブルとインデックスのサイズを確認

テーブルのサイズ　約845MB
select pg_relation_size('hugetable');
pg_relation_size

































-
















844537856
(1 row)

インデックスのサイズ　約406MB
select pg_relation_size('hugetable_pkey');
pg_relation_size

































-
















406142976
(1 row)


参考Webサイト
SQLによるテストテーブルの作成
http://www.geocities.co.jp/SiliconValley-PaloAlto/1037/tech/sql/CreateTestTable.html

なんちゃって個人情報
http://kazina.com/dummy/index.html

開発思考実験日記
http://d.hatena.ne.jp/dotnetmemo/20111015/1318663995

spitz2bassの日記 The patch for Index Only Scan committed
http://d.hatena.ne.jp/spitz2bass/20111214/1323870308

SRA OSS, Inc. PostgreSQL 9.2 検証報告
http://www.sraoss.co.jp/technology/postgresql/images/20120618_PostgreSQL9.2_report_sraoss.pdf


>レコードの作成、MySQLの場合
>プライマリーキーを使用した場合、10万件は、数秒で終わるが、
>下記の1000万件では不可能。100万件で5分位。何らかのチューニングを要する。
INSERT INTO hugeTable
 SELECT concat('2012121211987600' ,
 lpad(cast(S1.seed * 1000000 + S2.seed * 100000 + S3.seed * 10000 +
 S4.seed * 1000 + S5.seed * 100 + S6.seed * 10 + S7.seed as char),7,'0')),
 S1.seed, S2.seed, S3.seed, S4.seed, S5.seed, S6.seed, S7.seed
 FROM
 seeds AS S1, seeds AS S2, seeds AS S3,
 seeds AS S4, seeds AS S5, seeds AS S6, seeds AS S7 ;

>件数の確認
SELECT count(*) FROM hugeTable;
100万件のテーブルで54.672sec