Kas korduste eemaldamine IN/NOT IN alampäringus muudab lause täitmise kiiremaks või mitte?

Postitas Erki Eessaar 14.04.2024 15:56 (muudeti 14.04.2024 16:08)
Sõltub andmebaasisüsteemist. Näiteks PostgreSQLis on sellel suur mõju, MySQLis ei ole mõju. PostgreSQLis aeglustab korduste eemaldamine alampäringus IN päringu täitmist kolm korda kuid muudab NOT IN päringu täitmise absurdselt palju kiiremaks.

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 (16) 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 tervishoiuasutused, kuhu on tehtud vähemalt üks visiit.

Lahendus 1: Lause täitmiseks kasutatakse tabeli Facility primaarvõtmele ja tabeli Health_care_visit välisvõtmele loodud indekseid. Lause täitmine võtab umbes 100ms.

SELECT *
FROM Facility 
WHERE facility_id IN (SELECT facility_id
FROM Health_Care_visit);

Lahendus 2: Lause täitmiseks loetakse ainult tabelite andmeid (st indekseid ei kasutata). Lause täitmine võtab umbes 300 ms.

SELECT *
FROM Facility 
WHERE facility_id IN (SELECT DISTINCT facility_id
FROM Health_Care_visit);

Lahendus 3: Sama, mis lahendus 2.

SELECT *
FROM Facility 
WHERE facility_id IN (SELECT facility_id
FROM Health_Care_visit
GROUP BY facility_id);

Ülesanne: Leia tervishoiuasutused, kuhu pole tehtud ühtegi visiiti.

Lahendus 1: Iga rea kohta tabelist Facility materialiseeritakse e arvutatakse välja ja salvestatakse mällu indeksi põhjal täidetud alampäringu tulemus. Lause täitmine võtab umbes 31 minutit.

SELECT * 
FROM Facility 
WHERE facility_id NOT IN (SELECT facility_id 
FROM Health_care_visit 
WHERE facility_id IS NOT NULL);

Lahendus 2: Peaaegu sama plaan kui esimese lahenduse korral, ainult et alampäringu tulemust ei materialiseerita. Lause täitmine võtab umbes 125 ms.

SELECT * 
FROM Facility 
WHERE facility_id NOT IN (SELECT DISTINCT facility_id 
FROM Health_care_visit 
WHERE facility_id IS NOT NULL);

Lahendus 3: Sama, mis lahendus 2.

SELECT * 
FROM Facility 
WHERE facility_id NOT IN (SELECT facility_id 
FROM Health_care_visit 
WHERE facility_id IS NOT NULL
GROUP BY facility_id);

Proovisin alampäringus korduste eemaldamise mõju ka MySQL (8) andmebaasis ja seal ei paistnud sellel lause täitmisplaani koostamisele ja lause täitmise kiirusele mõju olevat.

Hinda postitust:

Keskmine hinne : Pole veel hinnanguid!