質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.30%
PostgreSQL

PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。

Q&A

解決済

2回答

1847閲覧

PostgreSQL11でHash Right JoinではなくParallel Hash Left Join を強制的に指定したい

june_223

総合スコア18

PostgreSQL

PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。

0グッド

0クリップ

投稿2023/04/19 14:16

編集2023/04/19 23:47

実現したいこと

PostgreSQL11で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句の検索条件については、数字3桁と数字1桁です。
PostgreSQL11、PostgreSQL9で試しましたが、どちらも同じ挙動をします。

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

退会済みユーザー

退会済みユーザー

2023/04/19 18:05

本当に知りたいのであれば、再現可能なDDL+DMLを用意すべきですね。 WHERE句の条件で実行計画が異なるのは単純に統計的にそうした方が有利なデータ構造なだけだと思いますよ。 一言で言うと、インデックスもないのに、ある程度絞れてある程度以下のサイズ感ならhash joinを使うというだけ。 nested loopは遅いので、強制したいときというのはあまりなく、説明のチグハグさも相まって何か重症な感じですが、計画時コストと実際に差があるということならanalyzeして更新でもすればいいのではないでしょうか? とはいえヒントだけでは思ったように動かず、私の実力ではどうしようもないときもあります。そういうケースは一時表作って回避したりしてました。 というわけで、問答では埒があかないと思うので、最初に書いた通り再現コード+データを用意しましょう。
sazi

2023/04/20 00:10

それから実行計画もあると良いですね
退会済みユーザー

退会済みユーザー

2023/04/21 01:04

通知が来なかったので気付くのが遅れました。 こういうのは本人が納得するだけでなく、周りと確認・共有できることが大事なので、原因が不明なうちに解説をしてしまうのはどうかと個人的には思います。一言でいうと、「甘やかしすぎ」かな、ということです。 質問者が楽をするために質問するのであれば、お金でも払って聞いてください。作業量が圧倒的に増えるものの、他の人と情報共有しながら、自分も正しく解決したいのであれば、ここで質問すればいいでしょう。
sazi

2023/04/21 03:39

> 再現コード+データを用意しましょう。 については実行計画もそうですけど、可能ならという前提です。 細かいチューニングに関して、大量の実データが無いと再現しないとか、テーブル構造は公開できないとかが普通なので。 なので、「解決は自分でするので、HINT下さい」という類の質問だと捉えています。
退会済みユーザー

退会済みユーザー

2023/04/21 04:04

「解決は自分でするので、HINT下さい」という類の質問は、当て推量な回答しか出なくなり、解決策としてはNGなものが多い。何より「原因が明確でない状態での対策提案」という時点で、もうダメなんですよ。たまたま動かしてるうちに対策できた!で不正解のまま解決しちゃうのが関の山です。 ヒントが欲しいなら原因が確定する状態の質問でないといけません。可哀想というだけで推測回答してたらキリがないし、知見にもなるどころかバッドノウハウが貯まるだけになります。あと何より本人が正しく原因を理解せず、成長しないので。
sazi

2023/04/21 04:27

この質問は私にとっては有用でしたし、私にとって回答のスタンスを変えなければいけないような気付きはありませんでした。
退会済みユーザー

退会済みユーザー

2023/04/21 04:56

このDDLもクエリも統計データもない状態の質問の何が有用だったのか知りませんし、あなたの回答スタンスにも興味はありません。バッドノウハウを溜め込ませないようにしてくださいね。
sazi

2023/04/21 05:43 編集

> あなたの回答スタンスにも興味はありません。 興味ないのに意見するのですか? コメントではありますが、あなたの > nested loopは遅いので は誤っています。 私はバッドノウハウになりそうなら指摘するように心掛けています。 この件については回答に該当する箇所がありますので、気になるようでしたら熟読されて下さい。
sazi

2023/04/21 05:53

私も最初はdameoさんと同じように詳細な情報が無いと解決しないだろうと思っていました。 ですが、よく読んでいくと、ある程度の推測が出来たので回答しました。 実際に推測で回答すると、質問者からのコメントで情報精度が上がっていく事はままある事です。
退会済みユーザー

退会済みユーザー

2023/04/21 06:15

