=$

|

czy stanęliście kiedyś przed problemem wylosowania rekordu z tabeli? dowolnego rekordu?
oczywistym pomysłem jest:

# SELECT * FROM tabelka ORDER BY random() LIMIT 1;

no ale to jest wolne. wymaga posortowania całej tabeli. co w najlepszym układzie ma złożoność "n log n".
przykładowo u mnie na testowej tabelce trwało to 90 sekund! (1.7 miliona rekordów).
no nie za dobrze.
niektórzy mogą sugerować takie rozwiązanie:

1. znajdź maksymalne
2. SELECT * FROM tabelka WHERE id <= random() * maksymalne_id limit 1;

na oko jest ok. tzn. akurat nie jest, bo random jest funkcją volatile, i trzeba by raczej … WHERE id <= (select random() * maksymalne_id) LIMIT 1, ale to już szczegół.
czemu to jest złe?
bo wprowadza pewien istotny problem. jeśli numeracja pola id w naszej tabelce zawiera dziury (czyli jest takie id, które jest większe od minimalnego i mniejsze od maksymalnego, dla którego nie ma rekordu) – to te losowane rekordy wcale nie będą dobrze losowane.
jako ekstremalny przykład (ale dobrze pokazujący rzeczywistość) podajmy tabelkę z dwoma rekordami, o id odpowiednio: 1 i 100. rekord z id = 1 będzie wypadał 99 razy częściej niż rekord z id = 100!.
cóż więc pozostaje? siąść i płakać?
nie.
można użyć inteligencji. czyli funkcji/procedury.
przykładowo taka funkcja:

CREATE OR REPLACE FUNCTION random_record() RETURNS tabelka AS $BODY$

DECLARE

    id_min INT8;

    id_max INT8;

    range INT8;

    temp_id INT8;

    temprec RECORD;

BEGIN

    SELECT min(id) INTO id_min FROM tabelka;

    SELECT max(id) INTO id_max FROM tabelka;

    range:= 1 + ( id_max - id_min );

    LOOP

        temp_id := id_min + (random() * range::float8)::INT8;

        SELECT * INTO temprec FROM tabelka WHERE id = temp_id;

        IF found THEN

            RETURN temprec;

        END IF;

    END LOOP;

END;

$BODY$ language 'plpgsql';

co ona robi?zwraca losowy rekord. całkowicie losowy – każdy rekord ma te same szanse bycia wylosowanym.
warunki brzegowe? pole id musi być unikatowe (szokujące, nie?). no i: im więcej dziur w numeracji tym wolniej działa. ale co znaczy wolniej?
ta moja testowa tabelka ma takie dane:

# select min(id), max(id), count(*) from tabelka;

 min |   max    |  count

-----+----------+---------

   3 | 36574227 | 1721217

(1 row)

czyli jak widać – dziur jest sporo. w szczególności – dziur jest 21 razy więcej niż istniejących rekordów!
przypomnę, że

select * from tabelka order by random() limit 1;

działało na tej tabelce w około 90 sekund.
ile czasu zajmuje to funkcji?
6 kolejnych wywołań. czasy odpowiednio: 124.700, 141.442, 201.708, 94.413, 145.128, 110.076. milisekund!
jak widać – jest szybko.
problemem tej funkcji jest to, że teoretycznie może się zdarzyć, że nigdy się nie skończy. ale w/g mnie jest to gdybanie. zresztą – zawsze można dorobić warunek, że jeśli np. wykonano już 1000 strzałów niecelnych, to zwróćmy pierwszy rekord z brzegu.
i już.
czy można to jakoś dopalić?
tak.
jeśli wiecie, że tabelka w której szukacie ma dużo dziur, to dodajcie do niej jedno pole:

create sequence random_thing_seq;

alter table tabelka add column random_thing int8;

alter table tabelka alter column random_thing set default nextval('random_thing_seq');

update tabelka set random_thing = nextval('random_thing_seq') where random_thing is null;

alter table tabelka alter column random_thing set not null;

create unique index ui_random_thing on tabelka (random_thing);

i potem używajcie w funkcji random_thing a nie id.
cel ćwiczenia?
jak sie pojawi za dużo dziur w numeracji (random_thing też będzie miał dziury) to zawsze możecie:

update tabelka set random_thing = nextval('random_thing_seq');

i już dziur nie ma,
a ponieważ random_thing nie jest do niczego innego używane – jest to w pełni bezpieczne.
oczywiście po takim update'cie dobrze jest zrobić vacuum'a. a najlepiej vacuum full'a.