大規模な PostgreSQL テーブルからランダムな行を効率的に選択するにはどうすればよいですか?

大きな PostgreSQL テーブルからランダムな行を選択します

大規模なデータセットを扱う場合、ランダムな行を選択するのは計算負荷の高いタスクになる可能性があります。この記事では、約 5 億行を含むテーブルからランダムな行を取得するさまざまな方法を検討し、そのパフォーマンスと精度について説明します。

方法 1: RANDOM() と LIMIT を使用する

最初の方法では、RANDOM() 関数を使用して乱数を生成し、次に LIMIT 句を使用して結果をフィルターし、必要な行数を取得します。

SELECT * FROM table WHERE RANDOM() < 0.000002 LIMIT 1000;

このアプローチには実装が簡単という利点がありますが、大きなテーブルの場合は非効率的になる可能性があります。 LIMIT 句があるため、データベースはテーブルのすべての行をスキャンしてランダムな行を選択し、残りを破棄する必要があります。

方法 2: ORDER BY RANDOM() と LIMIT を使用する

もう 1 つの方法は、最初に RANDOM() 関数によって行を並べ替えてから、LIMIT 句を使用してランダムな行を取得することです。

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

この方法は最初の方法に似ていますが、並べ替えによりランダムな行のより効率的な選択が保証されます。必要なスキャンの数が減るため、大規模なテーブルに適した選択肢になります。ただし、行数が非常に多いテーブルには依然として最適な選択ではありません。

効率的なアプローチ: 数値 ID 列とインデックスを使用します

数値 ID 列がありギャップが少ないテーブルの場合は、より効率的なアプローチを使用できます。これには、ID の範囲内で乱数を生成し、それらを使用してテーブルに結合することが含まれます。

WITH params AS (
   SELECT 1 AS min_id,              -- 最小 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   table USING (id)
LIMIT  1000;

このアプローチでは、インデックス アクセスを利用して、必要なスキャンの数を大幅に削減します。これは、行数が多く、ID 列のギャップがほとんどないテーブルに最適です。

考慮事項と推奨事項

ランダムな行を選択する最適な方法は、特定のテーブルの特性とパフォーマンス要件によって異なります。小さなテーブルの場合は、RANDOM() メソッドまたは ORDER BY RANDOM() メソッドで十分な場合があります。ただし、数値 ID 列がありギャップがほとんどない大きなテーブルの場合は、最高のパフォーマンスを得るために上記の最適化方法を使用することをお勧めします。

コンピューターにおける擬似乱数生成の性質により、これらの方法はいずれも真のランダム性を保証できないことに注意してください。ただし、これらは、大きなテーブルから行のランダムなサンプルを適度な効率と精度で取得する実用的な方法を提供します。

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