PostgreSQL でランダムな行を効率的に選択するにはどうすればよいですか?

PostgreSQL の効率的なランダム行選択方法

PostgreSQL でランダムな行を選択するための最適な方法は、テーブルのサイズ、利用可能なインデックス、必要なランダム性のレベルによって異なります。

5 億行と数値 ID 列 (id など) を持つ非常に大きなテーブルの場合:

• 最速の方法:

  • CTE 関数と random() 関数を使用して、ID 空間内でランダムな ID を生成します。
  • id 列を使用して、生成された ID をテーブルに結合します。
  • 重複をフィルタリングして、重複する ID を削除します。

<code class="language-sql">WITH params AS (
   SELECT 1       AS min_id,           -- 最小id
        , 5100000 AS id_span          -- 四舍五入。(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                        -- 去除重复项
) r
JOIN   big USING (id)
LIMIT  1000;                          -- 去除多余项</code>

• 改善方法:

  • 再帰 CTE (random_pick) を使用して、ID 空間内のギャップを排除します。
  • 再帰的な結果をマージして重複を排除します。
  • 外部を適用LIMIT して制約を満たす。

<code class="language-sql">WITH RECURSIVE random_pick AS (
   SELECT *
   FROM  (
      SELECT 1 + trunc(random() * 5100000)::int AS id
      FROM   generate_series(1, 1030)  -- 1000 + 百分之几 - 根据需要调整
      LIMIT  1030                      -- 查询规划器提示
      ) r
   JOIN   big b USING (id)             -- 消除缺失

   UNION                               -- 消除重复项
   SELECT b.*
   FROM  (
      SELECT 1 + trunc(random() * 5100000)::int AS id
      FROM   random_pick r             -- 加上百分之三 - 根据需要调整
      LIMIT  999                       -- 小于1000，查询规划器提示
      ) r
   JOIN   big b USING (id)             -- 消除缺失
   )
TABLE  random_pick
LIMIT  1000;  -- 实际限制</code>

• 一般的な機能:

  • 上記のクエリを関数にラップして、一意の整数列を持つテーブルで再利用できるようにします。

<code class="language-sql">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
   _tbl text := pg_typeof(_tbl_type)::text;
   _estimate int := (...);
BEGIN
   RETURN QUERY EXECUTE format(
   $$
   WITH RECURSIVE random_pick AS (
      SELECT ...
      FROM  ...
     ...
   )
   TABLE  random_pick
   LIMIT  ;
   $$
 , _tbl, _id
   )
   USING (...);
END
$func$;</code>

正確なランダム性や呼び出しの繰り返しを必要としないシナリオの場合:

• 具体化されたビュー:

  • ほぼランダムに選択された行を保存するマテリアライズド ビューを作成します。
  • マテリアライズド ビューを定期的に更新します。

• TABLESAMPLE SYSTEM (n):

  • PostgreSQL 9.5 で導入された TABLESAMPLE SYSTEM (n) は、高速かつ不正確なランダム サンプリング手法を提供します。
  • n パラメーターは、サンプリングされるテーブルの割合を表します。

<code class="language-sql">SELECT * FROM big TABLESAMPLE SYSTEM ((1000 * 100) / 5100000.0);</code>

その他の注意事項:

• 最高のパフォーマンスを得るには、ID 列にインデックスを使用します。
• PostgreSQL の random() 関数は暗号的に安全ではありません。
• 提案されたアプローチは、ほとんどの実際的な使用例に対して高度なランダム性を提供します。

以上がPostgreSQL でランダムな行を効率的に選択するにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。