Nested Loopは一般に遅いですよ。熟読も何もあなたの回答にも書いてあります。当然インデックスが使われている場合は飛ばせるのでさほど遅くないわけですが、それは条件次第だというだけです。私の文脈での対比は、Hash Joinなので、これをわざわざする理由を考えればすぐ分かると思いますが。。。 あなたが何を伝えたいのかは分かりませんが、バッドノウハウというのは、「事実関係が不明な状態」でも推測だけで判断を進めてしまうことを指して言っています。精度をあげるかどうかは本人次第であり、我々が言えるのは足りない部分の指摘だけです。間違ったパスで進めば、原因に到達しないまま誤った解決でゴールしてしまうため、そのケースで質問者さんに溜まるものがバッドノウハウだと言っているわけです。
sazi

2023/04/21 06:49

質問者さんに溜まるバッドノウハウの事を言っていたんですね。 ここのコメントが私宛かと思って反応していました。 それであれば、これ以上コメントするのは不毛ですね。
guest

回答2

0

ベストアンサー

どういう理由でHash JoinとNested Loopが別れるか教えて下さい。

リファレンスによれば、それぞれの選択肢で計画し一番コストの低いものを選択するとあります。

51.5. プランナ/オプティマイザ

以下抜粋

問い合わせが2つ以上のリレーションの結合を必要とすると、リレーションを結合する計画は、単一のリレーションをスキャンするために全ての実行可能な計画が探し出された後に検討されます。3つの実行可能な結合戦略を示します。

ネステッドループ結合: 左側のリレーションの中で見つけられた行ごとに右側のリレーションが1回スキャンされます。 この戦略は実装が簡単ですが、時間がかかる場合があります (とは言っても右側のリレーションがインデックススキャンによってスキャン可能であればよい戦略になります。 右側のインデックススキャンのキーとして左側のリレーションの現在の行の値を使用することができます。)

マージ結合: 結合を開始する前に、それぞれのリレーションを結合属性でソートします。 そして、2つのリレーションを並行してスキャンし、一致する行を結合行の形にまとめます。 それぞれのリレーションがたった1回しかスキャンされなくて済むのでこの結合は魅力的です。 要求されるソートは、明示的なソート段階、または、結合キー上のインデックスを使用して適切な順序でリレーションをスキャンすることにより行われます。

ハッシュ結合: 右側のリレーションがハッシュキーとして結合属性を用いて初めにスキャンされ、ハッシュテーブルに読み込まれます。 次に左側のリレーションがスキャンされ、見つかったそれぞれの行に相応しい値が、右側のリレーションの行を探し出すためのハッシュキーとして使われます。

※コメントに対して追記

抽出文の変更かINDEXしか対策はなさそうだと判断いたしました。

拡張モジュールでヒント文での制御が可能です。

pg_hint_plan (PostgreSQL 実行計画制御ツール)

以下抜粋

<利用可能なヒントのカテゴリ>

スキャン方式テーブルのスキャン方式を指定できます
結合順序複数あるテーブルをどの順で結合するか指定できます
結合方式テーブル結合の方式を指定できます
見積件数補正テーブル結合結果の件数見積もりを補正する指定ができます
パラレル実行パラレル実行を強制あるいは禁止する指定ができます
設定パラメータSETコマンドで指定できるパラメータ設定をヒントから与えます

投稿2023/04/20 00:18

編集2023/04/20 06:10
sazi

総合スコア25430

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

june_223

2023/04/20 01:45

ありがとうございます。 リンク先を拝読したところ、結合方法は強制的に指示することはできないということなんですね。 EXPLAIN ANALYZEで確認すると必ずハッシュ結合の時に遅くなっているので対策したかったのですが、抽出文の変更かINDEXしか対策はなさそうだと判断いたしました。
june_223

2023/04/21 13:25

ご回答ありがとうございます。どのようにこの問題にアプローチするか迷っていたので大変参考になりました。 LATERALは知らなかったので勉強になりました。プログラムに限らず何事にも言えますが「機能の100%を把握している」という状態はなかなかないので、この質問をきっかけに、これまで知り得なかった情報を知ることがができ、今回知ったことを色々と調べるうちに更に知識が増えていくので、大変ありがたいご回答でした。 どうもありがとうございました。
退会済みユーザー

退会済みユーザー

2023/04/21 18:22

見事にバッドノウハウを獲得してしまいましたね。十分な事実提示ができない状態で質問をし、不相応な回答を得て、原因に辿り着くことなく他の人までミスリードする間違った解決で終わらせるという結果です。
guest

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

退会済みユーザー

退会済みユーザー

総合スコア0

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.30%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問