PostgreSQL で行をランダムに選択したいので、これを試しました:
select * from table where random() < 0.01;
しかし、他の人はこれを推奨しています:
select * from table order by random() limit 1000;
5 億行の非常に大きなテーブルがあり、それを高速化したいと考えています。
どちらのアプローチが優れているでしょうか? 違いは何でしょうか? ランダムな行を選択する最良の方法は何でしょうか?
ベストアンサー1
速い方法
あなたの仕様（およびコメント内の追加情報）を考慮すると：
- ギャップがわずか（または中程度に少ない）の数値 ID 列（整数）があります。
- 明らかに書き込み操作はまったくないか、ほとんどありません。
- ID 列にインデックスを付ける必要があります。主キーを使用すると便利です。
以下のクエリでは、大きなテーブルの順次スキャンは必要なく、インデックス スキャンのみが必要です。
まず、メインクエリの見積もりを取得します。
SELECT count(*) AS ct -- optional
, min(id) AS min_id
, max(id) AS max_id
, max(id) - min(id) AS id_span
FROM big;
唯一高価な部品はcount(*)(巨大なテーブル用) です。上記の仕様を考慮すると、これは必要ありません。全数交換の見積もりはほぼ無料で入手できます。
SELECT (reltuples / relpages * (pg_relation_size(oid) / 8192))::bigint AS ct
FROM pg_class
WHERE oid = 'big'::regclass; -- your table name
詳細な説明:
が より大幅に小さくctない限り、クエリは他のアプローチよりも優れたパフォーマンスを発揮します。id_span
WITH params AS (
SELECT 1 AS min_id -- minimum id <= current min id
, 5100000 AS id_span -- rounded up. (max_id - min_id + buffer)
)
SELECT *
FROM (
SELECT p.min_id + trunc(random() * p.id_span)::integer AS id
FROM params p
, generate_series(1, 1100) g -- 1000 + buffer
GROUP BY 1 -- trim duplicates
) r
JOIN big USING (id)
LIMIT 1000; -- trim surplus
空間内に乱数を生成します
id。「ギャップが少ない」ため、取得する行数に 10 % (空白を簡単にカバーできる程度) を追加します。それぞれが
id偶然に複数回選択される可能性があります (ただし、ID スペースが大きい場合は可能性が非常に低い)。そのため、生成された番号をグループ化します (または を使用しますDISTINCT)。s を大きなテーブルに結合します
id。インデックスが配置されているため、これは非常に高速になるはずです。最後に、
id重複やギャップによって消費されなかった余分な を削除します。すべての行が選択される可能性は完全に等しくなります。
短縮版
このクエリを簡略化することができます。上記のクエリの CTE は教育目的のみです。
SELECT *
FROM (
SELECT DISTINCT 1 + trunc(random() * 5100000)::integer AS id
FROM generate_series(1, 1100) g
) r
JOIN big USING (id)
LIMIT 1000;
rCTEで絞り込む
特にギャップや見積もりについて確信が持てない場合はそうです。
WITH RECURSIVE random_pick AS (
SELECT *
FROM (
SELECT 1 + trunc(random() * 5100000)::int AS id
FROM generate_series(1, 1030) -- 1000 + few percent - adapt to your needs
LIMIT 1030 -- hint for query planner
) r
JOIN big b USING (id) -- eliminate miss
UNION -- eliminate dupe
SELECT b.*
FROM (
SELECT 1 + trunc(random() * 5100000)::int AS id
FROM random_pick r -- plus 3 percent - adapt to your needs
LIMIT 999 -- less than 1000, hint for query planner
) r
JOIN big b USING (id) -- eliminate miss
)
TABLE random_pick
LIMIT 1000; -- actual limit
基本クエリでより小さな余剰で作業できます。ギャップが多すぎて最初の反復で十分な行が見つからない場合、rCTE は再帰項を使用して反復を続けます。ID スペースには依然として比較的少ないギャップが必要です。そうしないと、制限に達する前に再帰が枯渇する可能性があります。または、パフォーマンスの最適化の目的に反して、十分に大きなバッファーから開始する必要があります。
UNIONrCTE では重複が削除されます。
外側では、LIMIT十分な行数に達するとすぐに CTE が停止します。
このクエリは、利用可能なインデックスを使用し、実際にランダムな行を生成し、制限を満たすまで停止しないように注意深く作成されています (再帰が枯渇しない限り)。これを書き直す場合、いくつかの落とし穴があります。
関数にラップする
異なるパラメータを持つ同じテーブルを繰り返し使用する場合:
CREATE OR REPLACE FUNCTION f_random_sample(_limit int = 1000, _gaps real = 1.03)
RETURNS SETOF big
LANGUAGE plpgsql VOLATILE ROWS 1000 AS
$func$
DECLARE
_surplus int := _limit * _gaps;
_estimate int := ( -- get current estimate from system
SELECT (reltuples / relpages * (pg_relation_size(oid) / 8192))::bigint
FROM pg_class
WHERE oid = 'big'::regclass);
BEGIN
RETURN QUERY
WITH RECURSIVE random_pick AS (
SELECT *
FROM (
SELECT 1 + trunc(random() * _estimate)::int
FROM generate_series(1, _surplus) g
LIMIT _surplus -- hint for query planner
) r (id)
JOIN big USING (id) -- eliminate misses
UNION -- eliminate dupes
SELECT *
FROM (
SELECT 1 + trunc(random() * _estimate)::int
FROM random_pick -- just to make it recursive
LIMIT _limit -- hint for query planner
) r (id)
JOIN big USING (id) -- eliminate misses
)
TABLE random_pick
LIMIT _limit;
END
$func$;
電話：
SELECT * FROM f_random_sample();
SELECT * FROM f_random_sample(500, 1.05);
汎用関数
これを汎用化して、一意の整数列 (通常は PK) を持つ任意のテーブルで動作するようにすることができます。テーブルを多態型として渡し、(オプションで) PK 列の名前を指定して、次を使用しますEXECUTE。
CREATE OR REPLACE FUNCTION f_random_sample(_tbl_type anyelement
, _id text = 'id'
, _limit int = 1000
, _gaps real = 1.03)
RETURNS SETOF anyelement
LANGUAGE plpgsql VOLATILE ROWS 1000 AS
$func$
DECLARE
-- safe syntax with schema & quotes where needed
_tbl text := pg_typeof(_tbl_type)::text;
_estimate int := (SELECT (reltuples / relpages
* (pg_relation_size(oid) / 8192))::bigint
FROM pg_class -- get current estimate from system
WHERE oid = _tbl::regclass);
BEGIN
RETURN QUERY EXECUTE format(
$$
WITH RECURSIVE random_pick AS (
SELECT *
FROM (
SELECT 1 + trunc(random() * $1)::int
FROM generate_series(1, $2) g
LIMIT $2 -- hint for query planner
) r(%2$I)
JOIN %1$s USING (%2$I) -- eliminate misses
UNION -- eliminate dupes
SELECT *
FROM (
SELECT 1 + trunc(random() * $1)::int
FROM random_pick -- just to make it recursive
LIMIT $3 -- hint for query planner
) r(%2$I)
JOIN %1$s USING (%2$I) -- eliminate misses
)
TABLE random_pick
LIMIT $3;
$$
, _tbl, _id
)
USING _estimate -- $1
, (_limit * _gaps)::int -- $2 ("surplus")
, _limit -- $3
;
END
$func$;
デフォルトで呼び出します (重要!):
SELECT * FROM f_random_sample(null::big); --!
あるいはもっと具体的には:
SELECT * FROM f_random_sample(null::"my_TABLE", 'oDD ID', 666, 1.15);
静的バージョンとほぼ同じパフォーマンス。
関連している：
- PL/pgSQL 関数をリファクタリングして、さまざまな SELECT クエリの出力を返す- 章「さまざまな完全なテーブルの種類」
- PostgreSQL関数からSETOF行を返す
- EXECUTE の format() 内の整数変数のフォーマット指定子ですか?
- トリガー関数で動的テーブル名を指定した INSERT
これは SQL インジェクションに対して安全です。参照:
代替案
繰り返しの呼び出しに対して同一のセットが許容される場合（繰り返しの呼び出しについて話している）、MATERIALIZED VIEW上記のクエリを 1 回実行し、結果をテーブルに書き込みます。ユーザーは、超高速で準ランダム選択を取得できます。選択した間隔またはイベントでランダム選択を更新します。
Postgres 9.5では、TABLESAMPLE SYSTEM (n)
nパーセンテージはここです。マニュアル:
およびサンプリング方法はそれぞれ、サンプリングするテーブルの割合を表す単一の引数を受け入れます。この引数は、 0 から 100 までのパーセンテージで表されます
BERNOULLI。この引数には、任意の 値の式を指定できます。SYSTEMreal
太字は私の強調です。非常に高速ですが、結果は完全にランダムではありません。マニュアルをもう一度引用します。
この
SYSTEM方法は、小さなサンプリング パーセンテージが指定されている場合、方法よりも大幅に高速ですBERNOULLIが、クラスタリング効果の結果として、テーブルのランダム性の低いサンプルが返される可能性があります。
返される行数は大きく異なる場合があります。この例では、約1000 行を取得するには、次のようにします。
SELECT * FROM big TABLESAMPLE SYSTEM ((1000 * 100) / 5100000.0);
関連している：
または追加モジュールをインストールするtsm_システム行要求された行数を正確に取得し（十分な数がある場合）、より便利な構文を使用できるようにします。
SELECT * FROM big TABLESAMPLE SYSTEM_ROWS(1000);
見るエヴァンの答え詳細については。
しかし、それはまだ完全にランダムというわけではありません。