帰ってきた！ 標準はあるにはあるが癖の多いSQL #7 - Hash Joinの実行計画の見せ方にも癖がでる Tweet
本エントリーは、MySQL Advent Calendar 2023 シリーズ2 / Day 10とPostgreSQL Advent Calendar 2023 シリーズ2 / Day 10へのクロスポスト、および、JPOUG Advent Calendar 2023 / Day 10 の裏番組 （ADVENTARはシリーズ増やせないので） エントリーで、12/17日向けの 予習 その３ という位置付けのエントリーです。
予習 その１は、以下。
帰ってきた！ 標準はあるにはあるが癖の多いSQL #5 - Optimizer Hint でも癖が多い
予習 その２は、以下。
帰ってきた！ 標準はあるにはあるが癖の多いSQL #6 - Hash Joinの結合ツリーにも癖がでる
MySQLは、Left Deep Join Treeにしかならない、というところまで理解できたのが前回までの予習でした。
今日は、もう少し、理解を深めておこうと言うことで、MySQLでは無理だけど、 PostgreSQL と Oracle で結合ツリーの種別（Oracleのマニュアルに記載されている分類の範囲）で、Right Deep Join、Left Deep Join、Bushy Joinでは、どのような実行計画として見えるのかを確認しておきましょう。
まず初めに、MySQLはできない Right Deep Join Tree で Hash Join させると、どのような実行計画に見えるか？ の比較から。
Oracle Database 21c
Oraclerなら見慣れたSWAP_JOIN_INPUTSヒント利用の典型的な例です。Id=6でt1(build)がtable full scanされ、ID=7のt2(Probe)と結合されています。t3, t4はそれぞれ、Buildとなっていることが読み取れます。
Oracle Database 21c / Right Deep Join Tree / Hash Join
（実行計画の右端に (Build) なのか、 (Probe) を確認しやすいようにコメントを付加しています）
SCOTT@orclpdb1> @ora_sql_rightdj.sql
1 SELECT
2 /*+
3 MONITOR
4 LEADING(t1 t2 t3 t4)
5 USE_HASH(t1 t2 t3 t4)
6 SWAP_JOIN_INPUTS(t3)
7 SWAP_JOIN_INPUTS(t4)
8 */
9 t1.id
10 , t1.t1_c1
11 , t2.s_id
12 , t2.t2_c1
13 , t3.b_id
14 , t3.t3_c1
15 , t4.a_id
16 , t4.c_id
17 , t4.t4_c1
18 FROM
19 t1
20 INNER JOIN t2
21 ON
22 t1.id = t2.id
23 INNER JOIN t3
24 ON
25 t1.id = t3.id
26 INNER JOIN t4
27 ON
28* t1.id = t4.id
...略...
SQL Plan Monitoring Details (Plan Hash Value=3835853103)
===================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (Max) | (%) | (# samples) |
===================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 2 | +0 | 1 | 25000 | | | . | | |
| 1 | HASH JOIN | | 25000 | 12 | 2 | +0 | 1 | 25000 | | | 1MB | | |
| 2 | TABLE ACCESS FULL | T4 | 500 | 3 | 1 | +0 | 1 | 500 | 2 | 49152 | . | | | (Build)
| 3 | HASH JOIN | | 500 | 9 | 2 | +0 | 1 | 500 | | | 1MB | | | (Probe)
| 4 | TABLE ACCESS FULL | T3 | 100 | 3 | 1 | +0 | 1 | 100 | 2 | 49152 | . | | | (Build)
| 5 | HASH JOIN | | 50 | 6 | 2 | +0 | 1 | 50 | | | 1MB | | | (Probe)
| 6 | TABLE ACCESS FULL | T1 | 10 | 3 | 1 | +0 | 1 | 10 | 2 | 49152 | . | | | (Build)
| 7 | TABLE ACCESS FULL | T2 | 50 | 3 | 2 | +0 | 1 | 50 | 2 | 49152 | . | | | (Probe)
===================================================================================================================================================
PostgreSQL 13.4 / pg_hint_plan 1.3.9 / Right Deep Join Tree / Hash Join
OracleとはBuild表の位置が逆なので、OracleのLeft deep join treeの実行計画に読み間違えそうですがw PostgreSQLのRight Deep Join Treeだとこのような実行計画として表示されます。
Oracleでは、Id=6でT1がBuild表になり、Id=7のt2(Probe)と結合するように表現されますが、
PostgreSQLでは、 ★ で示した行で、t1が、Seq ScansされてHash表が作成されている。つまり、Build表になっており、t2(Probe)と結合されています。Oracleの感覚で読んでしまうと、あれ？ となるところだと思います。
t3, t4から、それぞれ、Hash表が作成されているので、Buildになっていることがわかります:)
同じ実行計画になるようにしてみましたが、実行計画の見せ方では、Oracle/PostgreSQLでは差異がありますよね。ただ、MySQLのTree表示もPostgreSQLに類似しているので、PostgreSQLのに慣れてると、MySQLのTree formatの実行計画は読みやすいかもしれません）
Orableのように、SWAP_JOIN_INPUTSヒントでBuild/Probeを制御するヒントが存在しないので、代わりに、LeadingヒントのLeading(
（実行計画の左端に (Build) なのか、 (Probe) を確認しやすいようにコメントを付加しています）
perftestdb=> \! cat pg_sql_rightdj.sql
explain (analyze)
SELECT
/*+
Leading((((t2 t1) t3) t4))
HashJoin(t1 t2 t3 t4)
*/
t1.id
, t1.t1_c1
, t2.s_id
, t2.t2_c1
, t3.b_id
, t3.t3_c1
, t4.a_id
, t4.c_id
, t4.t4_c1
FROM
t1
INNER JOIN t2
ON
t1.id = t2.id
INNER JOIN t3
ON
t1.id = t3.id
INNER JOIN t4
ON
t1.id = t4.id
;
perftestdb=> \i pg_sql_rightdj.sql
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Hash Join (cost=18.73..308.66 rows=25000 width=36) (actual time=3.352..8.740 rows=25000 loops=1)
Hash Cond: (t1.id = t4.id)
(Probe) -> Hash Join (cost=4.47..11.91 rows=500 width=32) (actual time=2.316..2.460 rows=500 loops=1)
Hash Cond: (t1.id = t3.id)
(Probe) -> Hash Join (cost=1.23..2.91 rows=50 width=20) (actual time=1.534..1.562 rows=50 loops=1)
Hash Cond: (t2.id = t1.id)
(Probe) -> Seq Scan on t2 (cost=0.00..1.50 rows=50 width=12) (actual time=0.744..0.750 rows=50 loops=1)
(Build) -> Hash (cost=1.10..1.10 rows=10 width=8) (actual time=0.723..0.724 rows=10 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
★ -> Seq Scan on t1 (cost=0.00..1.10 rows=10 width=8) (actual time=0.705..0.707 rows=10 loops=1)
(Build) -> Hash (cost=2.00..2.00 rows=100 width=12) (actual time=0.760..0.761 rows=100 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 13kB
-> Seq Scan on t3 (cost=0.00..2.00 rows=100 width=12) (actual time=0.715..0.732 rows=100 loops=1)
(Build) -> Hash (cost=8.00..8.00 rows=500 width=16) (actual time=1.012..1.013 rows=500 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 32kB
-> Seq Scan on t4 (cost=0.00..8.00 rows=500 width=16) (actual time=0.792..0.905 rows=500 loops=1)
MySQL 8.0.32 / Right Deep Join Tree / Hash Join
できないので、なし。
次、みんさんが、見慣れている（？）、Left Deep Join Tree にすると Hash Join の実行計画は、それぞれどのように見えるのでしょうか？ 比較してみましょう。
Oraclerには、わかりやすいですよね(慣れの問題かもしれませんけどもw) 。Id=4で、t1がBuild表となってt2(Probe)と結合。。。以下、t3, t4がそれぞれ、ProbeとしてHash Joinされています。
Oracle 21 / Left Deep Join Tree / Hash Join
Id=4でt1がtable full scan(Build)で、ID=5 のt2 (Probe) と結合されています。t3, t4は それぞれのProbeとなっていることが読み取れます。;)
（実行計画の右端に (Build) なのか、 (Probe) を確認しやすいようにコメントを付加しています）
SCOTT@orclpdb1> @ora_sql_leftdj.sql
1 SELECT
2 /*+
3 MONITOR
4 LEADING(t1 t2 t3 t4)
5 USE_HASH(t1 t2 t3 t4)
6 NO_SWAP_JOIN_INPUTS(t4)
7 */
8 t1.id
9 , t1.t1_c1
10 , t2.s_id
11 , t2.t2_c1
12 , t3.b_id
13 , t3.t3_c1
14 , t4.a_id
15 , t4.c_id
16 , t4.t4_c1
17 FROM
18 t1
19 INNER JOIN t2
20 ON
21 t1.id = t2.idv 22 INNER JOIN t3
23 ON
24 t1.id = t3.id
25 INNER JOIN t4
26 ON
27* t1.id = t4.id
...略...
SQL Plan Monitoring Details (Plan Hash Value=894925296)
====================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (Max) | (%) | (# samples) |
====================================================================================================================================
| 0 | SELECT STATEMENT | | | | 2 | +0 | 1 | 25000 | . | | |
| 1 | HASH JOIN | | 25000 | 12 | 2 | +0 | 1 | 25000 | 1MB | | |
| 2 | HASH JOIN | | 500 | 9 | 1 | +0 | 1 | 500 | 1MB | | | (Build)
| 3 | HASH JOIN | | 50 | 6 | 1 | +0 | 1 | 50 | 1MB | | | (Build)
| 4 | TABLE ACCESS FULL | T1 | 10 | 3 | 1 | +0 | 1 | 10 | . | | | (Build)
| 5 | TABLE ACCESS FULL | T2 | 50 | 3 | 1 | +0 | 1 | 50 | . | | | (Probe)
| 6 | TABLE ACCESS FULL | T3 | 100 | 3 | 1 | +0 | 1 | 100 | . | | | (Probe)
| 7 | TABLE ACCESS FULL | T4 | 500 | 3 | 2 | +0 | 1 | 500 | . | | | (Probe)
====================================================================================================================================
PostgreSQL 13.4 / pg_hint_plan 1.3.9 / Left Deep Join Tree / Hash Join
★ のある行で、t1がSeq Scanされ、その上位の行で、Hashが作成されているので、Buildとなっていることがわかります。次に、同一階層で t2がSeq ScanされHash Joinされています。
t3, t4はそれぞれProbeになっていることが読み取れます。ぱっと見は、Oracleの ight Deep Join Tree で見られる実行計画の形に似てますが。。。(^^;;;;
（実行計画の左端に (Build) なのか、 (Probe) を確認しやすいようにコメントを付加しています）
perftestdb=> \! cat pg_sql_leftdj.sql
explain (analyze)
SELECT
/*+
Leading((t4 (t3 (t2 t1))))
HashJoin(t2 t1 t3 t4)
*/
t1.id
, t1.t1_c1
, t2.s_id
, t2.t2_c1
, t3.b_id
, t3.t3_c1
, t4.a_id
, t4.c_id
, t4.t4_c1
FROM
t1
INNER JOIN t2
ON
t1.id = t2.id
INNER JOIN t3
ON
t1.id = t3.id
INNER JOIN t4
ON
t1.id = t4.id
;
perftestdb=>
perftestdb=> \i pg_sql_leftdj.sql
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=17.66..308.16 rows=25000 width=36) (actual time=0.522..8.362 rows=25000 loops=1)
Hash Cond: (t4.id = t1.id)
(Probe) -> Seq Scan on t4 (cost=0.00..8.00 rows=500 width=16) (actual time=0.012..0.101 rows=500 loops=1)
(Build) -> Hash (cost=11.41..11.41 rows=500 width=32) (actual time=0.462..0.467 rows=500 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 40kB
-> Hash Join (cost=3.54..11.41 rows=500 width=32) (actual time=0.122..0.328 rows=500 loops=1)
Hash Cond: (t3.id = t1.id)
(Probe) -> Seq Scan on t3 (cost=0.00..2.00 rows=100 width=12) (actual time=0.004..0.017 rows=100 loops=1)
(Build) -> Hash (cost=2.91..2.91 rows=50 width=20) (actual time=0.098..0.102 rows=50 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 11kB
-> Hash Join (cost=1.23..2.91 rows=50 width=20) (actual time=0.039..0.068 rows=50 loops=1)
Hash Cond: (t2.id = t1.id)
(Probe) -> Seq Scan on t2 (cost=0.00..1.50 rows=50 width=12) (actual time=0.005..0.011 rows=50 loops=1)
(Build) -> Hash (cost=1.10..1.10 rows=10 width=8) (actual time=0.016..0.018 rows=10 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
★ -> Seq Scan on t1 (cost=0.00..1.10 rows=10 width=8) (actual time=0.003..0.004 rows=10 loops=1)
MySQL 8.0.32 / Left Deep Join Tree / Hash Join
これは、前回のエントリーでも紹介した無理やり Hash Join にした例なので、特に解説はしませんが、PostgreSQLに類似した実行計画になっていますよね。
現状、MySQLは、HASH JOIN を強制するヒント、Build/Probeを制御するヒント、PostgreSQLのように結合順の優先順位を細かく指定するヒントもないため、Nested Loop Join にならないようヒントで制御するしか Hash Join を強制する方法はなさそうです。
Left Deep Join Tree にしかならないのでこともあり、Hash Join で、 Bushy Join Treeや、Right Deep Join Treeのようなチューニングはできないですね。
実際、関わった案件で、Hash Join が選択されたのは良いと思うけど、Build/Probeを入れ替えられたら、もっとサクッと終わるはずなのに、と言う状況になって初めて、この事実を知りました。（なので、このエントリー書いているのですけどもw）
できないのかよ。。とw
以下実行計画の ★ を付与した行で、t1がTable Scanされ、その上位の行で、Hashが作成されているので、Buildとなっていることがわかります。次に、同一階層で t2がTable Scan後に、Hash Joinされています。
t3, t4は、Probeになっていることも読み取れます。PostgreSQLの実行計画の表示方法に類似してますよね。
（実行計画の左端に (Build) なのか、 (Probe) を確認しやすいようにコメントを付加しています）
mysql> \! cat my_sql_leftdj.sql
explain analyze
SELECT
/*+
JOIN_ORDER(t1,t2,t3,t4)
NO_JOIN_INDEX(t2 primary)
NO_JOIN_INDEX(t3 primary)
NO_JOIN_INDEX(t4 primary)
*/
t1.id
, t1.t1_c1
, t2.s_id
, t2.t2_c1
, t3.b_id
, t3.t3_c1
, t4.a_id
, t4.c_id
, t4.t4_c1
FROM
t1
INNER JOIN t2
ON
t1.id = t2.id
INNER JOIN t3
ON
t1.id = t3.id
INNER JOIN t4
ON
t1.id = t4.id
;
mysql> \. my_sql_leftdj.sql
+-----------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+-----------------------------------------------------------------------------------------------------------------------+
| -> Inner hash join (t4.id = t1.id) (cost=25552.55 rows=25000) (actual time=0.643..5.045 rows=25000 loops=1)
(Probe) -> Table scan on t4 (cost=0.01 rows=500) (actual time=0.052..0.346 rows=500 loops=1)
(Build) -> Hash
-> Inner hash join (t3.id = t1.id) (cost=551.75 rows=500) (actual time=0.216..0.380 rows=500 loops=1)
(Probe) -> Table scan on t3 (cost=0.03 rows=100) (actual time=0.041..0.069 rows=100 loops=1)
(Build) -> Hash
-> Inner hash join (t2.id = t1.id) (cost=51.50 rows=50) (actual time=0.100..0.141 rows=50 loops=1)
(Probe) -> Table scan on t2 (cost=0.08 rows=50) (actual time=0.026..0.041 rows=50 loops=1)
(Build) -> Hash
★ -> Table scan on t1 (cost=1.25 rows=10) (actual time=0.039..0.047 rows=10 loops=1)
|
+-----------------------------------------------------------------------------------------------------------------------+
と言うことで、
Advent Calendar 2023 / Day 17向けの準備運動的な予習（その３）はここまで。
では、また。
関連エントリー
・標準はあるにはあるが癖の多いSQL 全部俺 #1 Pagination
・標準はあるにはあるが癖の多いSQL 全部俺 #2 関数名は同じでも引数が逆の罠！
・標準はあるにはあるが癖の多いSQL 全部俺 #3 データ型確認したい時あるんです
・標準はあるにはあるが癖の多いSQL 全部俺 #4 リテラル値での除算の内部精度も違うのよ!
・標準はあるにはあるが癖の多いSQL 全部俺 #5 和暦変換機能ある方が少数派
・標準はあるにはあるが癖の多いSQL 全部俺 #6 時間厳守！
・標準はあるにはあるが癖の多いSQL 全部俺 #7 期間リテラル！
・標準はあるにはあるが癖の多いSQL 全部俺 #8 翌月末日って何日？
・標準はあるにはあるが癖の多いSQL 全部俺 #9 部分文字列の扱いでも癖が出る＞＜
・標準はあるにはあるが癖の多いSQL 全部俺 #10 文字列連結の罠（有名なやつ）
・標準はあるにはあるが癖の多いSQL 全部俺 #11 デュエル、じゃなくて、デュアル
・標準はあるにはあるが癖の多いSQL 全部俺 #12 文字[列]探すにも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺 #13 あると便利ですが意外となかったり
・標準はあるにはあるが癖の多いSQL 全部俺 #14 連番の集合を返すにも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺 #15 SQL command line client
・標準はあるにはあるが癖の多いSQL 全部俺 #16 SQLのレントゲンを撮る方法
・標準はあるにはあるが癖の多いSQL 全部俺 #17 その空白は許されないのか？
・標準はあるにはあるが癖の多いSQL 全部俺 #18 (+)の外部結合は方言
・標準はあるにはあるが癖の多いSQL 全部俺 #19 帰ってきた、部分文字列の扱いでも癖w
・標準はあるにはあるが癖の多いSQL 全部俺 #20 結果セットを単一列に連結するにも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺 #21 演算結果にも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺 #22 集合演算にも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺 #23 複数行INSERTにも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺 #24 乱数作るにも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺 #25 SQL de Fractalsにも癖がある:)
・標準はあるにはあるが癖の多いSQL 全部俺 おまけ SQL de 湯婆婆やるにも癖がでるw
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #1 SQL de ROT13 やるにも癖が出るw・帰ってきた！ 標準はあるにはあるが癖の多いSQL #2 Actual Plan取得中のキャンセルでも癖が出る・帰ってきた！ 標準はあるにはあるが癖の多いSQL #3 オプティマイザの結合順評価テーブル数上限にも癖が出る
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #4 Optimizer Traceの取得でも癖がでる
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #5 - Optimizer Hint でも癖が多い
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #6 - Hash Joinの結合ツリーにも癖がでる
| 固定リンク | 0
コメント