投稿2023/04/19 14:16
編集2023/04/19 23:47PostgreSQL11でHash Right JoinではなくParallel Hash Left Join を強制的に指定したい。
内部でHash Right JoinではなくParallel Hash Left Joinが使い分けれる仕組みを知りたい。
SELECT *
FROM shouhin
LEFT JOIN koumoku ON shouhin.id = koumoku.id
WHERE shouhin.price = 100
AND koumoku.bangou = 3
例えば上記の様なSQL文を流した時にEXPLAIN ANALYZE で確認すると、
WHERE shouhin.price = 123
AND koumoku.bangou = 3
の時は、Parallel Hash Left Joinが使われますが、
WHERE shouhin.price = 345
AND koumoku.bangou = 4
↑別の値で検索するとHash Right Joinが必ず利用されます。
上記のWHERE句の部分は有効な数字で似た様な値に関わらず、
結合方法が、Hash JoinとNested Loopで別れます。
Hash Joinの時はSeq Scanになり速度がかなり遅くなります。
WHERE句の値が少し違うだけにもかかわらず、
どういう理由でHash JoinとNested Loopが別れるか知りたいです。
また速度が遅くなるのでNested Loopを強制したいです。
INDEXで速度の問題については解決できるので、
どういう理由でHash JoinとNested Loopが別れるか教えて下さい。
WHERE句の検索条件については、数字３桁と数字１桁です。
PostgreSQL11、PostgreSQL9で試しましたが、どちらも同じ挙動をします。
回答2件
0
ベストアンサー
どういう理由でHash JoinとNested Loopが別れるか教えて下さい。
リファレンスによれば、それぞれの選択肢で計画し一番コストの低いものを選択するとあります。
以下抜粋
問い合わせが２つ以上のリレーションの結合を必要とすると、リレーションを結合する計画は、単一のリレーションをスキャンするために全ての実行可能な計画が探し出された後に検討されます。３つの実行可能な結合戦略を示します。
ネステッドループ結合： 左側のリレーションの中で見つけられた行ごとに右側のリレーションが1回スキャンされます。 この戦略は実装が簡単ですが、時間がかかる場合があります （とは言っても右側のリレーションがインデックススキャンによってスキャン可能であればよい戦略になります。 右側のインデックススキャンのキーとして左側のリレーションの現在の行の値を使用することができます。）
マージ結合： 結合を開始する前に、それぞれのリレーションを結合属性でソートします。 そして、2つのリレーションを並行してスキャンし、一致する行を結合行の形にまとめます。 それぞれのリレーションがたった1回しかスキャンされなくて済むのでこの結合は魅力的です。 要求されるソートは、明示的なソート段階、または、結合キー上のインデックスを使用して適切な順序でリレーションをスキャンすることにより行われます。
ハッシュ結合： 右側のリレーションがハッシュキーとして結合属性を用いて初めにスキャンされ、ハッシュテーブルに読み込まれます。 次に左側のリレーションがスキャンされ、見つかったそれぞれの行に相応しい値が、右側のリレーションの行を探し出すためのハッシュキーとして使われます。
※コメントに対して追記
抽出文の変更かINDEXしか対策はなさそうだと判断いたしました。
拡張モジュールでヒント文での制御が可能です。
pg_hint_plan (PostgreSQL 実行計画制御ツール)
以下抜粋
<利用可能なヒントのカテゴリ>
| スキャン方式 | テーブルのスキャン方式を指定できます |
| 結合順序 | 複数あるテーブルをどの順で結合するか指定できます |
| 結合方式 | テーブル結合の方式を指定できます |
| 見積件数補正 | テーブル結合結果の件数見積もりを補正する指定ができます |
| パラレル実行 | パラレル実行を強制あるいは禁止する指定ができます |
| 設定パラメータ | SETコマンドで指定できるパラメータ設定をヒントから与えます |
投稿2023/04/20 00:18
編集2023/04/20 06:10総合スコア25195
0
何度言っても事実の提示をせず、原因も不明なまま解決を選択してしまったので、質問者さん以外の人にとっては掴みどころのない質問になってしまいました。ここでは他の人のために回答ではないサンプルを用意しました。
dockerがインストールされたLinuxでユーザーがdocker操作権限を持っている場合に動作します。
bash
1#!/bin/sh 2docker run -d --rm --name postgres -p 5432:5432 -e POSTGRES_USER=postgres -e POSTGRES_DB=postgres -e POSTGRES_PASSWORD=secret -v $(pwd)/data:/var/lib/postgresql/data postgres:11 3docker exec -i postgres bash <<EOF 4sleep 5 5psql -a postgres postgres <<EOF2 6select version(); 7create table if not exists item(id numeric(20) primary key, name varchar(100), price numeric(20)); 8create table if not exists attr(item_id numeric(20), name varchar(20), value numeric(20), primary key(item_id, name)); 9do \\$\\$ 10declare 11 cnt numeric(20) := 0; 12begin 13 delete from item; 14 delete from attr; 15 commit; 16 insert into item values (1, '商品1', 100); 17 insert into item values (2, '商品2', 150); 18 commit; 19 for i in 1..15 loop 20 select count(*) into cnt from item; 21 insert into item select id + cnt, concat('商品', cast(id+cnt as varchar(20))), price from item; 22 commit; 23 end loop; 24 insert into attr values(50, '属性1', 300); 25 insert into attr values(100, '属性2', 400); 26 commit; 27 for i in 1..9 loop 28 select max(item_id) into cnt from attr; 29 insert into attr select item_id + cnt, name, value from attr; 30 commit; 31 end loop; 32end \\$\\$; 33vacuum item; 34analyze item; 35vacuum attr; 36analyze attr; 37explain analyze select * from item left join attr on item.id=attr.item_id where item.price=150 and attr.name='属性2'; 38explain analyze select * from item left join attr on item.id=attr.item_id where item.price=150 and attr.name='属性3'; 39\\q 40EOF2 41exit 42EOF 43docker stop postgres
docker runはpostgres:11イメージからコンテナを作りデーモン起動しています。
docker execは作ったコンテナ上でbashを起動し、EOFまでのhere documentをコンテナ内で実行しています。
docker stopは最後に終了させています。
here documentの中身は、5秒待ってpsqlでpostgresサーバーに繋げてさらに内側のhere documentでSQLを実行してるだけです。
実行しているSQLについては読めば分かるでしょう。
do $$～end $$までは匿名のプロシージャです。
データを水増しする際に変数が必要だったので、仕方なく使っています。
ER図的なものは↓
各テーブルには
item: 65536行
attr: 1024行
入っていて、大体こんな感じのデータになっています。
sql
1postgres=# select * from item left join attr on item.id=attr.item_id where item.id in (1,2,3,25,50,75,100,125,150,175,200) order by item.id; 2 id | name | price | item_id | name | value 3-----+---------+-------+---------+-------+------- 4 1 | 商品1 | 100 | | | 5 2 | 商品2 | 150 | | | 6 3 | 商品3 | 100 | | | 7 25 | 商品25 | 100 | | | 8 50 | 商品50 | 150 | 50 | 属性1 | 300 9 75 | 商品75 | 100 | | | 10 100 | 商品100 | 150 | 100 | 属性2 | 400 11 125 | 商品125 | 100 | | | 12 150 | 商品150 | 150 | 150 | 属性1 | 300 13 175 | 商品175 | 100 | | | 14 200 | 商品200 | 150 | 200 | 属性2 | 400 15(11 rows) 16 17postgres=#
bash
1$ sh create_env.sh 2f86d2c5b542fb0a5a862c0aba425c61423c9693296462bfb8d6ca709ed76b766 3select version(); 4 version 5------------------------------------------------------------------------------------------------------------------------------------ 6 PostgreSQL 11.16 (Debian 11.16-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit 7(1 row) 8 9create table if not exists item(id numeric(20) primary key, name varchar(100), price numeric(20)); 10CREATE TABLE 11create table if not exists attr(item_id numeric(20), name varchar(20), value numeric(20), primary key(item_id, name)); 12CREATE TABLE 13do $$ 14declare 15 cnt numeric(20) := 0; 16begin 17 delete from item; 18 delete from attr; 19 commit; 20 insert into item values (1, '商品1', 100); 21 insert into item values (2, '商品2', 150); 22 commit; 23 for i in 1..15 loop 24 select count(*) into cnt from item; 25 insert into item select id + cnt, concat('商品', cast(id+cnt as varchar(20))), price from item; 26 commit; 27 end loop; 28 insert into attr values(50, '属性1', 300); 29 insert into attr values(100, '属性2', 400); 30 commit; 31 for i in 1..9 loop 32 select max(item_id) into cnt from attr; 33 insert into attr select item_id + cnt, name, value from attr; 34 commit; 35 end loop; 36end $$; 37DO 38vacuum item; 39VACUUM 40analyze item; 41ANALYZE 42vacuum attr; 43VACUUM 44analyze attr; 45ANALYZE 46explain analyze select * from item left join attr on item.id=attr.item_id where item.price=150 and attr.name='属性2'; 47 QUERY PLAN 48----------------------------------------------------------------------------------------------------------------- 49 Hash Join (cost=26.20..1349.37 rows=256 width=41) (actual time=0.251..14.552 rows=512 loops=1) 50 Hash Cond: (item.id = attr.item_id) 51 -> Seq Scan on item (cost=0.00..1237.20 rows=32751 width=22) (actual time=0.009..10.173 rows=32768 loops=1) 52 Filter: (price = '150'::numeric) 53 Rows Removed by Filter: 32768 54 -> Hash (cost=19.80..19.80 rows=512 width=19) (actual time=0.173..0.174 rows=512 loops=1) 55 Buckets: 1024 Batches: 1 Memory Usage: 34kB 56 -> Seq Scan on attr (cost=0.00..19.80 rows=512 width=19) (actual time=0.006..0.113 rows=512 loops=1) 57 Filter: ((name)::text = '属性2'::text) 58 Rows Removed by Filter: 512 59 Planning Time: 0.269 ms 60 Execution Time: 14.607 ms 61(12 rows) 62 63explain analyze select * from item left join attr on item.id=attr.item_id where item.price=150 and attr.name='属性3'; 64 QUERY PLAN 65------------------------------------------------------------------------------------------------------ 66 Nested Loop (cost=0.29..28.11 rows=1 width=41) (actual time=0.086..0.087 rows=0 loops=1) 67 -> Seq Scan on attr (cost=0.00..19.80 rows=1 width=19) (actual time=0.086..0.086 rows=0 loops=1) 68 Filter: ((name)::text = '属性3'::text) 69 Rows Removed by Filter: 1024 70 -> Index Scan using item_pkey on item (cost=0.29..8.31 rows=1 width=22) (never executed) 71 Index Cond: (id = attr.item_id) 72 Filter: (price = '150'::numeric) 73 Planning Time: 0.287 ms 74 Execution Time: 0.106 ms 75(9 rows) 76 77\q 78postgres
存在しない属性値を使用するとattr側の行がなくなるので、joinの方法が合理的に変わった。
こういう話からスタートさせてください。
させられないなら、この質問をするだけの前提知識が足りていません。
投稿2023/04/21 19:54
総合スコア943
あなたの回答
tips
プレビュー
Q&A
