JOINした場合の実行計画について

• 报告人: ssn
• 属主: (无)
• 类型: Bugs
• 状态: 开启
• 组件: (无)
• 里程碑: (无)
• 优先: 5 - Medium
• 严重性: 5 - Medium
• 处理结果: 无
• 文件: 无

Details 回复

■環境
Redhat Enterprise Linux 4 U5
ludia 1.3.1 (mecab-0.96 ipadic-2.7.0 senna-1.0.9
postgresql 8.2.4

■設定
postgres.conf
・ludia.max_n_sort_result = 100000
・ludia.enable_seqscan = on
・ludia_sen_index_flags = 31
・ludia.max_n_index_cache = 16
・ludia.initial_n_segments = 2048

■DB
・table_a : aid int4,bid int4
・table_b : bid int4,data text
※table_a,table_b共にbidで一対一の関係
・INDEX : table_bのdataに対するfulltext or fulltextb

■現象

JOIN したテーブルに対しての @@ 検索が遅い問題について
下記ケースの違いは LIKE か @@ の違いのみです。

・LIKEケース(高速)
SELECT aid FROM table_a
LEFT JOIN table_b ON table_a.bid = table_b.bid
where table_b.data LIKE '%検索文字列%'

実行計画上table_b.dataを絞り込んだ結果で
JOINを実行している

・@@ケース(低速)
SELECT aid FROM table_a
LEFT JOIN table_b ON table_a.bid = table_b.bid
where table_b.data @@ '検索文字列'

実行計画上JOINした結果でtable_b.dataを絞り込んでいる

■想定
より少ないcostで実行して欲しいので
table_b.dataの絞り込みを先に実行して欲しいのですが、
なぜか先にJOINして欲しい

■疑問点
問題に直接関係しているのかはわかりませんが、
実行計画ではFULLTEXTINDEXのCOSTが負数になっている。
過去ログで発見したのですが
pgsenna2.cの1204行目に
indexTotalCost -= random_page_cost;
という式が入っていてこれが原因でoptimizerの解釈が
想定していたものとは違う動き(この式をコメントアウトする
と想定した動き)になってしまっていると思うのですが、
何の意図があってこの式を入れているのかを、もし良かった
ら教えて下さい。


よろしくお願いします。

任务单历史 (3/7 Histories)

2007-11-01 09:14 Updated by: co-saka

评论 回复

Logged In: YES
user_id=23292

テーブルサイズなど違うとは思いますが、
こちらでは、LIKEでも@@でも同じプランになりました・・・。
両者とも先にLIKE(@@)を実行してからJOINしています。
@@の場合はINDEXが使われているため、高速です。
ANALYZEなどは実行していますでしょうか？
低速という事はINDEXが使われていないという事でしょうか？
EXPLAINでの確認をお願いします。
また、参考までにテーブルの行数を教えていただけないでしょう
か？

indexTotalCost -= random_page_cost;
この行は強引にインデックスを使わせるための行です。
今回の例では、この行があったほうがssnさんの
意図した動きになりやすいはずなのですが・・・。

ちなみに、この行の理由は以下となります。
http://lists.sourceforge.jp/mailman/archives/ludia-
users/2007-September/000083.html

2007-11-01 09:59 Updated by: ssn

评论 回复

Logged In: YES
user_id=31583

説明不足ですいません。

双方共にINDEXは使われています。
違いが見られたのはnested loopの回数です。

例えば：データを
・table_a :
aid=(1～100)
bid=(1～100)
・table_b :
bid=(1～10)
data('検索文字列')
&
bid=(11～100)
data('ハズレ')
とした場合

想定する動きは(今回のLIKEでの結果)

table_b.data @@ '検索文字列'で絞った
結果をJOINするので、10行の結合表が作成される。

実際の動き(今回の@@での結果)
LEFT JOIN table_b ON table_a.bid = table_b.bid
でいったん100行の結合表を作成し、その結合表に対して
table_b.data @@ '検索文字列'が走る。

と言った感じです。
実際に導入検討しているデータベースの行数は20万を超えるので
この差が重要になってきています。

よろしくお願いします。

2007-11-01 19:48 Updated by: co-saka

评论 回复

Logged In: YES
user_id=23292

ssnさんの示したデータで実施してみました。
以下のように、nested loopはないように見えます･･･。

> 双方共にINDEXは使われています
LIKEは中間一致なのでINDEXが使われないと思いますが。

test=# explain select aid from table_a left join table_b
on table_a.bid = table_b.bid where table_b.data LIKE '%検索
文字列%';
QUERY PLAN
-----------------------------------------------------------
---------
Hash Join (cost=2.38..4.86 rows=10 width=4)
Hash Cond: (table_a.bid = table_b.bid)
-> Seq Scan on table_a (cost=0.00..2.01 rows=101
width=8)
-> Hash (cost=2.25..2.25 rows=10 width=4)
-> Seq Scan on table_b (cost=0.00..2.25 rows=10
width=4)
Filter: (data ~~ '%検索文字列%'::text)
(6 rows)

test=# explain select aid from table_a left join table_b
on table_a.bid = table_b.bid where table_b.data @@ '検索文
字列';
QUERY PLAN
-----------------------------------------------------------
----------------------
Hash Join (cost=0.02..2.42 rows=1 width=4)
Hash Cond: (table_a.bid = table_b.bid)
-> Seq Scan on table_a (cost=0.00..2.01 rows=101
width=8)
-> Hash (cost=0.01..0.01 rows=1 width=4)
-> Index Scan using idx_b on table_b
(cost=0.00..0.01 rows=1 width=4)
Index Cond: (data @@ '検索文字列'::text)
(6 rows)

同じソフトウェアのバージョン、同じデータで、同じクエリで
違いが出るのは不思議ですね。
analyzeは実行しましたよね？
おかしいですね・・・。
他に何か条件とかあるのでしょうか？
ssnさんのexplainはどのような結果になっているのでしょうか？

2007-11-02 12:57 Updated by: ssn

评论 回复

Logged In: YES
user_id=31583

大変申し訳ないのですが、
実環境で起きた現象を想定でシンプルなテストケースを作り質問し
ていたので
今回のテストケースにて実行した場合と少し現象に違いがありまし
た。

もう一度再現環境を構築し直したので
環境を下記に変更させて下さい。

>LIKEは中間一致なのでINDEXが使われないと思いますが。
そうですね。。。JOIN時に主キーINDEXを使用するケースと勘違い
していました、申し訳無いです。

■DB
・table_a : aid int4,bid int4 (aidに主キー)
・table_b : bid int4,data text (bidに主キー)
※table_a,table_b共にbidで一対一の関係
・INDEX : table_bのdataに対するfulltext or fulltextb
■データ
・table_a :
aid=(1～10000)
bid=(1～10000)
・table_b :
bid=(1～10)
data('検索文字列')
&
bid=(11～10000)
data('ハズレ')

■結果
1. LIKE ケース

○SQL
EXPLAIN ANALYZE SELECT aid FROM table_a
LEFT JOIN table_b ON table_a.bid = table_b.bid
where table_b.data LIKE '%検索文字列%'

○実行計画
"Hash Join (cost=225.29..348.54 rows=450 width=4) (actual
time=42.101..775.748 rows=10 loops=1)"
" Hash Cond: (table_a.bid = table_b.bid)"
" -> Seq Scan on table_a (cost=0.00..100.00 rows=5000
width=8) (actual time=0.082..248.677 rows=10000 loops=1)"
" -> Hash (cost=217.75..217.75 rows=603 width=4) (actual
time=41.411..41.411 rows=10 loops=1)"
" -> Seq Scan on table_b (cost=0.00..217.75
rows=603 width=4) (actual time=0.317..41.273 rows=10
loops=1)"
" Filter: (data ~~ '%検索文字列%'::text)"
"Total runtime: 776.109 ms"


