Küsimus

Anonüümne (06.03.2025 16:39)

Kuidas leida iga rea kohta ühest tabelist selliste ridade arv teisest tabelist, mis rahuldavad mingit tingimust? Kas erinevatel lahendustel on töökiiruse mõttes erinevusi?

Vastus (06.03.2025 16:52):

Toon näite. Olgu meil PostgreSQL andmebaas, millel on järgmine kontseptuaalne struktuur

[Facility]-1-------------0..*-[Health_care_visit]

Tabelis Facility on 50_000 rida.
Tabelis Health_care_visit on 1_000_000 rida.

Järgnevad katsetused on tehtud PostgreSQL (17) põhjal. Täitmisplaanide ja täitmise kiiruse vaatamiseks kasutasin EXPLAIN ANALYZE lauset. Rõhutan, et nende ülesannete juures pakutud lahendusvariandid on loogiliselt samaväärsed e annavad alati sama tulemuse. Kõik need on kokku näide "abstraktsioonide lekkimise" kohta e selle kohta, kuidas andmete füüsilise ("süsteemi kaane all") salvestamise ja töötlemise küsimused "lekivad" andmete kasutaja maailma ja kokkuvõttes peab ta teadma, kuidas oleks konkreetses süsteemis kõige otstarbekam oma küsimusi formuleerida.

Ülesanne: Leia iga rea kohta tabelis Facility selliste ridade arv tabelis Health_care_visit, kus külastus algas 2010. aastal.

Kõikides lahendustes kasutatakse välisühendamist, et tulemuses oleksid ka sellised read tabelist Facility, millele ei ole tingimusele vastavaid ridu tabelis Health_care_visit.

Lahendus 1:
SELECT facility_name, Count(hcv.facility_id) AS cnt

FROM Facility LEFT JOIN health_care_visit AS hcv ON Facility.facility_id=hcv.facility_id

AND Extract(year FROM from_date)=2010

GROUP BY facility_name;

Teises tabelis huvipakkuvaid ridu piirav tingimus on kirjutatud ühendamise tingimusse.

Lahendus 2:

WITH hcv AS (SELECT facility_id

FROM health_care_visit

WHERE Extract(year FROM from_date)=2010)

SELECT facility_name, Count(hcv.facility_id) AS cnt

FROM Facility LEFT JOIN hcv ON Facility.facility_id=hcv.facility_id

GROUP BY facility_name;

Teises tabelis huvipakkuvad read leitakse alampäringuga, mis on vormistatud ühise tabeli avaldisena. Põhipäringus ühendatakse need andmed esimese tabeliga ning arvutatakse koondandmed.

Lahendus 3:

SELECT facility_name, Count(hcv.facility_id) FILTER (WHERE Extract(year FROM from_date)=2010) AS cnt

FROM Facility LEFT JOIN health_care_visit AS hcv ON Facility.facility_id=hcv.facility_id

GROUP BY facility_name;

Kasutatakse FILTER funktsionaalsust.

Lahenduste 1 ja 2 korral kasutas andmebaasisüsteem tabeli Health_care_visit kõigi ridade läbivaatamiseks kahte paralleelprotsessi. Lause täitmine võttis umbes 150 ms.
Lahenduse 3 korral vaatas andmebaasisüsteem samuti läbi kõik tabeli Health_care_visit read, kuid paralleeltööd sealjuures ei kasutanud ning lause täitmine võttis umbes 500 ms.

Hinda vastust:

Keskmine hinne: Pole veel hinnanguid!