PostgreSQL の SQL やプロシージャでランダムデータの入ったテーブルを生成する

お疲れ様です、サイオス 那賀です。

テーブルの抱えるサンプルデータの行数が 2～3 行だと、何をどうしてもインデックスなど使われないのでつまらない時に、自動的に生成した沢山のランダムデータを INSERT する方法です。連番 10000 個につき、ランダムな 20 文字からなる名前をそれぞれに持つ "users" テーブルを作成してみます。

SQL のみで実行

まずは、バージョン 9.0 以降での例です。

DROP TABLE users CASCADE;

CREATE TABLE users (id integer PRIMARY KEY, name text);

INSERT INTO users (id, name) (
  SELECT id,
   string_agg(substr(s, ceil(random() * length(s))::int, 1),'')
    FROM (
      SELECT 'abcdefghijklmnopqrstuvwxyz'
       'ABCDEFGHIJKLMNOPQRSTUVWXYZ'::text s, *
        FROM generate_series(1, 10000) AS id, generate_series(1, 20)
    ) AS tmp
    GROUP BY id
);

CREATE INDEX ON users (name); -- 9.0 から、インデックス名は省略可能

ここでは、新しく導入された string_agg() 集約関数を使用しているため、8.4 以前では動きません。サブクエリをそれぞれ実行してみれば、何をしているかは分かると思います。

ランダムな文字列が生成されていることと、充分にデータが大きいのでインデックスが使われていることを確認します。

postgres=# SELECT * FROM users LIMIT 10;
 id |         name
----+----------------------
  1 | bCphDuvZliwsKjSciOZA
  2 | oYTQPutKQqYRTnYwIuwU
  3 | DSmnbfqkTpKioEZeZsOP
  4 | JNHCbFYJauDDnPRpVizO
  5 | xkXMPWQOoFEXslAtRycS
  6 | TGvhwnxrvWgThdFXawMo
  7 | bqmtCNNulQmfwInTWKkr
  8 | HrlPvRMvnyJpPWJrJwLV
  9 | mYbJGoCCZNUGefvzXiUl
 10 | GEAwBkNlGzgTxiCexfGw
(10 rows)

postgres=# EXPLAIN SELECT * FROM users WHERE name = 'hoge';
                                  QUERY PLAN
------------------------------------------------------------------------------
 Bitmap Heap Scan on users  (cost=4.64..75.57 rows=50 width=36)
   Recheck Cond: (name = 'hoge'::text)
   ->  Bitmap Index Scan on users_name_idx  (cost=0.00..4.63 rows=50 width=0)
         Index Cond: (name = 'hoge'::text)
(4 rows)

postgres=# 

バージョン 8.4 でも、配列に対する集約関数である array_agg() 関数が使えるので、以下のようにできます。

DROP TABLE users CASCADE;

CREATE TABLE users (id integer PRIMARY KEY, name text);

INSERT INTO users (id, name) (
  SELECT id, array_to_string(
   array_agg(a[ceil(random() * array_length(a, 1))]),'' )
    FROM (
      SELECT ARRAY[
       'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm',
       'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z',
       'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M',
       'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'
      ] AS a, *
        FROM generate_series(1, 10000) AS id, generate_series(1, 20)
    ) AS tmp
    GROUP BY id
);

CREATE INDEX users_name_idx ON users (name);

8.3 以前では、array_agg() も無いので、集約関数では無理だと思います。ストアドプロシージャを使います。

ストアドプロシージャで実行

9.0 以降には DO コマンドがあり、無名関数の実行ができますので "DO $$～" のように実行できますが、8.4 以前では、一旦名前をつけて関数を定義する必要があります。

以下は、8.3 でも動きます。

DROP TABLE users CASCADE;

CREATE TABLE users (id integer PRIMARY KEY, name text);

CREATE OR REPLACE FUNCTION tmp() RETURNS void AS
$$
  DECLARE
    i INTEGER;
    id INTEGER;
    name text;
    s text := 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
  BEGIN
    FOR id IN 1 .. 10000 LOOP
      name := '';
      FOR i IN 1 .. 20 LOOP
        name := name || substr(s, ceil(random() * length(s))::int, 1);
      END LOOP;
      INSERT INTO users VALUES(id, name);
    END LOOP;
  END
$$ LANGUAGE plpgsql;

SELECT tmp();

DROP FUNCTION tmp();

CREATE INDEX users_name_idx ON users (name);

では。