Vastus (10.10.2023 11:48): Tuleb korraldada eksperiment ja erinevaid lahendusi katsetada. Kui Teil pole käepärast serverit, kus saate "liivakastis" mängida, siis isegi keskkonnas nagu DB Fiddle saab luua tabeleid, genereerida sinna mõõdukal hulgal testandmeid ja vaadata päringute täitmise kiirust. Töökiiruse eksperimentide tulemused võivad sõltuda ka kasutatavast andmebaasisüsteemi versioonist (st erinevates versioonides on tulemused erinevad, kuna vahepeal on muutunud andmebaasisüsteemi sisemised viisid päringute töötlemiseks ja täitmiseks).

Kirjeldan kõigepealt üldist protsessi ja siis toon näite.

1. Looge tabelid (ilma kasutaja-loodud indeksiteta, et kiirendada hiljem testandmete lisamist).
2. Lisage väga vähe testandmeid (mõned kuni kümned read), et kõigepealt koostada võimalikud ülesande lahendusvariandid ja kontrollida, et need annavad õige tulemuse. Testandmed peaksid olema sellised, et kontrollida lahenduse õigsust ka piirjuhtudel.
3. Koostage ja testige lahendusvariante.
4. Genereerige mõõdukal hulgal testandmeid (kümned tuhanded kuni sajad tuhanded read).
5. Lisage puuduvad indeksid, värskendage andmebaasi statistikat.
6. Testige lahendusvariante.
7. Kustutage testandmed, eemaldage kasutaja-loodud indeksid.
8. Genereerige suurel hulgal testandmeid (miljonid read). See on vajalik, sest töökiiruse halvenemine ei pruugi toimuda koos ridade arvu kasvuga lineaarselt, vaid see võib toimuda eksponentsiaalselt.
9. Lisage puuduvad indeksid, värskendage andmebaasi statistikat.
10. Testige lahendusvariante.

Järgnevalt toon konkreetse näite. Katsetus on tehtud kasutades PostgreSQL 16.

Ülesanne: Tellimuste andmebaasis on tellimused ja tellimuste read. Ülesandeks on leida iga kuu(+aasta) kohta, kus on tehtud mõni tellimus, millised kliendid on teinud selles kuus(+aastas) kõige suuremas mahus tellimusi.

Andmebaasi kontseptuaalne struktuur: [Tellimus]-1-----------1..*-[Tellimuse_rida]

Samm 1

CREATE TABLE Tellimus (tellimus_kood INTEGER NOT NULL,
klient_id INTEGER NOT NULL,
tellimuse_kp DATE NOT NULL,
CONSTRAINT pk_tellimus PRIMARY KEY (tellimus_kood));

CREATE TABLE Tellimuse_rida (tellimus_kood INTEGER NOT NULL,
toode_id INTEGER NOT NULL,
yhiku_hind INTEGER NOT NULL,
kogus INTEGER NOT NULL,
CONSTRAINT pk_tellimuse_rida PRIMARY KEY (tellimus_kood, toode_id),
CONSTRAINT fk_tellimuse_rida_tellimus FOREIGN KEY (tellimus_kood)
REFERENCES Tellimus (tellimus_kood) ON DELETE CASCADE);

Välisvõtme veerule tellimus_kood pole eraldi indeksit vaja, sest see on primaarvõtmes esimene veerg ja primaarvõtme loomisel luuakse automaatselt indeks, kus see veerg on esimene veerg.

Samm 2

INSERT INTO Tellimus (tellimus_kood, klient_id, tellimuse_kp)
VALUES (1, 1, '2023-10-08'),
(2, 1, '2023-10-09'),
(3, 2, '2023-10-09'),
(4, 1, '2023-09-09'),
(5, 2, '2023-09-11'),
(6, 2, '2023-09-15'),
(7, 3, '2023-09-15');

INSERT INTO Tellimuse_rida (tellimus_kood, toode_id, yhiku_hind, kogus)
VALUES (1, 1, 5, 5),
(1, 2, 3, 6),
(2, 1, 5, 4),
(2, 2, 3, 2),
(3, 3, 10, 2),
(4, 3, 10, 5),
(5, 1, 5, 1),
(5, 2, 3, 1),
(6, 5, 6, 7),
(7, 4, 5, 10);


Pange tähele, et ühe lausega saab lisada mitu rida. Tellimuse ridade lisamisel tekitan olukorra, et mitmel kliendil on ühes kuus kõige suurem tellimuste maht. Lahendused peavad leidma selle kuu jaoks kõik need kliendid.

Samm 3

Lahendusvariandid

Lahendus 1

Ühise tabeli avaldisega (Common Table Expression, CTE) leian iga kuu+aasta ning kliendi kombinatsiooni kohta tema tellimuste mahu.
Põhipäringus jätan korreleeruvat alampäringut kasutades alles read, kus vaadeldava kuu+aasta korral on see maht kõige suurem.

WITH Tellimus_stats AS (SELECT extract(year FROM tellimuse_kp) AS aasta,
extract(month FROM tellimuse_kp) AS kuu,
klient_id, Sum(yhiku_hind*kogus) AS summa
FROM Tellimus INNER JOIN Tellimuse_rida USING (tellimus_kood)
GROUP BY aasta, kuu, klient_id)
SELECT aasta, kuu, klient_id, summa
FROM Tellimus_stats AS Ts
WHERE summa=(SELECT Max(summa) AS m
FROM Tellimus_stats AS Ts2
WHERE Ts.aasta=Ts2.aasta
AND Ts.kuu=Ts2.kuu);

Lahendus 2

Ühise tabeli avaldisega (Common Table Expression, CTE) leian iga kuu+aasta ning kliendi kombinatsiooni kohta tema tellimuste mahu ning
asetuse tellimuste mahtude pingereas vaadeldava kuu+aasta korral. Põhipäringus võtan välja read, kus asetus on esimene.

WITH Tellimus_stats AS (SELECT extract(year FROM tellimuse_kp) AS aasta,
extract(month FROM tellimuse_kp) AS kuu,
klient_id, Sum(yhiku_hind*kogus) AS summa,
Rank() OVER (PARTITION BY extract(year FROM tellimuse_kp), extract(month FROM tellimuse_kp) ORDER BY Sum(yhiku_hind*kogus) DESC) AS asetus
FROM Tellimus INNER JOIN Tellimuse_rida USING (tellimus_kood)
GROUP BY aasta, kuu, klient_id)
SELECT aasta, kuu, klient_id, summa
FROM Tellimus_stats
WHERE asetus=1;

Lahendus 3

Variatsioonina lahendusest 2 loon hetktõmmise e materialiseeritud vaate ning teen päringu selle vaate põhjal.

CREATE MATERIALIZED VIEW Tellimus_stats AS
SELECT extract(year FROM tellimuse_kp) AS aasta,
extract(month FROM tellimuse_kp) AS kuu,
klient_id, Sum(yhiku_hind*kogus) AS summa,
Rank() OVER (PARTITION BY extract(year FROM tellimuse_kp), extract(month FROM tellimuse_kp) ORDER BY Sum(yhiku_hind*kogus) DESC) AS asetus
FROM Tellimus INNER JOIN Tellimuse_rida USING (tellimus_kood)
GROUP BY aasta, kuu, klient_id;

SELECT aasta, kuu, klient_id, summa
FROM Tellimus_stats
WHERE asetus=1;

Samm 4

Genereerin testandmeid kasutades andmebaasisüsteemi pakutavaid võimalusi. Kirjutan täisarve loetavuse huvides alakriipse kasutades, mis on võimalik alates PostgreSQL 16.

Genereerin 15 000 tellimust. klient_id on juhuslik täisarv vahemikus 1 ja 1 000 ning tellimuse kuupäev on  juhuslik kuupäev vahemikus 1. jaanuar 2015 ja tänane kuupäev (leitakse funktsiooniga CURRENT_DATE). SELLE FAILI lõpus on veel näiteid testandmete genereerimisest.

INSERT INTO Tellimus (tellimus_kood, klient_id, tellimuse_kp)
SELECT generator AS tellimus_kood,
floor(random() * (1_000 - 1 + 1) + 1)::int AS klient_id,
'2015-01-01'::date + floor(random() * (current_date - '2015-01-01' + 1) + 1)::int AS tellimuse_kp
FROM generate_series(1, 15_000) AS generator;

Genereerin 100 000 tellimuse rida. toode_id on juhuslik täisarv vahemikus 1 ja 2 000, ühiku hind on juhuslik täisarv vahemikus 1 ja 1 000 ning kogus on juhuslik täisarv vahemikus 1 ja 10 000. ON CONFLICT DO NOTHING on vajalik selleks, et juhul kui genereeritakse mitu rida, kus on sama tellimuse koodi ja toote id kombinatsioon, siis ei tekiks unikaalsuse rikkumise viga, vaid viga tekitanud rida lihtsalt ei lisata tabelisse ja jätkatakse tööd. Selle tulemusena tekib tabelisse vähem kui 100 000 tellimuse rida.

