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 중국어 웹사이트의 기타 관련 기사를 참조하세요!