ランダムな行を選択する最良の方法PostgreSQL



Answers

両方の実行計画を調べて比較するには、以下を使用します。

EXPLAIN select * from table where random() < 0.01;
EXPLAIN select * from table order by random() limit 1000;

大きな表1を簡単にテストすると、 ORDER BY最初に完全な表をソートし、最初の1000項目を選択します。 大きなテーブルをソートすると、そのテーブルだけでなく、テンポラリファイルの読み書きも含まれます。 where random() < 0.1は、完全なテーブルを1回スキャンするだけです。

大規模なテーブルでは、完全な1つのテーブルスキャンでも時間がかかることがあるので、これは必要なものではありません。

3番目の提案は

select * from table where random() < 0.01 limit 1000;

これは、1000行が見つかると直ちにテーブルスキャンを停止し、より早く戻ります。 もちろん、これはランダム性を少し低下させますが、おそらくこれで十分です。

編集:この考慮事項の他に、あなたはこれについて既に質問された質問をチェックするかもしれません。 クエリ[postgresql] randomを使用するとかなりのヒット数を返します。

そしていくつかのアプローチを概説したdepezのリンクされた記事:

"完全なテーブルはメモリに収まらない"のように "大" 1

Question

私はPostgreSQLで行をランダムに選択したい、私はこれを試した:

select * from table where random() < 0.01;

しかし、他の人はこれをお勧めします:

select * from table order by random() limit 1000;

私は500万行の非常に大きなテーブルを持っています。

どのアプローチが良いですか? 違いは何ですか? ランダムな行を選択する最善の方法は何ですか?




Erwin Brandstetterが概説したマテリアライズド・ビュー「可能な代替」のバリエーションが可能です。

たとえば、返されるランダム化された値に重複が欲しくないとします。 したがって、(無作為化されていない)値セットを含むプライマリテーブルにブール値を設定する必要があります。

これが入力テーブルであると仮定します:

id_values  id  |   used
           ----+--------
           1   |   FALSE
           2   |   FALSE
           3   |   FALSE
           4   |   FALSE
           5   |   FALSE
           ...

必要に応じてID_VALUES表に移入します。 次に、Erwinの説明ID_VALUESID_VALUES表を一度ランダム化するマテリアライズド・ビューを作成します。

CREATE MATERIALIZED VIEW id_values_randomized AS
  SELECT id
  FROM id_values
  ORDER BY random();

マテリアライズド・ビューにはusedカラムが含まれていないことに注意してください。これはすぐに期限切れになるためです。 ビューには、 id_valuesテーブルにある可能性のある他の列も含まれている必要はありません。

ランダムな値を取得(および「消費」)するには、 id_valuesでUPDATE-RETURNINGを使用し、結合でid_valuesからid_values_randomizedを選択し、関連する可能性のみを得るために必要な基準を適用します。 例えば:

UPDATE id_values
SET used = TRUE
WHERE id_values.id IN 
  (SELECT i.id
    FROM id_values_randomized r INNER JOIN id_values i ON i.id = r.id
    WHERE (NOT i.used)
    LIMIT 5)
RETURNING id;

必要に応じてLIMITを変更します。一度に1つの乱数値が必要な場合は、 LIMIT1に変更します。

id_valuesに適切なインデックスを付けてid_valuesと、UPDATE-RETURNINGは負荷がほとんどなくても非常に迅速に実行されるはずです。 1回のデータベース往復で無作為化された値を返します。 「適格な」行の基準は、必要なだけ複雑にすることができます。 新しい行はいつでもid_values表に追加することができ、マテリアライズド・ビューがリフレッシュされるとすぐにアプリケーションにアクセスできます(オフピーク時に実行される可能性があります)。 マテリアライズド・ビューの作成とリフレッシュは遅くなりますが、新しいIDがid_values表に追加されたときに実行する必要があります。




思考のSQLモードに巻き込まれた後、私のユースケースでは、次のメソッドが動作するはずであることに気付きました。

私の目的では、「過去1か月の」ランダム要素の指定数(約10〜20)を選択したいと考えています(300〜400要素のサブセット - サーバーアクティビティの増加に伴って増加しない数値)。 Mickaëlのソリューションはほとんど法案に適合しましたが、 WHERE句の後にTABLESAMPLEを使用することはできないようTABLESAMPLE

ここに私が到着した解決策があります:

まず、簡単なクエリを実行しました。

SELECT id FROM table WHERE "timestamp" > now()::Date - 30

結果が私のプログラムに戻ったら、IDのランダムサンプルを選択しました。 それから私は単に走った:

SELECT * FROM table WHERE id IN (1,2,3) (ここで(1,2,3)はランダムに選択されたサンプルです)。

私はこれが厳密にPostgreSQLのソリューションではないことを認識していますが、スケーリングの制約を気にすれば、うまくいくはずです。 うまくいけば、それは同じような立場の誰かにはうってつけでしょう。




私はパーティーに少し遅れていることを知っていますが、私はこの素晴らしいツールpg_sample見つけました。

私はこれを350M行のデータベースで試してみましたが、それは本当に速く、 ランダム性についてはわかりません。

./pg_sample --limit="small_table = *" --limit="large_table = 100000" -U postgres source_db | psql -U postgres target_db



ここに私のために働く決定があります。 私は理解して実行するのがとても簡単だと思います。

SELECT 
  field_1, 
  field_2, 
  field_2, 
  random() as ordering
FROM 
  big_table
WHERE 
  some_conditions
ORDER BY
  ordering 
LIMIT 1000;



ORDER BYを持つものは遅いものになります。

select * from table where random() < 0.01; レコードごとにランダムにフィルタリングするかどうかを決定します。 これはO(N)なぜなら、各レコードを1回だけチェックする必要があるからです。

select * from table order by random() limit 1000; テーブル全体をソートし、最初の1000を選びます。シーンの背後にあるブードゥーの魔法の他に、順序はO(N * log N)です。

random() < 0.01の欠点は、出力レコードの数が変わることです。

注意してください。ランダムに並べ替えるよりも、データセットをシャッフルする方が良い方法があります.Fisher-Yates Shuffleは、 O(N)で実行されます。 しかし、SQLでシャッフルを実装することはかなり難しい問題です。




Related