INSERT INTO Tellimuse_rida (tellimus_kood, toode_id, yhiku_hind, kogus)
SELECT floor(random() * (1_000 - 1 + 1) + 1)::int AS tellimus_kood,
floor(random() * (2_000 - 1 + 1) + 1)::int AS toode_id,
floor(random() * (1_000 - 1 + 1) + 1)::int AS yhiku_hind,
floor(random() * (10_000 - 1 + 1) + 1)::int AS kogus
FROM generate_series(1, 100_000) AS generator
ON CONFLICT DO NOTHING;

Eemaldan tellimused, millel pole ühtegi tellimuse rida. Kustutatakse kuni 5000 tellimust, e tellimusi jääb alles umbes 10 000.

DELETE FROM Tellimus WHERE NOT EXISTS (SELECT *
FROM Tellimuse_rida
WHERE Tellimus.tellimus_kood=Tellimuse_rida.tellimus_kood);

Värskendan hetktõmmist.

REFRESH MATERIALIZED VIEW Tellimus_stats;

Samm 5

Värskendan statistikat kasutades ANALYZE lauset.

ANALYZE;

Samm 6

Töökiiruse testimiseks kasutan EXPLAIN ANALYZE lauset. Sellisel juhul lause täidetakse, ning ma näen lause täitmise plaani, plaani koostamise aega ja täitmiseks kulunud aega. Lause tulemusena leitud andmeid ei kuvata. Üldiselt võiks selliseid lauseid käivitada korduvalt ja leida aegade keskmise. Piirdun antud juhul ühe käivitamisega.

See on EXPLAIN ANALYZE lause näide.

EXPLAIN ANALYZE WITH Tellimus_stats AS (SELECT extract(year FROM tellimuse_kp) AS aasta,
extract(month FROM tellimuse_kp) AS kuu,
klient_id, Sum(yhiku_hind*kogus) AS summa
FROM Tellimus INNER JOIN Tellimuse_rida USING (tellimus_kood)
GROUP BY aasta, kuu, klient_id)
SELECT aasta, kuu, klient_id, summa
FROM Tellimus_stats AS Ts
WHERE summa=(SELECT Max(summa) AS m
FROM Tellimus_stats AS Ts2
WHERE Ts.aasta=Ts2.aasta
AND Ts.kuu=Ts2.kuu);

Tulemused mõõduka testandmete hulga korral.

Lahendus 1: 295 ms
Lahendus 2: 95 ms
Lahendus 3: 0.3 ms Sinna tuleb juurde arvestada see, et hetktõmmise värskendamiseks kulus 90 ms. Kuid hetktõmmise kasutamise eelis tuleb sellest, et juhul kui kõige uuemaid andmeid ei pea arvestama, siis saab hetktõmmist värskendada teatud intervalli tagant (mitte koos iga andmemuudatusega) e asünkroonselt.

Samm 7

Kasutan testandmete kiireks kustutamiseks TRUNCATE lauset.

TRUNCATE Tellimus CASCADE;

Võrdluseks: DELETE lausega kustutades (DELETE FROM Tellimus;) kulub 185 ms, TRUNCATE lausega 8 ms.

Samm 8

Genereerin testandmeid nii, et tellimusi oleks umbes 1 miljon ja tellimuse ridu umbes 10 miljonit. Kuna osadele tellimustele ei pruugita tellimuse ridu genereerida, siis loon algul 1.5 miljonit tellimust ja kustutan tellimused, millele pole tellimuse ridu lisatud.

Samm 9

ANALYZE;

Samm 10

Uus katsetus

Lahendus 1: Peale tunniajalist töötamist katkestasin, sest tulemust ei olnud.
Lahendus 2: 4837 ms
Lahendus 3: 15 ms. Sinna tuleb juurde arvestada see, et hetktõmmise värskendamiseks kulus 4853 ms. Kuid hetktõmmise kasutamise eelis tuleb sellest, et juhul kui kõige uuemaid andmeid ei pea arvestama, siis saab hetktõmmist värskendada teatud intervalli tagant (mitte koos iga andmemuudatusega) e asünkroonselt.

Järeldused

Optimaalseim on Lahendus 2. Juhul kui kõige uuemaid andmeid ei pea tulemuse arvutamisel arvestama, siis on optimaalseim Lahendus 3.

Lahendus 1 näitas, et koos andmehulga kasvuga võib töökiirus halveneda mitte lineaarselt, vaid eksponentsiaalselt.