2.@@ ケース

○SQL
EXPLAIN ANALYZE SELECT aid FROM table_a
LEFT JOIN table_b ON table_a.bid = table_b.bid
where table_b.data @@ '検索文字列'

○実行計画
"Nested Loop (cost=0.00..-19400.00 rows=5 width=4) (actual
time=15.654..42822.770 rows=10 loops=1)"
" Join Filter: (table_a.bid = table_b.bid)"
" -> Seq Scan on table_a (cost=0.00..100.00 rows=5000
width=8) (actual time=0.031..241.712 rows=10000 loops=1)"
" -> Index Scan using idx_tableb on table_b (cost=0.00..-
3.99 rows=7 width=4) (actual time=0.055..0.261 rows=10
loops=10000)"
" Index Cond: (data @@ '検索文字列'::text)"
"Total runtime: 42859.039 ms"


■相違点
LIKE検索では"rows=10 loops=1"ですが、
@@検索では"roos=10 loops=10000"
になってしまい低速で実行します。

2007-11-05 14:08 Updated by: co-saka

评论 回复

Logged In: YES
user_id=23292

10000行の環境で再度実施してみましたが、
@@でNested Loopが選択されませんでした･･･。
以下の２点を試してみて頂ければと思います。

１、(EXPLAIN ANALYZEではなく)ANALYZEコマンドを実行する。
http://www.postgresql.jp/document/pg825doc/html/sql-
analyze.html
プランを見ると、統計情報が若干ずれているように見受けられま
す。

２、pgsenna2.cの特定行をコメントアウトし、Ludiaを再インスト
ールする。（インデックスの再構築などは必要ありません。）
/* *indexTotalCost -= random_page_cost; */

2007-11-06 10:51 Updated by: ssn

评论 回复

Logged In: YES
user_id=31583

> １、(EXPLAIN ANALYZEではなく)ANALYZEコマンドを実行する。
> http://www.postgresql.jp/document/pg825doc/html/sql-
> analyze.html
> プランを見ると、統計情報が若干ずれているように見受けられま
> す。

ANALYZEは実行しています。

> ２、pgsenna2.cの特定行をコメントアウトし、Ludiaを再インス
ト
> ールする。（インデックスの再構築などは必要ありません。）
> /* *indexTotalCost -= random_page_cost; */

この構成変更後でのコンパイル＆インストールで
予想どうりの挙動になりました。

前回のメッセージ(2007-11-02 12:57)ので主キーなどの
設定にも変更があるのですが、そちらも合わせてもらえたでしょう
か？

2007-11-06 11:11 Updated by: co-saka

评论 回复

Logged In: YES
user_id=23292

> 設定にも変更があるのですが、そちらも合わせてもらえたでし
ょうか？
こちらを見逃していました。大変申し訳ありませんでした。

ssnさんと同様にnested loopが確認されました。複雑なクエリで
はないのに、nested loopになるのは良くないですね･･･。
Ludia1.4では何らかの対処を行う事とします。

ご報告ありがとうございました。非常に助かります。
今後もよろしくお願いします。