SQLでなんかしらの条件でレコードを絞り込むときのやり方には、複数のやり方がありますが、どれが一番効率が良いのか調べてみました🤔
前提条件
今回はpostgresqlで調べていますバージョン等の情報は下記の通りです。
$ psql --version psql (PostgreSQL) 12.1
また対象となるERは下記のようなものです。
テーブルの細かい名前は置いといて、feedsというテーブルとfeedsとboardsの中間テーブルです。(feedsとboard_feedsの関係は1:Nです)
ここからboardsが紐づくfeedsのみを取得する際にIN、EXISTS、INNER JOINどれを使うのがパフォーマンスが良さそうなのか、実行計画を見ながら考えてみます。
取得結果のサンプルは下記のような感じです。
id | title 70 | MadogiwaBlog 73 | Technology of DeNA 74 | Engineering The GitHub Blog 78 | さくらのナレッジ
実行計画を見てみる
IN
まずはINを見てみます、実行計画を見てみると下記のような流れで実行されているようです👀
Seq Scan on board_feeds->board_feedsを全件読み込むHashAggregate Group Key: board_feeds.feed_id->board_feeds.feed_idで結果セットをGroup化Sort Sort Key: board_feeds.feed_id-> 結果セットをboard_feeds.feed_idでソートSeq Scan on feeds->feedsを全件読み込むHash Join Hash Cond: (feeds.id = board_feeds.feed_id)->feedsとboard_feedsをHash結合Sort Sort Key: feeds.id->feeds.idで最終的な結果セットをソート
オプティマイザが導き出した最終的なコストは3.10..3.13でした。
-- IN Sort (cost=3.10..3.13 rows=11 width=23) Sort Key: feeds.id -> Hash Join (cost=1.60..2.91 rows=11 width=23) Hash Cond: (feeds.id = board_feeds.feed_id) -> Seq Scan on feeds (cost=0.00..1.15 rows=15 width=23) -> Hash (cost=1.46..1.46 rows=11 width=8) -> HashAggregate (cost=1.35..1.46 rows=11 width=8) Group Key: board_feeds.feed_id -> Seq Scan on board_feeds (cost=0.00..1.28 rows=28 width=8)
EXISTS
次にEXISTSを見てみます、実行計画を見てみると下記のようなINと同じ流れで実行されているようです👀
Seq Scan on board_feeds->board_feedsを全件読み込むHashAggregate Group Key: board_feeds.feed_id->board_feeds.feed_idで結果セットをGroup化Sort Sort Key: board_feeds.feed_id-> 結果セットをboard_feeds.feed_idでソートSeq Scan on feeds->feedsを全件読み込むHash Join Hash Cond: (feeds.id = board_feeds.feed_id)->feedsとboard_feedsをHash結合Sort Sort Key: feeds.id->feeds.idで最終的な結果セットをソート
オプティマイザが導き出した最終的なコストは当然ですがINと同じ3.10..3.13でした。
-- EXISTS $ EXPLAIN SELECT feeds.id,feeds.title FROM feeds WHERE id IN (SELECT feed_id FROM board_feeds) ORDER BY id; Sort (cost=3.10..3.13 rows=11 width=23) Sort Key: feeds.id -> Hash Join (cost=1.60..2.91 rows=11 width=23) Hash Cond: (feeds.id = board_feeds.feed_id) -> Seq Scan on feeds (cost=0.00..1.15 rows=15 width=23) -> Hash (cost=1.46..1.46 rows=11 width=8) -> HashAggregate (cost=1.35..1.46 rows=11 width=8) Group Key: board_feeds.feed_id -> Seq Scan on board_feeds (cost=0.00..1.28 rows=28 width=8)
EXISTSとINだと実行計画が一緒なんですね、EXISTSの半結合(Semi Join)になって早くなると思ってたのですが、どうやらIN句とパフォーマンスは特に変わらないようですね🤔
INNER JOIN
最後にINNER JOINを見てみます、実行計画を見てみると下記のような流れで実行されているようです。
Seq Scan on feeds->feedsを全件読み込むHash->feedsの全件のハッシュテーブルを作成Seq Scan on board_feeds->board_feedsを全件読み込むHash Join Hash Cond->feedsとboard_feedsをHash結合HashAggregate Group Key: feeds.id->feeds.idで結果セットをグループ化Sort Sort Key: feeds.id->feeds.idで最終的な結果セットをソート
-- INNER JOIN $ EXPLAIN SELECT feeds.id, feeds.title FROM feeds INNER JOIN board_feeds ON feeds.id = board_feeds.feed_id GROUP BY feeds.id, feeds.title ORDER BY id; Sort (cost=3.23..3.26 rows=15 width=23) Sort Key: feeds.id -> HashAggregate (cost=2.78..2.93 rows=15 width=23) Group Key: feeds.id -> Hash Join (cost=1.34..2.71 rows=28 width=23) Hash Cond: (board_feeds.feed_id = feeds.id) -> Seq Scan on board_feeds (cost=0.00..1.28 rows=28 width=8) -> Hash (cost=1.15..1.15 rows=15 width=23) -> Seq Scan on feeds (cost=0.00..1.15 rows=15 width=23)
INNER JOINの場合はGROUP BY feeds.id, feeds.titleの処理(PHashAggregate Group Key: feeds.id`)が必要となるので、他よりもコストが高いようですね(・・;)
しかし、実際に対象レコードを絞り込むまで(Hash Join (cost=1.34..2.71 rows=28 width=23))までのコストは一番低いので、1:1のTBLの存在確認をしてレコードを絞り込む場合には一番効率が良さそう🤔
結果
| 絞り込み方 | コスト | メモ |
|---|---|---|
| IN | 2.45..2.53 | |
| EXISTS | 2.45..2.53 | INと実行計画が同じ |
| INNER JOIN | 3.23..3.26 | 一意の処理を除くとコストが一番低い(1.34..2.71 ) |
結果を見るとオプティマイザの評価としては、INとEXISTSにはパフォーマンス的に差がなくINNER JOINはコストが高いという結果でした👀
しかし、今回は1:NのTABLEの存在チェックだったので一意にする処理が必要なケースでしたが、一意にする必要がない1:1のTABLEの存在チェックの場合はINNER JOINが一番コストが低そうです！
※実行計画はDBMSによって違うので、今回の結果はMySQL等では違うかもしれません。
