development

임의의 행을 선택하는 가장 좋은 방법 PostgreSQL

big-blog 2020. 3. 16. 08:24
반응형

임의의 행을 선택하는 가장 좋은 방법 PostgreSQL

PostgreSQL에서 무작위로 행을 선택하고 싶습니다.

select * from table where random() < 0.01;

그러나 다른 사람들은 이것을 추천합니다 :

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

나는 500 백만 행이있는 매우 큰 테이블을 가지고 있습니다.

어떤 접근법이 더 낫습니까? 차이점은 무엇입니까? 임의의 행을 선택하는 가장 좋은 방법은 무엇입니까?

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

  • 간격이 거의없는 숫자 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하십시오. 보다:

가능한 대안

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

Postgres 9.5 소개 TABLESAMPLE SYSTEM (n)

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

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

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

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

리턴되는 행 수는 크게 다를 수 있습니다. 이 예에서는 약 1000 개의 행 을 가져옵니다 .

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

관련 :

또는 추가 모듈 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;

큰 테이블 1 에 대한 빠른 테스트 는 ORDER BY첫 번째 테이블이 전체 테이블을 정렬 한 다음 처음 1000 개의 항목을 선택 함을 보여줍니다 . 큰 테이블을 정렬하면 해당 테이블을 읽을뿐만 아니라 임시 파일을 읽고 쓰는 작업도 포함됩니다. 는 where random() < 0.1한 번만 전체 테이블을 스캔합니다.

큰 테이블의 경우 한 번의 전체 테이블 스캔으로 시간이 오래 걸릴 수 있으므로 원하는 것이 아닐 수도 있습니다.

세 번째 제안은

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

이것은 1000 개의 행이 발견 되 자마자 테이블 스캔을 중지하고 더 빨리 리턴합니다. 물론 이것은 임의성을 조금 떨어 뜨립니다. 그러나 아마도 이것은 귀하의 경우에 충분합니다.

편집 : 이 고려 사항 외에도 이미 질문 한 내용을 확인할 수 있습니다. 쿼리를 사용하면 [postgresql] random상당히 많은 히트가 반환됩니다.

그리고 몇 가지 접근 방식을 간략히 설명하는 링크 된 depez 기사 :

1 "전체 테이블이 메모리에 맞지 않습니다"와 같이 "큰"것입니다.

random ()에 의한 postgresql 순서, 무작위 순서로 행을 선택하십시오.

select your_columns from your_table ORDER BY random()

고유 한 random () 별 postgresql 순서 :

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

임의의 한 행으로 postgresql 순서 :

select your_columns from your_table ORDER BY random() limit 1

PostgreSQL 9.5부터는 테이블에서 임의의 요소를 가져 오는 전용 구문이 새로 추가되었습니다.

SELECT * FROM mytable TABLESAMPLE SYSTEM (5);

이 예제는의 요소 중 5 %를 제공합니다 mytable.

이 블로그 게시물에 대한 자세한 설명을 참조하십시오 : http://www.postgresql.org/docs/current/static/sql-select.html

ORDER BY가있는 것이 느릴 것입니다.

select * from table where random() < 0.01;레코드별로 기록하고 임의로 필터링할지 여부를 결정합니다. 이것은 O(N)각 레코드를 한 번만 확인 하면 되기 때문입니다.

select * from table order by random() limit 1000;전체 테이블을 정렬 한 다음 처음 1000 개를 선택합니다. 장면 뒤의 부두 마법을 제외하고 순서는입니다 O(N * log N).

random() < 0.01하나 의 단점 은 가변 수의 출력 레코드를 얻는다는 것입니다.

: 주, 무작위로 정렬보다 데이터 세트를 셔플에 더 나은 방법이 피셔 - 예이츠 셔플 에서 실행 O(N). 그러나 SQL에서 셔플을 구현하는 것은 상당히 어려운 일입니다.

여기 나를위한 결정이 있습니다. 이해하고 실행하는 것이 매우 간단하다고 생각합니다.

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;

원하는 행 수를 알고 있으면를 확인하십시오 tsm_system_rows.

tsm_system_rows

모듈은 SELECT 명령의 TABLESAMPLE 절에서 사용할 수있는 테이블 샘플링 방법 SYSTEM_ROWS를 제공합니다.

이 테이블 샘플링 방법은 읽을 최대 행 수인 단일 정수 인수를 허용합니다. 테이블에 충분한 행이 포함되어 있지 않으면 전체 테이블이 선택되지 않는 한 결과 샘플에는 항상 정확히 많은 행이 포함됩니다. 내장 된 SYSTEM 샘플링 방법과 마찬가지로 SYSTEM_ROWS는 블록 수준 샘플링을 수행하므로 샘플이 완전히 임의적이지는 않지만 특히 적은 수의 행만 요청되는 경우 클러스터링 효과가 발생할 수 있습니다.

먼저 확장을 설치하십시오

CREATE EXTENSION tsm_system_rows;

그런 다음 쿼리

SELECT *
FROM table
TABLESAMPLE SYSTEM_ROWS(1000);

하나의 행만 원하면 offset에서 파생 된 계산을 사용할 수 있습니다 count.

select * from table_name limit 1
offset floor(random() * (select count(*) from table_name));

