Have a happy new year! Tweet
2023年12月28日 (木)
2023年12月24日 (日)
12月リリースのDTM / N + 1 Loops Tweet
多分、2023年最後の投稿w
少々早めですが、12月リリースのDTM
ここのところアジャイルDTMでリリースした曲のメーじゃバージョンアップが多いわけですが、今月はAdvent Calendarなどもありw
この一曲だけ:)
XYZ Loops をVer.1 -> Ver.2 にメジャーアップデートですw
Ver.1 はシンセ中心でしたが、なんか物足りんと、やはりリズムセクション含めファンキーな方向に変えて、本年の締めとさせていただきます:)
XYZ Loops Ver.2 / N + 1 Loops
ちなみに、Ver.1 はこんなでしたw かなり変えてます
このVer.1 からベースとか一旦なしにして、どう雰囲気変えるかを延々と一ヶ月ぐらい試行錯誤してました:)
で、昨日やっと。。ドラムとかベースと追加したギターもなんとなく、迷走から抜け出した感じで。と。
（GarageBand プロジェクトの動画です）
では、メリークリスマス & 良いお年をお迎えください。
2023年12月22日 (金)
PostgreSQLのexplain analyze中にCTRL+Cでキャンセルすると、途中まで実行されているActual Planの結果は出力されないわけだが、その挙動を、ほんの少し追ってみた件 Tweet
本エントリーは、PostgreSQL Advent Calendar 2023 シリーズ2の Day 22向けエントリーです
今回は、PostgreSQLオンリーで、投げっぱなしだったやつをちょっと掘り下げた時のメモ的なエントリーです。。
以前、
・MySQL 8.0.32 / explain analyze 実行途中でキャンセルできるみたいだけど、キャンセルしたら、Actual Plan、途中まで出るの？
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #2 Actual Plan取得中のキャンセルでも癖が出る
というエントリーを書いたのですが、
explain (analyze) 実行中、CTRL+C した時、どのようにハンドングしてるんだろう？
Hookで拾って、extensionで、explain (analyze) でも途中までは結果を書き出せるようにできちゃったりするのだろうか？ と。
気になりすぎてしまったのでw、該当ソースコードを追い、象が生息する森の奥地に入ってみることにした。。。。
https://github.com/postgres/postgres
ただ、あてもないのに森林の奥地入り込むのは、迷子になる危険を感じたのでw、ちょっとだけ、生態を調べてみたw
すると、幸運なことに、先人が足を踏み入れた痕跡と思われるメモ見つけることができた。
PostgreSQLのシグナル周り
https://qiita.com/KazuyaTomita/items/6feb708d73190032dfed
ふむふむ、なんとなくわかった気持ちになる。。（理解したとは言ってない）
PostgreSQLのActual Plan取得のログ。
見ての通り、PostgreSQLは、explain (analyze)実行中にCTRL+Cでキャンセルすると、シンプルなキャンセルメッセージを返すだけ。
（MySQL/Oracleでは、キャンセルされるまでのActual Planを返してくれる。冒頭のリンク参照）
この表示されているメッセージは手がかりになりそうだなぁ。。。。
perftestdb=> explain (analyze, buffers, verbose)
WITH RECURSIVE gen_nums(v)
AS
(
SELECT 1
UNION ALL
SELECT v + 1
FROM
gen_nums
WHERE v + 1 <= 100000000
)
SELECT v from gen_nums;
^Cキャンセル要求を送信しました
ERROR: canceling statement due to user request
perftestdb=>
シグナル周りのハンドリング部分のコードを追って、このメッセージを出力しているところを特定すると良さげな気がしますよね。
ただ、explain を実行している箇所とは違いそうではある。。。ざっくり目だが。
postgres.cのPostgresMain()に以下の文がありますな〜
postgres.c
pqsignal(SIGINT, StatementCancelHandler); /* cancel current query */
そして、至る所で、 CHECK_FOR_INTERRUPTS ()マクロが呼ばれてます〜〜。
CHECK_FOR_INTERRUPTS()マクロで、呼ばれているのが、 ProcessInterrupts() ですね。
miscadmin.h
/* Service interrupt, if one is pending and it's safe to service it now */
#define CHECK_FOR_INTERRUPTS() \
do { \
if (INTERRUPTS_PENDING_CONDITION()) \
ProcessInterrupts(); \
} while(0)
ProcessInterrupts()をみると、以下に見覚えのあるエラーメッセージ "canceling statement due to user request"を出力している箇所を発見。
postgres.c
/*
* If we are reading a command from the client, just ignore the cancel
* request --- sending an extra error message won't accomplish
* anything. Otherwise, go ahead and throw the error.
*/
if (!DoingCommandRead)
{
LockErrorCleanup();
ereport(ERROR,
(errcode(ERRCODE_QUERY_CANCELED),
errmsg("canceling statement due to user request")));
}
explain (analyze)に関わりそうな以下のコードでは、CHECK_FOR_INTERRUPTSはないのだけど、executer配下のコードには沢山ある。。。
explain.c
execMain.c
... extensionでなんとか、、というのは雰囲気的に難しそうな。。。
explain (actual plan取得含む）実行中に CTRL+C キャンセルすると、
postgres.c の PostgresMain() -> CHECK_FOR_INTERRUPTS() -> ProcessInterrupts() -> ereport() -> errmsg("canceling statement due to user request") を出力して実行キャンセル
と言う流れっぽい。斜め読みした限りでは。
ぱっと見だが、
errmsg("canceling statement due to user request")の前に、
1. commandが実行されているか
2. 1.であれば、commandは、explain かつ analyzeか？
3. 2.であれば、explain analyzeのキャンセルされる前に取得されたActual Planの結果を出力
みたいなことを、ProcessInterrupts()内でやらないと難しそうだよなぁ〜と。extensionでやれそうな感じにも見えないし。。。
一旦、そっとGitHubを開いていたブラウザのタブを閉じたww
と言うことで、冬の夜長に、PostgreSQLのコードをちょっと追ってみた話はここまで。
PostgreSQL Advent Calendar 2023 ですがw 恒例？の、Oracle Pipelined Table Function で Christmas ASCII Art(クロスポストに続き2回目）
関連エントリー
・MySQL 8.0.32 / explain analyze 実行途中でキャンセルできるみたいだけど、キャンセルしたら、Actual Plan、途中まで出るの？
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #2 Actual Plan取得中のキャンセルでも癖が出る
2023年12月17日 (日)
帰ってきた！ 標準はあるにはあるが癖の多いSQL #8、Hash Joinさせるにも癖が出る Tweet
本エントリーは、
MySQL Advent Calendar 2023 シリーズ1
PostgreSQL Advent Calendar 2023 シリーズ1
JPOUG Advent Calendar 2023
の Day 17 向けエントリーです。
また、本エントリー向け予習エントリーを投稿していますので、一読していただくと私が何と戦っていたのかw 理解しやすいのではないかと思います
予習 その１は、以下。
帰ってきた！ 標準はあるにはあるが癖の多いSQL #5 - Optimizer Hint でも癖が多い
予習 その２は、以下。
帰ってきた！ 標準はあるにはあるが癖の多いSQL #6 - Hash Joinの結合ツリーにも癖がでる
予習 その３は、以下。
帰ってきた！ 標準はあるにはあるが癖の多いSQL #7 - Hash Joinの実行計画の見せ方にも癖がでる
ということで、これまでの予習で学んだことを利用して、もしも、こんなデータ（予習 その２ を参照のこと）で、以下のようなSQLがあった場合、Hash Joinの特性上いい感じの実行計画にさせるにはどうHintingするのが良いのか考えてみたいと思います。
（答えが見えている方は多いとは思いますけどもw）
以下のSQL文を使い、後述のような実行計画に制御することを目指してみます。
SELECT
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
, t0.m_id
, t0.t0_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
INNER JOIN t0
ON
t1.id = t0.id
;
いざというときに慌てずに、どうするかを判断、チューニングできるようになっておくと良いですよねー。
そう言う、いざ、という場面には付き合わされない人も、雑学的になんとなーーーく理解しているだけでも良いのではないかと思います。
Oracleの実行計画風ですが、こんな実行計画にオプティマイザーヒントだけで制御してみたいなぁ。というのがお題。右側にコメントしているBuild/Probeの関係を覚えておいてください。この形にするのが目的です。
（予習に目を通した方は、あ”〜っ。と何かに気づいちゃったと思いますけどもw）
ポイントになるのは、t0の結合です。 t1,t2,t3,t4と結合した結果が、t0の行数より多くなります。（そうなるようにデータを用意したので）Hash Joinを想定しているので、最後にt0を結合する場合には、t0をBuildにしたいですよね。。。
（Hash Joinの結合順で理想なのは、常にBuildの方が小さくなる結合順なので、以下の形が理想的だとのがわかりやすいようにJoin cardinarityも記載してあります）
HJ (join card = 2,500,000)
-> t0 (rows = 1,000) -- Build
-> HJ (join card = 250,000) -- Probe
-> HJ (join card = 500) -- Build
-> HJ (join card = 50) -- Build
-> t1 (rows = 10) -- Build
-> t2 (rows = 50) -- Probe
-> t3 (rows = 100) -- Probe
-> t4 (rows = 500) -- Probe
なお、これまでの予習で、MySQLは、Hash Join はできるけど、Build/Probeの制御ができない。 Left Deep Join Treeにしかならないのもどうしようもない。
オプティマイザヒントでは無理だろうと想像はできるわけですがw 折角なので、できるところまで試して、挙動をみておきましょう:)
再掲
テーブル定義とデータ量は以下、予習 その２ 参照のこと。
帰ってきた！ 標準はあるにはあるが癖の多いSQL #6 - Hash Joinの結合ツリーにも癖がでる
まず、Oracleからですかねぇ。やはり。豊富なオプティマイザヒントと最適化が行えることが強みの一つだと思います。
Oracle Database 21c
ちょっと暴れてたのでw NO_SWAP_JOIN_INPUTSを使ってますが、実行計画としては狙ったところに持っていけますよね。Oraclerの方なら、ふむふむ。というところだと思います。
t1, t2, t3, t4, t0の順で結合しますが、最後の、t0の結合時は、Buildを t0 にしてね！ というHinging。実践でも結構使う場面はありますよね。
(実行計画の右側に確認しやすくするため、どちらが (Build) / (Probe) なのかをコメントしてあります）
SCOTT@ORCLCDB> select banner_full from v$version;
BANNER_FULL
-----------------------------------------------------------------------
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
SCOTT@orclpdb1> @advent_sql_ora
1 SELECT
2 /*+
3 MONITOR
4 LEADING(t1 t2 t3 t4 t0)
5 USE_HASH(t1 t2 t3 t4 t0)
6 NO_SWAP_JOIN_INPUTS(t4)
7 SWAP_JOIN_INPUTS(t0)
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 , t0.m_id
19 , t0.t0_c1
20 FROM
21 t1
22 INNER JOIN t2
23 ON
24 t1.id = t2.id
25 INNER JOIN t3
26 ON
27 t1.id = t3.id
28 INNER JOIN t4
29 ON
30 t1.id = t4.id
31 INNER JOIN t0
32 ON
33* t1.id = t0.id
...略...
SQL Plan Monitoring Details (Plan Hash Value=3728371915)
====================================================================================================================================================
| 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 | | | | 106 | +0 | 1 | 3M | | | . | | |
| 1 | HASH JOIN | | 3M | 29 | 106 | +0 | 1 | 3M | | | 1MB | | |
| 2 | TABLE ACCESS FULL | T0 | 1000 | 3 | 1 | +0 | 1 | 1000 | 2 | 49152 | . | | | (Build)
| 3 | HASH JOIN | | 25000 | 12 | 106 | +0 | 1 | 25000 | | | 1MB | | | (Probe)
| 4 | HASH JOIN | | 500 | 9 | 1 | +0 | 1 | 500 | | | 1MB | | | (Build)
| 5 | HASH JOIN | | 50 | 6 | 1 | +0 | 1 | 50 | | | 1MB | | | (Build)
| 6 | TABLE ACCESS FULL | T1 | 10 | 3 | 1 | +0 | 1 | 10 | 2 | 49152 | . | | | (Build)
| 7 | TABLE ACCESS FULL | T2 | 50 | 3 | 1 | +0 | 1 | 50 | 2 | 49152 | . | | | (Probe)
| 8 | TABLE ACCESS FULL | T3 | 100 | 3 | 1 | +0 | 1 | 100 | 2 | 49152 | . | | | (Probe)
| 9 | TABLE ACCESS FULL | T4 | 500 | 3 | 106 | +0 | 1 | 500 | 3 | 81920 | . | | | (Probe)
====================================================================================================================================================
簡単に解説すると、t1からt4まではLeft Deep Join Tree、t0は、術式反転w で Right Deep Join Treeになるように、SWAP_JOIN_INPUTSしてます。
Id=5で、Id=6のt1（Build）と、Id=7のt2（Probe）をHash Join
Id=4で、Id=5の結果（Build）と、Id=8のt3（Probe）をHash Join
Id=3で、Id=4の結果（Build）と、Id=9のt4（Probe）をHash Join
Id=2で、SWAP_JOIN_INPUTSヒントで入れ替えた、Id=2のt0(Build)と、Id=3の結果（Probe）をHash Join
狙った実行計画に制御できました。ニッコリ。
参考）使ったSQLスクリプトは以下の通り。
SCOTT@ORCLCDB> !cat show_realtime_sqlplan.sql
set linesize 1000
set long 1000000
set longchunksize 1000000
select dbms_sqltune.report_sql_monitor(sql_id=>'', type=>'text') from dual;
SCOTT@ORCLCDB> !cat advent_sql_ora.sql
SELECT
/*+
MONITOR
LEADING(t1 t2 t3 t4 t0)
USE_HASH(t1 t2 t3 t4 t0)
NO_SWAP_JOIN_INPUTS(t4)
SWAP_JOIN_INPUTS(t0)
*/
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
, t0.m_id
, t0.t0_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
INNER JOIN t0
ON
t1.id = t0.id
.
l
set tab off
set termout off
/
set termout on
@show_realtime_sqlplan
PostgreSQL 13.4 with pg_hint_plan 1.3.9
なんで、最新のPostgreSQLじゃないのか？ 単にアップデートサボってるだなので、気にししないでw
perftestdb=# select version();
version
--------------------------------------------------------------------------------------------------------
PostgreSQL 13.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit
perftestdb=# select * from pg_extension where extname='pg_hint_plan';
oid | extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
--------+--------------+----------+--------------+----------------+------------+-----------------+--------------
131364 | pg_hint_plan | 10 | 131363 | f | 1.3.9 | {131367,131365} | {"",""}
perftestdb=> show shared_preload_libraries;
shared_preload_libraries
-----------------------------------------------
pg_hint_plan
pg_hint_planのLeadingヒントの使い方慣れるまで結構迷子になってましたw。空気感でなんとなーく使い方を理解できたかなぁ。という感じ。
pg_hint_planのLeading(pair)の書き方と、順序の指定順って、Oraclerの感覚的には逆なんですよね。Hash Joinの時だけは。。。というところ気づきました？ みなさん。（主に、Oracle方面の）
perftestdb=> \! cat advent_sql_pg.sql
explain (analyze)
SELECT
/*+
Leading(((t4 (t3 (t2 t1))) t0))
HashJoin(t0 t1 t2 t3 t4)
SeqScan(t0)
SeqScan(t1)
SeqScan(t2)
SeqScan(t3)
SeqScan(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
, t0.m_id
, t0.t0_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
INNER JOIN t0
ON
t1.id = t0.id
;
perftestdb=> \i advent_sql_pg.sql
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Id=1 Hash Join (cost=46.66..28524.66 rows=2500000 width=44) (actual time=0.875..574.957 rows=2500000 loops=1)
Hash Cond: (t1.id = t0.id)
Id=2 (Probe) -> Hash Join (cost=18.16..308.66 rows=25000 width=48) (actual time=0.429..11.123 rows=25000 loops=1)
Hash Cond: (t4.id = t1.id)
Id=3 (Probe) -> Seq Scan on t4 (cost=0.00..8.00 rows=500 width=16) (actual time=0.006..0.776 rows=500 loops=1)
Id=4 (Build) -> Hash (cost=11.91..11.91 rows=500 width=32) (actual time=0.418..0.423 rows=500 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 40kB
Id=5 -> Hash Join (cost=4.04..11.91 rows=500 width=32) (actual time=0.072..0.270 rows=500 loops=1)
Hash Cond: (t3.id = t1.id)
Id=6 (Probe) -> Seq Scan on t3 (cost=0.00..2.00 rows=100 width=12) (actual time=0.004..0.016 rows=100 loops=1)
Id=7 (Build) -> Hash (cost=3.41..3.41 rows=50 width=20) (actual time=0.064..0.067 rows=50 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 11kB
Id=8 -> Hash Join (cost=1.23..3.41 rows=50 width=20) (actual time=0.018..0.053 rows=50 loops=1)
Hash Cond: (t2.id = t1.id)
Id=9 (Probe) -> Seq Scan on t2 (cost=0.00..1.50 rows=50 width=12) (actual time=0.004..0.011 rows=50 loops=1)
Id=10 (Build) -> Hash (cost=1.10..1.10 rows=10 width=8) (actual time=0.008..0.009 rows=10 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Id=11 -> Seq Scan on t1 (cost=0.00..1.10 rows=10 width=8) (actual time=0.003..0.005 rows=10 loops=1)
Id=12 (Build) -> Hash (cost=16.00..16.00 rows=1000 width=12) (actual time=0.437..0.437 rows=1000 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 51kB
Id=13 -> Seq Scan on t0 (cost=0.00..16.00 rows=1000 width=12) (actual time=0.022..0.219 rows=1000 loops=1)
解説のために、Id=noと、Build/Probeを見やすくするためにコメント (Build) / (Probe) を追加しています
Oracleと同じように制御できているか、確認しましょう。
Id=8で、Id=11のt1をSeq ScanしてId=10でハッシュ表作成（Build）と、Id=9のt2をSec Scan（Probe）をHash Join
Id=5で、Id=8の結果を元に、Id=7でハッシュ表作成（Build）と、Id=6のt3 Seq Scan（Probe）をHash Join
Id=2で、Id=5の結果を元に、Id=4でハッシュ表作成（Build）と、Id=3のt4 Seq Scan（Probe）をHash Join
Id=1で、Id=13のt0をSeq ScanしてId=12でハッシュ表作成（Build）と、Id=2の結果（Probe）をHash Join
狙い通りの実行計画になっています！、Yahooooo!!
今一度、Oracleの実行計画と比較してみると、Hash JoinのBuild/Probeの表現が逆になっていることがよくわかりますよね。（前回の予習の通りです）
=======================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (Max) |
=======================================================================================================================
| 0 | SELECT STATEMENT | | | | 106 | +0 | 1 | 3M | | | . |
| 1 | HASH JOIN | | 3M | 29 | 106 | +0 | 1 | 3M | | | 1MB |
(Build) | 2 | TABLE ACCESS FULL | T0 | 1000 | 3 | 1 | +0 | 1 | 1000 | 2 | 49152 | . |
(Probe) | 3 | HASH JOIN | | 25000 | 12 | 106 | +0 | 1 | 25000 | | | 1MB |
(Build) | 4 | HASH JOIN | | 500 | 9 | 1 | +0 | 1 | 500 | | | 1MB |
(Build) | 5 | HASH JOIN | | 50 | 6 | 1 | +0 | 1 | 50 | | | 1MB |
(Build) | 6 | TABLE ACCESS FULL | T1 | 10 | 3 | 1 | +0 | 1 | 10 | 2 | 49152 | . |
(Probe) | 7 | TABLE ACCESS FULL | T2 | 50 | 3 | 1 | +0 | 1 | 50 | 2 | 49152 | . |
(Probe) | 8 | TABLE ACCESS FULL | T3 | 100 | 3 | 1 | +0 | 1 | 100 | 2 | 49152 | . |
(Probe) | 9 | TABLE ACCESS FULL | T4 | 500 | 3 | 106 | +0 | 1 | 500 | 3 | 81920 | . |
=======================================================================================================================
最後に、
MySQL 8.0.32
これも現時点のリリースよりちょっと前のですが、こちらの都合なので、気にしないでくださいw
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.32 |
+-----------+
1 row in set (0.01 sec)
mysql>
無理やり、Hash Joinにはしていますが、PostgreSQLのpg_hint_planのように結合優先順位をペアで制御する方法も、Oracleのように、SWAP_JOIN_INPUTSヒントを使うこともできません。Left Deep Join Treeにしかならないということなので、それが理由ではありますが。とりあえず、こんな感じにしかなりません。JOIN_ORDERヒントの結合順は、OracleのLEADINGヒントに近い考え方で支えているのがわかります。要するに、Buhy Join Treeが可能であれば、これは解決できるわけですが、それは無理なので、t0をBuildにすることができず、これが限界といことになりますよね。（ちょっと思いついたことがあるので、このあと、もうひとあがきしてみますがw）
あと、ヒントで面白いのは、主キー索引を利用させないためのヒント。primaryって指定が必要なんので覚えておくと便利ですよね。
マニュアルにも記載されていますが、インデックスヒントが非推奨になる前に、オプティマイザヒントに慣れておいたほうがよさそうですし。
MySQL 8.0 / 8.9.4 インデックスヒント
”注記
MySQL 8.0.20 の時点で、サーバーは、インデックスオプティマイザヒント JOIN_INDEX, GROUP_INDEX, ORDER_INDEX、および INDEX(NO_JOIN_INDEX, NO_GROUP_INDEX, NO_ORDER_INDEX および FORCE INDEX オプティマイザヒントに相当およびを置き替える)、および NO_INDEX オプティマイザヒント (IGNORE INDEX インデックスヒントに相当し、それらを置き換える) をサポートします。 したがって、USE INDEX、FORCE INDEX および IGNORE INDEX は、MySQL の将来のリリースで非推奨になり、後で完全に削除される予定です。 詳細は、インデックスレベルのオプティマイザヒントを参照してください。”
（確認し訳するため、Id=nと(Build) / (Probe)を示すコメントを追記してあります）
mysql> \! cat advent_sql_my.sql
explain analyze
SELECT
/*+
JOIN_ORDER(t1,t2,t3,t4,t0)
NO_JOIN_INDEX(t1 primary)
NO_JOIN_INDEX(t2 primary)
NO_JOIN_INDEX(t3 primary)
NO_JOIN_INDEX(t4 primary)
NO_JOIN_INDEX(t0 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
, t0.m_id
, t0.t0_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
INNER JOIN t0
ON
t1.id = t0.id
;
mysql> \. advent_sql_my.sql
+---------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+---------------------------------------------------------------------------------------------------------------------------+
Id=1 | -> Inner hash join (t0.id = t1.id) (cost=2525558.24 rows=2500000) (actual time=23.139..356.660 rows=2500000 loops=1)
Id=2 (Probe) -> Table scan on t0 (cost=0.00 rows=1000) (actual time=0.083..0.512 rows=1000 loops=1)
Id=3 (Build) -> Hash
Id=4 -> Inner hash join (t4.id = t1.id) (cost=25552.55 rows=25000) (actual time=0.679..4.613 rows=25000 loops=1)
Id=5 (Probe) -> Table scan on t4 (cost=0.01 rows=500) (actual time=0.057..0.330 rows=500 loops=1)
Id=6 (Build) -> Hash
Id=7 -> Inner hash join (t3.id = t1.id) (cost=551.75 rows=500) (actual time=0.223..0.412 rows=500 loops=1)
Id=8 (Probe) -> Table scan on t3 (cost=0.03 rows=100) (actual time=0.041..0.069 rows=100 loops=1)
Id=9 (Build) -> Hash
Id=10 -> Inner hash join (t2.id = t1.id) (cost=51.50 rows=50) (actual time=0.106..0.146 rows=50 loops=1)
Id=11 (Probe) -> Table scan on t2 (cost=0.08 rows=50) (actual time=0.025..0.040 rows=50 loops=1)
Id=12 (Build) -> Hash
Id=13 -> Table scan on t1 (cost=1.25 rows=10) (actual time=0.040..0.048 rows=10 loops=1)
|
+---------------------------------------------------------------------------------------------------------------------------+
Id=10で、Id=13のt1をTable Scanてから、Id=12でハッシュ表作成（Build）と、Id=11で、t2をTable Scan（Probe）をHash Join
Id=7で、Id=10の結果を元に、Id=9でハッシュ表作成（Build）と、Id=8のt3 Table Scan（Probe）をHash Join
Id=4で、Id=7の結果を元に、Id=6でハッシュ表作成（Build）と、Id=5のt4 Table Scan（Probe）をHash Join
Id=1で、Id=4の結果を元に、Id=3でハッシュ表作成（Build）と、Id=2のt0 Table Scan（Prove）をHash Hoin
最後のt0をBuildにしたいわけですけども、Build表制御することができないので。こんな感じですね。
と、一つだけ、思いついたので、SQL魔改造でなんとかならんか、試してみます。
（ヒントだけでなんとかするという趣旨からはズレるのですけどもw）
ダメ元で試してみますw
おおおおお、一瞬、できた！ か？ と思いましたが、やはり無理です。CTEでサブクエリーとして先に一時表としてマテリアライズしたら行くかなぁ。
と思いましたが、MySQLは、一時表でも、自動的に内部的な主キーが作成されるという、... clustered indexなのか。。。一時表も。という気づき。
この方法では、CTEを使っていますが、インラインビューにして、NO_MERGEヒントにしても同様に、一時表としてマテリアライズされるようで、同様の結果でした。。。。
記載はしていませんが、 マテリアライズされた一時表の
現時点のMySQLのオプティマイザの気持ちが、少しだけ、理解できるようになった気がします :)
mysql> \! cat advent_sql2_my.sql
explain analyze
WITH
t1234 AS
(
SELECT
/*+
QB_NAME(ilv1)
JOIN_ORDER(t1,t2,t3,t4)
NO_JOIN_INDEX(t1 primary)
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
),
t00 AS
(
SELECT
/*+
QB_NAME(ilv2)
NO_INDEX(t0 primary)
*/
t0.id
, t0.m_id
, t0.t0_c1
FROM
t0
)
SELECT
/*+
JOIN_ORDER(@ilv2)
NO_MERGE(t00)
*/
t1234.id
, t1234.t1_c1
, t1234.s_id
, t1234.t2_c1
, t1234.b_id
, t1234.t3_c1
, t1234.a_id
, t1234.c_id
, t1234.t4_c1
, t00.m_id
, t00.t0_c1
FROM
t1234
INNER JOIN t00
ON
t1234.id = t00.id
;
mysql> \. advent_sql2_my.sql
+---------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+---------------------------------------------------------------------------------------------------------------------------+
| -> Nested loop inner join (cost=2588052.66 rows=25000001) (actual time=2.449..1233.983 rows=2500000 loops=1)
-> Inner hash join (t4.id = t1.id) (cost=25552.55 rows=25000) (actual time=0.626..11.339 rows=25000 loops=1)
-> Table scan on t4 (cost=0.01 rows=500) (actual time=0.031..2.330 rows=500 loops=1)
-> Hash
-> Inner hash join (t3.id = t1.id) (cost=551.75 rows=500) (actual time=0.403..0.483 rows=500 loops=1)
-> Table scan on t3 (cost=0.03 rows=100) (actual time=0.024..0.037 rows=100 loops=1)
-> Hash
-> Inner hash join (t2.id = t1.id) (cost=51.50 rows=50) (actual time=0.329..0.350 rows=50 loops=1)
-> Table scan on t2 (cost=0.08 rows=50) (actual time=0.021..0.028 rows=50 loops=1)
-> Hash
-> Table scan on t1 (cost=1.25 rows=10) (actual time=0.190..0.199 rows=10 loops=1)
-> Index lookup on t00 using(id=t1.id) (cost=201.25..203.50 rows=10) (actual time=0.002..0.036 rows=100 loops=25000)
-> Materialize CTE t00 (cost=201.00..201.00 rows=1000) (actual time=1.803..1.803 rows=1000 loops=1)
-> Table scan on t0 (cost=101.00 rows=1000) (actual time=0.024..0.514 rows=1000 loops=1)
|
+---------------------------------------------------------------------------------------------------------------------------+
1 row in set (2.77 sec)
（Warningも出てないので、オプティマイザヒントの指定方法としては良さげだが、狙い通りにはなりませんw）
では、最後に、このポストへ至るまでの予習ポストも含め、リンクを再掲しておきます
予習 その１
帰ってきた！ 標準はあるにはあるが癖の多いSQL #5 - Optimizer Hint でも癖が多い
予習 その２
帰ってきた！ 標準はあるにはあるが癖の多いSQL #6 - Hash Joinの結合ツリーにも癖がでる
予習 その３
帰ってきた！ 標準はあるにはあるが癖の多いSQL #7 - Hash Joinの実行計画にも癖がでる
皆様、メリークリスマス & 良いお年をお迎えください。 :) / Oracle database 23c with pipe line function / SQL de Christmas Tree 、4K対応で作り直しましたw
恒例？の、Oracle Pipelined Table Function で Christmas ASCII Art
関連エントリー
・標準はあるにはあるが癖の多い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の結合ツリーにも癖がでる
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #7 - Hash Joinの実行計画にも癖がでる
2023年12月13日 (水)
MySQL 8.0.32 , PostgreSQL 13.4 and Oracle Database 21c on Oracle Linux 8 on VirtualBox for Apple Silicon Test Build 7.0.97_BETA5r160702 Tweet
予定外ですが、Advent Calendarのシーズンついでなので、空いていた以下のアドベントカレンダー向けエントリーとなります。
Kernel/VM Advent Calendar 2023 / Day 13
macOS / ARM64 Beta development revision 160702
https://www.virtualbox.org/wiki/Testbuilds
以前、Oracle Linux 8 and MySQL 8.0.32 on VirtualBox for Apple Silicon Test Build 7.0.97_BETA5r160167で、MySQL 8.0.32が起動した！と言うことを書きましたが、その後のBETAリリースではOS起動中にクラッシュしていました。
で、今回は、development revision 160702をダメもとで試してみました。
結果から書くと、色々もっさりしているのは仕方ないかないところですが、このrevisionでは、MySQLに加えて、PostgreSQLも起動しました！！！
VirtualBox for Apple Silicon Test Build 7.0.97_BETA5r160702
GuestOS : Oracle Linux Server release 8.5
MySQL 8.0.32 - 起動した！
PostgreSQL 13.4 - 起動した！
Oracle database 21c - 起動せず！
試した内容は前回と同じですが、Oracleについては変えています。
1) Oracle Linux 8.5 に、PostgreSQL 13.4 と MySQL 8.0.32 を構成したVMをエクスポートして ova 作成
2) 1)のOracle Linux 8.5 に、Oracle Database 21cを構成したものを ova としてエクスポート。
それぞれをVirtualBoxへインポートして検証しました。
ちなみに、
Oracle Linux 7.6のPre-Built Developer VMs (for Oracle VM VirtualBox) および、Oracle Linux 8.6に構築したOracle 23c Freeは、OS起動にクラッシュしていたので、うまく起動した1)を利用して、OS起動後にOracleの挙動を確認する方法にしましたが、Oracleがコケたと言う結果となりました。
Test Buildは、結構進んできているようで、今後が楽しみです ;)
では、また。
関連エントリー
・Oracle Linux 8 and MySQL 8.0.32 on VirtualBox for Apple Silicon Test Build 7.0.97_BETA5r160167
2023年12月10日 (日)
帰ってきた！ 標準はあるにはあるが癖の多い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の結合ツリーにも癖がでる
2023年12月 9日 (土)
帰ってきた！ 標準はあるにはあるが癖の多いSQL #6 - Hash Joinの結合ツリーにも癖がでる Tweet
本エントリーは、MySQL Advent Calendar 2023 シリーズ2 / Day 9とPostgreSQL Advent Calendar 2023 シリーズ2 / Day 9へのクロスポスト、および、JPOUG Advent Calendar 2023 / Day 9 の裏番組 （ADVENTARはシリーズ増やせないので） エントリーで、12/17日向けの 予習 その２ という位置付けのエントリーです。
予習 その１は、以下。
帰ってきた！ 標準はあるにはあるが癖の多いSQL #5 - Optimizer Hint でも癖が多い
少し前に、MySQLでもHash Joinになるんだね。といいうエントリーを書きました。覚えてますか？
MySQL 8.0.32では NLJに使えるINDEXが存在していても、Hash Joinをヒントで強制することができる！（オプティマイザが選択することもある！）
その時点では、
MySQLのHash Joinを強制するオプティマイザーヒントも廃止されているようだし、Build/Probeの制御もできないよね？ ヒントないし。
とか、
オプティマイザーヒントで強制できないけど、オプティマイザーが勝手に選んじゃうこともあるのか〜、NLJに利用できる索引あっても。。
勝手に選ばれることはあってもヒントではもろもろ制御できない。最初に戻る。
という状況ループ状態でしたw
そこで、閃いたのが、MySQLで、Hash Joinが自動発動する条件。その条件に合う状況にすればいいじゃん。と！
Hash Joinを強制するヒントはなくても、Nested Loop Joinに使ってる主索引（MySQLの場合は主キーはクラスター索引）を、オプティマイザーヒントで無効化してしまえば、勝手に、Hash Joinするよね！？
という確認ばかりに目が行ってしまって、気づきませんでした！！！！
MySQLのHash Joinの実行計画ツリーを見て、Oracle/PostgreSQLと違う点、何か気づきませんか？
みなさん！ これ試験に出ますよw（嘘
答え合わせは後半でw
それでは、環境づくりから（少々長めですw）
それぞれの DB（ Oracle Database 21c / PostgreSQL 13.4 with pg_hint_plan 1.3.9 / MySQL 8.0.32 ）で、そのまま実行できるDDLにしてあります。
表と索引作成（各DB共通）、データ登録、そして、統計取得まで行っています。
Oracle/PostgreSQL/MySQL共通DDL
cre_advent_tabs.sql
-- 10 rows
CREATE TABLE t1
(
id INT NOT NULL
, t1_c1 INT NOT NULL
, CONSTRAINT t1_pk PRIMARY KEY (id)
);
-- 50 rows
CREATE TABLE t2
(
id INT NOT NULL
, s_id INT NOT NULL
, t2_c1 INT NOT NULL
, CONSTRAINT t2_pk PRIMARY KEY (id, s_id)
);
-- 100 rows
CREATE TABLE t3
(
id INT NOT NULL
, b_id INT NOT NULL
, t3_c1 INT NOT NULL
, CONSTRAINT t3_pk PRIMARY KEY (id, b_id)
);
-- 500 rows
CREATE TABLE t4
(
id INT NOT NULL
, a_id INT NOT NULL
, c_id INT NOT NULL
, t4_c1 INT NOT NULL
, CONSTRAINT t4_pk PRIMARY KEY (id, a_id, c_id)
);
-- 1000 rows
CREATE TABLE t0
(
id INT NOT NULL
, m_id INT NOT NULL
, t0_c1 INT NOT NULL
, CONSTRAINT t0_pk PRIMARY KEY (id, m_id)
);
Oracle 21c向けデータ登録と統計取得からデータ件数確認までのスクリプトと実行例
make_data4oracle.sql
-- for t1
BEGIN
FOR i IN 1..10 LOOP
INSERT INTO t1 VALUES(i,i);
END LOOP;
COMMIT;
DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SCOTT', tabname=>'T1', cascade=>true, no_invalidate=>false);
END;
/
SELECT COUNT(1) FROM t1;
-- for t2
BEGIN
FOR i IN 1..10 LOOP
FOR j IN 1..5 LOOP
INSERT INTO t2 VALUES(i,j,i+j);
END LOOP;
END LOOP;
COMMIT;v DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SCOTT', tabname=>'T2', cascade=>true, no_invalidate=>false);
END;
/
SELECT COUNT(1) FROM t2;
-- for t3
BEGINv FOR i IN 1..10 LOOP
FOR j IN 1..10 LOOP
INSERT INTO t3 VALUES(i,j,i+j);
END LOOP;
END LOOP;
COMMIT;
DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SCOTT', tabname=>'T3', cascade=>true, no_invalidate=>false);
END;
/
SELECT COUNT(1) FROM t3;
-- for t4
BEGIN
FOR i IN 1..10 LOOP
FOR j IN 1..10 LOOP
FOR k IN 1..5 LOOP
INSERT INTO t4 VALUES(i,j,k,i+j);
END LOOP;
END LOOP;
COMMIT;
END LOOP;
DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SCOTT', tabname=>'T4', cascade=>true, no_invalidate=>false);
END;
/
SELECT COUNT(1) FROM t4;
-- for t0
BEGIN
FOR i IN 1..10 LOOP
FOR j IN 1..100 LOOP
INSERT INTO t0 VALUES(i,j,i+j);
END LOOP;
COMMIT;
END LOOP;
DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SCOTT', tabname=>'T0', cascade=>true, no_invalidate=>false);
END;
/
SELECT COUNT(1) FROM t0;
SCOTT@orclpdb1> @make_data4oracle
PL/SQLプロシージャが正常に完了しました。
COUNT(1)
----------
10
PL/SQLプロシージャが正常に完了しました。
COUNT(1)
----------
50
PL/SQLプロシージャが正常に完了しました。
COUNT(1)
----------
100
PL/SQLプロシージャが正常に完了しました。
COUNT(1)
----------
500
PL/SQLプロシージャが正常に完了しました。
COUNT(1)
----------
1000
PostgreSQL 13.4向けデータ登録と統計取得からデータ件数確認までのスクリプトと実行例
perftestdb=> \i cre_advent_tabs.sql
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
perftestdb=>
PostgreSQL向けデータ登録と統計取得からデータ件数確認までのスクリプトと実行例
make_data4postgresql.sql
-- for t1
DO $$
BEGIN
FOR i IN 1..10 LOOP
INSERT INTO t1 VALUES(i,i);
END LOOP;
COMMIT;
END
$$
;
VACUUM VERBOSE ANALYZE t1;
SELECT COUNT(1) FROM t1;
-- for t2
DO $$
BEGIN
FOR i IN 1..10 LOOP
FOR j IN 1..5 LOOP
INSERT INTO t2 VALUES(i,j,i+1);
END LOOP;
END LOOP;
COMMIT;
END
$$
;
VACUUM VERBOSE ANALYZE t2;
SELECT COUNT(1) FROM t2;
-- for t3
DO $$
BEGIN
FOR i IN 1..10 LOOP
FOR j IN 1..10 LOOP
INSERT INTO t3 VALUES(i,j,i+1);
END LOOP;
END LOOP;
COMMIT;
END
$$
;
VACUUM VERBOSE ANALYZE t3;
SELECT COUNT(1) FROM t3;
-- for t4
DO $$
BEGIN
FOR i IN 1..10 LOOP
FOR j IN 1..10 LOOP
FOR k IN 1..5 LOOP
INSERT INTO t4 VALUES(i,j,k,i+j);
END LOOP;
END LOOP;
COMMIT;
END LOOP;
END
$$
;
VACUUM VERBOSE ANALYZE t4;
SELECT COUNT(1) FROM t4;
-- for t0
DO $$
BEGIN
FOR i IN 1..10 LOOP
FOR j IN 1..100 LOOP
INSERT INTO t0 VALUES(i,j,i+j);
END LOOP;
COMMIT;
END LOOP;
END
$$
;
VACUUM VERBOSE ANALYZE t0;
SELECT COUNT(1) FROM t0;
perftestdb=> \i make_data4postgresql.sql
DO
psql:make_data4postgresql.sql:12: INFO: vacuuming "public.t1"
psql:make_data4postgresql.sql:12: INFO: "t1": found 0 removable, 10 nonremovable row versions in 1 out of 1 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 530026
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
psql:make_data4postgresql.sql:12: INFO: analyzing "public.t1"
psql:make_data4postgresql.sql:12: INFO: "t1": scanned 1 of 1 pages, containing 10 live rows and 0 dead rows; 10 rows in sample, 10 estimated total rows
VACUUM
count
-------
10
(1 行)
DO
psql:make_data4postgresql.sql:30: INFO: vacuuming "public.t2"
psql:make_data4postgresql.sql:30: INFO: "t2": found 0 removable, 50 nonremovable row versions in 1 out of 1 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 530028
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
psql:make_data4postgresql.sql:30: INFO: analyzing "public.t2"
psql:make_data4postgresql.sql:30: INFO: "t2": scanned 1 of 1 pages, containing 50 live rows and 0 dead rows; 50 rows in sample, 50 estimated total rows
VACUUM
count
-------
50
(1 行)
DO
psql:make_data4postgresql.sql:48: INFO: vacuuming "public.t3"
psql:make_data4postgresql.sql:48: INFO: "t3": found 0 removable, 100 nonremovable row versions in 1 out of 1 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 530030
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
psql:make_data4postgresql.sql:48: INFO: analyzing "public.t3"
psql:make_data4postgresql.sql:48: INFO: "t3": scanned 1 of 1 pages, containing 100 live rows and 0 dead rows; 100 rows in sample, 100 estimated total rows
VACUUM
count
-------
100
(1 行)
DO
psql:make_data4postgresql.sql:67: INFO: vacuuming "public.t4"
psql:make_data4postgresql.sql:67: INFO: "t4": found 0 removable, 500 nonremovable row versions in 3 out of 3 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 530041
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
psql:make_data4postgresql.sql:67: INFO: analyzing "public.t4"
psql:make_data4postgresql.sql:67: INFO: "t4": scanned 3 of 3 pages, containing 500 live rows and 0 dead rows; 500 rows in sample, 500 estimated total rows
VACUUM
count
-------
500
(1 行)
DO
psql:make_data4postgresql.sql:85: INFO: vacuuming "public.t0"
psql:make_data4postgresql.sql:85: INFO: "t0": found 0 removable, 1000 nonremovable row versions in 6 out of 6 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 530052
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
psql:make_data4postgresql.sql:85: INFO: analyzing "public.t0"
psql:make_data4postgresql.sql:85: INFO: "t0": scanned 6 of 6 pages, containing 1000 live rows and 0 dead rows; 1000 rows in sample, 1000 estimated total rows
VACUUM
count
-------
1000
(1 行)
MySQL 8.0.32向けデータ登録と統計取得からデータ件数確認までのスクリプトと実行例
mysql> \. cre_advent_tabs.sql
Query OK, 0 rows affected (0.06 sec)
Query OK, 0 rows affected (0.05 sec)
Query OK, 0 rows affected (0.05 sec)
Query OK, 0 rows affected (0.04 sec)
Query OK, 0 rows affected (0.07 sec)
mysql>
MySQL向けデータ登録と統計取得からデータ件数確認までのスクリプトと実行例
make_data4mysql.sql
DELIMITER //
CREATE PROCEDURE make_data4mysql()
BEGIN
DECLARE i, j, k INT;
-- for t1
SET i = 1;
WHILE i <= 10 DO
INSERT INTO t1 VALUES(i,i);
SET i = i + 1;
END WHILE;
COMMIT;
-- for t2
SET i = 1;
WHILE i <= 10 DO
SET j = 1;
WHILE j <= 5 DO
INSERT INTO t2 VALUES(i,j,i+1);
SET j = j + 1;
END WHILE;
SET i = i + 1;
END WHILE;
COMMIT;
-- for t3
SET i = 1;
WHILE i <= 10 DO
SET j = 1;
WHILE j <= 10 DO
INSERT INTO t3 VALUES(i,j,i+1);
SET j = j + 1;
END WHILE;
SET i = i + 1;
END WHILE;
COMMIT;
-- for t4
SET i = 1;
WHILE i <= 10 DO
SET j = 1;
WHILE j <= 10 DO
SET k = 1;
WHILE k <= 5 DO
INSERT INTO t4 VALUES(i,j,k,i+j);
SET k = k + 1;
END WHILE;
SET j = j + 1;
END WHILE;
SET i = i + 1;
COMMIT;
END WHILE;
-- for t0
SET i = 1;
WHILE i <= 10 DO
SET j = 1;
WHILE j <= 100 DO
INSERT INTO t0 VALUES(i,j,i+j);
SET j = j + 1;
END WHILE;
SET i = i + 1;
COMMIT;
END WHILE;
END
//
DELIMITER ;
CALL make_data4mysql();
ANALYZE TABLE t1, t2, t3, t4, t0;
SELECT COUNT(1) FROM t1;
SELECT COUNT(1) FROM t2;
SELECT COUNT(1) FROM t3;
SELECT COUNT(1) FROM t4;
SELECT COUNT(1) FROM t0;
DROP PROCEDURE make_data4mysql;
準備できた！
mysql> \. make_data4mysql.sql
Query OK, 0 rows affected (0.03 sec)
Query OK, 0 rows affected (11.20 sec)
+---------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------------+---------+----------+----------+
| perftestdb.t1 | analyze | status | OK |
| perftestdb.t2 | analyze | status | OK |
| perftestdb.t3 | analyze | status | OK |
| perftestdb.t4 | analyze | status | OK |
| perftestdb.t0 | analyze | status | OK |
+---------------+---------+----------+----------+
5 rows in set (0.07 sec)
+----------+
| COUNT(1) |
+----------+
| 10 |
+----------+
1 row in set (0.01 sec)
+----------+
| COUNT(1) |
+----------+
| 50 |
+----------+
1 row in set (0.00 sec)
+----------+
| COUNT(1) |
+----------+
| 100 |
+----------+
1 row in set (0.02 sec)
+----------+
| COUNT(1) |v+----------+
| 500 |
+----------+
1 row in set (0.01 sec)
+----------+
| COUNT(1) |
+----------+
| 1000 |
+----------+
1 row in set (0.02 sec)
Query OK, 0 rows affected (0.01 sec)
mysql>
実行計画を理解するための参考
（Oracleのマニュアルで解説されているBushy Join Treeは、Zigzag Join Treeと言われている結合ツリーに見えますよね。Oracleは、Bushy/Zigzagは区別してなさそう）
参考）
Hash JoinのBuild/Probeって何？ という方は、以下も読んでおくと良いです
Oracle Database Release 23 / SQL Tuning Guide - 9.2.2.2.2 Hash Join: Basic Steps
https://docs.oracle.com/en/database/oracle/oracle-database/23/tgsql/joins.html#GUID-9EE5CD4C-B90C-4E61-83DC-BD585D79635C
結合ツリーついて
Oracle Database Release 23 / SQL Tuning Guide - 9.1.1 Join Trees
https://docs.oracle.com/en/database/oracle/oracle-database/23/tgsql/joins.html#GUID-31B0F249-A5AA-41E9-AE98-A484FC5C487C
では最初に、Oracle Database 21cから。
それっぽいの形にしたHash Joinの実行計画をお見せします。
これは、Oracleのマニュアルで言うと、Bushy Join Tree です。
SCOTT@orclpdb1> @ora_sql_hj.sql
1 SELECT
2 /*+
3 MONITOR
4 USE_HASH(t1 t2 t3 t4)
5 */
6 t1.id
7 , t1.t1_c1
8 , t2.s_id
9 , t2.t2_c1
10 , t3.b_id
11 , t3.t3_c1
12 , t4.a_id
13 , t4.c_id
14 , t4.t4_c1
15 FROM
16 t1
17 INNER JOIN t2
18 ON
19 t1.id = t2.id
20 INNER JOIN t3
21 ON
22 t1.id = t3.id
23 INNER JOIN t4
24 ON
25* t1.id = t4.id
...略...
SQL Plan Monitoring Details (Plan Hash Value=122725940)
===================================================================================================================================================
| 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 | | | | 1 | +0 | 1 | 25000 | | | . | | |
| 1 | HASH JOIN | | 25000 | 12 | 1 | +0 | 1 | 25000 | | | 1MB | | |
| 2 | TABLE ACCESS FULL | T4 | 500 | 3 | 1 | +0 | 1 | 500 | 2 | 49152 | . | | |
| 3 | HASH JOIN | | 500 | 9 | 1 | +0 | 1 | 500 | | | 1MB | | |
| 4 | HASH JOIN | | 50 | 6 | 1 | +0 | 1 | 50 | | | 1MB | | |
| 5 | TABLE ACCESS FULL | T1 | 10 | 3 | 1 | +0 | 1 | 10 | 2 | 49152 | . | | |
| 6 | TABLE ACCESS FULL | T2 | 50 | 3 | 1 | +0 | 1 | 50 | 2 | 49152 | . | | |
| 7 | TABLE ACCESS FULL | T3 | 100 | 3 | 1 | +0 | 1 | 100 | 2 | 49152 | . | | |
===================================================================================================================================================
上記の解説
Id=5 T1をTable Full Scan (Build表)
Id=6 T2をTable Full Scan (Probe表).
Id=4 T1とT2をHash Join (on memoryでHash Join完了）
Id=4 Id=5,6のHash Joinの結果(Build表)
Id=7 T3をTable Full Scan (Probe表)
Id=3 Id=4の結合結果とT3をHash Join (on memoryでHash Join完了）
Id=2 T4をTable Full Scan (Build表)
Id=3 Id=4の結合結果とT3の結合結果 (Probe表)
Id=2 T4とId=3の結合結果をHash Join (On memoryでHash Join完了）
という順序なので、
引用したOracleのマニュアルで言う、Bushy Join Tree になっています。
Oracleのマニュアルでは、Bushy Join Treeとなっていますが、他では、Zigzag Join Treeとして分類されていたりするツリーです。。LeftとRight Deep Join TreeがMixされた形ですよね、これ。
これを結合ツリーで書くと、以下のようになります。
次は、PostgreSQL 13.4 with pg_hint_plan 1.3.9で同じくHash Joinになるようにヒントで強制した実行計画です。
この結合ツリーは、Bushy Join Treeですね。
perftestdb=> \! cat pg_sql_hj.sql
explain (analyze)
SELECT
/*+
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_hj.sql
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Id=1 Hash Join (cost=17.48..309.84 rows=25000 width=36) (actual time=3.405..10.466 rows=25000 loops=1)
Hash Cond: (t1.id = t2.id)
Id=2 -> Hash Join (cost=1.23..11.09 rows=500 width=24) (actual time=1.696..1.989 rows=500 loops=1)
Hash Cond: (t4.id = t1.id)
Id=3 -> Seq Scan on t4 (cost=0.00..8.00 rows=500 width=16) (actual time=0.907..0.993 rows=500 loops=1)
Id=4 -> Hash (cost=1.10..1.10 rows=10 width=8) (actual time=0.721..0.722 rows=10 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Id=5 -> Seq Scan on t1 (cost=0.00..1.10 rows=10 width=8) (actual time=0.705..0.707 rows=10 loops=1)
Id=6 -> Hash (cost=10.00..10.00 rows=500 width=24) (actual time=1.687..1.687 rows=500 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 36kB
Id=7 -> Hash Join (cost=2.12..10.00 rows=500 width=24) (actual time=1.400..1.554 rows=500 loops=1)
Hash Cond: (t3.id = t2.id)
Id=8 -> Seq Scan on t3 (cost=0.00..2.00 rows=100 width=12) (actual time=0.701..0.712 rows=100 loops=1)
Id=9 -> Hash (cost=1.50..1.50 rows=50 width=12) (actual time=0.679..0.680 rows=50 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 11kB
Id=10 -> Seq Scan on t2 (cost=0.00..1.50 rows=50 width=12) (actual time=0.648..0.656 rows=50 loops=1)
解説しやすいように実行計画のノードにIdを振っています:)
Oracleの実行計画と大きく違うのは、Build表とProbe表の順序です。Oracleの場合、同一階層の上位が、Build表で、その下にProbe表がリストされますが、
PostgreSQL(MySQLも同様）では、同一階層上で、Probe表が上位に、その下に Hash（ハッシュ表を作成している操作。Oracleでは実行計画上この操作は現れません）、さらにその下位階層にBuild表のTable Full scanという形で表現されています。
階層の一番深い部分から実行されるので、上記例だと、Id=16のt2（Build表）のSeq Scan (Oracleで言うTable Full Scanが最初に実行され、次に、Id=15のHashが行われていることがわかります。
Id=10 t2 Seq Scan (Oracleで言うTable Full Scan)
Id=9 Id=10の結果を元にHash表作成 （つまり、t2がBuild)
Id=8 t3 Seq Scan (Probe)
Id=7 t2, t3をHash Join
Id=6 Id=7(t2, t3のHash Join)の結果を元にHash表作成（Build)
Id=5 t1 Seq Scan
Id=4 Id=5の結果を元にHash表作成（Build）
Id=3 t4 Seq Scan (Probe)
Id=2 t1, t4をHash Join
Id=1 Id=6の結果(Build), Id=2の結果（Probe)としてHash Join
こうやって1Step毎に追っていくと、OracleとPostgreSQLの実行計画のTree listで、Hash JoinのBuildとProbeの位置が異なっていることに気づきやすいですよね。（ちなみに、MySQLのTree formatも同様です）
これを結合ツリーで書くと、以下のようになります。
最後に、MySQL 8.0.32です。
Hash Joinを強制する直接的なヒントも、Budling/Probe表を制御するヒントも方法存在しませんが、Hash Joinになるよう結合順を指定した上で、Nested Loop Joinになってしまうのを抑止するために、Nested Loop Joindで利用する主キー索引の利用を抑止しました。
（MySQLの主キーはクラスター索引なので、主キーがNested Loop Joinに利用される場合、索引の利用を抑止するには、NO_JOIN_INDEXヒントで primary を指定する必要があります）
なお、JOIN_ORDERヒントで結合順を指定した理由は、結合順を指定することでNested Loop Joinに利用される主キー(primary)を、NO_JOIN_INDEXヒントで確実抑止したかったためです。
ちなみに、この結合ツリーは、Left Deep Join Treeですね。
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 |
+-----------------------------------------------------------------------------------------------------------------------+
Id=1 | -> Inner hash join (t4.id = t1.id) (cost=25552.55 rows=25000) (actual time=0.643..5.045 rows=25000 loops=1)
Id=2 -> Table scan on t4 (cost=0.01 rows=500) (actual time=0.052..0.346 rows=500 loops=1)
Id=3 -> Hash
Id=4 -> Inner hash join (t3.id = t1.id) (cost=551.75 rows=500) (actual time=0.216..0.380 rows=500 loops=1)
Id=5 -> Table scan on t3 (cost=0.03 rows=100) (actual time=0.041..0.069 rows=100 loops=1)
Id=6 -> Hash
Id=7 -> Inner hash join (t2.id = t1.id) (cost=51.50 rows=50) (actual time=0.100..0.141 rows=50 loops=1)
Id=8 -> Table scan on t2 (cost=0.08 rows=50) (actual time=0.026..0.041 rows=50 loops=1)
Id=9 -> Hash
Id=10 -> Table scan on t1 (cost=1.25 rows=10) (actual time=0.039..0.047 rows=10 loops=1)
|
+-----------------------------------------------------------------------------------------------------------------------+
既にお分かりだと思いますが、答え合わせです。:)
OracleのRight Deep Joinに見慣れている皆さん（私も含むw）が、Right Deep Joinと勘違いしちゃうケースはありますが、Build表とProbe表の現れる位置が、Oracleの実行計画と逆なんですよね。
（何度見ても、OracleでRight Deep Join Treeにした実行計画の形とダブってしまって、慣れないw）
このような形になっているHash Joinの実行計画って、PostgreSQLも含め、Left Deep Join Treeなんですよね。
参考）MySQLが、Left Deep Join Treeにしかならない理由はこれ。
Chapter 4. Query Performance Optimization The execution plan
"MySQL always begins with one table and finds matching rows in the next table. Thus, MySQL’s query execution plans always take the form of a left-deep tree"とあります。
Nested Loop Joinって、Left Deep Join Treeになるから、MySQLだとHash Joinでも、 Left Deep Join Treeなのかなぁ。と。ただ、一時期、クラウド方面にあるMySQL互換で、HASH_JOIN_BUILDINGとかいうヒントが加えられていたこともありました。NewSQL系でも拡張されているケースもあるようです。
前述のMySQLの実行計画を追っかけてみると、、、
Id=10 t1 Table Scan
Id=11 Id=10で読み込んだ結果からHash表を作成（つまり、t1がBuild）
Id=7,8 T2 Table Scan (Probe）してId=11とHash Join
Id=6 Id=7のHash Joinの結果からHash表作成 (Build）
Id=4,5 T3 Table Scan (Probe)してId=6とHash Join
Id=3 Id=4のHash Joinの結果からHash表作成（Build）
Id=1,2 T4 Table Scan (Probe)しId=3とHash Join
これを結合ツリーで書くと、以下のように Left Deep Treeになりまよね。
（表示されているExlplainの実行計画を見ていると、OracleのHash JoinでRight Deep Join Treeに錯覚してしまうのなんとかならんかねぇ。と思いつつ、慣れるしかないな。と言う答えしか出てこなかった。PostgreSQLも同じ表示方法だし。慣れろ→自分w）
ということで、
MySQLのHash Joinの実行計画ツリーを見て、何か気づきませんか？
みなさん！ これ試験に出ますよw（嘘
の答えは、
MySQLは、Left Deep Join Tree しか、しない。でした！
ざっくりとしたまとめ的なやつ
Oracle/PostgreSQL/MySQLそれぞれ、Hash Joinだけを強制して結合順はオプティマイザー任せにした場合。
Oracle/PostgreSQLは、Bushy Join Tree
データ次第では、Left/Right Deep Join Treeの可能性もあるでしょうけども。今回のケースでは、Bushy Join Tree, 細かめに分類すると、Oracleの結合ツリーは、Zigzagですね。ちなみに、Oracleには、BUSHY_JOINというヒントがあったりします（使ったことないけどw）。
MySQLは、Left Deep Join Tree にしかならんよ。と。
以下の方針が変更されない限り、変わることはなさそう。
再掲
https://www.oreilly.com/library/view/high-performance-mysql/9780596101718/ch04.html#how_mysql_joins_multiple_table
"MySQL always begins with one table and finds matching rows in the next table. Thus, MySQL’s query execution plans always take the form of a left-deep tree"
予習、その３へ続く。
では、また。:)
関連エントリー
・標準はあるにはあるが癖の多い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 でも癖が多い
2023年12月 3日 (日)
11月にリリースした曲 / DTM / GarageBand / N + 1 Loops Tweet
11月はこの2曲をリリース:)
80' Loops Ver.2 / N + 1 Loops
Ver.2ってくらいなので、Ver.1 ( 80' Loops ) はあってw 昨年末にリリースしてたのですが、アジャイルDTMなので、変えたいところがあるとバックログに貯まるわけで、それをやっつけたら、こうなったというところ。
BASSをre:Chopしまして、最初と後半にアドリブ的な部分としてslapな感じを追加して、Voxもちょっと追加。
フリー動画もクロマキー加工部分追加（うまくできないところはそのままですがw）
できれば、Dua LipaのDance The Nightみたいな、ちょっと割れ気味のBASS音にしたいなというバックログを積んだ状態にはなってますが、一旦これで:)
XYZ Loops / N + 1 Loops
シンセサイザー中心でループを集めてたら、あ〜、なんか、XYZな雰囲気のイメージが浮かんだので、その勢いで作った曲。
すでに、バックログ積んで、ベースライン変えたいかもなぁという、ボヤ〜〜〜〜んとした雰囲気だけが残っていますw
昨日散歩したお寺が、ベストタイミングで、色づいた木々が最高に映えていた。手入れの行き届いた境内って落ち着く。
では、また:)
最近のコメント