La mia introduzione ai database e PostgreSQL era per lo sviluppo di applicazioni web e l’analisi statistica. Ho imparato abbastanza SQL per ottenere le query per restituire le risposte giuste. Grazie al mio lavoro con PostGIS (e FOSS4G) sono diventato amico di Paul Ramsey. Ora siamo colleghi di lavoro a Crunchy Data e lui mi sta aiutando il mio SQL-fu. Una delle prime lezioni che mi ha insegnato è stata “Prova a usare join piuttosto che subquery.”

Il post di oggi funzionerà attraverso questo consiglio, come Paul e io lavoriamo attraverso alcuni SQL.

Quello che stiamo cercando di rispondere

Sto cercando di creare alcuni nuovi materiali didattici e parlanti sull’utilizzo di SQL nella scienza dei dati e stavo lavorando su alcune manipolazioni dei dati pre-analisi. Avevo una tabella, fire_weather, che è un sottoinsieme della tabella meteo, e voglio trovare tutte le voci in weather che NON sono in fire_weather. Il mio istinto iniziale era quello di scrivere una sottoquery, ma questa sembrava una query semplice e facile da seguire il consiglio “usa un join” di Paul.

Query di join errata

Ho iniziato con questa query di join:

 select count(weather.*) from weather, fire_weather where weather.id != fire_weather.id;

E non ha funzionato (altrimenti non avrei scritto questo post sul blog). Si scopre che questo fa un cross join in cui finiamo con tutte le combinazioni a coppie di tutte le righe in entrambe le tabelle.

La query di join corretta

Quindi a questo punto mi allento (invece di suonare al telefono) Paul e iniziamo a discutere su come fare il join corretto. Si scopre che la sintassi giusta è:

select count(weather.*)from weather left join fire_weather on weather.id = fire_weather.idwhere fire_weather.id is null;

Fondamentalmente fai un join esterno sinistro, dandoti tutte le righe della tabella meteo e solo le voci fire_weather che corrispondono. Quindi filtra tutti i record in cui ci sono corrispondenze per fire_weather. Abbastanza semplice da capire ma non molto impostato come, come nell’uso della teoria degli insiemi (che è la base delle relazioni nei sistemi di database relazionali). Poi di nuovo, ora abbiamo una query join funzionante.

Analizza questo

Come parte del mio viaggio verso una maggiore comprensione di SQL in PostgreSQL, sono diventato un grande fan di EXPLAIN ANALYZE for per i tempi e guardando il piano di query. Solo per curiosità decido di esaminare i tempi e il piano di query per la query join. Ecco l’output e ci sono voluti circa 7 millisecondi con un piano di query un po complicato:

Aggregate (cost=1358.58..1358.59 rows=1 width=8) (actual time=6.780..6.781 rows=1 loops=1)
-> Hash Anti Join (cost=1168.76..1358.58 rows=1 width=64) (actual time=1.648..6.218 rows=6802 loops=1)
Hash Cond: (weather.id = fire_weather.id)
-> Seq Scan on weather (cost=0.00..159.05 rows=8205 width=68) (actual time=0.008..3.157 rows=8205 loops=1)
-> Hash (cost=785.56..785.56 rows=30656 width=4) (actual time=1.609..1.609 rows=1403 loops=1)
Buckets: 32768 Batches: 1 Memory Usage: 306kB
-> Seq Scan on fire_weather (cost=0.00..785.56 rows=30656 width=4) (actual time=0.008..1.399 rows=1403 loops=1)
Planning Time: 0.110 ms
Execution Time: 6.807 ms

Ora la Subquery

E ora volevo vedere come avrebbe funzionato la mia idea originale per una subquery. Ecco il modo subquery per rispondere alla stessa domanda:

select count(weather.*) from weather where id not in (select id from fire_weather);

Più analisi

Dovresti capire perché questa query mi è piaciuta, è molto impostata e molto semplice da scrivere. Quando guardo questa query con explain analyze ottengo:

Aggregate (cost=1052.02..1052.03 rows=1 width=8) (actual time=7.458..7.459 rows=1 loops=1)
-> Seq Scan on weather (cost=862.20..1041.76 rows=4102 width=64) (actual time=1.139..6.727 rows=6802 loops=1)
Filter: (NOT (hashed SubPlan 1))
Rows Removed by Filter: 1403
SubPlan 1
-> Seq Scan on fire_weather (cost=0.00..785.56 rows=30656 width=4) (actual time=0.004..0.643 rows=1403 loops=1)
Planning Time: 0.068 ms
Execution Time: 7.497 ms

Confronto delle query

Quindi finiamo con un piano e tempi molto semplici che sono all’incirca uguali al join. Paul ed io abbiamo discusso perché i tempi potrebbero essere così simili e siamo venuti su con almeno due ragioni:

  1. Il set di dati ha pochissime righe (8k), quindi le prestazioni della sottoquery potrebbero degradarsi con un set di dati più grande.
  2. La mia macchina ha unità disco NVMe che offrono all’accesso sequenziale una differenza di prestazioni ancora maggiore.

Un’altra sottoquery basata su set

Infine Paul, ha inventato un’altra query basata su set per rispondere alla stessa domanda:

