귀하의 사양 (주석에 추가 정보 포함)이 주어지면,

• 공백이 거의없는 숫자 ID 열 (정수)이 있습니다.
• 쓰기 작업이 거의 없거나 거의 없습니다.
• ID 열을 색인해야합니다! 기본 키가 훌륭하게 제공됩니다.

아래 쿼리에는 큰 테이블의 순차적 스캔이 필요하지 않고 인덱스 스캔 만 필요합니다.

먼저 기본 쿼리에 대한 추정치를 가져옵니다.

SELECT count(*) AS ct              -- optional
     , min(id)  AS min_id
     , max(id)  AS max_id
     , max(id) - min(id) AS id_span
FROM   big;

유일하게 비싼 부분은 count(*)(거대한 테이블)입니다. 위의 사양이 주어지면 필요하지 않습니다. 견적은 거의 무료로 사용할 수 있습니다 ( 자세한 설명은 여기 참조 ).

SELECT reltuples AS ct FROM pg_class WHERE oid = 'schema_name.big'::regclass;

긴뿐만 ct아닌 훨씬 보다 작은 id_span쿼리는 다른 접근 방식을 능가 할 것이다.

WITH params AS (
    SELECT 1       AS min_id           -- minimum id <= current min id
         , 5100000 AS id_span          -- rounded up. (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                        -- trim duplicates
    ) r
JOIN   big USING (id)
LIMIT  1000;                           -- trim surplus

• id공간 에서 난수를 생성하십시오 . 공백이 거의 없으므로 검색 할 행 수에 10 % (공백을 쉽게 덮을 수 있음)를 추가하십시오.

• 각각 id은 우연히 여러 번 선택할 수 있으므로 (ID 공간이 크지는 않지만) 생성 된 숫자를 그룹화하십시오 (또는 use DISTINCT).

• ids를 큰 테이블에 결합하십시오 . 인덱스가 있으면 매우 빠릅니다.

• 마지막으로 id속박과 틈으로 먹지 않은 잉여분을 다듬 습니다. 모든 행은 완전히 같은 기회 를 선택할 수 있습니다.

짧은 버전

이 쿼리 를 단순화 할 수 있습니다 . 위 쿼리에서 CTE는 교육 목적으로 만 사용됩니다.

SELECT *
FROM  (
    SELECT DISTINCT 1 + trunc(random() * 5100000)::integer AS id
    FROM   generate_series(1, 1100) g
    ) r
JOIN   big USING (id)
LIMIT  1000;

rCTE로 개선

특히 차이와 추정치가 확실하지 않은 경우.

WITH RECURSIVE random_pick AS (
   SELECT *
   FROM  (
      SELECT 1 + trunc(random() * 5100000)::int AS id
      FROM   generate_series(1, 1030)  -- 1000 + few percent - adapt to your needs
      LIMIT  1030                      -- hint for query planner
      ) r
   JOIN   big b USING (id)             -- eliminate miss

   UNION                               -- eliminate dupe
   SELECT b.*
   FROM  (
      SELECT 1 + trunc(random() * 5100000)::int AS id
      FROM   random_pick r             -- plus 3 percent - adapt to your needs
      LIMIT  999                       -- less than 1000, hint for query planner
      ) r
   JOIN   big b USING (id)             -- eliminate miss
   )
SELECT *
FROM   random_pick
LIMIT  1000;  -- actual limit

기본 쿼리에서 더 작은 잉여 로 작업 할 수 있습니다. 간격이 너무 많아서 첫 번째 반복에서 충분한 행을 찾지 못하면 rCTE는 반복적 인 용어로 계속 반복됩니다. 여전히 ID 공간에 상대적으로 적은 간격이 필요 하거나 한계에 도달하기 전에 재귀가 건조하게 실행될 수 있습니다.

UNIONrCTE에서 중복이 제거됩니다 .

외부 LIMIT는 행이 충분 해지면 CTE를 중지시킵니다.

이 쿼리는 사용 가능한 인덱스를 사용하고 실제로 임의의 행을 생성하며 제한이 충족 될 때까지 멈추지 않습니다 (재귀가 건조하지 않는 한). 다시 쓰려고하면 여기에 여러 가지 함정이 있습니다.

기능으로 포장

다양한 파라미터로 반복 사용하는 경우 :

CREATE OR REPLACE FUNCTION f_random_sample(_limit int = 1000, _gaps real = 1.03)
  RETURNS SETOF big AS
$func$
DECLARE
   _surplus  int := _limit * _gaps;
   _estimate int := (           -- get current estimate from system
      SELECT c.reltuples * _gaps
      FROM   pg_class c
      WHERE  c.oid = 'big'::regclass);
BEGIN

   RETURN QUERY
   WITH RECURSIVE random_pick AS (
      SELECT *
      FROM  (
         SELECT 1 + trunc(random() * _estimate)::int
         FROM   generate_series(1, _surplus) g
         LIMIT  _surplus           -- hint for query planner
         ) r (id)
      JOIN   big USING (id)        -- eliminate misses

      UNION                        -- eliminate dupes
      SELECT *
      FROM  (
         SELECT 1 + trunc(random() * _estimate)::int
         FROM   random_pick        -- just to make it recursive
         LIMIT  _limit             -- hint for query planner
         ) r (id)
      JOIN   big USING (id)        -- eliminate misses
   )
   SELECT *
   FROM   random_pick
   LIMIT  _limit;
END
$func$  LANGUAGE plpgsql VOLATILE ROWS 1000;

요구:

SELECT * FROM f_random_sample();
SELECT * FROM f_random_sample(500, 1.05);

이 표를 모든 표에서 작동하도록 만들 수도 있습니다. PK 열과 표의 이름을 다형성 유형으로 사용하고 사용 EXECUTE하십시오. 보다:

• 다양한 SELECT 쿼리의 출력을 반환하도록 PL / pgSQL 함수를 리팩터링

가능한 대안

요구 사항 이 반복 통화에 대해 동일한 세트를 허용하는 경우 (그리고 반복 통화에 대해 이야기하는 경우) 구체화 된 관점을 고려합니다 . 위의 쿼리를 한 번 실행하고 결과를 테이블에 씁니다. 사용자는 가벼운 속도로 준 무작위 선택을합니다. 선택한 간격 또는 이벤트마다 무작위 선택을 새로 고칩니다.

Postgres 9.5 소개 TABLESAMPLE SYSTEM (n)

n백분율은 어디에 있습니까 ? 매뉴얼 :

BERNOULLI및 SYSTEM샘플링 방법은 각각 다음과 같이 표현 샘플 테이블의 일부이며, 하나의 인자 수용
0과 100 사이의 비율 . 이 인수는 모든 real값을 갖는 표현식 일 수 있습니다 .

대담한 강조 광산. 그건 매우 빠르게 ,하지만 결과는 정확히 무작위 없습니다 . 매뉴얼 다시 :

이 SYSTEM방법은 BERNOULLI작은 샘플링 백분율이 지정된 경우 방법 보다 훨씬 빠르지 만 군집화 효과의 결과로 테이블의 덜 무작위 샘플을 반환 할 수 있습니다.

리턴되는 행 수는 크게 다를 수 있습니다. 예를 들어, 대략 1000 행 을 얻으려면 :

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

관련 :

• PostgreSQL에서 테이블의 행 수를 발견하는 빠른 방법

또는 추가 모듈 tsm_system_rows 를 설치하여 요청 된 행 수를 정확하게 (충분한 경우) 가져오고보다 편리한 구문을 허용하십시오.

SELECT * FROM big TABLESAMPLE SYSTEM_ROWS(1000);

자세한 내용은 Evan의 답변 을 참조하십시오.

그러나 그것은 여전히 ​​정확히 무작위가 아닙니다.

답변

EXPLAIN select * from table where random() < 0.01;
EXPLAIN select * from table order by random() limit 1000;

select * from table where random() < 0.01 limit 1000;

select your_columns from your_table ORDER BY random()

select * from
  (select distinct your_columns from your_table) table_alias
ORDER BY random()

select your_columns from your_table ORDER BY random() limit 1

SELECT * FROM mytable TABLESAMPLE SYSTEM (5);

SELECT
  field_1,
  field_2,
  field_2,
  random() as ordering
FROM
  big_table
WHERE
  some_conditions
ORDER BY
  ordering
LIMIT 1000;

select * from table order by random() limit 1000;

CREATE EXTENSION tsm_system_rows;

SELECT *
FROM table
TABLESAMPLE SYSTEM_ROWS(1000);