Biorąc pod uwagę specyfikację (plus dodatkowe informacje w komentarzach),
- Masz kolumnę identyfikatora numerycznego (liczby całkowite) z niewielkimi (lub umiarkowanie małymi) przerwami.
- Oczywiście nie ma żadnych operacji zapisu.
- Twoja kolumna identyfikacyjna musi zostać zaindeksowana! Klucz podstawowy służy ładnie.
Poniższe zapytanie nie wymaga sekwencyjnego skanowania dużej tabeli, tylko skanowanie indeksu.
Najpierw uzyskaj oszacowania dla głównego zapytania:
SELECT count(*) AS ct -- optional
, min(id) AS min_id
, max(id) AS max_id
, max(id) - min(id) AS id_span
FROM big;
Jedyną prawdopodobnie kosztowną częścią jest count(*)(dla dużych stołów). Biorąc pod uwagę powyższe specyfikacje, nie potrzebujesz go. Kosztorys będzie wystarczający, dostępny prawie za darmo ( szczegółowe wyjaśnienie tutaj ):
SELECT reltuples AS ct FROM pg_class WHERE oid = 'schema_name.big'::regclass;
Tak długo, jak ctnie jest dużo mniejszy id_span, zapytanie będzie przewyższać inne podejścia.
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
Generuj losowe liczby w idprzestrzeni. Masz „kilka luk”, więc dodaj 10% (wystarczająco, aby łatwo zakryć puste miejsca) do liczby wierszy do odzyskania.
Każdą z nich idmożna wybrać wiele razy przypadkowo (choć jest to mało prawdopodobne przy dużej przestrzeni identyfikatora), więc zgrupuj wygenerowane liczby (lub użyj DISTINCT).
Dołącz iddo dużego stołu. Powinno to być bardzo szybkie przy założonym indeksie.
Na koniec przytnij nadwyżki id, które nie zostały zjedzone przez duplikaty i luki. Każdy rząd ma całkowicie taką samą szansę na wybranie.
Krótka wersja
Możesz uprościć to zapytanie. CTE w powyższym zapytaniu służy wyłącznie celom edukacyjnym:
SELECT *
FROM (
SELECT DISTINCT 1 + trunc(random() * 5100000)::integer AS id
FROM generate_series(1, 1100) g
) r
JOIN big USING (id)
LIMIT 1000;
Udoskonal za pomocą rCTE
Zwłaszcza jeśli nie masz pewności co do luk i szacunków.
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
Możemy pracować z mniejszą nadwyżką w zapytaniu podstawowym. Jeśli jest zbyt wiele luk, więc nie znajdziemy wystarczającej liczby wierszy w pierwszej iteracji, rCTE kontynuuje iterację z terminem rekurencyjnym. Wciąż potrzebujemy stosunkowo niewielkich przerw w przestrzeni ID lub rekursja może wyschnąć przed osiągnięciem limitu - lub musimy zacząć od wystarczająco dużego bufora, który nie pozwala na optymalizację wydajności.
Duplikaty są eliminowane przez UNIONw rCTE.
Zewnętrzne LIMITpowoduje, że CTE zatrzymuje się, gdy tylko mamy wystarczającą liczbę rzędów.
To zapytanie jest starannie opracowane, aby użyć dostępnego indeksu, wygenerować faktycznie losowe wiersze i nie zatrzymywać się, dopóki nie osiągniemy limitu (chyba że rekursja nie będzie sucha). Istnieje wiele pułapek, jeśli zamierzasz go przepisać.
Zawiń w funkcję
Do wielokrotnego użytku z różnymi parametrami:
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;
Połączenie:
SELECT * FROM f_random_sample();
SELECT * FROM f_random_sample(500, 1.05);
Możesz nawet sprawić, by ten ogólny działał dla dowolnej tabeli: Weź nazwę kolumny PK i tabeli jako typ polimorficzny i użyj EXECUTE... Ale to wykracza poza zakres tego pytania. Widzieć:
Możliwa alternatywa
JEŻELI twoje wymagania zezwalają na identyczne zestawy dla powtarzanych połączeń (a mówimy o powtarzanych połączeniach), rozważyłbym widok zmaterializowany . Wykonaj powyższe zapytanie raz i zapisz wynik w tabeli. Użytkownicy otrzymują quasi-losowy wybór z prędkością błyskawicy. Odśwież swój losowy wybór w odstępach czasu lub wybranych wydarzeniach.
Gdzie njest procent. Instrukcja:
BERNOULLII SYSTEMmetody próbkowania każdego przyjmować jeden argument, który stanowi frakcję tabeli w próbce wyrażono jako
procent między 0 a 100 . Ten argument może być dowolnym realwyrażeniem wartościowanym.
Odważny nacisk moje. Jest bardzo szybki , ale wynik nie jest dokładnie losowy . Instrukcja ponownie:
SYSTEMSposób jest znacznie szybszy niż BERNOULLImetoda w przypadku małych procenty próbkowania jest określony, ale może powrócić do mniej wybranych losowo stół w wyniku grupowania efekty.
Liczba zwracanych wierszy może się znacznie różnić. W naszym przykładzie, aby uzyskać około 1000 wierszy:
SELECT * FROM big TABLESAMPLE SYSTEM ((1000 * 100) / 5100000.0);
Związane z:
Lub zainstaluj dodatkowy moduł tsm_system_rows, aby dokładnie uzyskać liczbę żądanych wierszy (jeśli jest ich wystarczająco dużo) i umożliwić wygodniejszą składnię:
SELECT * FROM big TABLESAMPLE SYSTEM_ROWS(1000);
Szczegółowe informacje można znaleźć w odpowiedzi Evana .
Ale to wciąż nie jest przypadkowe.