Postgres에서 빠른 임의 행 선택

Postgres에서 빠른 임의 행 선택

저는 수백만 개의 줄이 있는 포스트그레스 테이블을 가지고 있습니다.인터넷에서 확인해보니 다음과 같습니다.

SELECT myid FROM mytable ORDER BY RANDOM() LIMIT 1;

효과는 있지만, 정말 느립니다.쿼리를 수행하는 다른 방법이 있습니까? 아니면 모든 테이블을 읽지 않고 임의의 행을 직접 선택하는 방법이 있습니까?그런데 'myid'는 정수이지만 빈 필드일 수 있습니다.

실험을 해보는 것이 좋을 것입니다.OFFSET에 있어서와 같이

SELECT myid FROM mytable OFFSET floor(random() * N) LIMIT 1;

그N는 의행 다수니입에 의 개수입니다.mytable먼저 다음 작업을 수행해야 할 수 있습니다.SELECT COUNT(*)의 N.

업데이트(Antony Hatchkins)

사용해야 합니다.floor예외:

SELECT myid FROM mytable OFFSET floor(random() * N) LIMIT 1;

2개의 행으로 구성된 표를 생각합니다.random()*N를 생성합니다.0 <= x < 2 예를 들면 리고예를들면그,면,SELECT myid FROM mytable OFFSET 1.7 LIMIT 1;가장 가까운 int에 대한 암시적 반올림으로 인해 0개의 행을 반환합니다.

PostgreSQL 9.5는 훨씬 더 빠른 샘플 선택을 위한 새로운 접근 방식인 TABLESAMPLE을 도입했습니다.

구문은

SELECT * FROM my_table TABLESAMPLE BERNOULLI(percentage);
SELECT * FROM my_table TABLESAMPLE SYSTEM(percentage);

정확한 백분율을 계산하려면 테이블의 카운트를 알아야 하므로 행을 하나만 선택하려는 경우에는 이 솔루션이 최적의 솔루션이 아닙니다.

느린 카운트를 피하고 1행부터 수십억 행까지의 테이블에 빠른 TABLESSAMPLE을 사용하려면 다음 작업을 수행할 수 있습니다.

 SELECT * FROM my_table TABLESAMPLE SYSTEM(0.000001) LIMIT 1;
 -- if you got no result:
 SELECT * FROM my_table TABLESAMPLE SYSTEM(0.00001) LIMIT 1;
 -- if you got no result:
 SELECT * FROM my_table TABLESAMPLE SYSTEM(0.0001) LIMIT 1;
 -- if you got no result:
 SELECT * FROM my_table TABLESAMPLE SYSTEM(0.001) LIMIT 1;
 ...

이것은 그렇게 우아하게 보이지 않을 수도 있지만, 아마도 다른 어떤 대답보다 빠를 것입니다.

BERNULLYODER SYSTEM을 사용할지 여부를 결정하려면 http://blog.2ndquadrant.com/tablesample-in-postgresql-9-5-2/ 에서 차이점에 대해 읽어보십시오.

저는 이것을 서브쿼리와 함께 시도했고 그것은 잘 작동했습니다.오프셋, 적어도 Postgresql v8.4.4에서는 정상적으로 작동합니다.

select * from mytable offset random() * (select count(*) from mytable) limit 1 ;

은 야합다니해를 사용해야 .floor:

SELECT myid FROM mytable OFFSET floor(random()*N) LIMIT 1;

이 링크에서 몇 가지 다른 옵션을 확인하십시오.http://www.depesz.com/index.php/2007/09/16/my-thoughts-on-getting-random-row/

업데이트:(A.해치킨스)

(매우) 긴 기사의 요약은 다음과 같습니다.

저자는 네 가지 접근 방식을 나열합니다.

1)ORDER BY random() LIMIT 1;느릿느릿

2)ORDER BY id where id>=random()*N LIMIT 1 ㅠㅠㅠㅜㅜㅜㅜㅜㅜㅜㅜㅜㅜㅜㅜㅜㅜ

임의 열 - 때때로 업데이트해야 합니다.

사용자 지정 임의 집계 -- 커닝 메서드가 느릴 수 있습니다. 랜덤()을 N번 생성해야 합니다.

