[pgsql-jp: 40716] 1:Nの結合に対して更に結合を行う場合の検索性能
武田 憲太郎
takeda @ youmind.jp
2011年 3月 21日 (月) 20:54:28 JST
武田と申します。結合のチューニングについて質問させて下さい。
例えば以下のようなSQLがあったとします。
select * from t1
natural join t2
natural join t3
where
t1pkey = [pkey]
以下のような条件を想定します。
・t1に対するwhere t1pkey = [pkey]の結果は常に1件
・t1→t2の対応関係は1:N。
・t2→t3の対応関係は1:1。
・t1→t3と直接結合するのは不可。必ずt2を経由する必要がある。
・【※】Nの値は不定だが極めて少ないことは予め分かっている。
例えば「ID○○番のマイミクのニックネームと画像URLが欲しい」
みたいな処理です。
(t1…ユーザーマスタ / t2…フレンドのリレーション / t3…プロフィール)
Nの値が極めて少ない、つまりt1 join t2の行数が極めて少ないのだから
t2→t3もIndexScanとなって欲しい、というのがコーディングの意図です。
実際、データ件数が少ないうちはIndexScanとなります。
ところが、予想外と言うか予想通りというか、t1,t2の件数が増えると
あるタイミングで突然SeqScanに変わります。(突然ガクンと性能落ちます)
Nの値にアプリケーション側で制限をかけていても、変わるときは変わります。
対策として、
・effective_cache_sizeを増加させる
一旦はこれで解決します。ところが、更に件数が増えると
やはり再度SeqScanとなってしまうため、イタチごっこな気がします。
・サブクエリ
冒頭のSQLを例えば以下のように書き換えます。
select
*,
(select t3c1 from t3 where pkey = t2.pkey) as t3c1,
(select t3c2 from t3 where pkey = t2.pkey) as t3c2,
(select t3c3 from t3 where pkey = t2.pkey) as t3c3,
…
(select t3cn from t3 where pkey = t2.pkey) as t3cn
from t1
natural join t2
where
t1pkey = [pkey]
これであれば件数に関係なく全てIndexScanとなります、が、
可読性が落ちる上、t1 join t2の結果一行一行に対して
カラム数の文だけサブクエリが走るコストも気になります。
もし「アナライザがそう判断している、従ってそういうもの」であれば
諦めるまでなのですが、設定値のチューニングなどで回避できるのであれば
その方が望ましいのではないかと思いました。
ちなみに、そもそも発生条件が不明なので参考になるか解りませんが、
8.4.4/9.0.1/9.0.2にて上記現象が発生することを確認しています。
回避方法、又は発生条件をご存じの方いらっしゃいませんでしょうか。
pgsql-jp メーリングリストの案内