mijn inleiding tot databases en PostgreSQL was voor de ontwikkeling van webapplicaties en statistische analyse. Ik heb net genoeg SQL geleerd om de vragen te krijgen om de juiste antwoorden terug te geven. Door mijn werk met PostGIS (en FOSS4G) werd ik bevriend met Paul Ramsey. We zijn nu collega ‘ s bij Crunchy Data en hij helpt me mijn SQL-fu omhoog te krijgen. Een van de eerste lessen die hij me leerde was “probeer joins te gebruiken in plaats van subqueries.”

de post van vandaag zal werken door middel van dit advies, zoals Paul en ik werken door middel van sommige SQL.

wat we proberen te beantwoorden

ik probeer wat nieuw onderwijs – en spreekmateriaal te maken over het gebruik van SQL in data science en ik werkte aan een aantal pre-analyse data manipulatie. Ik had een tabel, fire_weather, dat is een subset van de weer tabel, en Ik wil alle vermeldingen in weer die niet in fire_weather vinden. Mijn eerste instinct was om een subquery te schrijven, maar dit leek een eenvoudige en eenvoudige vraag om Paul ‘ s “gebruik een join” advies te volgen.

verkeerde Join Query

ik ben begonnen met deze join query:

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

en het werkte niet (anders zou ik deze blogpost niet schrijven). Het blijkt dat dit doet een cross join waar we eindigen met alle paarsgewijs combinaties van alle rijen in beide tabellen.

de juiste Join Query

dus op dit punt slack-up (in tegenstelling tot bellen op de telefoon) Paul en we beginnen te bespreken hoe de juiste join doen. Het blijkt dat de juiste syntaxis is:

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

in principe doe je een linker buitenste join, waardoor je alle rijen van de weer tabel en alleen de fire_weather items die overeenkomen. Vervolgens filtert u alle records waar er overeenkomsten zijn voor fire_weather. Vrij eenvoudig te begrijpen, maar niet erg set als, zoals in het gebruik van verzamelingenleer (die de basis is van relaties in relationele databasesystemen). Dan weer, we hebben nu een werkende join query.

analyseer dit

als onderdeel van mijn reis naar een beter begrip van SQL in PostgreSQL, ben ik een grote fan geworden van EXPLAIN ANALYZE for for timings en het kijken naar het query plan. Gewoon uit nieuwsgierigheid besluit ik om te kijken naar de timing en query plan voor de join query. Hier is de output en het duurde ongeveer 7 milliseconden met een enigszins ingewikkelde query plan:

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

nu de Subquery

en nu wilde ik zien hoe mijn oorspronkelijke idee voor een subquery zou presteren. Hier is de subquery manier om dezelfde vraag te beantwoorden:

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

meer analyse

je zou moeten zien waarom deze query mij aansprak, het is zeer ingesteld gebaseerd en zeer eenvoudig te schrijven. Als ik kijk naar deze vraag met uitleggen analyseren krijg ik:

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

Query vergelijking

dus we eindigen met een zeer eenvoudig plan en timings die ongeveer hetzelfde zijn als de join. Paul en ik bespraken waarom de tijden zo vergelijkbaar kunnen zijn en we kwamen met ten minste twee redenen:

  1. de dataset heeft zeer weinig rijen (8k), zodat de subquery prestaties kunnen degraderen met een grotere dataset.
  2. mijn machine heeft NVMe-schijven die sequentiële toegang geven tot een nog groter prestatieverschil.

een andere set-gebaseerde Subquery

tenslotte kwam Paul met nog een set-gebaseerde query om dezelfde vraag te beantwoorden:

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

deze gebruikt een nieuwe SQL-clausule, behalve, die deel uitmaakt van de set operation query combiners. Een grote beperking op deze queries is dat de queries aan elke kant van de except-clausule dezelfde kolommen en datatypes moeten retourneren. Dit verklaart waarom deze query het totaal aantal rijen niet kan retourneren. Maar deze query bleek slechter in prestaties en een veel ingewikkelder query plan:

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

toen dacht ik wat meer over de query die Paul voorstelde en realiseerde ik me dat we de join aan de rechterkant van de except-clausule niet echt nodig hadden. Omdat fire_weather dezelfde kolommen bevat als weather kunnen we gewoon de kolommen gebruiken die we willen en de reactie krijgen die we verwacht hadden.

select id from weather exceptselect id from fire_weather;

nu heeft dit een mooie set syntaxis waardoor het heel gemakkelijk te begrijpen is. Als we eigenlijk de telling wilden krijgen, zoals in de andere query ‘ s, kunnen we onze query in een CTE wikkelen.

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

met dit gouden ticket krijgen we 6 ms query tijden en een query plannen die schoner maar niet eenvoudig is. Ik moet er rekening mee dat netheid en eenvoud zijn geen belangrijke factoren bij het evalueren van een query plan.

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

mijn laatste afhaalmaaltijden

hier zijn de laatste lessen die ik u uit deze kleine oefening wil geven.

  1. never eyeball query times-deze waren allemaal dezelfde snelheid voor mijn oog. Leg uit dat analyseren je vriend is.
  2. schrijf de query op de manier die het meest zinvol is en doe dan timings. Als het niet goed is, kijk dan naar een alternatief (waarschijnlijk joins)
  3. er zijn meerdere manieren om tot hetzelfde antwoord te komen in SQL – het “juiste” antwoord zal zeer situationeel afhankelijk zijn. Een paar dingen die het resultaat zullen beïnvloeden:
    1. uw gegevensgrootte – een query kan stoppen met “ok” als uw gegevensgrootte groeit
    2. indexen
    3. uw schijfsnelheid
    4. geheugengrootte
    5. processorsnelheid
    6. hoe vaak u van plan bent de query uit te voeren
  4. tot slot, de tijd besteed aan het verbeteren van uw SQL kennis en vaardigheden zal betalen royaal. Zelfs als je niet de meest efficiënte query ‘ s schrijft, zijn ze meestal nog steeds sneller dan het schrijven van veel procedurele code. Als ik leer meer en meer SQL patronen hoe meer verbaasd Ik ben op alle de code die ik kan vervangen door een paar regels van SQL (en ik krijg meestal een enorme prestatie boost).

Geef een antwoord

Het e-mailadres wordt niet gepubliceerd.