그리고 2번 방법을 사용하여 개선할 것을 제안합니다.

5)ORDER BY id where id=random()*N LIMIT 1결과가 비어 있는 경우 후속 재쿼리를 사용합니다.

빠른 은 임의행가가쉽장은방을 입니다.tsm_system_rows "는 다음과 같습니다.

CREATE EXTENSION IF NOT EXISTS tsm_system_rows;

그런 다음 원하는 행 수를 정확하게 선택할 수 있습니다.

SELECT myid  FROM mytable TABLESAMPLE SYSTEM_ROWS(1);

이 기능은 Postgre에서 사용할 수 있습니다.SQL 9.5 이상.

참조: https://www.postgresql.org/docs/current/static/tsm-system-rows.html

고객의 요구사항을 충족하지 않는 매우 빠른 해결책을 생각해 냈습니다.TABLESAMPLE보다 훨씬 빠름OFFSET random()*N LIMIT 1테이블 수를 세지 않아도 됩니다.

이 아이디어는 무작위이지만 예측 가능한 데이터로 표현 인덱스를 만드는 것입니다. 예를 들어,md5(primary key).

다음은 1M 행 샘플 데이터를 사용한 테스트입니다.

create table randtest (id serial primary key, data int not null);

insert into randtest (data) select (random()*1000000)::int from generate_series(1,1000000);

create index randtest_md5_id_idx on randtest (md5(id::text));

explain analyze
select * from randtest where md5(id::text)>md5(random()::text)
order by md5(id::text) limit 1;

결과:

 Limit  (cost=0.42..0.68 rows=1 width=8) (actual time=6.219..6.220 rows=1 loops=1)
   ->  Index Scan using randtest_md5_id_idx on randtest  (cost=0.42..84040.42 rows=333333 width=8) (actual time=6.217..6.217 rows=1 loops=1)
         Filter: (md5((id)::text) > md5((random())::text))
         Rows Removed by Filter: 1831
 Total runtime: 6.245 ms

이 쿼리는 때때로 (약 1/Number_of_rows 확률로) 0 행을 반환할 수 있으므로 확인하고 다시 실행해야 합니다.또한 확률이 정확히 같지는 않습니다. 일부 행은 다른 행보다 더 확률이 높습니다.

비교를 위해:

explain analyze SELECT id FROM randtest OFFSET random()*1000000 LIMIT 1;

결과는 매우 다양하지만 상당히 나쁠 수 있습니다.

 Limit  (cost=1442.50..1442.51 rows=1 width=4) (actual time=179.183..179.184 rows=1 loops=1)
   ->  Seq Scan on randtest  (cost=0.00..14425.00 rows=1000000 width=4) (actual time=0.016..134.835 rows=915702 loops=1)
 Total runtime: 179.211 ms
(3 rows)

저는 각 행에 무작위로 생성된 숫자를 추가하고 각 행에 추가된 임의의 숫자를 프로그래밍 언어로 생성했습니다.전화를 걸 때, 저는 임의의 번호를 쿼리에 전달합니다(이 경우 0.27).

SELECT * FROM
(
  (SELECT id, random FROM t where <condition> and random >= 0.27 ORDER BY random LIMIT 1)
  UNION ALL
  (SELECT id, random FROM t where <condition> and random < 0.27 ORDER BY random DESC LIMIT 1)
) as results
ORDER BY abs(0.27-random) LIMIT 1;

(여기서 가져온 쿼리)

만약 당신이 당신의 조건에 있는 행과 임의의 행(난수 포함)에 인덱스가 있다면, 나는 850만 행 테이블에서 6ms의 결과를 얻을 수 있습니다.이는 임의의 순서()를 사용하는 것보다 훨씬 빠릅니다.

랜덤성을 개선하기 위해 적중한 각 결과에 대해 새로운 난수를 생성할 수도 있습니다. (이 값이 없으면 일부 난수가 다른 값보다 더 자주 발생합니다.)

TABLESSAMPLE과는 달리 조건도 지원합니다.

언급URL : https://stackoverflow.com/questions/5297396/quick-random-row-selection-in-postgres