Ecco una domanda comune: scoprire i medici attivi l’anno scorso? Supponiamo che esista una tabella “medici” che registra le informazioni sui medici e una tabella “ricoveri di pazienti” che registra i casi di pazienti ricoverati dai medici. L’obiettivo è filtrare i medici che hanno avuto almeno un ricovero di pazienti nell’ultimo anno (questo potrebbe essere un periodo di tempo dinamico nelle pipeline di machine learning o nei dashboard interattivi).
In pratica, esistono tre modi comuni per scrivere questa query: EXIST, IN e JOIN. Li analizzeremo ed eseguiremo esperimenti su Bigquery e PostgreSQL, per convalidare la nostra analisi.
Primo approccio: IN
Per gli utenti Python, l’operatore IN potrebbe essere l’approccio più intuitivo. Ciò comporta innanzitutto il filtraggio dei registri di ammissione dell’ultimo anno e quindi il controllo se i medici sono elencati in tali registri. Verificheremo anche se l’aggiunta di un DISTINCT aumenterà le prestazioni.
SELECT d.*
FROM `tool-for-analyst.richard_workspace.doctors` d
WHERE d.doctor_id IN (
SELECT doctor_id
FROM `tool-for-analyst.richard_workspace.patient_admissions` admissions
WHERE admissions.Admission_Date BETWEEN '2023–01–01' AND '2023–12–31'
);
Il secondo approccio ESISTE:
Un altro approccio prevede l’utilizzo dell’operatore EXISTS, che filtra i risultati per includere solo quelli per i quali la sottoquery restituisce almeno un record. EXISTS opera sul concetto di “Semi JOIN”, il che significa che in realtà non esegue un join sul lato destro; invece, controlla semplicemente se un’unione produrrebbe risultati per una determinata tupla. Quando ne trova uno si ferma. Ciò potrebbe offrire alcuni vantaggi in termini di prestazioni.
SELECT d.*
FROM `tool-for-analyst.richard_workspace.doctors` d
WHERE EXISTS (
SELECT 1
FROM `tool-for-analyst.richard_workspace.patient_admissions` pa
WHERE pa.doctor_id = d.doctor_id
AND pa.Admission_Date BETWEEN '2023–01–01' AND '2023–12–31'
)
Terzo approccio:
Il terzo approccio prevede l’utilizzo di JOIN, che è il metodo più classico nella filosofia dei database relazionali. Ci sono alcune controversie frequenti nei forum su quando filtrare e se utilizzare una sottoquery o una Common Table Expression (CTE). Abbiamo incluso queste considerazioni anche nel nostro esperimento.
UNISCITI dopo il filtro nella sottoquery
SELECT d.doctor_id, name, Hospital, Age, Gender
FROM `tool-for-analyst.richard_workspace.doctors` d
INNER JOIN (
SELECT DISTINCT doctor_id
FROM `tool-for-analyst.richard_workspace.patient_admissions`
WHERE Admission_Date BETWEEN '2023–01–01' AND '2023–12–31'
) admissions
ON d.doctor_id = admissions.doctor_id;
Filtra e RAGGRUPPA PER dopo UNISCITI
SELECT d.doctor_id, d.name, d.Hospital, d.Age, d.Gender
FROM `tool-for-analyst.richard_workspace.doctors` d
INNER JOIN `tool-for-analyst.richard_workspace.patient_admissions` pa
ON d.doctor_id = pa.doctor_id
WHERE pa.Admission_Date BETWEEN '2023–01–01' AND '2023–12–31'
GROUP BY d.doctor_id, d.name, d.Hospital, d.Age, d.Gender;
Filtro CTE prima di JOIN
WITH filtered_admissions AS(
SELECT DISTINCT doctor_id
FROM `tool-for-analyst.richard_workspace.patient_admissions` admissions
WHERE admissions.Admission_Date
BETWEEN '2023–01–01' AND '2023–12–31'
)
SELECT d.*
FROM `tool-for-analyst.richard_workspace.doctors` d
JOIN filtered_admissions
ON d.doctor_id = filtered_admissions.doctor_id;
Ora abbiamo sei query da testare. Ottengono tutti lo stesso risultato dal database ma presentano lievi differenze nella logica o nella sintassi.
Domanda 1: DENTRO
Q2: IN con DISTINCT nella sottoquery
Q3: ESISTE
Q4: JOIN con una sottoquery che filtra l’intervallo di tempo
Q5: UNISCITI prima di qualsiasi filtro, quindi utilizza GRUPPO BY per deduplicare
Q6: UNISCITI con un CTE che filtra l’intervallo di tempo
Abbiamo eseguito ciascuna query 10 volte in un set di dati di test generato, spostando l’intervallo di tempo di 1 giorno per ciascun test. Utilizzando i dettagli di esecuzione di BigQuery e il comando EXPLAIN ANALYZE in PostgreSQL, abbiamo ottenuto informazioni dettagliate sui tempi e sui piani di esecuzione. I risultati dei test parlano chiaro. Se si tratta di un caso d’uso reale, possiamo semplicemente selezionare l’opzione con le migliori prestazioni e andare avanti. Tuttavia, in questo blog, scaveremo un po’ più a fondo e ci chiederemo: Perché?
La risposta potrebbe essere trovata nel piano di esecuzione, che rivela il vero approccio con cui il motore del database calcola la query.
Grande query:
I piani di esecuzione per Q1 ‘IN’ e Q3 ‘EXISTS’ sono esattamente gli stessi. L’esecuzione in due passaggi ha prima filtrato la sottoquery, quindi ha utilizzato un SEMI JOIN per identificare i medici con almeno un paziente ricoverato. Questo era un esempio perfetto di ciò che abbiamo menzionato prima: SQL è un linguaggio dichiarativo che descrive ciò di cui hai bisogno e BigQuery capisce come eseguirlo. Anche se la logica SQL differiva nell’approccio al problema, BigQuery ha riconosciuto che richiedevano lo stesso risultato e ha deciso di utilizzare lo stesso approccio di esecuzione per ottimizzarli.
L’aggiunta di DISTINCT nella sottoquery IN ha comportato prestazioni molto peggiori. È stato piuttosto interessante osservare che l’aggiunta di un singolo DISTINCT potrebbe avere un impatto così significativo sulla velocità di esecuzione della query. Quando abbiamo esaminato il piano di esecuzione della query, abbiamo potuto vedere che un singolo DISTINCT provoca due passaggi aggiuntivi nell’esecuzione della query. Ciò ha comportato il salvataggio di più tabelle temporanee nel processo, con conseguente tempo di esecuzione notevolmente più lento.
Tra i tre metodi JOIN, è stato sorprendente che Q5 ‘JOIN before filter’ dimostri le prestazioni migliori, mentre gli altri due approcci che tentano di ottimizzare il filtro e la sequenza JOIN, Q4 ‘JOIN with subquery’ e Q6 ‘JOIN with CTE’, mostrano scarsi risultati. prestazione. Dall’esame del pianificatore, è emerso che BigQuery ha effettivamente riconosciuto che l’esecuzione del filtro prima del JOIN può ottimizzare l’efficienza. Tuttavia, quando abbiamo provato a controllare manualmente la sequenza forzando l’applicazione del filtro prima del JOIN, il risultato sono stati più passaggi nel piano di esecuzione e tempi di esecuzione significativamente più lenti. È interessante notare che gli approcci subquery e CTE avevano esattamente lo stesso piano di esecuzione, che è anche molto simile al piano “IN with DISTINCT” del secondo trimestre. L’unica differenza era che nel passaggio finale veniva utilizzato un INNER JOIN invece di un SEMI JOIN.
PostgreSQL:
Per quanto riguarda Postgres, la differenza nel tempo di query tra le sei query analizzate è stata relativamente minima. Ciò potrebbe essere dovuto al fatto che il set di dati del test non era abbastanza grande da evidenziare in modo significativo le differenze. Man mano che il set di dati aumenta di dimensioni, è probabile che le differenze di prestazioni tra gli approcci diventino più sostanziali.
La nostra analisi si è basata sui risultati di “EXPLAIN ANALYZE”. Questo strumento è prezioso per comprendere le caratteristiche prestazionali di una query PostgreSQL. ‘EXPLAIN’ fornisce il piano di esecuzione che il pianificatore di query PostgreSQL genera per una determinata istruzione, mentre l’opzione ‘ANALYZE’ esegue effettivamente l’istruzione, consentendo una valutazione più accurata delle prestazioni.
Q1 ‘IN’ e Q3 ‘EXISTS’ avevano lo stesso piano di esecuzione con il costo più basso. Similmente a BigQuery, anche PostgreSQL ha riconosciuto che le due query richiedevano gli stessi dati e ha ottimizzato per essi.
Q2, Q4 e Q6 hanno tutti esattamente lo stesso piano di esecuzione con un costo leggermente più elevato. Nonostante le query siano diverse nella logica o nella sintassi, il pianificatore Postgres ha deciso di eseguire la stessa esecuzione: Filter -> Group by(DISTINCT) -> JOIN,
Q5 “JOIN prima del filtro” presentava il piano di esecuzione con i costi più elevati. Sebbene il pianificatore PostgreSQL sia comunque riuscito ad applicare il filtro prima del JOIN, il processo di deduplicazione è stato applicato alla tabella più grande, con un conseguente costo maggiore.
Nel nostro esperimento, approcci come forzare un filtro prima di un JOIN o aggiungere l’opzione DISTINCT per l’operatore IN non hanno aumentato le prestazioni della nostra query; invece, lo hanno reso più lento. Confrontando BigQuery con Postgres, è evidente che ognuno di essi ha le proprie nicchie e i propri punti di forza. I loro pianificatori sono inoltre ottimizzati per obiettivi diversi utilizzando approcci diversi.
Detto questo, l’ottimizzazione dell’efficienza in un linguaggio dichiarativo come SQL non è determinata esclusivamente dalla query. Altrettanto importante è il modo in cui il motore del database lo interpreta, lo pianifica e lo esegue. Questo processo può dipendere in gran parte dalla progettazione del database, nonché dalla struttura e dall’indicizzazione dei dati.
L’esperimento che abbiamo condotto per il blog è specifico per determinati casi d’uso e set di dati. Il modo più efficace per comprendere le prestazioni è eseguire le proprie query, esaminare il piano di esecuzione delle query e vedere cosa farà. Non ottimizzare eccessivamente sulla base di presupposti teorici. I test pratici e l’osservazione dovrebbero sempre essere i principi guida nell’ottimizzazione delle query.
Fonte: towardsdatascience.com
