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

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

PostgreSQL は、ランダムな行を効率的に選択するためのさまざまな方法を提供します。

方法 1: Random() と Limit 句を使用する

このメソッドは、random() 関数と LIMIT 句を使用します。

SELECT *
FROM table
ORDER BY random()
LIMIT 1000;

ただし、大きなテーブルの場合、この方法ではテーブル全体のスキャンが必要になるため、速度が遅くなる可能性があります。

方法 2: インデックスベースの方法

このメソッドは主キー インデックスを使用してクエリを最適化します:

WITH params AS (
   SELECT 1       AS min_id,          -- 最小ID (大于等于当前最小ID)
        , 5100000 AS id_span          -- 四舍五入 (max_id - min_id + 缓冲)
)
SELECT *
FROM  (
   SELECT p.min_id + trunc(random() * p.id_span)::integer AS id
   FROM   params p
        , generate_series(1, 1100) g  -- 1000 + 缓冲
   GROUP  BY 1                        -- 去除重复项
) r
JOIN   table USING (id)
LIMIT  1000;                          -- 去除多余项

この方法は、フルテーブルスキャンの代わりにインデックススキャンを使用するため、方法 1 よりも高速です。

方法 3: 再帰的 CTE を使用する

このメソッドは、再帰共通テーブル式 (CTE) を使用して、ID 列の欠損値を処理します。

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   table b USING (id)             -- 去除缺失值

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

方法 4: TABLESAMPLE SYSTEM (n) を使用する

PostgreSQL 9.5 では TABLESAMPLE SYSTEM (n) 構文が導入されました。ここで、n は 0 から 100 までのパーセンテージです:

SELECT *
FROM big
TABLESAMPLE SYSTEM ((1000 * 100) / 5100000.0);

このメソッドは高速ですが、クラスタリング効果により真にランダムなサンプルを返さない可能性があります。

比較と提案

テーブルに ID 列の欠損値がほとんどなく、主キー インデックスが配置されている場合は、方法 2 (インデックス ベースの方法) が最良の選択です。これは最高の速度とパフォーマンスを提供します。正確なセックス。

欠損値が多いテーブルの場合は、欠損値を効果的に処理できる 方法 3 (再帰的 CTE) を検討してください。

方法 1 (random() および limit) はパフォーマンスが低いため、小さいテーブルで使用する必要があります。

方法 4(TABLESAMPLE SYSTEM) は高速ですが、他の方法ほど正確ではありません。これを使用すると、大きなテーブルで迅速な見積もりを行うことができます。

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