select * from weather exceptselect weather.* from weather join fire_weather on weather.id = fire_weather.id

Questo utilizza una nuova clausola SQL, ECCETTO, che fa parte dei combinatori di query di operazione impostata. Una grande limitazione su queste query è che le query su ciascun lato della clausola except devono restituire le stesse colonne e tipi di dati. Questo spiega perché questa query non può restituire il conteggio totale delle righe. Ma questa query si è rivelata peggiore in termini di prestazioni e un piano di query molto più complicato:

HashSetOp Except (cost=0.00..1984.13 rows=8205 width=74) (actual time=10.382..11.171 rows=6802 loops=1)
-> Append (cost=0.00..1532.86 rows=16410 width=74) (actual time=0.021..5.867 rows=9608 loops=1)
" -> Subquery Scan on ""*SELECT* 1"" (cost=0.00..241.10 rows=8205 width=44) (actual time=0.021..2.300 rows=8205 loops=1)"
-> Seq Scan on weather (cost=0.00..159.05 rows=8205 width=40) (actual time=0.006..0.537 rows=8205 loops=1)
" -> Subquery Scan on ""*SELECT* 2"" (cost=261.61..1209.71 rows=8205 width=44) (actual time=1.796..2.788 rows=1403 loops=1)"
-> Hash Join (cost=261.61..1127.66 rows=8205 width=40) (actual time=1.795..2.583 rows=1403 loops=1)
Hash Cond: (fire_weather.id = weather_1.id)
-> Seq Scan on fire_weather (cost=0.00..785.56 rows=30656 width=4) (actual time=0.008..0.413 rows=1403 loops=1)
-> Hash (cost=159.05..159.05 rows=8205 width=40) (actual time=1.768..1.768 rows=8205 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 765kB
-> Seq Scan on weather weather_1 (cost=0.00..159.05 rows=8205 width=40) (actual time=0.002..0.518 rows=8205 loops=1)
Planning Time: 0.127 ms
Execution Time: 11.975 ms

The Surprise Twist

Poi ho pensato un po ‘ di più sulla query suggerita da Paul e mi sono reso conto che non avevamo davvero bisogno del join sul lato destro della clausola except. Poiché fire_weather contiene tutte le stesse colonne di weather, possiamo semplicemente usare le colonne che vogliamo e ottenere la risposta che ci aspettavamo.

select id from weather exceptselect id from fire_weather;

Ora questo ha una bella sintassi impostata che lo rende davvero facile da capire. Se volessimo ottenere effettivamente il conteggio come nelle altre query, possiamo avvolgere la nostra query in un CTE.

with count_me as (select id from weather exceptselect id from fire_weather)select count(*) from count_me;

Con questo biglietto d’oro otteniamo tempi di query 6 ms e piani di query più puliti ma non più semplici. Devo notare che la pulizia e la semplicità non sono fattori chiave nella valutazione di un piano di query.

HashSetOp Except (cost=0.00..1624.68 rows=8205 width=8) (actual time=4.834..5.313 rows=6802 loops=1)
-> Append (cost=0.00..1527.52 rows=38861 width=8) (actual time=0.004..3.248 rows=9608 loops=1)
-> Subquery Scan on "*SELECT* 1" (cost=0.00..241.10 rows=8205 width=8) (actual time=0.004..1.903 rows=8205 loops=1)
-> Seq Scan on weather (cost=0.00..159.05 rows=8205 width=4) (actual time=0.003..1.001 rows=8205 loops=1)
-> Subquery Scan on "*SELECT* 2" (cost=0.00..1092.12 rows=30656 width=8) (actual time=0.003..0.554 rows=1403 loops=1)
-> Seq Scan on fire_weather (cost=0.00..785.56 rows=30656 width=4) (actual time=0.002..0.409 rows=1403 loops=1)
Planning Time: 0.246 ms
Execution Time: 5.897 ms

I miei ultimi take Away

Ecco le lezioni finali che vorrei lasciarvi con questo piccolo esercizio.

  1. Mai tempi di query bulbo oculare – questi erano tutti la stessa velocità per il mio occhio. Spiegare analizzare è tuo amico.
  2. Scrivi la query nel modo più sensato e poi esegui i tempi. Se non va bene, cerca un’alternativa (probabilmente join)
  3. Ci sono più modi per arrivare alla stessa risposta in SQL – la risposta “giusta” sarà altamente dipendente dalla situazione. Un paio di cose che influenzano il risultato:
    1. la dimensione dei dati – una query potrebbe smettere di essere “ok”, come la dimensione dei dati aumenta
    2. Indici
    3. la Vostra velocità del disco
    4. la dimensione della Memoria
    5. velocità Processore
    6. Come spesso si pensa di eseguire la query
  4. Infine, il tempo speso a migliorare le vostre conoscenze di SQL e le abilità pagare profumatamente. Anche se non si scrivono le query più efficienti, di solito sono ancora più veloci della scrittura di un sacco di codice procedurale. Man mano che imparo sempre più modelli SQL, più sono stupito del codice che posso sostituire con poche righe di SQL (e di solito ottengo un enorme aumento delle prestazioni).

Lascia un commento

Il tuo indirizzo email non sarà pubblicato.