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

PostgreSQL などの大規模なデータベースから行をランダムに選択することは、パフォーマンスに負荷がかかる作業になる可能性があります。この記事では、この目標を効率的に達成するための 2 つの一般的な方法を検討し、それぞれの長所と短所について説明します。

方法 1: ランダムな値でフィルターする

<code class="language-sql">select * from table where random() < 0.01;</code>

このメソッドは行をランダムに並べ替えてから、しきい値に基づいてフィルター処理します。ただし、テーブル全体のスキャンが必要であり、大規模なデータ セットの場合は時間がかかる可能性があります。

方法 2: ランダムな値で並べ替え、結果を制限する

<code class="language-sql">select * from table order by random() limit 1000;</code>

このメソッドは行をランダムに並べ替えて、上位 n 行を選択します。最初の方法よりもパフォーマンスは向上しますが、制限があります。行グループ内の行が多すぎると、ランダムなサブセットを選択できない可能性があります。

大規模なデータセット向けの最適化ソリューション

多数の行 (この例では 5 億行など) を持つテーブルの場合、次のアプローチにより最適化されたソリューションが提供されます。

<code class="language-sql">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   big USING (id)
LIMIT  1000;                          -- 去除多余项</code>

このクエリは、効率的な取得のために ID 列のインデックスを利用します。 ID 空間内で一連の乱数を生成し、ID が一意であることを確認し、データをメイン テーブルと結合して必要な行数を選択します。

その他の考慮事項

境界クエリ: 乱数生成時に大きなバッファが必要になるのを避けるために、テーブル ID 列のギャップが比較的少ないことが重要です。

具体化されたビュー: ランダム データに繰り返しアクセスする必要がある場合は、マテリアライズド ビューを作成してパフォーマンスを向上させることを検討してください。

PostgreSQL 9.5 のテーブルサンプルシステム: PostgreSQL 9.5 で導入されたこの最適化手法により、指定された割合の行を高速にサンプリングできます。

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