Filtrowanie podczas złączenia

Optymalizacja wydajności zapytań w PostgreSQL

Amy McCarty

Instructor

Złączenia – przypomnienie

Złączenia

Łączą tabele za pomocą co najmniej 1 wspólnego pola

SELECT * 
FROM TABLE_A AS A
INNER JOIN TABLE_B AS B 
  ON A.NAME = B.NAME

Złączenia do łączenia danych

Z wielu tabel
Wewnętrzne i zewnętrzne

Złączenia do filtrowania danych

Złączenie wewnętrzne
Złączenie zewnętrzne z warunkiem niezwiązanym

Dane pacjentów i wizyt

Wizyty

visit_id	reason	patient_id
01	checkup	999
02	infection	888

Pacjenci

patient_id	name	sex
999	Lotte Smith	F
888	Zhang Wei	M
777	Amelia Hernandez	F

Złączenia wewnętrzne do filtrowania

SELECT *
FROM appointments a
INNER JOIN patients p
ON a.patient_id = p.patient_id

ID pacjenta 999 z tabeli Visits łączy się z ID 999 w tabeli Patients. ID 888 łączy się z ID 888. ID 777 w tabeli Patients nie ma powiązania.

Złączenia wewnętrzne do filtrowania

SELECT *
FROM appointments a
INNER JOIN patients p
ON a.patient_id = p.patient_id

Wynik

visit_id	reason	patient_id	name	sex
01	checkup	999	Lotte Smith	F
02	infection	888	Zhang Wei	M

Złączenia zewnętrzne do filtrowania

SELECT *
FROM appointments a
LEFT JOIN patients p
  ON a.patient_id = p.patient_id
  AND p.sex = 'M'

Tabela Patients filtruje się do mężczyzn – zaznaczony jest pacjent 888 Zhang Wei M. To jedyny rekord męski, więc jedyny łączący się z tabelą Visits.

Złączenia zewnętrzne do filtrowania

SELECT *
FROM appointments a
LEFT JOIN patients p
  ON a.patient_id = p.patient_id
  AND p.sex = 'M'

Wynik

visit_id	reason	patient_id	name	sex
01	checkup	999
02	infection	888	Zhang Wei	M

Pułapki filtrowania

SELECT * 
FROM appointments a
LEFT JOIN patients p 
  ON a.patient_id = p.patient_id
WHERE p.sex = 'M'

1) FROM ID pacjenta 999 z tabeli Visits łączy się z ID 999 w tabeli Patients. ID 888 łączy się z ID 888. ID 777 w tabeli Patients nie ma powiązania.

2) WHERE Tabela z wierszem wizyty 888 Zhang Wei i informacjami o pacjencie oraz przekreślonym wierszem wizyty 999 Lotte Smith.

Pułapki filtrowania

SELECT * 
FROM appointments a
LEFT JOIN patients p 
  ON a.patient_id = p.patient_id
WHERE p.sex = 'M'

Wynik

visit_id	reason	patient_id	name	sex
02	infection	888	Zhang Wei	M

Pułapki filtrowania

SELECT * 
FROM appointments a
LEFT JOIN patients p 
  ON a.patient_id = p.patient_id
WHERE p.sex = 'M'

Wynik

visit_id	reason	patient_id	name	sex
02	infection	888	Zhang Wei	M

SELECT *
FROM appointments a
LEFT JOIN patients p
  ON a.patient_id = p.patient_id
  AND p.sex = 'M'

visit_id	reason	patient_id	name	sex
01	checkup	999
02	infection	888	Zhang Wei	M

Jak unikać pułapek filtrowania

SELECT *
FROM appointments a
INNER JOIN patients p
  ON a.patient_id = p.patient_id
WHERE p.sex = 'M'

visit_id	reason	patient_id	name	sex
02	infection	888	Zhang Wei	M

SELECT *
FROM appointments a
INNER JOIN patients p
  ON a.patient_id = p.patient_id
  AND p.sex = 'M'

Czas na ćwiczenia!

Optymalizacja wydajności zapytań w PostgreSQL