Meine Einführung in Datenbanken und PostgreSQL war für die Entwicklung von Webanwendungen und statistische Analysen gedacht. Ich habe gerade genug SQL gelernt, um die Abfragen dazu zu bringen, die richtigen Antworten zurückzugeben. Durch meine Arbeit mit PostGIS (und FOSS4G) habe ich mich mit Paul Ramsey angefreundet. Wir sind jetzt Mitarbeiter bei Crunchy Data und er hilft mir bei meinem SQL-Fu. Eine der ersten Lektionen, die er mir beibrachte, war „Versuchen Sie, Joins anstelle von Unterabfragen zu verwenden.“

Der heutige Beitrag wird diesen Ratschlag durcharbeiten, da Paul und ich einige SQL durcharbeiten.

Was wir zu beantworten versuchen

Ich versuche, einige neue Lehr- und Sprechmaterialien zur Verwendung von SQL in der Datenwissenschaft zu erstellen, und ich habe an einer Datenmanipulation vor der Analyse gearbeitet. Ich hatte eine Tabelle, fire_weather , die eine Teilmenge der Wettertabelle ist, und ich möchte alle Einträge in weather finden, die NICHT in fire_weather sind. Mein anfänglicher Instinkt war es, eine Unterabfrage zu schreiben, aber dies schien eine unkomplizierte und einfache Abfrage zu sein, um Pauls Ratschlag „Verwenden Sie einen Join“ zu befolgen.

Falsche Join-Abfrage

Ich habe mit dieser Join-Abfrage begonnen:

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

Und es hat nicht funktioniert (sonst würde ich diesen Blogbeitrag nicht schreiben). Es stellt sich heraus, dass dies einen Cross-Join ausführt, bei dem wir alle paarweisen Kombinationen aller Zeilen in beiden Tabellen erhalten.

Die richtige Join-Abfrage

An diesem Punkt habe ich nachgelassen (im Gegensatz zum Telefonieren), und wir fangen an zu diskutieren, wie man den richtigen Join macht. Es stellt sich heraus, dass die richtige Syntax lautet:

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

Grundsätzlich führen Sie einen left Outer Join durch, bei dem Sie alle Zeilen aus der Wettertabelle und nur die übereinstimmenden fire_weather-Einträge fire_weather . Dann filtern Sie alle Datensätze heraus, in denen Übereinstimmungen für fire_weather . Ziemlich einfach zu verstehen, aber nicht sehr gesetzt, wie bei der Verwendung der Mengenlehre (die die Grundlage für Beziehungen in relationalen Datenbanksystemen bildet). Andererseits haben wir jetzt eine funktionierende Join-Abfrage.

Analysiere dies

Als Teil meiner Reise zu einem besseren Verständnis von SQL in PostgreSQL bin ich ein großer Fan von EXPLAIN ANALYZE for für Timings geworden und habe mir den Abfrageplan angesehen. Nur aus Neugier entscheide ich mich, das Timing und den Abfrageplan für die Join-Abfrage zu betrachten. Hier ist die Ausgabe und es dauerte ungefähr 7 Millisekunden mit einem etwas komplizierten Abfrageplan:

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

Jetzt die Unterabfrage

Und jetzt wollte ich sehen, wie meine ursprüngliche Idee für eine Unterabfrage funktionieren würde. Hier ist die Unterabfrage, um die gleiche Frage zu beantworten:

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

Weitere Analyse

Sie sollten sehen, warum diese Abfrage mich angesprochen hat, sie ist sehr Set-basiert und sehr einfach zu schreiben. Wenn ich mir diese Abfrage mit explain analyze anschaue, bekomme ich:

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

Abfragevergleich

Am Ende haben wir also einen sehr einfachen Plan und Timings, die ungefähr mit dem Join übereinstimmen. Paul und ich diskutierten, warum die Timings so ähnlich sein könnten, und wir hatten mindestens zwei Gründe:

  1. Das Dataset enthält nur sehr wenige Zeilen (8 kb), sodass sich die Leistung der Unterabfrage bei einem größeren Datensatz möglicherweise verschlechtert.
  2. Mein Computer verfügt über NVMe-Laufwerke, die dem sequentiellen Zugriff einen noch größeren Leistungsunterschied verleihen.

Eine weitere Set-basierte Unterabfrage

Schließlich hat Paul eine weitere Set-basierte Abfrage entwickelt, um dieselbe Frage zu beantworten:

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

Dieser verwendet eine neue SQL-Klausel, EXCEPT , die Teil der set operation Query Combiner ist. Eine große Einschränkung bei diesen Abfragen besteht darin, dass die Abfragen auf jeder Seite der except Klausel dieselben Spalten und Datentypen zurückgeben müssen. Dies erklärt, warum diese Abfrage die Gesamtzahl der Zeilen nicht zurückgeben kann. Diese Abfrage erwies sich jedoch als schlechter in der Leistung und ein viel komplizierterer Abfrageplan:

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

Die überraschende Wendung

Dann dachte ich etwas mehr über die von Paul vorgeschlagene Abfrage nach und stellte fest, dass wir den Join auf der rechten Seite der except Klausel nicht wirklich brauchten. Da fire_weather dieselben Spalten wie weather enthält, können wir einfach die gewünschten Spalten verwenden und die erwartete Antwort erhalten.

select id from weather exceptselect id from fire_weather;

Jetzt hat dies eine schöne Syntax, die es wirklich einfach zu verstehen macht. Wenn wir die Anzahl tatsächlich wie in den anderen Abfragen erhalten möchten, können wir unsere Abfrage in einen CTE einschließen.

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

Mit diesem goldenen Ticket erhalten wir 6 ms Abfragezeiten und einen Abfrageplan, der sauberer, aber nicht einfacher ist. Ich sollte beachten, dass Sauberkeit und Einfachheit keine Schlüsselfaktoren bei der Bewertung eines Abfrageplans sind.

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

Meine letzten Erkenntnisse

Hier sind die letzten Lektionen, die ich Ihnen aus dieser kleinen Übung überlassen möchte.

  1. Nie Augapfelabfragezeiten – das waren alle die gleiche Geschwindigkeit für mein Auge. Erkläre, wer dein Freund ist.
  2. Schreiben Sie die Abfrage so, wie es am sinnvollsten ist, und führen Sie dann Timings durch. Wenn es nicht gut ist, dann suchen Sie nach einer Alternative (wahrscheinlich Joins)
  3. Es gibt mehrere Möglichkeiten, in SQL zur gleichen Antwort zu gelangen – die „richtige“ Antwort wird sehr situationsabhängig sein. Ein paar Dinge, die das Ergebnis beeinflussen:
    1. Ihre Datengröße – eine Abfrage ist möglicherweise nicht mehr „ok“, wenn Ihre Datengröße wächst
    2. Indizes
    3. Ihre Festplattengeschwindigkeit
    4. Speichergröße
    5. Prozessorgeschwindigkeit
    6. Wie oft Sie die Abfrage ausführen möchten
  4. Schließlich wird sich die Zeit, die Sie damit verbringen, Ihre SQL-Kenntnisse und -Fähigkeiten zu verbessern, auszahlen. Selbst wenn Sie nicht die effizientesten Abfragen schreiben, sind diese normalerweise schneller als das Schreiben vieler prozeduraler Codes. Wenn ich mehr und mehr SQL-Muster lerne, bin ich umso erstaunter über den Code, den ich durch ein paar Zeilen SQL ersetzen kann (und ich bekomme normalerweise einen enormen Leistungsschub).

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht.