introducerea mea la baze de date și PostgreSQL a fost pentru dezvoltarea de aplicații web și analiză statistică. Am învățat suficient SQL pentru a obține interogările pentru a returna răspunsurile corecte. Datorită muncii mele cu PostGIS (și FOSS4G) am devenit prieten cu Paul Ramsey. Acum suntem colegi la Crunchy Data și el mă ajută să-mi ridic SQL-fu-ul. Una dintre primele lecții pe care mi le-a învățat a fost „încercați să folosiți joins mai degrabă decât subqueries.”

postul de astăzi va lucra prin acest sfat, așa cum Paul și cu mine lucrăm prin unele SQL.

la ce încercăm să răspundem

încerc să creez câteva materiale noi de predare și vorbire despre utilizarea SQL în știința datelor și lucram la o manipulare a datelor pre-analiză. Am avut un tabel, fire_weather, care este un subset al tabelului meteo, și vreau să găsesc toate intrările în vreme care nu sunt în fire_weather. Instinctul meu inițial a fost de a scrie un subquery, dar acest lucru părea ca o interogare simplă și ușor să urmeze sfatul lui Pavel „utilizați un join”.

interogare de asociere greșită

am început cu această interogare de asociere:

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

și nu a funcționat (altfel nu aș scrie această postare pe blog). Se pare că acest lucru face o încrucișare în care ajungem cu toate combinațiile pereche ale tuturor rândurilor din ambele tabele.

buna se alăture interogare

deci, în acest moment am slack-up (spre deosebire de a suna la telefon) Paul și vom începe să discutăm cum să facă buna se alăture. Se pare că sintaxa corectă este:

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

Practic faci un exterior se alăture stânga, oferindu-vă toate rândurile din tabelul de vreme și numai intrările fire_weather care se potrivesc. Apoi filtrați toate înregistrările în care există meciuri pentru fire_weather. Destul de simplu de înțeles, dar nu foarte setat, ca în utilizarea teoriei mulțimilor (care este baza relațiilor în sistemele de baze de date relaționale). Apoi, din nou, avem acum o interogare de lucru se alăture.

analizați acest

ca parte a călătoriei mele către o mai bună înțelegere a SQL în PostgreSQL, am devenit un mare fan al explicați analiza pentru temporizări și privind planul de interogare. Doar din curiozitate am decis să se uite la calendarul și planul de interogare pentru interogare join. Iată ieșirea și a durat aproximativ 7 milisecunde cu un plan de interogare oarecum complicat:

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

acum, Subquery

și acum am vrut să văd cum ideea mea originală pentru un subquery ar efectua. Aici este modul de subquery pentru a răspunde la aceeași întrebare:

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

mai multe analize

ar trebui să vedeți de ce această interogare a apelat la mine, este foarte stabilit pe bază și foarte simplu de a scrie. Când mă uit la această interogare cu explica analiza i a lua:

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

comparație interogare

deci, vom termina cu un plan foarte simplu și temporizări, care sunt despre aproximativ la fel ca join. Paul și am discutat de ce timpii ar putea fi atât de asemănătoare și am venit cu cel puțin două motive:

  1. setul de date are foarte puține rânduri (8k), astfel încât performanța subinterogării s-ar putea degrada cu un set de date mai mare.
  2. mașina mea are unități de disc NVMe care oferă acces secvențial o diferență de performanță și mai mare.

un alt Subquery Set bazat

în cele din urmă Paul, a venit cu o interogare mai mult set bazat pentru a răspunde la aceeași întrebare:

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

Aceasta folosește o nouă clauză SQL, cu excepția, care face parte din combinatoarele de interogare de operare stabilite. O reținere mare asupra acestor interogări este că interogările de pe fiecare parte a clauzei exceptate trebuie să returneze aceleași coloane și tipuri de date. Aceasta explică de ce această interogare nu poate returna numărul total de rânduri. Dar această interogare s-a dovedit a fi mai slabă în performanță și un plan de interogare mult mai complicat:

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

surpriza Twist

apoi m-am gândit ceva mai mult despre interogare Pavel a sugerat și a dat seama că nu am nevoie într-adevăr se alăture pe partea dreaptă a clauzei cu excepția. Deoarece fire_weather conține toate aceleași coloane ca vremea putem folosi doar coloanele pe care le dorim și pentru a obține răspunsul ne-am așteptat.

select id from weather exceptselect id from fire_weather;

acum, acest lucru are sintaxa set frumos ceea ce face foarte ușor de înțeles. Dacă am vrut să obținem numărul ca în celelalte interogări, putem încheia interogarea noastră într-un CTE.

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

cu acest bilet de aur vom obține 6 ms interogare ori și o interogare planuri care este mai curat, dar nu mai simplu. Ar trebui să rețineți că curățenia și simplitatea nu sunt factori cheie în evaluarea unui plan de interogare.

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

ultimele mele lecții

Iată ultimele lecții cu care aș vrea să vă las din acest mic exercițiu.

  1. niciodată timpii de interogare a globului ocular – toate acestea au fost aceeași viteză pentru ochiul meu. Explicați analiza este prietenul tău.
  2. scrieți interogarea în modul în care are cel mai mult sens și apoi faceți temporizări. Dacă nu este bine, atunci uita – te la o alternativă (probabil se alătură)
  3. există mai multe moduri de a ajunge la același răspuns în SQL-răspunsul „corect” va fi extrem de dependent de situație. Câteva lucruri care vor influența rezultatul:
    1. Dimensiunea datelor – o interogare ar putea să nu mai fie „ok” pe măsură ce dimensiunea datelor crește
    2. indexuri
    3. viteza discului
    4. dimensiunea memoriei
    5. viteza procesorului
    6. cât de des intenționați să executați interogarea
  4. în cele din urmă, timpul petrecut îmbunătățirea cunoștințelor SQL și abilitățile vor achita generos. Chiar dacă nu scrieți cele mai eficiente interogări, acestea sunt de obicei mai rapide decât scrierea multor coduri procedurale. Pe măsură ce învăț din ce în ce mai multe modele SQL, cu atât sunt mai uimit de codul pe care îl pot înlocui cu câteva linii de SQL (și de obicei obțin un impuls imens de performanță).

Lasă un răspuns

Adresa ta de email nu va fi publicată.