Pytanie szybki losowy wybór wierszy w Postgresie


Mam tabelę w postgresie zawierającą kilka milionów wierszy. Sprawdziłem w Internecie i znalazłem następujące

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

działa, ale jest naprawdę powolny ... czy istnieje inny sposób na zrobienie tego zapytania lub bezpośredni sposób na wybranie losowego wiersza bez czytania całej tabeli? przy okazji "myid" jest liczbą całkowitą, ale może być pustym polem.

dzięki


76
2018-03-14 10:33


pochodzenie


Jeśli chcesz wybrać wiele losowych wierszy, zobacz to pytanie: stackoverflow.com/q/8674718/247696 - Flimm


Odpowiedzi:


Możesz chcieć eksperymentować OFFSET, jak w

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

The N to liczba wierszy w mytable. Być może najpierw musisz zrobić SELECT COUNT(*) ustalić wartość N.

Aktualizacja (autor: Antony Hatchkins)

Musisz użyć floor tutaj:

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

Rozważmy tabelę 2 rzędów; random()*N generuje 0 <= x < 2 i na przykład SELECT myid FROM mytable OFFSET 1.7 LIMIT 1; zwraca 0 wierszy z powodu niejawnego zaokrąglenia do najbliższej int.


84
2018-03-14 10:45



sprawić, że będzie używał N mniej niż SELECT COUNT(*)?, Mam na myśli, nie używać wszystkich wartości w tabeli, ale tylko część z nich? - Juan
@Juan To zależy od Twoich wymagań. - NPE
używając EXPLAIN SELECT ... z różnymi wartościami N dają ten sam koszt dla zapytania, wtedy myślę, że lepiej jest przejść do maksymalnej wartości N. - Juan
zobacz poprawkę w mojej odpowiedzi poniżej - Antony Hatchkins
Występuje jeden błąd. Nigdy nie zwróci pierwszego wiersza i wygeneruje błąd 1 / COUNT (*), ponieważ spróbuje zwrócić wiersz po ostatnim wierszu. - Ian


PostgreSQL 9.5 wprowadził nowe podejście do znacznie szybszego wyboru próbki: TABLESAMPLE

Składnia jest

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

To nie jest optymalne rozwiązanie, jeśli chcesz wybrać tylko jeden wiersz, ponieważ musisz znać LICZBĘ tabeli, aby obliczyć dokładną wartość procentową.

Aby uniknąć powolnego COUNT i używać szybkiego TABLESAMPLE dla tabel od 1 rzędu do miliardów wierszy, możesz:

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

To może nie wyglądać tak elegancko, ale prawdopodobnie jest szybsze niż jakakolwiek inna odpowiedź.

Aby zdecydować, czy chcesz używać BERNULLI oder SYSTEM, przeczytaj o różnicach na stronie http://blog.2ndquadrant.com/tablesample-in-postgresql-9-5-2/


33
2017-08-15 09:49



Jest to znacznie szybsze i łatwiejsze niż jakakolwiek inna odpowiedź - ta powinna być na górze. - Hayden Schiff


Próbowałem tego z podzapytaniem i działało dobrze. Offset, przynajmniej w Postgresql v8.4.4 działa dobrze.

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

32
2017-08-01 19:18



W rzeczywistości w wersji 8.4 jest to niezbędne do działania, nie działa dla <= 8,3. - Antony Hatchkins
zobacz poprawkę w mojej odpowiedzi poniżej - Antony Hatchkins


Musisz użyć floor:

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

26
2017-10-26 08:46



Rozważmy tabelę 2 rzędów; random()*N generuje 0 <= x <2 i na przykład SELECT myid FROM mytable OFFSET 1.7 LIMIT 1; zwraca 0 wierszy z powodu niejawnego zaokrąglenia do najbliższej int. - Antony Hatchkins
Niestety to nie działa, jeśli chcesz użyć wyższego LIMITU ... Potrzebuję 3 elementów, więc potrzebuję użyć składni ORDER BY RANDOM (). - Alexis Wilke
Trzy kolejne zapytania będą nadal szybsze niż jeden order by random(), w przybliżeniu jako 3*O(N) < O(NlogN) - dane liczbowe będą nieco inne z powodu indeksów. - Antony Hatchkins
Mój problem polega na tym, że 3 elementy muszą być odrębne i WHERE myid NOT IN (1st-myid) i WHERE myid NOT IN (1st-myid, 2nd-myid) nie zadziała, ponieważ decyzję podejmuje OFFSET. Hmmm ... Myślę, że mógłbym zmniejszyć N o 1 i 2 w drugim i trzecim SELECT. - Alexis Wilke
Czy ty lub ktoś może rozwinąć tę odpowiedź z odpowiedzią? czemu Muszę użyć floor()? Jakie ma to zalety? - ADTC


Sprawdź ten link, aby uzyskać dostęp do różnych opcji. http://www.depesz.com/index.php/2007/09/16/my-thoughts-on-getting-random-row/

Aktualizacja: (A.Hatchkins)


14
2018-03-14 12:29



Zastanawiam się, dlaczego nie pokrywają OFFSET? Korzystanie z ZAMÓWIENIA nie wchodzi w grę, aby uzyskać losowy wiersz. Na szczęście OFFSET jest dobrze uwzględniony w odpowiedziach. - androidguy
nie wiem, dlaczego losowa kolumna kiedykolwiek musiałaby być aktualizowana ... - rogerdpack


Wymyśliłem bardzo szybkie rozwiązanie bez TABLESAMPLE. Znacznie szybciej niż OFFSET random()*N LIMIT 1. Nie wymaga to nawet liczenia stolików.

Chodzi o stworzenie indeksu ekspresji z losowymi, ale przewidywalnymi danymi, na przykład md5(primary key).

Oto test z danymi przykładowymi wierszy 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;

Wynik:

 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

To zapytanie może czasami (z prawdopodobieństwem około 1 / Number_ofrow) zwrócić 0 wierszy, więc musi zostać sprawdzone i ponownie uruchomione. Również prawdopodobieństwa nie są dokładnie takie same - niektóre wiersze są bardziej prawdopodobne niż inne.

Dla porownania:

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

Wyniki są bardzo różne, ale może być całkiem źle:

 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



Szybko, tak. Naprawdę losowe, nie. Wartości md5, które okazują się być kolejną większą wartością po innej istniejącej wartości, mają bardzo niewielką szansę na wybranie, podczas gdy wartości po dużej luce w przestrzeni liczbowej mają znacznie większą szansę (większą o liczbę możliwych wartości pomiędzy) . Wynikowa dystrybucja nie jest przypadkowa. - Erwin Brandstetter
bardzo interesujące, czy może działać w przypadku kwerendy podobnej do loterii: zapytanie musi uwzględniać wszystkie dostępne bilety i losowo zwracać tylko JEDEN pojedynczy bilet. czy mogę użyć pesymistycznej blokady (wybierz ... do aktualizacji) z twoją techniką? - Mathieu
W przypadku jakiejkolwiek loterii powinieneś używać uczciwej i bezpiecznej kryptograficznie próbkowania losowej - na przykład wybierz losową liczbę od 1 do max (id), aż znajdziesz istniejący identyfikator. Metoda z tej odpowiedzi nie jest ani sprawiedliwa, ani bezpieczna - jest szybka. Nadaje się do takich rzeczy jak "pobierz losowo 1% wierszy, aby przetestować coś na" lub "pokaż losowo 5 wpisów". - Tometzky
dzięki za twoją odpowiedź, rozumiem! - Mathieu