Erwin Brandstetter에 의해 요약 된 구체화 된 관점 "가능한 대안"의 변형 이 가능하다.

예를 들어, 리턴 된 무작위 값에서 중복을 원하지 않는다고 가정하십시오. 따라서 (랜덤 화되지 않은) 값 세트를 포함하는 기본 테이블에서 부울 값을 설정해야합니다.

이것이 입력 테이블이라고 가정합니다.

id_values  id  |   used
           ----+--------
           1   |   FALSE
           2   |   FALSE
           3   |   FALSE
           4   |   FALSE
           5   |   FALSE
           ...

ID_VALUES필요에 따라 테이블을 채우십시오 . 그런 다음 Erwin에서 설명한대로 ID_VALUES테이블을 한 번 무작위 화하는 구체화 된보기를 작성하십시오 .

CREATE MATERIALIZED VIEW id_values_randomized AS
  SELECT id
  FROM id_values
  ORDER BY random();

구체화 된보기에는 사용 된 열이 포함되어 있지 않습니다.이 열은 빠르게 오래된 정보가되기 때문입니다. 뷰에는 id_values테이블에 있을 수있는 다른 열이 포함될 필요도 없습니다 .

임의의 값을 얻으려면 (및 "소비") UPDATE-RETURNING on 을 사용하고 조인으로 id_values선택 하고 원하는 기준 만 적용하여 관련 가능성 만 얻으십시오. 예를 들면 다음과 같습니다.id_valuesid_values_randomized

UPDATE id_values
SET used = TRUE
WHERE id_values.id IN 
  (SELECT i.id
    FROM id_values_randomized r INNER JOIN id_values i ON i.id = r.id
    WHERE (NOT i.used)
    LIMIT 5)
RETURNING id;

변경 LIMIT필요에 따라 - 당신이 한 번에 하나 개의 임의의 값 변경이 필요한 경우 LIMIT에를 1.

적절한 인덱스를 사용하면 id_valuesUPDATE-RETURNING이 적은로드로 매우 빠르게 실행되어야한다고 생각합니다. 하나의 데이터베이스 왕복으로 무작위 값을 리턴합니다. "적격"행에 대한 기준은 필요한만큼 복잡 할 수 있습니다. 새 행은 id_values언제든지 테이블에 추가 할 수 있으며 구체화 된보기를 새로 고치면 (피크가 적은 시간에 실행될 수 있음) 애플리케이션에 액세스 할 수 있습니다. 구체화 된 뷰의 작성 및 새로 고침은 느리지 만 새 ID를 id_values테이블에 추가 할 때만 실행하면됩니다 .

rtype 이라는 열을 추가하십시오 serial. 색인 r.

200,000 개의 행이 있다고 가정하면 n0 n<<= 200, 000 인 난수를 생성 할 것 입니다.

로 행을 선택하고 r > n정렬 ASC한 후 가장 작은 행을 선택하십시오 .

암호:

select * from YOUR_TABLE 
where r > (
    select (
        select reltuples::bigint AS estimate
        from   pg_class
        where  oid = 'public.YOUR_TABLE'::regclass) * random()
    )
order by r asc limit(1);

코드는 자명하다. 가운데의 하위 쿼리는 https://stackoverflow.com/a/7945274/1271094 에서 테이블 행 수를 빠르게 추정하는 데 사용됩니다 .

응용 프로그램 레벨에서 n> 행 수> 여러 행을 선택해야하는 경우 명령문을 다시 실행 해야합니다.

나는 파티에 조금 늦었다는 것을 알고 있지만 pg_sample 이라는 멋진 도구를 발견했습니다 .

pg_sample -참조 무결성을 유지하면서 더 큰 PostgreSQL 데이터베이스에서 작은 샘플 데이터 세트를 추출합니다.

나는 350M 행 데이터베이스로 이것을 시도했고 정말 빠르다 . 임의 에 대해 모른다 .

./pg_sample --limit="small_table = *" --limit="large_table = 100000" -U postgres source_db | psql -U postgres target_db

내 경험에서 얻은 한 가지 교훈 :

offset floor(random() * N) limit 1보다 빠르지 않습니다 order by random() limit 1.

offsetPostgres에서 정렬 시간을 절약해야하기 때문에 접근법이 더 빠를 것이라고 생각했습니다 . 그렇지 않은 것으로 밝혀졌습니다.

참고 URL : https://stackoverflow.com/questions/8674718/best-way-to-select-random-rows-postgresql

반응형

'development' 카테고리의 다른 글

JavaScript를 사용하여 새 페이지를로드하지 않고 브라우저에서 URL을 변경하십시오.  (0) 2020.03.16
주요 C # DI / IoC 프레임 워크는 어떻게 비교됩니까?  (0) 2020.03.16
세션이란 무엇입니까?  (0) 2020.03.16
Ruby에서 require_relative와 require의 차이점은 무엇입니까?  (0) 2020.03.16
GitHub에서 리포지토리의 이름을 바꾸려면 어떻게합니까?  (0) 2020.03.16

'development'의 다른글

  • 현재글임의의 행을 선택하는 가장 좋은 방법 PostgreSQL

관련글

댓글

티스토리툴바