Biorąc pod uwagę twoje specyfikacje (plus dodatkowe informacje w komentarzach),

• masz kolumnę numeryczną (liczby całkowite) z tylko kilkoma (lub umiarkowanie niewielkimi) przerwami.
• oczywiście brak lub kilka operacji zapisu.
• twoja kolumna ID musi być indeksowana! Klucz główny dobrze służy.

Poniższe zapytanie nie wymaga sekwencyjnego skanowania dużej tabeli, tylko skanowania indeksu.

Najpierw uzyskaj szacunki 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;

Jedyny prawdopodobnie kosztowną częścią jest count(*) (dla ogromnych stołów). Biorąc pod uwagę powyższe specyfikacje, nie potrzebujesz go. Kosztorys będzie w porządku, dostępny prawie bez kosztów (szczegółowe wyjaśnienie tutaj):

SELECT reltuples AS ct FROM pg_class WHERE oid = 'schema_name.big'::regclass;

Tak długo, jak ct nie jest dużo mniejszy niż 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

• Generowanie liczb losowych w przestrzeni id. Masz "kilka luk", więc dodaj 10 % (wystarczająco, aby łatwo pokryć puste miejsca) do liczby wiersze do pobrania.

• Każda id może być wybrana kilka razy przez przypadek( choć bardzo mało prawdopodobne z dużą spacją id), więc Grupuj wygenerowane liczby (lub użyj DISTINCT).

• Dołącz do id s do dużego stołu. Powinno to być bardzo szybkie z indeksem na miejscu.

• Wreszcie wykończyć nadwyżki id S, które nie zostały zjedzone przez dupki i luki. Każdy rząd ma całkowicie równe szanse do wyboru.

Krótki wersja

Możesz uprościć to zapytanie. CTE w powyższym zapytaniu jest tylko do celów edukacyjnych:

SELECT *
FROM  (
    SELECT DISTINCT 1 + trunc(random() * 5100000)::integer AS id
    FROM   generate_series(1, 1100) g
    ) r
JOIN   big USING (id)
LIMIT  1000;

Udoskonalaj za pomocą rCTE

Szczególnie, jeśli nie jesteś taki pewien 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 mniejsza nadwyżka 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. Nadal potrzebujemy stosunkowo kilka luk w przestrzeni ID lub rekurencja może wyschnąć przed osiągnięciem limitu - lub musimy zacząć od wystarczająco dużego bufora, który nie służy optymalizacji wydajności.

Duplikaty są eliminowane przez UNION w rCTE.

Zewnętrzny LIMIT sprawia, że CTE zatrzymuje się, gdy tylko mamy wystarczającą ilość wierszy.

To zapytanie jest starannie przygotowane, aby korzystać z dostępnego indeksu, generować faktycznie losowe wiersze i nie zatrzymywać się, dopóki nie wypełnimy limitu (chyba że recursion runs dry). Istnieje wiele pułapek tutaj, jeśli masz zamiar go przepisać.

Zawiń w funkcję

Do wielokrotnego użytku o różnych parametrach:

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;

Wywołanie:

SELECT * FROM f_random_sample();
SELECT * FROM f_random_sample(500, 1.05);

Możesz nawet sprawić, by ten rodzajnik działał dla dowolnej tabeli: weź nazwę kolumny PK i tabelę jako typ polimorficzny i użyj EXECUTE... Ale to wykracza poza zakres tego pytania. Zobacz:

• Refactor a funkcja PL / pgSQL zwracająca wyjście różne zapytania SELECT

Możliwa alternatywa

Jeśli twoje wymagania dopuszczają identyczne zestawy dla powtarzających się wywołań (a mówimy o powtarzających się wywołaniach), rozważyłbym zmaterializowany widok. Wykonaj powyższe zapytanie raz i zapisz wynik do tabeli. Użytkownicy otrzymują quasi losowy wybór przy prędkości rozjaśniania. Odśwież swój losowy wybór w odstępach czasu lub w wybranych przez Ciebie wydarzeniach.

Postgres 9.5 wprowadza TABLESAMPLE SYSTEM (n)

To jest bardzo szybkie , ale wynik jest nie do końca przypadkowy . Instrukcja:

Metoda SYSTEM jest znacznie szybsza niż metoda BERNOULLI gdy podano małe procenty pobierania próbek, ale może to zwrócić less-losowa próbka tabeli wynikająca z efektów klastrowania.

I liczba zwracanych wierszy może się bardzo różnić. Dla naszego przykładu, aby uzyskać mniej więcej 1000 wierszy, spróbuj:

SELECT * FROM big TABLESAMPLE SYSTEM ((1000 * 100) / 5100000.0);

Gdzie n jest procentem. Instrukcja:

Metody pobierania próbek BERNOULLI i SYSTEM akceptują pojedynczą argument będący ułamkiem tabeli do próby, wyrażony jako procent od 0 do 100 . Argument ten może być dowolnym wyrażeniem o wartości real.

Powiązane:

• szybki sposób na sprawdzenie liczby wierszy tabeli w PostgreSQL

Lub zainstalować dodatkowy moduł tsm_system_rows aby uzyskać dokładną liczbę żądanych wierszy (jeśli jest ich wystarczająco dużo) i pozwolić na wygodniejszą składnię:

SELECT * FROM big TABLESAMPLE SYSTEM_ROWS(1000);

Zobacz odpowiedź Evana Po szczegóły.