min introduktion till databaser och PostgreSQL var för webbapplikationsutveckling och statistisk analys. Jag lärde mig bara tillräckligt med SQL för att få frågorna att returnera rätt svar. På grund av mitt arbete med PostGIS (och FOSS4G) blev jag vän med Paul Ramsey. Vi är nu medarbetare på Crunchy Data och han hjälper mig upp min SQL-fu. En av de första lektionerna han lärde mig var ”försök att använda kopplingar snarare än subqueries.”

dagens inlägg kommer att fungera genom detta råd, som Paul och jag arbetar igenom några SQL.

vad vi försöker svara på

jag försöker skapa några nya undervisnings-och talmaterial om att använda SQL i datavetenskap och jag arbetade med någon pre-analysdata manipulation. Jag hade ett bord, fire_weather, som är en delmängd av vädertabellen, och jag vill hitta alla poster i väder som inte finns i fire_weather. Min första instinkt var att skriva en subquery men det verkade som en enkel och enkel fråga att följa Pauls ”använd en koppling” råd.

fel Anslutningsfråga

jag började med den här anslutningsfrågan:

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

och det fungerade inte (annars skulle jag inte skriva det här blogginlägget). Det visar sig att detta gör ett kors gå där vi sluta med alla parvisa kombinationer av alla rader i båda tabellerna.

den korrekta Anslutningsfrågan

så vid denna tidpunkt slår jag upp (i motsats till att ringa upp på telefonen) Paul och vi börjar diskutera hur man gör rätt anslutning. Det visar sig att rätt syntax är:

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

i grund och botten gör du en vänster yttre koppling, vilket ger dig alla rader från väderbordet och bara fire_weather-posterna som matchar. Sedan filtrerar du bort alla poster där det finns matchningar för fire_weather. Ganska enkelt att förstå men inte så mycket som, som att använda uppsättningsteori (som ligger till grund för relationer i relationsdatabassystem). Sedan igen, vi har nu en fungerande gå fråga.

analysera detta

som en del av min resa till större förståelse av SQL i PostgreSQL har jag blivit ett stort fan av EXPLAIN ANALYZE for För tidpunkter och tittar på frågeplanen. Bara av nyfikenhet bestämmer jag mig för att titta på tidpunkten och frågeplanen för anslutningsfrågan. Här är utgången och det tog ungefär 7 millisekunder med en något komplicerad frågeplan:

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 Subquery

och nu ville jag se hur min ursprungliga IDE för en subquery skulle fungera. Här är subquery sättet att svara på samma fråga:

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

mer analys

du bör se varför den här frågan vädjade till mig, den är väldigt baserad och mycket enkel att skriva. När jag tittar på den här frågan med explain analyze får jag:

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

Frågejämförelse

så vi slutar med en mycket enkel plan och tidpunkter som är ungefär samma som kopplingen. Paul och jag diskuterade varför tiderna kan vara så lika och vi kom med minst två skäl:

  1. datauppsättningen har mycket få rader (8k) så subquery prestanda kan försämras med en större datamängd.
  2. min maskin har NVMe-hårddiskar som ger sekventiell åtkomst en ännu större prestandaskillnad.

en annan uppsättning baserad Subquery

slutligen Paul, kom upp med en mer uppsättning baserad Fråga för att svara på samma fråga:

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

den här använder en ny SQL-klausul, förutom, som är en del av set operation query combiners. En stor återhållsamhet på dessa frågor är att frågorna på varje sida av utom klausulen måste returnera samma kolumner och datatyper. Detta förklarar varför den här frågan inte kan returnera det totala antalet rader. Men den här frågan visade sig vara sämre i prestanda och en mycket mer komplicerad frågeplan:

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

Överraskningsvridningen

då tänkte jag lite mer om frågan Paul föreslog och insåg att vi inte verkligen behövde gå med på höger sida av utom klausulen. Eftersom fire_weather innehåller alla samma kolumner som weather kan vi bara använda de kolumner vi vill ha och få det svar vi förväntade oss.

select id from weather exceptselect id from fire_weather;

nu har detta en fin uppsättning syntax som gör det väldigt lätt att förstå. Om vi faktiskt ville få räkningen som i de andra frågorna kan vi slå in vår fråga i en CTE.

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

med denna gyllene biljett får vi 6 ms frågetider och en fråga planer som är renare men inte enklaste. Jag bör notera att renlighet och enkelhet inte är nyckelfaktorer för att utvärdera en frågeplan.

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

mina sista Takeaways

här är de sista lektionerna jag skulle vilja lämna dig med från denna lilla övning.

  1. aldrig eyeball query times-dessa var alla samma hastighet för mitt öga. Förklara analysera är din vän.
  2. skriv frågan på det sätt som är mest meningsfullt och gör sedan tidpunkter. Om det inte är bra, se till ett alternativ (förmodligen går med)
  3. det finns flera sätt att komma fram till samma svar i SQL – det ”rätta” svaret kommer att vara mycket situationsberoende. Några saker som kommer att påverka resultatet:
    1. din datastorlek – en fråga kan sluta vara ” ok ” när din datastorlek växer
    2. index
    3. din diskhastighet
    4. minnesstorlek
    5. processorhastighet
    6. hur ofta du planerar att utföra frågan
  4. slutligen kommer tiden att förbättra dina SQL-kunskaper och färdigheter att löna sig vackert. Även om du inte skriver de mest effektiva frågorna är de fortfarande vanligtvis snabbare än att skriva mycket procedurkod. När jag lär mig mer och mer SQL-mönster desto mer förvånad är jag alls koden jag kan ersätta med några rader SQL (och jag brukar få en enorm prestationsökning).

Lämna ett svar

Din e-postadress kommer inte publiceras.