Vastus (01.04.2023 13:24): Teen katsetuse PostgreSQL (15) andmebaasis, millel on järgnev kontseptuaalne struktuur.

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

Tabelis
Health_care_visit - 1 miljon rida
Person - 70000 rida
Facility - 50000 rida

Päring ühe tabeli põhjal - kolm alternatiivset viisi päringutingimuste AND-idega sidumiseks

EXPLAIN ANALYZE SELECT health_care_visit_id
FROM Health_care_visit
WHERE facility_id BETWEEN 1 AND 100
INTERSECT  SELECT health_care_visit_id
FROM Health_care_visit
WHERE patient_id BETWEEN 47000 AND 55000;
--90 ms

EXPLAIN ANALYZE SELECT health_care_visit_id
FROM Health_care_visit
WHERE facility_id BETWEEN 1 AND 100
AND patient_id BETWEEN 47000 AND 55000;
--2.5 ms

EXPLAIN ANALYZE SELECT health_care_visit_id
FROM (SELECT health_care_visit_id
FROM Health_care_visit
WHERE facility_id BETWEEN 1 AND 100) AS a INNER JOIN
(SELECT health_care_visit_id
FROM Health_care_visit
WHERE patient_id BETWEEN 47000 AND 55000) AS b
USING (health_care_visit_id);
--6 ms

Päring mitme tabeli põhjal - kolm alternatiivset viisi päringutingimuste AND-idega sidumiseks

EXPLAIN ANALYZE SELECT Health_care_visit.health_care_visit_id
FROM Health_care_visit INNER JOIN Facility USING (facility_id)
INNER JOIN Person ON Health_care_visit.patient_id=Person.party_id
WHERE facility_name LIKE 'A%'
AND last_name LIKE 'A%';
--97 ms

EXPLAIN ANALYZE SELECT Health_care_visit.health_care_visit_id
FROM Health_care_visit INNER JOIN Facility USING (facility_id)
WHERE facility_name LIKE 'A%'
INTERSECT SELECT Health_care_visit.health_care_visit_id
FROM Health_care_visit INNER JOIN Person ON Health_care_visit.patient_id=Person.party_id
WHERE last_name LIKE 'A%';
--181 ms

EXPLAIN ANALYZE SELECT health_care_visit_id
FROM (SELECT health_care_visit_id
FROM Health_care_visit INNER JOIN Facility USING (facility_id)
WHERE facility_name LIKE 'A%') AS a INNER JOIN (SELECT Health_care_visit.health_care_visit_id
FROM Health_care_visit INNER JOIN Person ON Health_care_visit.patient_id=Person.party_id
WHERE last_name LIKE 'A%') AS b USING (health_care_visit_id);
--91 ms

Mõlemal juhul on INTERSECT kasutamine kõige aeglasem

Vaate kasutamine, et otsida ühe või mitme tingimuse järgi

EXPLAIN ANALYZE SELECT Health_care_visit.health_care_visit_id
FROM Health_care_visit INNER JOIN Facility USING (facility_id)
WHERE facility_name LIKE 'A%';
--67 ms

CREATE VIEW v_hcv AS
SELECT Health_care_visit.health_care_visit_id, facility_name, last_name
FROM Health_care_visit INNER JOIN Facility USING (facility_id)
INNER JOIN Person ON Health_care_visit.patient_id=Person.party_id;

EXPLAIN ANALYZE SELECT health_care_visit_id
FROM v_hcv
WHERE facility_name LIKE 'A%';
--105 ms

Kui otsisin ühe tingimuse järgi, siis päring vaate põhjal, mis ühendas mitut tabelit, oli aeglasem kui päring ainult nende tabelite põhjal, mida oli vaja päringule vastamiseks.

EXPLAIN ANALYZE SELECT Health_care_visit.health_care_visit_id
FROM Health_care_visit INNER JOIN Facility USING (facility_id)
WHERE facility_name LIKE 'A%'
INTERSECT SELECT Health_care_visit.health_care_visit_id
FROM Health_care_visit INNER JOIN Person ON Health_care_visit.patient_id=Person.party_id
WHERE last_name LIKE 'A%';
--181 ms

EXPLAIN ANALYZE SELECT health_care_visit_id
FROM v_hcv
WHERE facility_name LIKE 'A%'
AND last_name LIKE 'A%';
--95 ms

Kui otsisin mitme tingimuse järgi, siis päring vaate põhjal, mis ühendas mitut tabelit, oli kiirem kui INTERSECTi kasutav päring otse tabelite põhjal.