Vastus (09.11.2023 14:51): Sellest kirjutatakse pikemalt SIIN, SIIN ja SIIN.

Seal tuuakse välja kaks lahendust:

1. OFFSET'i e nihke kasutamine (nihke meetod)
2. Päringu tingimuse sellisel viisil seadmine, et OFFSET'i ei ole vaja kasutada (otsingu meetod).

OFFSET'i kasutamisel on mitmeid puuduseid, millest üheks on jõudluse pidev langus, mida kaugemaid lehekülgi tulemuse algusest kasutaja vaatab. Tegemist on ühe töökiiruse antimustri ilminguga.

Järgnev töökiiruse näide põhineb PostgreSQL'il ja katsetus on tehtud PostgreSQL 16-ga. Tabelis Hcv (tervishoiuasutuse külastus) on 1_000_000 rida. Soovin kõigepealt leida kõik külastused sorteerituna id väärtuse järgi kasvavalt ja väljastada andmed 10-rea suuruste portsudena.

Kõigi järgnevate lausete täitmisel loetakse andmebaasisüsteemi poolt primaarvõtme veerule (health_care_visit_id) loodud indeksit (index scan operatsioon), sest selle lehtedes on võtmeväärtused sorteeritud. Iga tabeli rea kohta loetakse indeksipuu leheks olevat plokki ja sealt leitud info alusel tabeli rea andmeid sisaldavat plokki.

SELECT *
FROM Hcv
ORDER BY health_care_visit_id
OFFSET 0
FETCH FIRST 10 ROWS ONLY;
Lause täitmiseks kulub 0.1 ms, loetakse 10 rida.

SELECT *
FROM Hcv
ORDER BY health_care_visit_id
OFFSET 10_000
FETCH FIRST 10 ROWS ONLY;
Lause täitmiseks kulub 2.6 ms, loetakse 10_010 rida.

SELECT *
FROM Hcv
ORDER BY health_care_visit_id
OFFSET 999_990
FETCH FIRST 10 ROWS ONLY;
Lause täitmiseks kulub 344 ms, loetakse 1_000_000 rida.

Seega nihke abil portsude leidmiseks otsib süsteem ka kõik need read, mis jäävad tulemuseks olevas ridade hulgast nihkest ettepoole. Seega, mida kaugemale tulemuste väljastamisega liikuda, seda aeglasemaks muutub päring.

Alternatiivne ja parema töökiirusega lahendus leiab vajaliku ridade hulga WHERE klausli abil. Ka järgnev lahenduse korral kasutatakse päringu täitmiseks primaarvõtmele loodud indeksit, kui sõltumata sellest, kas soovitav ports on tulemusteks oleva ridade hulga alguses või lõpus on lause täitmiseks loetavate andmete hulk ja seega ka lause täitmise kiirus ühesugune.

SELECT *
FROM Hcv
WHERE health_care_visit_id>0
ORDER BY health_care_visit_id
FETCH FIRST 10 ROWS ONLY;
Lause täitmiseks kulub 0.05 ms, loetakse 10 rida.

SELECT *
FROM Hcv
WHERE health_care_visit_id>999990
ORDER BY health_care_visit_id
FETCH FIRST 10 ROWS ONLY;
Lause täitmiseks kulub 0.03 ms, loetakse 10 rida.

Selle lahenduse korral peab portse sooviv tarkvara meeles pidama, millised olid kõige viimased andmed, mida see sai ning kasutama seda päringu tingimuses. Näiteks teise päringu korral teadis tarkvara, et kõige viimane visiit, mille andmeid see kuvas oli identifikaatoriga 999_990.

Sama põhimõte kehtib ka siis, kui sorteeritakse mõne muu veeru põhjal kui võtmeveerg.

SELECT *
FROM Hcv
ORDER BY from_date, patient_id
OFFSET 0
FETCH FIRST 10 ROWS ONLY;
Päringu täitmiseks toimub tabeli täielik läbiskaneerimine, sest pole indeksit, kus oleks samad veerud ja samas järjekorras kui ORDER BY klauslis. Lause täitmiseks kulub 112 ms.

SELECT *
FROM Hcv
ORDER BY from_date, patient_id
OFFSET 999990
FETCH FIRST 10 ROWS ONLY;
Lause täitmiseks kulub 545 ms.

Loon indeksi, kus on veerud, mida kasutatakse sorteerimisel ning need on samas järjekorras kui ORDER BY klauslis.
CREATE INDEX idx_hcv_date_patient ON Hcv (from_date, patient_id);

ANALYZE;

Peale seda kasutati eelnimetatud kahe lause täitmiseks indeksit (index scan). Esimese lause täitmiseks kulub 0.79 ms ja teise lause täitmiseks 1101 ms. Nii et indeksist oli kasu, kuid saaks veel paremini. Kui kasutada portsudeks jagamisel otsingu meetodit, siis on töökiirus palju parem. Ka mõlema järgneva lause täitmiseks kasutatakse eelnevalt loodud indeksit.

SELECT *
FROM Hcv
WHERE (from_date, patient_id)>('2000-10-13', 30017)
ORDER BY from_date, patient_id
FETCH FIRST 10 ROWS ONLY;
Lause täitmiseks kulub 0.139 ms.

SELECT *
FROM Hcv
WHERE (from_date, facility_id)>('2014-10-12', 95334)
ORDER BY from_date, facility_id
FETCH FIRST 10 ROWS ONLY;
Lause täitmiseks kulub 1.278 ms.