min introduktion til databaser var til udvikling af applikationer og statistisk analyse. Jeg lærte lige nok til at få spørgsmålene til at returnere de rigtige svar. På grund af mit arbejde med PostGIS (og FOSS4G) blev jeg venner med Paul Ramsey. Vi er nu kolleger hos Crunchy Data, og han hjælper mig med at få min KVL-fu. En af de første lektioner, han lærte mig, var “Prøv at bruge sammenføjninger snarere end underforespørgsler.”

dagens indlæg vil arbejde gennem dette råd, som Paul og jeg arbejder gennem nogle kvm.

hvad vi forsøger at svare

jeg forsøger at skabe nogle nye undervisnings-og talematerialer om brug af KVM i datalogi og jeg arbejdede på nogle præ-analyse data manipulation. Jeg havde et bord, fire_vejr, som er en delmængde af vejrbordet, og jeg vil gerne finde alle de poster i vejr, der ikke er i fire_vejr. Mit oprindelige instinkt var at skrive en underforespørgsel, men dette virkede som en ligetil og nem forespørgsel for at følge Pauls “brug en join” – råd.

forkert Joinforespørgsel

jeg startede med denne joinforespørgsel:

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

og det virkede ikke (ellers ville jeg ikke skrive dette blogindlæg). Det viser sig, at dette gør en krydsforbindelse, hvor vi ender med alle de parvise kombinationer af alle rækker i begge tabeller.

den korrekte Joinforespørgsel

så på dette tidspunkt slår jeg op (i modsætning til at ringe op på telefonen) Paul, og vi begynder at diskutere, hvordan man gør det rigtige join. Det viser sig, at den rigtige syntaks er:

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

dybest set gør du en venstre ydre joinforbindelse, hvilket giver dig alle rækkerne fra vejrbordet og kun de fire_vejrposter, der matcher. Derefter filtrerer du alle poster, hvor der er kampe for fire_vejr. Temmelig enkel at forstå, men ikke meget indstillet som ved brug af sætteori (som er grundlaget for relationer i relationsdatabasesystemer). Så igen har vi nu en fungerende joinforespørgsel.

Analyser dette

som en del af min rejse til større forståelse af KVL i postgraduate er jeg blevet en stor fan af forklar analyse for til tider og kigger på forespørgselsplanen. Bare af nysgerrighed beslutter jeg at se på timingen og forespørgselsplanen for joinforespørgslen. Her er output, og det tog omkring 7 millisekunder med en noget kompliceret forespørgselsplan:

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 Underforespørgslen

og nu ville jeg se, hvordan min oprindelige ide til en underforespørgsel ville udføre. Her er underforespørgslen måde at besvare det samme spørgsmål på:

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

mere analyse

du skal se, hvorfor denne forespørgsel appellerede til mig, den er meget indstillet og meget enkel at skrive. Når jeg ser på denne forespørgsel med forklare analysere får jeg:

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

Forespørgselssammenligning

så vi ender med en meget enkel plan og tidspunkter, der er omtrent det samme som sammenføjningen. Paul og jeg diskuterede, hvorfor timingen kunne være så ens, og vi kom med mindst to grunde:

  1. datasættet har meget få rækker (8k), så underforespørgselsydelsen kan forringes med et større datasæt.
  2. min maskine har NVMe-diskdrev, der giver sekventiel adgang en endnu større ydelsesforskel.

et andet sæt baseret underforespørgsel

endelig Paul, kom med endnu et sæt baseret forespørgsel for at besvare det samme spørgsmål:

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

denne bruger en ny KVL-klausul, undtagen, som er en del af sætoperationsforespørgselkombinatorerne. En stor tilbageholdenhed på disse forespørgsler er, at forespørgsler på hver side af undtagen klausul skal returnere de samme kolonner og datatyper. Dette forklarer, hvorfor denne forespørgsel ikke kan returnere det samlede antal rækker. Men denne forespørgsel viste sig at være værre i ydeevne og en meget mere kompliceret forespørgselsplan:

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

Overraskelsesvridningen

så tænkte jeg lidt mere på forespørgslen Paul foreslog og indså, at vi ikke rigtig havde brug for sammenføjningen på højre side af undtagen klausulen. Da fire_vejr indeholder alle de samme kolonner som vejr, kan vi bare bruge de kolonner, vi ønsker, og få det Svar, Vi forventede.

select id from weather exceptselect id from fire_weather;

nu dette har nice sæt syntaks gør det virkelig nemt at forstå. Hvis vi faktisk ville få optællingen som i de andre forespørgsler, kan vi pakke vores forespørgsel i en CTE.

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

med denne gyldne billet får vi 6 ms forespørgselstider og en forespørgselsplan, der er renere, men ikke enkleste. Jeg skal bemærke, at renlighed og enkelhed ikke er nøglefaktorer i evalueringen af en forespørgselsplan.

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

mine sidste grillbarer

her er de sidste lektioner, jeg gerne vil forlade dig med fra denne lille øvelse.

  1. aldrig eyeball forespørgselstider – disse var alle samme hastighed for mit øje. Forklar analysere er din ven.
  2. skriv forespørgslen på den måde, der giver mest mening og derefter gøre tider. Hvis det ikke er godt, så kig på et alternativ (sandsynligvis sammenføjning)
  3. der er flere måder at nå frem til det samme svar i KVM – det “rigtige” svar vil være meget situationsafhængigt. Et par ting, der vil påvirke resultatet:
    1. din datastørrelse – en forespørgsel kan stoppe med at være” ok”, når din datastørrelse vokser
    2. indekser
    3. din diskhastighed
    4. hukommelsesstørrelse
    5. processorhastighed
    6. hvor ofte du planlægger at udføre forespørgslen
  4. endelig vil tid brugt på at forbedre din viden og færdigheder betale sig smukt. Selvom du ikke skriver de mest effektive forespørgsler, er de stadig normalt hurtigere end at skrive en masse procedurekode. Efterhånden som jeg lærer flere og flere KVL-mønstre, jo mere forbløffet er jeg over den kode, Jeg kan erstatte med et par linjer KVL (og jeg får normalt et stort præstationsforøgelse).

Skriv et svar

Din e-mailadresse vil ikke blive publiceret.