Soru Postgres'de hızlı rastgele sıra seçimi


Birkaç milyonlarca satır içeren postgres bir masam var. İnterneti kontrol ettim ve aşağıdakileri buldum

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

Çalışıyor, ama gerçekten yavaş ... Bu tabloyu yapmak için başka bir yol var mı, yoksa tüm tabloyu okumadan rastgele bir satır seçmek için doğrudan bir yol var mı? Bu arada 'myid' bir tam sayıdır, ancak boş bir alan olabilir.

Teşekkürler


76
2018-03-14 10:33


Menşei


Birden çok rastgele satır seçmek isterseniz, şu soruya bakın: stackoverflow.com/q/8674718/247696 - Flimm


Cevaplar:


İle denemek isteyebilirsiniz OFFSET, de olduğu gibi

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

N içindeki satır sayısıdır mytable. İlk önce bir SELECT COUNT(*) değerini anlamak N.

Güncelleştirme (Antony Hatchkins tarafından)

Kullanmalısın floor İşte:

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

2 satırlık bir tablo düşünün; random()*N üretir 0 <= x < 2 ve örneğin SELECT myid FROM mytable OFFSET 1.7 LIMIT 1; En yakın int için örtülü yuvarlama nedeniyle 0 satır döndürür.


84
2018-03-14 10:45



N'den daha az bir N kullanmak için mantıklı SELECT COUNT(*)? Yani, tablodaki tüm değerleri değil, sadece bir kısmını kullanmalı mıyım? - Juan
@Juan Bu gereksinimlerinize bağlıdır. - NPE
kullanmak EXPLAIN SELECT ... N farklı değerleri ile sorgu için aynı maliyeti verir, o zaman ben N'nin maksimum değeri için gitmek daha iyidir. - Juan
cevabımda aşağıdan bir hata görüyorum - Antony Hatchkins
Bu bir hata ile kapalı. İlk satırı asla iade etmeyecek ve 1 / COUNT (*) hata üretecektir, çünkü satır son satırdan sonra geri dönmeye çalışacaktır. - Ian


PostgreSQL 9.5, daha hızlı numune seçimi için yeni bir yaklaşım geliştirdi: TABLESAMPLE

Sözdizimi

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

Bu, yalnızca bir satırın seçilmesini istiyorsanız en uygun çözüm değildir, çünkü tam yüzdeyi hesaplamak için tablonun COUNT değerini bilmeniz gerekir.

COUNT düzeyini yavaşlatmamak ve 1 satırdan milyarlarca satıra kadar olan tabloların hızlıca TABLESAMPLE kullanmasını sağlamak için şunları yapabilirsiniz:

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

Bu çok şık görünmeyebilir, ancak muhtemelen diğer cevaplardan daha hızlıdır.

BERNULLI oder SYSTEM'i kullanmak isteyip istemediğinize karar vermek için http://blog.2ndquadrant.com/tablesample-in-postgresql-9-5-2/


33
2017-08-15 09:49



Bu, diğer cevaplardan çok daha hızlı ve kolay - bu en tepede olmalı. - Hayden Schiff


Bunu bir alt sorgu ile denedim ve iyi çalıştı. Ofset, en azından Postgresql v8.4.4'te çalışıyor.

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

32
2017-08-01 19:18



Aslında, bunun çalışması için v8.4 esastır, <= 8.3 için çalışmıyor. - Antony Hatchkins
cevabımda aşağıdan bir hata görüyorum - Antony Hatchkins


Kullanmalısın floor:

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

26
2017-10-26 08:46



2 satırlık bir tablo düşünün; random()*N 0 <= x <2 ve örneğin üretir SELECT myid FROM mytable OFFSET 1.7 LIMIT 1; En yakın int için örtülü yuvarlama nedeniyle 0 satır döndürür. - Antony Hatchkins
Ne yazık ki bu daha yüksek bir LIMIT kullanmak istiyorsanız işe yaramıyor ... 3 öğe almam gerekiyor, bu yüzden ORDER BY RANDOM () sözdizimini kullanmam gerekiyor. - Alexis Wilke
Üç ardışık sorgu, bir kereden daha hızlı olacak order by random(), yaklaşık olarak 3*O(N) < O(NlogN) - endeksler nedeniyle gerçek hayat rakamları biraz farklı olacaktır. - Antony Hatchkins
Benim sorunum 3 öğenin farklı olması ve bir WHERE myid NOT IN (1st-myid) ve WHERE myid NOT IN (1st-myid, 2nd-myid) Karar OFSET tarafından yapıldığından işe yaramaz. Hmmm ... Sanırım N'yi ikinci ve üçüncü SELECT'te 1 ve 2'ye indirebilirim. - Alexis Wilke
Siz veya bu cevabı bir cevap ile genişletebilir misiniz niye ya Kullanmam gerek floor()? Ne avantajı vardır? - ADTC


Bazı farklı seçenekler için bu bağlantıyı kontrol edin. http://www.depesz.com/index.php/2007/09/16/my-thoughts-on-getting-random-row/

Güncelleştirme: (A.Hatchkins)


14
2018-03-14 12:29



Neden OFFSET'i kapsamadıklarını merak ediyorum? SİPARİŞİ kullanmak sadece rastgele bir satır almak için sorulan soru. Neyse ki, OFFSET cevaplarda iyi durumda. - androidguy
rastgele sütunun neden güncellenmesi gerektiğinden emin değil misiniz? - rogerdpack


Çok hızlı bir çözüm buldum. TABLESAMPLE. Çok daha hızlı OFFSET random()*N LIMIT 1. Masa sayımı gerektirmez.

Buradaki fikir, örneğin rastgele ama tahmin edilebilir veriler içeren bir ifade dizini oluşturmaktır. md5(primary key).

İşte 1M satır örnek verileri ile bir test:

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;

Sonuç:

 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

Bu sorgu bazen (yaklaşık 1 / Number_of_rows olasılıkla) 0 satır döndürür, bu nedenle kontrol edilmesi ve yeniden çalıştırılması gerekir. Ayrıca olasılıklar tam olarak aynı değildir - bazı satırlar diğerlerinden daha olasıdır.

Karşılaştırma için:

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

Sonuçlar yaygın olarak değişir, ancak oldukça kötü olabilir:

 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



Hızlı, evet. Gerçekten rasgele, hayır. Diğer bir mevcut değerden sonra bir sonraki büyük değer olan bir md5 değerinin çok düşük bir şansa sahip olması gerekirken, sayı alanındaki büyük bir boşluktan sonraki değerlerin daha büyük bir şansı vardır (aradaki olası değerlerin sayısıyla daha büyük). . Elde edilen dağılım rastgele değil. - Erwin Brandstetter
Çok ilginç, bir piyango benzeri sorguda bir usecase içinde çalışabilir: sorgu tüm mevcut biletlere bakmak ve rastgele sadece bir tek bilet dönmelidir. Ayrıca tekniğinizle kötümser bir kilit (güncelleme için ... seçebilir miyim) kullanabilirim? - Mathieu
Herhangi bir piyango ile ilgili olarak, adil ve kriptografik olarak güvenli rastgele örneklemeyi kullanmalısınız - örneğin, mevcut kimliği bulana kadar 1 ile maks (id) arasında rastgele bir sayı seçin. Bu cevabın yöntemi ne adil ne de güvenlidir - hızlıdır. 'Bir şeyi test etmek için satırların% 1'ini rasgele al' veya 'rastgele 5 girişi göster' gibi şeyler için kullanılabilir. - Tometzky
Cevabınız için teşekkürler, anladım! - Mathieu