私のデータベースとPostgreSQLの紹介は、webアプリケーション開発と統計分析のためのものでした。 私は正しい答えを返すためにクエリを取得するのに十分なSQLを学びました。 PostGIS(とFOSS4G)との仕事のために、私はPaul Ramseyと友達になりました。 私たちは今、Crunchy Dataの同僚であり、彼は私のSQL-fuを手伝っています。 彼が私に教えた最初の教訓の一つは、”サブクエリではなく結合を使用しようとすることでした。”

今日の投稿は、Paulと私がいくつかのSQLを使って作業するので、このアドバイスを通じて動作するようになります。

私たちが答えようとしていること

私はデータサイエンスでSQLを使用する上でいくつかの新しい教育と話す資料を作成しようとしており、私はいくつかの分析前のデータ操作に取り組んでいました。 Weatherテーブルのサブセットであるfire_weatherテーブルがあり、fire_weatherにないweatherのすべてのエントリを検索したいと思います。 私の最初の本能はサブクエリを書くことでしたが、これはPaulの”use a join”アドバイスに従うための簡単で簡単なクエリのように見えました。

間違った結合クエリ

私はこの結合クエリから始めました:

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

そして、それは動作しませんでした(そうでなければ、私はこのブログ記事を書いていないでしょう)。 これは、両方のテーブルのすべての行のすべてのペアワイズの組み合わせで終わるクロス結合を行うことが判明しました。

適切な結合クエリ

だから、この時点で私は(電話で鳴るのではなく)Paulを緩めると、適切な結合を行う方法について議論し始めます。 正しい構文は次のとおりです:

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

基本的には、left outer joinを実行して、weatherテーブルのすべての行と一致するfire_weatherエントリのみを指定します。 次に、fire_weather一致するすべてのレコードを除外します。 理解するのは非常に簡単ですが、セット理論(関係データベースシステムの関係の基礎である)を使用する場合のように、あまり設定されていません。 その後、再び、我々は今、作業結合クエリを持っています。PostgreSQLでのSQLの理解を深めるための旅の一環として、EXPLAIN ANALYZE for timingsの大ファンになり、クエリプランを見ています。 好奇心から、私はjoinクエリのタイミングとクエリプランを見ることにしました。 ここに出力があり、やや複雑なクエリプランで約7ミリ秒かかりました:

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

今、サブクエリ

そして今、私はサブクエリのための私の元のアイデアがどのように実行されるかを見たいと思っていました。 同じ質問に答えるためのサブクエリの方法は次のとおりです:

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

より多くの分析

このクエリが私に訴えた理由を確認する必要があります。 Explain analyzeを使用してこのクエリを見ると、次のようになります:

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

クエリ比較

だから、結合とほぼ同じ非常に単純な計画とタイミングになります。 ポールと私はタイミングがとても似ているかもしれない理由を議論し、我々は少なくとも二つの理由を思い付いた:

  1. データセットの行数が非常に少ない(8k)ため、データセットが大きいとサブクエリのパフォーマンスが低下する可能性があります。
  2. 私のマシンにはnvmeディスクドライブがあり、シーケンシャルアクセスにはさらに大きなパフォーマ

別のセットベースのサブクエリ

最後に、Paulは、同じ質問に答えるためにもう一つのセットベースのクエリを思い付きました:

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

これは、set操作クエリコンバイナの一部である新しいSQL句EXCEPTを使用します。 これらのクエリの大きな制約の1つは、except句の各側のクエリが同じ列とデータ型を返す必要があることです。 これは、このクエリが合計行数を返すことができない理由を説明しています。 しかし、このクエリはパフォーマンスが悪化し、はるかに複雑なクエリプランが判明しました:驚きのねじれ

次に、Paulが提案したクエリについてもう少し考え、except節の右側にjoinが実際には必要ないことに気付きました。 Fire_weatherにはweatherと同じ列がすべて含まれているため、必要な列を使用して期待した応答を得ることができます。

select id from weather exceptselect id from fire_weather;

今、これはそれが本当に理解しやすくする素敵なセット構文を持っています。 他のクエリのように実際にカウントを取得したい場合は、クエリをCTEでラップできます。

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

このゴールデンチケットを使用すると、6msのクエリ時間と、よりクリーンではあるが最も単純ではないクエリプランが得られます。 私は、清潔さとシンプルさは、クエリプランを評価する上で重要な要因ではないことに注意してくださ

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

私の最後の持ち帰り

ここで私はこの小さな練習からあなたを残したいと思います最後のレッスンがあります。

  1. 決して眼球クエリ時間-これらはすべて私の目に同じ速度でした。 Explain analyzeはあなたの友人です。
  2. 最も理にかなった方法でクエリを記述し、タイミングを実行します。 それがうまくいかない場合は、代替(おそらく結合)
  3. SQLで同じ答えに到達するには複数の方法があります-「正しい」答えは状況に大きく依存します。 結果に影響するいくつかのこと:
    1. データサイズ-データサイズが大きくなるにつれてクエリが”ok”になることがあります
    2. インデックス
    3. ディスク速度
    4. メモリサイズ
    5. プロセッサ速度
    6. クエリを実行する頻度
  4. 最後に、SQLの知識とスキルを向上させるのに費やされた時間は、気前よく報われます。 最も効率的なクエリを記述しなくても、多くの手続き型コードを記述するよりも通常は高速です。 私はますます多くのSQLパターンを学ぶにつれて、私はSQLの数行に置き換えることができるすべてのコードで驚いています(そして、私は通常、巨大なパフォーマ

コメントを残す

メールアドレスが公開されることはありません。