Câu hỏi lựa chọn hàng ngẫu nhiên nhanh trong Postgres


Tôi có một bảng ở bưu cục có chứa hàng triệu hàng. Tôi đã kiểm tra trên internet và tôi thấy những điều sau đây

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

nó hoạt động, nhưng nó thực sự chậm ... là có một cách khác để làm cho truy vấn đó, hoặc một cách trực tiếp để chọn một hàng ngẫu nhiên mà không cần đọc tất cả các bảng? bằng cách 'myid' là một số nguyên nhưng nó có thể là một trường trống.

cảm ơn


76
2018-03-14 10:33


gốc


Nếu bạn muốn chọn nhiều hàng ngẫu nhiên, hãy xem câu hỏi này: stackoverflow.com/q/8674718/247696 - Flimm


Các câu trả lời:


Bạn có thể muốn thử nghiệm OFFSET, như trong

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

Các N là số hàng trong mytable. Bạn có thể cần phải làm một SELECT COUNT(*) để tìm ra giá trị của N.

Cập nhật (bởi Antony Hatchkins)

Bạn phải dùng floor đây:

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

Xem xét một bảng gồm 2 hàng; random()*N tạo ra 0 <= x < 2 và ví dụ SELECT myid FROM mytable OFFSET 1.7 LIMIT 1; trả về 0 hàng vì làm tròn ẩn để int gần nhất.


84
2018-03-14 10:45



làm cho nó ý nghĩa để sử dụng N ít hơn SELECT COUNT(*)? Ý tôi là, không sử dụng tất cả các giá trị trong bảng nhưng chỉ là một phần của chúng? - Juan
@Juan Điều đó phụ thuộc vào yêu cầu của bạn. - NPE
sử dụng EXPLAIN SELECT ... với các giá trị khác nhau của N cho cùng một chi phí cho truy vấn, thì tôi đoán là tốt hơn để đi cho giá trị lớn nhất của N. - Juan
thấy một bugfix trong câu trả lời của tôi bên dưới - Antony Hatchkins
Điều này có một lỗi. Nó sẽ không bao giờ trả lại hàng đầu tiên và sẽ tạo ra lỗi 1 / COUNT (*) vì nó sẽ cố gắng trả về hàng sau hàng cuối cùng. - Ian


PostgreSQL 9.5 đã giới thiệu một phương pháp mới để lựa chọn mẫu nhanh hơn nhiều: TABLESAMPLE

Cú pháp là

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

Đây không phải là giải pháp tối ưu nếu bạn chỉ muốn chọn một hàng, vì bạn cần biết COUNT của bảng để tính phần trăm chính xác.

Để tránh COUNT chậm và sử dụng TABLESAMPLE nhanh cho các bảng từ 1 hàng đến hàng tỷ hàng, bạn có thể thực hiện:

 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;
 ...

Điều này có thể không trông rất thanh lịch, nhưng có lẽ nhanh hơn bất kỳ câu trả lời nào khác.

Để quyết định xem bạn có muốn sử dụng BERNULLI oder SYSTEM hay không, hãy đọc về sự khác biệt tại http://blog.2ndquadrant.com/tablesample-in-postgresql-9-5-2/


33
2017-08-15 09:49



Điều này nhanh hơn và dễ dàng hơn nhiều so với bất kỳ câu trả lời nào khác - câu trả lời này phải ở trên cùng. - Hayden Schiff


Tôi đã thử điều này với một truy vấn con và nó hoạt động tốt. Bù đắp, ít nhất trong Postgresql v8.4.4 hoạt động tốt.

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

32
2017-08-01 19:18



Trong thực tế, v8.4 là điều cần thiết để làm việc này, không hoạt động với <= 8.3. - Antony Hatchkins
thấy một bugfix trong câu trả lời của tôi bên dưới - Antony Hatchkins


Bạn cần sử dụng floor:

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

26
2017-10-26 08:46



Xem xét một bảng gồm 2 hàng; random()*N tạo ra 0 <= x <2 và ví dụ SELECT myid FROM mytable OFFSET 1.7 LIMIT 1; trả về 0 hàng vì làm tròn ẩn để int gần nhất. - Antony Hatchkins
Thật không may điều này không hoạt động nếu bạn muốn sử dụng LIMIT cao hơn ... Tôi cần phải có 3 mục vì vậy tôi cần sử dụng cú pháp ORDER BY RANDOM (). - Alexis Wilke
Ba truy vấn liên tiếp sẽ vẫn nhanh hơn một truy vấn order by random(), khoảng như 3*O(N) < O(NlogN) - con số thực tế sẽ hơi khác biệt do các chỉ số. - Antony Hatchkins
Vấn đề của tôi là 3 mục cần phải phân biệt và WHERE myid NOT IN (1st-myid) và WHERE myid NOT IN (1st-myid, 2nd-myid) sẽ không hoạt động kể từ khi OFFSET đưa ra quyết định. Hmmm ... Tôi đoán tôi có thể giảm N xuống 1 và 2 trong SELECT thứ hai và thứ ba. - Alexis Wilke
Bạn hoặc bất kỳ ai có thể mở rộng câu trả lời này bằng câu trả lời cho tại sao Tôi cần phải sử dụng floor()? Ưu điểm của nó là gì? - ADTC


Kiểm tra liên kết này ra cho một số tùy chọn khác nhau. http://www.depesz.com/index.php/2007/09/16/my-thoughts-on-getting-random-row/

Cập nhật: (A.Hatchkins)


14
2018-03-14 12:29



Tôi tự hỏi tại sao họ không bao gồm OFFSET? Sử dụng một ORDER là ra khỏi câu hỏi chỉ để có được một hàng ngẫu nhiên. May mắn thay, OFFSET cũng được đề cập đến trong các câu trả lời. - androidguy
không chắc chắn tại sao cột ngẫu nhiên cần được cập nhật ... - rogerdpack


Tôi đã đưa ra một giải pháp rất nhanh mà không cần TABLESAMPLE. Nhanh hơn nhiều OFFSET random()*N LIMIT 1. Nó thậm chí không yêu cầu đếm bảng.

Ý tưởng là tạo một chỉ mục biểu thức với dữ liệu ngẫu nhiên nhưng có thể dự đoán được, ví dụ md5(primary key).

Đây là một thử nghiệm với dữ liệu mẫu hàng 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;

Kết quả:

 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

Truy vấn này đôi khi có thể (với xác suất khoảng 1 / Number_of_rows) trả về 0 hàng, vì vậy nó cần phải được kiểm tra và chạy lại. Xác suất cũng không giống hệt nhau - một số hàng có thể xảy ra nhiều hơn các hàng khác.

Để so sánh:

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

Kết quả khác nhau rất nhiều, nhưng có thể khá tệ:

 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)

2
2017-10-25 19:37



Nhanh, vâng. Thật sự ngẫu nhiên, không. Giá trị md5 xảy ra là giá trị lớn hơn sau giá trị hiện tại khác có cơ hội rất mỏng để được chọn, trong khi giá trị sau khoảng cách lớn trong không gian số có cơ hội lớn hơn nhiều (lớn hơn số lượng giá trị có thể có ở giữa) . Phân phối kết quả không phải là ngẫu nhiên. - Erwin Brandstetter
rất thú vị, nó có thể làm việc trong một usecase của một truy vấn xổ số giống như: truy vấn phải xem xét tất cả các vé có sẵn và trả lại ngẫu nhiên chỉ ONE vé duy nhất. Tôi cũng có thể sử dụng một khóa bi quan (chọn ... để cập nhật) với kỹ thuật của bạn? - Mathieu
Đối với bất kỳ thứ gì liên quan đến xổ số, bạn thực sự nên sử dụng mẫu ngẫu nhiên an toàn và mật mã - ví dụ, chọn một số ngẫu nhiên từ 1 đến max (id) cho đến khi bạn tìm thấy id hiện tại. Phương pháp từ câu trả lời này là không công bằng và bảo mật - nó nhanh. Có thể sử dụng cho những thứ như 'nhận ngẫu nhiên 1% hàng để kiểm tra điều gì đó trên' hoặc 'hiển thị ngẫu nhiên 5 mục'. - Tometzky
cảm ơn cho trả lời của bạn, đã nhận nó! - Mathieu